"""
sales_sheet_data 테이블 접근.
1s(개인 시트) / 2s(팀 시트) 업로드 후 파싱된 레코드 저장·조회.
"""
from typing import List, Dict, Any, Optional
import mysql.connector
from app.core import logger
from app.core.config import get_db_config

A_DB_CONFIG = get_db_config("Sales_A_DB")


def insert_sales_sheet_records(records: List[Dict[str, Any]]) -> int:
    """
    sales_sheet_data 에 레코드 일괄 INSERT.
    uk_record (session_id, sheet_type, period_type, record_type, category) 기준 중복 시 value 갱신.
    """
    if not records:
        return 0
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SET time_zone = '+09:00'")  # JST (MySQL에 타임존 테이블 없을 때 오프셋 사용)
        sql = """
            INSERT INTO sales_sheet_data (session_id, sheet_type, period_type, record_type, category, value)
            VALUES (%s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE value = VALUES(value), created_at = CURRENT_TIMESTAMP
        """
        count = 0
        for r in records:
            session_id = r.get("session_id")
            sheet_type = r.get("sheet_type")
            period_type = r.get("period_type")
            record_type = r.get("record_type")
            category = r.get("category")
            value = r.get("value")
            if session_id is None or sheet_type is None or period_type is None or record_type is None or category is None:
                continue
            if value is not None:
                try:
                    value = float(value) if not isinstance(value, (int, float)) else value
                except (TypeError, ValueError):
                    value = None
            cursor.execute(sql, (session_id, sheet_type, period_type, record_type, category, value))
            count += 1
        conn.commit()
        cursor.close()
        conn.close()
        return count
    except Exception as e:
        logger.error(f"Sales sheet insert error: {e}")
        raise


def get_sales_sheet_by_session(
    session_id: str,
    sheet_type: Optional[str] = None,
) -> List[Dict[str, Any]]:
    """
    session_id 기준으로 sales_sheet_data 조회.
    sheet_type 이 있으면 '1s' / '2s' 로 필터.
    """
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SET time_zone = '+09:00'")
        if sheet_type:
            sql = """
                SELECT id, session_id, sheet_type, period_type, record_type, category, value, created_at
                FROM sales_sheet_data
                WHERE session_id = %s AND sheet_type = %s
                ORDER BY sheet_type, period_type, category, record_type
            """
            cursor.execute(sql, (session_id, sheet_type))
        else:
            sql = """
                SELECT id, session_id, sheet_type, period_type, record_type, category, value, created_at
                FROM sales_sheet_data
                WHERE session_id = %s
                ORDER BY sheet_type, period_type, category, record_type
            """
            cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
        return rows
    except Exception as e:
        logger.error(f"Sales sheet get error: {e}")
        return []