#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""数据库内容查看器模块"""

import os
import tkinter as tk
from tkinter import ttk, messagebox
import sqlite3
from datetime import datetime
from typing import Optional, Dict, List, Any

from app.config.settings import ConfigManager
from app.ui.ui_widgets import center_window
from app.ui.theme import THEMES, get_theme_mode

class DatabaseViewer:
    """通用数据库查看器，支持多表切换"""
    
    def __init__(self, root, config: Optional[ConfigManager] = None):
        self.root = root
        self.config = config or ConfigManager()
        self.db_path = self.config.get_path('Paths', 'product_db', fallback='data/product_cache.db')
        
        # 确保路径是绝对路径
        if not os.path.isabs(self.db_path):
            app_root = getattr(self.config, 'app_root', os.getcwd())
            self.db_path = os.path.join(app_root, self.db_path)
            
        self.dlg = tk.Toplevel(root)
        self.dlg.title("数据库内容查看器")
        self.dlg.geometry("1000x600")
        center_window(self.dlg)
        
        theme = THEMES[get_theme_mode()]
        self.dlg.configure(bg=theme["bg"])
        
        # 使用 Notebook 实现多表切换
        self.notebook = ttk.Notebook(self.dlg)
        self.notebook.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
        
        # 定义要查看的表及其列信息
        self.table_configs = {
            "order_metadata": {
                "title": "订单记录",
                "columns": {
                    "bill_date": ("单据日期", 100),
                    "supplier": ("供应商", 200),
                    "total_amount": ("总金额", 80),
                    "updated_at": ("处理时间", 150),
                    "source_image": ("原始图片", 250),
                    "file_hash": ("Hash", 120)
                },
                "query": "SELECT * FROM order_metadata ORDER BY updated_at DESC"
            },
            "missing_barcodes": {
                "title": "缺失条码",
                "columns": {
                    "barcode": ("条码", 150),
                    "name": ("商品名称", 200),
                    "count": ("出现次数", 80),
                    "last_seen": ("最后发现", 150),
                    "source_file": ("来源文件", 250)
                },
                "query": "SELECT * FROM missing_barcodes ORDER BY last_seen DESC"
            },
            "products": {
                "title": "商品记忆库",
                "columns": {
                    "barcode": ("条码", 120),
                    "name": ("名称", 180),
                    "specification": ("规格", 80),
                    "unit": ("单位", 50),
                    "price": ("单价", 70),
                    "confidence": ("置信度", 60),
                    "usage_count": ("使用次数", 70),
                    "last_seen": ("最后使用", 140)
                },
                "query": "SELECT * FROM products ORDER BY last_seen DESC"
            }
        }
        
        self.trees = {}
        self._init_tabs()
        
        # 底部按钮
        btn_frame = ttk.Frame(self.dlg)
        btn_frame.pack(fill=tk.X, padx=10, pady=(0, 10))
        
        ttk.Button(btn_frame, text="刷新当前表", command=self.refresh_current_tab).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="清空记录 (慎用)", command=self.clear_current_table).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="关闭", command=self.dlg.destroy).pack(side=tk.RIGHT, padx=5)

    def _init_tabs(self):
        """初始化各个标签页"""
        for table_id, config in self.table_configs.items():
            frame = ttk.Frame(self.notebook)
            self.notebook.add(frame, text=config["title"])
            
            # 搜索栏
            search_frame = ttk.Frame(frame)
            search_frame.pack(fill=tk.X, padx=5, pady=5)
            
            ttk.Label(search_frame, text="搜索:").pack(side=tk.LEFT)
            search_var = tk.StringVar()
            search_entry = ttk.Entry(search_frame, textvariable=search_var, width=30)
            search_entry.pack(side=tk.LEFT, padx=5)
            
            # Treeview
            cols = list(config["columns"].keys())
            tree = ttk.Treeview(frame, columns=cols, show="headings")
            
            for col, (text, width) in config["columns"].items():
                tree.heading(col, text=text)
                tree.column(col, width=width, anchor="center")
            
            scrollbar = ttk.Scrollbar(frame, orient=tk.VERTICAL, command=tree.yview)
            tree.configure(yscrollcommand=scrollbar.set)
            
            tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
            scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
            
            # 双击复制单元格内容
            tree.bind("<Double-1>", lambda e, tid=table_id: self.copy_cell_value(e, tid))
            
            self.trees[table_id] = {
                "tree": tree,
                "search_var": search_var,
                "config": config
            }
            
            # 绑定搜索事件
            search_var.trace_add("write", lambda *args, tid=table_id: self.load_table_data(tid))
            
            # 初始加载数据
            self.load_table_data(table_id)

    def load_table_data(self, table_id):
        """加载指定表的数据"""
        if not os.path.exists(self.db_path):
            return
            
        info = self.trees[table_id]
        tree = info["tree"]
        config = info["config"]
        search_text = info["search_var"].get().lower()
        
        # 清空现有数据
        for item in tree.get_children():
            tree.delete(item)
            
        try:
            conn = sqlite3.connect(self.db_path)
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # 检查表是否存在
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_id,))
            if not cursor.fetchone():
                conn.close()
                return
                
            cursor.execute(config["query"])
            rows = cursor.fetchall()
            
            cols = list(config["columns"].keys())
            for row in rows:
                vals = [row[c] if c in row.keys() else "" for c in cols]
                
                # 简单搜索过滤
                if search_text:
                    match = False
                    for val in vals:
                        if search_text in str(val).lower():
                            match = True
                            break
                    if not match:
                        continue
                
                # 格式化金额
                if "total_amount" in row.keys():
                    idx = cols.index("total_amount")
                    try:
                        vals[idx] = f"{float(vals[idx]):.2f}"
                    except:
                        pass
                
                tree.insert("", tk.END, values=vals)
                
            conn.close()
            
            # 自动调整列宽
            self.auto_resize_columns(table_id)
            
        except Exception as e:
            print(f"加载表 {table_id} 数据失败: {e}")

    def auto_resize_columns(self, table_id):
        """根据内容自动调整列宽"""
        info = self.trees[table_id]
        tree = info["tree"]
        config = info["config"]
        
        for col in list(config["columns"].keys()):
            # 获取表头宽度
            header_text = config["columns"][col][0]
            max_w = len(header_text) * 12 + 20
            
            # 获取内容宽度（检查前 20 行）
            for item in tree.get_children()[:20]:
                val = str(tree.set(item, col))
                w = len(val) * 8 + 20
                if w > max_w:
                    max_w = w
            
            # 限制最大宽度
            max_w = min(max_w, 400)
            tree.column(col, width=max_w)

    def copy_cell_value(self, event, table_id):
        """双击复制单元格内容到剪贴板"""
        tree = self.trees[table_id]["tree"]
        region = tree.identify_region(event.x, event.y)
        if region == "cell":
            column = tree.identify_column(event.x)
            item = tree.identify_row(event.y)
            value = tree.set(item, column)
            
            self.root.clipboard_clear()
            self.root.clipboard_append(value)
            messagebox.showinfo("成功", f"内容已复制到剪贴板：\n{value}")

    def refresh_current_tab(self):
        """刷新当前选中的标签页"""
        current_tab_idx = self.notebook.index(self.notebook.select())
        table_ids = list(self.table_configs.keys())
        if current_tab_idx < len(table_ids):
            self.load_table_data(table_ids[current_tab_idx])

    def clear_current_table(self):
        """清空当前表的记录"""
        current_tab_idx = self.notebook.index(self.notebook.select())
        table_ids = list(self.table_configs.keys())
        if current_tab_idx >= len(table_ids):
            return
            
        table_id = table_ids[current_tab_idx]
        title = self.table_configs[table_id]["title"]
        
        if not messagebox.askyesno("警告", f"确定要永久清空表 '{title}' 的所有记录吗？此操作不可恢复！"):
            return
            
        try:
            conn = sqlite3.connect(self.db_path)
            conn.execute(f"DELETE FROM {table_id}")
            conn.commit()
            conn.close()
            self.load_table_data(table_id)
            messagebox.showinfo("成功", f"表 '{title}' 已清空")
        except Exception as e:
            messagebox.showerror("错误", f"清空表失败: {e}")

def show_db_viewer(root, config: Optional[ConfigManager] = None):
    """显示数据库查看器"""
    DatabaseViewer(root, config=config)
