"""单据元信息 SQLite 存储。

独立于 ProductDatabase，与 product_cache.db 共用文件。
提供：save / get / list_all / count。
"""
from __future__ import annotations

import os
import sqlite3
import logging
from datetime import datetime
from typing import Dict, List, Optional

logger = logging.getLogger(__name__)


class OrderMetadataDB:
    """单据元信息库（共用 product_cache.db 文件，独立表 order_metadata）。"""

    SCHEMA = """
    CREATE TABLE IF NOT EXISTS order_metadata (
        file_hash         TEXT PRIMARY KEY,
        supplier          TEXT DEFAULT '',
        bill_date          TEXT DEFAULT '',
        total_amount       REAL DEFAULT 0.0,
        raw_supplier_text  TEXT DEFAULT '',
        source_image       TEXT DEFAULT '',
        created_at         TEXT DEFAULT '',
        updated_at         TEXT DEFAULT ''
    );
    """

    def __init__(self, db_path: str):
        self.db_path = db_path
        self._ensure_table()

    def _connect(self) -> sqlite3.Connection:
        return sqlite3.connect(self.db_path)

    def _ensure_table(self):
        """幂等创建表。"""
        try:
            os.makedirs(os.path.dirname(self.db_path) or '.', exist_ok=True)
        except OSError:
            pass
        conn = self._connect()
        try:
            conn.execute(self.SCHEMA)
            conn.commit()
        finally:
            conn.close()

    def save(self, file_hash: str, supplier: str = '', bill_date: str = '',
             total_amount: float = 0.0, raw_supplier_text: str = '',
             source_image: str = '') -> bool:
        """保存或更新单据元信息（PK = file_hash）。"""
        if not file_hash:
            return False
        now = datetime.now().isoformat(timespec='seconds')
        conn = self._connect()
        try:
            # 使用 UPSERT 语义
            conn.execute("""
                INSERT INTO order_metadata
                    (file_hash, supplier, bill_date, total_amount,
                     raw_supplier_text, source_image, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(file_hash) DO UPDATE SET
                    supplier = excluded.supplier,
                    bill_date = excluded.bill_date,
                    total_amount = excluded.total_amount,
                    raw_supplier_text = excluded.raw_supplier_text,
                    source_image = excluded.source_image,
                    updated_at = excluded.updated_at
            """, (file_hash, supplier, bill_date, total_amount,
                  raw_supplier_text, source_image, now, now))
            conn.commit()
            return True
        except Exception as e:
            logger.error(f"save_order_metadata 失败: {e}")
            return False
        finally:
            conn.close()

    def get(self, file_hash: str) -> Optional[Dict]:
        conn = self._connect()
        try:
            conn.row_factory = sqlite3.Row
            row = conn.execute(
                "SELECT * FROM order_metadata WHERE file_hash=?",
                (file_hash,)).fetchone()
            return dict(row) if row else None
        finally:
            conn.close()

    def list_all(self, limit: int = 1000) -> List[Dict]:
        """列出所有元信息，按 updated_at 倒序。"""
        conn = self._connect()
        try:
            conn.row_factory = sqlite3.Row
            rows = conn.execute(
                "SELECT * FROM order_metadata ORDER BY updated_at DESC LIMIT ?",
                (limit,)).fetchall()
            return [dict(r) for r in rows]
        finally:
            conn.close()

    def count(self) -> int:
        conn = self._connect()
        try:
            return conn.execute("SELECT COUNT(*) FROM order_metadata").fetchone()[0]
        finally:
            conn.close()

    def delete(self, file_hash: str) -> bool:
        conn = self._connect()
        try:
            conn.execute("DELETE FROM order_metadata WHERE file_hash=?", (file_hash,))
            conn.commit()
            return True
        finally:
            conn.close()