"""RPA发票数据服务：处理RPA提取的发票数据存储和查询。"""

from __future__ import annotations

import json
import sqlite3
from typing import Any, Dict, List, Optional

from db.connection import get_conn
from db.display_metadata import upsert_table_metadata, seed_column_metadata_batch
from db.rpa_invoice_schema import RPA_INVOICES_DDL, RPA_INVOICES_INDEX, RPA_INVOICES_COLUMNS
from util.constants import LOG_LOGGER_NAME
from util.logging_config import get_skill_logger


def init_rpa_invoice_table() -> None:
    """初始化RPA发票数据表。"""
    conn = get_conn()
    try:
        cursor = conn.cursor()
        
        # 创建表
        cursor.execute(RPA_INVOICES_DDL)
        cursor.execute(RPA_INVOICES_INDEX)
        
        # 添加表元数据
        upsert_table_metadata(
            cursor,
            table_name="rpa_invoices",
            display_name="RPA发票提取",
            description="通过RPA从网站提取的发票数据",
            grid_help="查看RPA提取的发票数据，可按批次ID筛选",
            visible=1,
            readonly=1,
            default_sort_json='[{"column":"id","direction":"desc"}]',
            default_visible_columns_json='["id","batch_id","extraction_time","username","status","created_at"]',
        )
        
        # 添加列元数据
        seed_column_metadata_batch(
            cursor,
            table_name="rpa_invoices",
            columns=RPA_INVOICES_COLUMNS,
            valid_column_names=[str(c["column_name"]) for c in RPA_INVOICES_COLUMNS],
        )
        
        conn.commit()
        
    except Exception as e:
        logger = get_skill_logger()
        logger.error("init_rpa_invoice_table_failed error=%s", str(e))
        raise
    finally:
        conn.close()


def save_rpa_invoice_data(
    batch_id: str,
    source_url: str,
    invoice_data: Dict[str, Any],
    username: str = "",
    status: str = "extracted",
    error_message: str = "",
) -> int:
    """保存RPA提取的发票数据。
    
    Args:
        batch_id: 批次ID
        source_url: 来源网址
        invoice_data: 发票数据
        username: 操作用户
        status: 状态
        error_message: 错误信息
        
    Returns:
        保存的记录ID
    """
    conn = get_conn()
    try:
        cursor = conn.cursor()
        
        # 将发票数据转换为JSON字符串
        invoice_data_json = json.dumps(invoice_data, ensure_ascii=False)
        
        # 插入数据
        cursor.execute("""
            INSERT INTO rpa_invoices (
                batch_id, source_url, username, invoice_data_json, status, error_message
            ) VALUES (?, ?, ?, ?, ?, ?)
        """, (batch_id, source_url, username, invoice_data_json, status, error_message))
        
        record_id = cursor.lastrowid
        conn.commit()
        
        logger = get_skill_logger()
        logger.info(
            "rpa_invoice_saved record_id=%s batch_id=%s invoice_count=%s",
            record_id,
            batch_id,
            invoice_data.get("total_count", 0),
        )
        
        return record_id
        
    except Exception as e:
        logger = get_skill_logger()
        logger.error("save_rpa_invoice_data_failed error=%s", str(e))
        raise
    finally:
        conn.close()


def get_rpa_invoice_data(record_id: int) -> Optional[Dict[str, Any]]:
    """获取RPA提取的发票数据。"""
    conn = get_conn()
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM rpa_invoices WHERE id = ?", (record_id,))
        row = cursor.fetchone()
        
        if not row:
            return None
        
        # 转换为字典
        columns = [desc[0] for desc in cursor.description]
        data = dict(zip(columns, row))
        
        # 解析JSON数据
        if "invoice_data_json" in data and data["invoice_data_json"]:
            try:
                data["invoice_data"] = json.loads(data["invoice_data_json"])
            except json.JSONDecodeError:
                data["invoice_data"] = {}
        
        return data
        
    except Exception as e:
        logger = get_skill_logger()
        logger.error("get_rpa_invoice_data_failed error=%s", str(e))
        return None
    finally:
        conn.close()


def list_rpa_invoices(
    limit: int = 20,
    batch_id: Optional[str] = None,
    username: Optional[str] = None,
    status: Optional[str] = None,
) -> List[Dict[str, Any]]:
    """查询RPA提取的发票数据列表。"""
    conn = get_conn()
    try:
        cursor = conn.cursor()
        
        # 构建查询条件
        conditions = []
        params = []
        
        if batch_id:
            conditions.append("batch_id = ?")
            params.append(batch_id)
        
        if username:
            conditions.append("username = ?")
            params.append(username)
        
        if status:
            conditions.append("status = ?")
            params.append(status)
        
        where_clause = " AND ".join(conditions) if conditions else "1=1"
        
        # 执行查询
        query = f"""
            SELECT id, batch_id, source_url, extraction_time, username, status, error_message, created_at
            FROM rpa_invoices 
            WHERE {where_clause}
            ORDER BY id DESC 
            LIMIT ?
        """
        params.append(limit)
        
        cursor.execute(query, params)
        rows = cursor.fetchall()
        
        # 转换为字典列表
        columns = [desc[0] for desc in cursor.description]
        result = [dict(zip(columns, row)) for row in rows]
        
        return result
        
    except Exception as e:
        logger = get_skill_logger()
        logger.error("list_rpa_invoices_failed error=%s", str(e))
        return []
    finally:
        conn.close()


def get_rpa_invoice_stats() -> Dict[str, Any]:
    """获取RPA提取统计信息。"""
    conn = get_conn()
    try:
        cursor = conn.cursor()
        
        # 总记录数
        cursor.execute("SELECT COUNT(*) FROM rpa_invoices")
        total_count = cursor.fetchone()[0]
        
        # 按状态统计
        cursor.execute("""
            SELECT status, COUNT(*) as count 
            FROM rpa_invoices 
            GROUP BY status
        """)
        status_stats = {row[0]: row[1] for row in cursor.fetchall()}
        
        # 最近提取记录
        cursor.execute("""
            SELECT id, batch_id, extraction_time, username, status
            FROM rpa_invoices 
            ORDER BY id DESC 
            LIMIT 5
        """)
        recent_extractions = [
            {
                "id": row[0],
                "batch_id": row[1],
                "extraction_time": row[2],
                "username": row[3],
                "status": row[4],
            }
            for row in cursor.fetchall()
        ]
        
        return {
            "total_count": total_count,
            "status_stats": status_stats,
            "recent_extractions": recent_extractions,
        }
        
    except Exception as e:
        logger = get_skill_logger()
        logger.error("get_rpa_invoice_stats_failed error=%s", str(e))
        return {"error": str(e)}
    finally:
        conn.close()