import tkinter as tk
from tkinter import ttk, messagebox
import sqlite3

# --- データベース関連の関数 ---

def get_db_connection():
    """データベース接続を取得します。"""
    conn = sqlite3.connect('customer_management.db')
    conn.row_factory = sqlite3.Row
    return conn

def create_table():
    """顧客マスタテーブルを作成します。"""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            kana TEXT,
            address TEXT,
            phone TEXT,
            email TEXT
        )
    ''')
    conn.commit()
    conn.close()

def get_all_customers():
    """すべての顧客情報を取得します。"""
    conn = get_db_connection()
    customers = conn.execute('SELECT * FROM customers ORDER BY id').fetchall()
    conn.close()
    return customers

def search_customers(keyword):
    """キーワードで顧客情報を検索します。"""
    conn = get_db_connection()
    query = f'''
        SELECT * FROM customers WHERE
        name LIKE ? OR
        kana LIKE ? OR
        address LIKE ? OR
        phone LIKE ? OR
        email LIKE ?
        ORDER BY id
    '''
    like_keyword = f'%{keyword}%'
    customers = conn.execute(query, (like_keyword, like_keyword, like_keyword, like_keyword, like_keyword)).fetchall()
    conn.close()
    return customers

def get_customer_by_id(customer_id):
    """IDで顧客情報を取得します。"""
    conn = get_db_connection()
    customer = conn.execute('SELECT * FROM customers WHERE id = ?', (customer_id,)).fetchone()
    conn.close()
    return customer

# --- GUIアプリケーションのクラス ---

class CustomerInfoWindow(tk.Toplevel):
    """顧客情報の登録・更新・削除を行うウィンドウ"""
    def __init__(self, parent, customer_id=None):
        super().__init__(parent)
        self.parent = parent
        self.customer_id = customer_id
        
        # モード設定 (新規登録 or 更新・削除)
        self.mode = 'edit' if customer_id else 'new'

        self.withdraw() # 描画を一時的に停止
        self.title(f"顧客情報 {'更新・削除' if self.mode == 'edit' else '新規登録'}")
        self.resizable(False, False)
        self.protocol("WM_DELETE_WINDOW", self.close_window)

        self._create_widgets()
        
        if self.mode == 'edit':
            self._load_customer_data()

        self.update_idletasks() # ウィジェットのサイズを計算
        self.center_window()
        self.deiconify() # 再描画
        self.transient(parent) # 親ウィンドウの上に表示
        self.grab_set() # モーダルにする

    def _create_widgets(self):
        """ウィジェットを作成・配置します。"""
        main_frame = ttk.Frame(self, padding=10)
        main_frame.pack(expand=True, fill=tk.BOTH)

        # 入力フィールド
        fields = {
            "顧客名 (必須)": "name",
            "フリガナ": "kana",
            "電話番号": "phone",
            "メールアドレス": "email"
        }
        self.entries = {}

        for i, (text, name) in enumerate(fields.items()):
            label = ttk.Label(main_frame, text=text)
            label.grid(row=i, column=0, sticky=tk.W, padx=5, pady=5)
            entry = ttk.Entry(main_frame, width=40)
            entry.grid(row=i, column=1, sticky=tk.EW, padx=5, pady=5)
            self.entries[name] = entry

        # 住所 (Textウィジェット)
        addr_label = ttk.Label(main_frame, text="住所")
        addr_label.grid(row=len(fields), column=0, sticky=tk.NW, padx=5, pady=5)
        self.address_text = tk.Text(main_frame, width=40, height=5)
        self.address_text.grid(row=len(fields), column=1, sticky=tk.EW, padx=5, pady=5)
        self.entries["address"] = self.address_text

        # ボタン
        button_frame = ttk.Frame(main_frame)
        button_frame.grid(row=len(fields) + 1, column=0, columnspan=2, pady=10)

        action_button_text = "更新" if self.mode == 'edit' else "登録"
        self.action_button = ttk.Button(button_frame, text=action_button_text, command=self.save_customer)
        self.action_button.pack(side=tk.LEFT, padx=5)

        if self.mode == 'edit':
            self.delete_button = ttk.Button(button_frame, text="削除", command=self.delete_customer)
            self.delete_button.pack(side=tk.LEFT, padx=5)

        self.cancel_button = ttk.Button(button_frame, text="キャンセル", command=self.close_window)
        self.cancel_button.pack(side=tk.LEFT, padx=5)

    def _load_customer_data(self):
        """既存の顧客データを読み込んで表示します。"""
        customer = get_customer_by_id(self.customer_id)
        if customer:
            self.entries["name"].insert(0, customer["name"] or "")
            self.entries["kana"].insert(0, customer["kana"] or "")
            self.entries["phone"].insert(0, customer["phone"] or "")
            self.entries["email"].insert(0, customer["email"] or "")
            self.address_text.insert("1.0", customer["address"] or "")

    def save_customer(self):
        """顧客情報を保存（登録または更新）します。"""
        name = self.entries["name"].get().strip()
        if not name:
            messagebox.showerror("入力エラー", "顧客名は必須項目です。", parent=self)
            return

        data = {
            "name": name,
            "kana": self.entries["kana"].get().strip(),
            "address": self.address_text.get("1.0", tk.END).strip(),
            "phone": self.entries["phone"].get().strip(),
            "email": self.entries["email"].get().strip(),
        }

        conn = get_db_connection()
        try:
            if self.mode == 'new':
                conn.execute(
                    'INSERT INTO customers (name, kana, address, phone, email) VALUES (?, ?, ?, ?, ?)',
                    (data["name"], data["kana"], data["address"], data["phone"], data["email"])
                )
                messagebox.showinfo("成功", "顧客情報を登録しました。", parent=self)
            else: # edit mode
                conn.execute(
                    'UPDATE customers SET name=?, kana=?, address=?, phone=?, email=? WHERE id=?',
                    (data["name"], data["kana"], data["address"], data["phone"], data["email"], self.customer_id)
                )
                messagebox.showinfo("成功", "顧客情報を更新しました。", parent=self)
            conn.commit()
        except sqlite3.Error as e:
            messagebox.showerror("データベースエラー", f"エラーが発生しました: {e}", parent=self)
        finally:
            conn.close()

        self.parent.refresh_customer_list()
        self.destroy()

    def delete_customer(self):
        """顧客情報を削除します。"""
        if messagebox.askyesno("削除確認", "この顧客情報を本当に削除しますか？", parent=self):
            conn = get_db_connection()
            try:
                conn.execute('DELETE FROM customers WHERE id = ?', (self.customer_id,))
                conn.commit()
                messagebox.showinfo("成功", "顧客情報を削除しました。", parent=self)
            except sqlite3.Error as e:
                messagebox.showerror("データベースエラー", f"エラーが発生しました: {e}", parent=self)
            finally:
                conn.close()
            
            self.parent.refresh_customer_list()
            self.destroy()

    def close_window(self):
        self.destroy()

    def center_window(self):
        """ウィンドウを画面の中央に配置します。"""
        self.update_idletasks()
        width = self.winfo_width()
        height = self.winfo_height()
        screen_width = self.winfo_screenwidth()
        screen_height = self.winfo_screenheight()
        x = (screen_width // 2) - (width // 2)
        y = (screen_height // 2) - (height // 2)
        self.geometry(f'{width}x{height}+{x}+{y}')


class App(tk.Tk):
    """メインアプリケーションウィンドウ"""
    def __init__(self):
        super().__init__()
        self.withdraw() # 描画を一時的に停止
        self.title("顧客管理システム")
        self.resizable(False, False)

        self._create_widgets()
        self.refresh_customer_list()

        self.update_idletasks() # ウィジェットのサイズを計算
        self.center_window()
        self.deiconify() # 再描画

    def _create_widgets(self):
        """ウィジェットを作成・配置します。"""
        main_frame = ttk.Frame(self, padding=10)
        main_frame.pack(expand=True, fill=tk.BOTH)

        # 検索フレーム
        search_frame = ttk.Frame(main_frame)
        search_frame.pack(fill=tk.X, pady=5)
        
        search_label = ttk.Label(search_frame, text="検索:")
        search_label.pack(side=tk.LEFT, padx=(0, 5))
        
        self.search_var = tk.StringVar()
        self.search_entry = ttk.Entry(search_frame, textvariable=self.search_var)
        self.search_entry.pack(side=tk.LEFT, expand=True, fill=tk.X)
        self.search_entry.bind('<Return>', self.search) # Enterキーで検索
        
        search_button = ttk.Button(search_frame, text="検索実行", command=self.search)
        search_button.pack(side=tk.LEFT, padx=5)

        clear_button = ttk.Button(search_frame, text="クリア", command=self.clear_search)
        clear_button.pack(side=tk.LEFT)

        # 顧客一覧 (Treeview)
        tree_frame = ttk.Frame(main_frame)
        tree_frame.pack(expand=True, fill=tk.BOTH, pady=5)
        
        columns = ("id", "name", "kana", "phone", "email")
        self.tree = ttk.Treeview(tree_frame, columns=columns, show="headings")
        
        # ヘッダー設定
        self.tree.heading("id", text="顧客ID")
        self.tree.heading("name", text="顧客名")
        self.tree.heading("kana", text="フリガナ")
        self.tree.heading("phone", text="電話番号")
        self.tree.heading("email", text="メールアドレス")
        
        # カラム幅設定
        self.tree.column("id", width=60, anchor=tk.CENTER)
        self.tree.column("name", width=150)
        self.tree.column("kana", width=150)
        self.tree.column("phone", width=120)
        self.tree.column("email", width=200)

        # スクロールバー
        scrollbar = ttk.Scrollbar(tree_frame, orient=tk.VERTICAL, command=self.tree.yview)
        self.tree.configure(yscroll=scrollbar.set)
        
        self.tree.pack(side=tk.LEFT, expand=True, fill=tk.BOTH)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

        # ボタンフレーム
        button_frame = ttk.Frame(main_frame)
        button_frame.pack(fill=tk.X, pady=5)

        new_button = ttk.Button(button_frame, text="新規登録", command=self.open_new_window)
        new_button.pack(side=tk.LEFT, padx=5)

        edit_button = ttk.Button(button_frame, text="更新・削除", command=self.open_edit_window)
        edit_button.pack(side=tk.LEFT, padx=5)

    def refresh_customer_list(self, customers=None):
        """顧客リストを再読み込みしてTreeviewに表示します。"""
        # Treeviewをクリア
        for row in self.tree.get_children():
            self.tree.delete(row)
        
        # データを取得して挿入
        if customers is None:
            customers = get_all_customers()
        
        for customer in customers:
            self.tree.insert("", "end", values=(
                customer['id'],
                customer['name'],
                customer['kana'],
                customer['phone'],
                customer['email']
            ))

    def search(self, event=None):
        """検索を実行します。"""
        keyword = self.search_var.get()
        if keyword:
            customers = search_customers(keyword)
            self.refresh_customer_list(customers)
        else:
            self.refresh_customer_list()

    def clear_search(self):
        """検索をクリアして全件表示に戻します。"""
        self.search_var.set("")
        self.refresh_customer_list()

    def open_new_window(self):
        """新規登録ウィンドウを開きます。"""
        CustomerInfoWindow(self)

    def open_edit_window(self):
        """更新・削除ウィンドウを開きます。"""
        selected_item = self.tree.focus()
        if not selected_item:
            messagebox.showwarning("選択エラー", "一覧から顧客を選択してください。")
            return
        
        customer_id = self.tree.item(selected_item)['values'][0]
        CustomerInfoWindow(self, customer_id)

    def center_window(self):
        """ウィンドウを画面の中央に配置します。"""
        self.update_idletasks()
        width = self.winfo_width()
        height = self.winfo_height()
        screen_width = self.winfo_screenwidth()
        screen_height = self.winfo_screenheight()
        x = (screen_width // 2) - (width // 2)
        y = (screen_height // 2) - (height // 2)
        self.geometry(f'{width}x{height}+{x}+{y}')


if __name__ == "__main__":
    create_table()  # データベースとテーブルの初期化
    app = App()
    app.mainloop()