"""Database model helpers for generated-contract ledger records."""
import json
import uuid
from datetime import datetime

from services.db import query


_schema_ready = False
_TYPE_CODES = {'domestic': 'GN', 'overseas': 'HW', 'packaging': 'BC'}


def ensure_schema():
    global _schema_ready
    if _schema_ready:
        return
    query("""
        CREATE TABLE IF NOT EXISTS contract_records (
            id BIGINT NOT NULL AUTO_INCREMENT,
            contract_no VARCHAR(80) NOT NULL,
            supplier_id INT NULL,
            supplier_name VARCHAR(255) NOT NULL DEFAULT '',
            buyer_name VARCHAR(255) NOT NULL DEFAULT '',
            order_type VARCHAR(32) NOT NULL DEFAULT '',
            template_id INT NULL,
            template_name VARCHAR(100) NOT NULL DEFAULT '',
            order_count INT NOT NULL DEFAULT 0,
            product_count INT NOT NULL DEFAULT 0,
            total_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
            status VARCHAR(32) NOT NULL DEFAULT '已生成',
            signer VARCHAR(100) NOT NULL DEFAULT '',
            signed_at DATE NULL,
            effective_date DATE NULL,
            expiry_date DATE NULL,
            payment_terms TEXT NULL,
            delivery_terms TEXT NULL,
            remark TEXT NULL,
            order_ids_json LONGTEXT NULL,
            orders_json LONGTEXT NULL,
            current_file_path VARCHAR(500) NOT NULL DEFAULT '',
            current_file_name VARCHAR(255) NOT NULL DEFAULT '',
            created_by INT NULL,
            created_by_name VARCHAR(100) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_by INT NULL,
            updated_by_name VARCHAR(100) NOT NULL DEFAULT '',
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            is_deleted TINYINT NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY uk_contract_no (contract_no),
            KEY idx_contract_supplier (supplier_name),
            KEY idx_contract_status (status, created_at),
            KEY idx_contract_type (order_type, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    query("""
        CREATE TABLE IF NOT EXISTS contract_files (
            id BIGINT NOT NULL AUTO_INCREMENT,
            contract_id BIGINT NOT NULL,
            version INT NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            file_name VARCHAR(255) NOT NULL DEFAULT '',
            generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            generated_by INT NULL,
            generated_by_name VARCHAR(100) NOT NULL DEFAULT '',
            is_current TINYINT NOT NULL DEFAULT 0,
            remark VARCHAR(500) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            UNIQUE KEY uk_contract_file_version (contract_id, version),
            KEY idx_contract_file_current (contract_id, is_current)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    query("""
        CREATE TABLE IF NOT EXISTS contract_events (
            id BIGINT NOT NULL AUTO_INCREMENT,
            contract_id BIGINT NOT NULL,
            action VARCHAR(80) NOT NULL,
            detail TEXT NULL,
            user_id INT NULL,
            user_name VARCHAR(100) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_contract_event (contract_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    _schema_ready = True


def new_contract_no(order_type, contract_short_name=None):
    code = str(contract_short_name or '').strip().upper()
    if not code:
        code = _TYPE_CODES.get(order_type, 'QT')
    return f"CGHTDY-{code}-{datetime.now():%Y%m%d}-{uuid.uuid4().hex[:6].upper()}"


def create_record(**values):
    ensure_schema()
    return query(
        """INSERT INTO contract_records
           (contract_no, supplier_id, supplier_name, buyer_name, order_type, template_id, template_name,
            order_count, product_count, total_amount, status, order_ids_json, orders_json,
            current_file_path, current_file_name, created_by, created_by_name, updated_by, updated_by_name)
           VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
        (
            values['contract_no'], values.get('supplier_id'), values.get('supplier_name', ''),
            values.get('buyer_name', '深圳市蝶雅云联信息技术有限公司'), values.get('order_type', ''),
            values.get('template_id'), values.get('template_name', ''), values.get('order_count', 0),
            values.get('product_count', 0), values.get('total_amount', 0), values.get('status', '已生成'),
            json.dumps(values.get('order_ids', []), ensure_ascii=False),
            json.dumps(values.get('orders', []), ensure_ascii=False, default=str),
            values.get('current_file_path', ''), values.get('current_file_name', ''),
            values.get('created_by'), values.get('created_by_name', ''),
            values.get('created_by'), values.get('created_by_name', ''),
        ),
    )


def add_file(contract_id, version, file_path, file_name, user_id=None, user_name='', remark=''):
    ensure_schema()
    query("UPDATE contract_files SET is_current=0 WHERE contract_id=%s", (contract_id,))
    return query(
        """INSERT INTO contract_files
           (contract_id, version, file_path, file_name, generated_by, generated_by_name, is_current, remark)
           VALUES (%s,%s,%s,%s,%s,%s,1,%s)""",
        (contract_id, version, file_path, file_name, user_id, user_name, remark),
    )


def add_event(contract_id, action, detail='', user_id=None, user_name=''):
    ensure_schema()
    return query(
        "INSERT INTO contract_events (contract_id, action, detail, user_id, user_name) "
        "VALUES (%s,%s,%s,%s,%s)",
        (contract_id, action, detail, user_id, user_name),
    )


def parse_json(value, default):
    if not value:
        return default
    try:
        return json.loads(value) if isinstance(value, str) else value
    except (TypeError, ValueError):
        return default