"""SQLite 连接与幂等初始化（项目经费进度播报助手）。

业务表 3 张：
- fund_projects（项目台账，金额/日期/状态字段可编辑）
- progress_reports（播报记录，只读，播报自动写入）
- alert_rules（预警规则，阈值可编辑）

表间禁止 SQLite 外键，仅存业务 id（见 references/SCHEMA.md）。
"""

from __future__ import annotations

import sqlite3

from db.display_metadata import (
    init_display_metadata,
    seed_column_metadata_batch,
    upsert_table_metadata,
)
from db.seed_data import seed_all
from db.timestamp_columns import init_updated_at_trigger
from util.runtime_paths import get_db_path


def get_conn() -> sqlite3.Connection:
    return sqlite3.connect(get_db_path())


_FUND_PROJECTS_DDL = """
CREATE TABLE IF NOT EXISTS fund_projects (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    project_no     TEXT NOT NULL UNIQUE,
    project_name   TEXT,
    project_type   TEXT,
    pi_name        TEXT,
    department     TEXT,
    total_approved REAL,
    total_received REAL,
    total_spent    REAL,
    frozen_amount  REAL,
    frozen_since   TEXT,
    start_date     TEXT,
    end_date       TEXT,
    status         TEXT NOT NULL DEFAULT '在研',
    remark         TEXT,
    created_at     INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at     INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

_PROGRESS_REPORTS_DDL = """
CREATE TABLE IF NOT EXISTS progress_reports (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_no         TEXT NOT NULL,
    project_id       INTEGER,
    project_no       TEXT,
    project_name     TEXT,
    pi_name          TEXT,
    as_of            TEXT,
    spend_rate       REAL,
    time_progress    REAL,
    deviation        REAL,
    status_tag       TEXT NOT NULL,
    summary          TEXT,
    suggestions_json TEXT,
    items_json       TEXT,
    report_file      TEXT,
    created_at       INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at       INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

_PROGRESS_REPORTS_INDEX = """
CREATE INDEX IF NOT EXISTS idx_progress_reports_batch
ON progress_reports(batch_no)
"""

_ALERT_RULES_DDL = """
CREATE TABLE IF NOT EXISTS alert_rules (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    rule_key        TEXT NOT NULL UNIQUE,
    display_name    TEXT,
    threshold_value REAL NOT NULL,
    description     TEXT,
    created_at      INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at      INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

_TS_COLUMN_NAMES = {"created_at", "updated_at"}


def _col(column_name: str, display_name: str, description: str, **extra: object) -> dict[str, object]:
    base: dict[str, object] = {
        "column_name": column_name,
        "display_name": display_name,
        "description": description,
        "visible": 1,
        "searchable": 0,
        "editable": 0,
    }
    base.update(extra)
    if column_name in _TS_COLUMN_NAMES:
        base["display_type"] = "datetime_unix_seconds"
    return base


_FUND_PROJECTS_COLUMNS: tuple[dict[str, object], ...] = (
    _col("id", "编号", "主键", searchable=1),
    _col("project_no", "项目编号", "项目唯一编号（财务/科研系统口径）。", searchable=1),
    _col("project_name", "项目名称", "项目全称。", searchable=1, editable=1),
    _col(
        "project_type",
        "项目类别",
        "纵向 / 横向 / 其他。",
        searchable=1,
        editable=1,
        options_json='[{"value":"纵向","label":"纵向"},{"value":"横向","label":"横向"},{"value":"其他","label":"其他"}]',
    ),
    _col("pi_name", "负责人", "项目负责人（PI）姓名。", searchable=1, editable=1),
    _col("department", "所属学院", "二级学院/单位。", searchable=1, editable=1),
    _col("total_approved", "总立项经费", "立项批复总经费（元）。", searchable=1, editable=1, display_type="number"),
    _col("total_received", "已到账金额", "已到账经费（元），执行率分母。", searchable=1, editable=1, display_type="number"),
    _col("total_spent", "已报销金额", "已报销支出（元），执行率分子。", searchable=1, editable=1, display_type="number"),
    _col("frozen_amount", "冻结/暂付款", "冻结或暂付款金额（元）。", searchable=1, editable=1, display_type="number"),
    _col("frozen_since", "暂付款最早发生日", "最早一笔暂付款发生日期（YYYY-MM-DD），用于账龄提醒。", editable=1),
    _col("start_date", "执行期开始", "项目执行期开始日期（YYYY-MM-DD）。", searchable=1, editable=1),
    _col("end_date", "执行期结束", "项目执行期结束日期（YYYY-MM-DD）。", searchable=1, editable=1),
    _col(
        "status",
        "项目状态",
        "在研 / 已结题 / 暂缓；播报默认只处理“在研”。",
        searchable=1,
        editable=1,
        options_json='[{"value":"在研","label":"在研"},{"value":"已结题","label":"已结题"},{"value":"暂缓","label":"暂缓"}]',
    ),
    _col("remark", "备注", "维护说明。", editable=1),
    _col("created_at", "创建时间", "记录创建时间（Unix 秒级时间戳，自动生成）。"),
    _col("updated_at", "更新时间", "记录最后更新时间（Unix 秒级时间戳，自动维护）。"),
)

_PROGRESS_REPORTS_COLUMNS: tuple[dict[str, object], ...] = (
    _col("id", "报告编号", "主键，播报记录编号。", searchable=1),
    _col("batch_no", "播报批次号", "同一次播报请求的批次号。", searchable=1),
    _col("project_id", "项目编号(id)", "关联项目台账主键。", searchable=1),
    _col("project_no", "项目编号", "项目唯一编号。", searchable=1),
    _col("project_name", "项目名称", "项目全称。", searchable=1),
    _col("pi_name", "负责人", "项目负责人姓名。", searchable=1),
    _col("as_of", "评估基准日", "本次播报的数据截止日期（YYYY-MM-DD）。", searchable=1),
    _col("spend_rate", "实际支出率", "已报销/已到账（小数）。", searchable=1, display_type="number"),
    _col("time_progress", "序时进度", "已过天数/总执行天数（小数）。", searchable=1, display_type="number"),
    _col("deviation", "偏差", "实际支出率 − 序时进度（小数，正=偏快，负=偏慢）。", searchable=1, display_type="number"),
    _col(
        "status_tag",
        "执行状态",
        "严重滞后 / 支出异常偏快 / 正常 / 待补录 / 未到账。",
        searchable=1,
        options_json='[{"value":"严重滞后","label":"🔴 严重滞后"},{"value":"支出异常偏快","label":"🟠 支出异常偏快"},{"value":"正常","label":"🟢 正常"},{"value":"待补录","label":"⚪ 待补录"},{"value":"未到账","label":"⚪ 未到账"}]',
    ),
    _col("summary", "播报摘要", "三段式播报文本（现状/诊断/建议）。", visible=0, display_type="textarea"),
    _col("suggestions_json", "建议清单", "建议条目（JSON 数组，默认隐藏）。", visible=0, display_type="textarea"),
    _col("items_json", "规则明细", "C/D/N 规则命中明细（JSON 数组，默认隐藏）。", visible=0, display_type="textarea"),
    _col("report_file", "简报文件", "《经费执行简报》HTML 文件路径。", visible=0),
    _col("created_at", "创建时间", "报告创建时间（Unix 秒级时间戳，自动生成）。"),
    _col("updated_at", "更新时间", "报告更新时间（Unix 秒级时间戳，自动维护）。"),
)

_ALERT_RULES_COLUMNS: tuple[dict[str, object], ...] = (
    _col("id", "编号", "主键", searchable=1),
    _col("rule_key", "规则键", "阈值键名（lag_threshold / overshoot_threshold / pending_aging_days / closing_warn_days）。", searchable=1),
    _col("display_name", "名称", "阈值中文名。", searchable=1),
    _col("threshold_value", "阈值", "阈值数值（比例为小数，天数为正整数）。", searchable=1, editable=1, display_type="number"),
    _col("description", "说明", "阈值口径说明。", searchable=1, editable=1),
    _col("created_at", "创建时间", "记录创建时间（Unix 秒级，自动生成）。"),
    _col("updated_at", "更新时间", "记录最后更新时间（Unix 秒级，自动维护）。"),
)


def _init_fund_projects_metadata(cursor) -> None:
    cursor.execute(_FUND_PROJECTS_DDL)
    init_updated_at_trigger(cursor, "fund_projects")
    upsert_table_metadata(
        cursor,
        table_name="fund_projects",
        display_name="项目台账",
        description="PI 名下项目的基础台账：立项/到账/报销/暂付款与执行周期；播报的数据源。",
        grid_help="金额单位为元；顶栏按钮可录入项目，行内可直接编辑金额与日期；播报默认只处理“在研”项目。",
        visible=1,
        readonly=0,
        default_sort_json='[{"column":"id","direction":"desc"}]',
        default_visible_columns_json='["project_no","project_name","pi_name","department","total_approved","total_received","total_spent","status"]',
    )
    seed_column_metadata_batch(
        cursor,
        table_name="fund_projects",
        columns=_FUND_PROJECTS_COLUMNS,
        valid_column_names=[str(c["column_name"]) for c in _FUND_PROJECTS_COLUMNS],
    )


def _ensure_progress_reports_columns(cursor) -> None:
    """旧库迁移：CREATE TABLE IF NOT EXISTS 不会给已有表加新字段。

    SQLite ``ALTER TABLE ... ADD COLUMN`` 不允许非常量默认值（``unixepoch()``），
    因此缺 ``items_json`` / ``updated_at`` 时按当前标准 DDL 重建表并拷贝历史数据
    （progress_reports 为只读播报记录，量级小；无外键，重建安全）。
    """
    cursor.execute("PRAGMA table_info(progress_reports)")
    cols = [str(row[1]) for row in cursor.fetchall()]
    target = (
        "id", "batch_no", "project_id", "project_no", "project_name", "pi_name",
        "as_of", "spend_rate", "time_progress", "deviation", "status_tag",
        "summary", "suggestions_json", "items_json", "report_file",
        "created_at", "updated_at",
    )
    if all(name in cols for name in target):
        return
    shared = [name for name in cols if name in target and name != "updated_at"]
    cursor.execute("ALTER TABLE progress_reports RENAME TO _progress_reports_legacy")
    cursor.execute(_PROGRESS_REPORTS_DDL)
    if shared:
        col_list = ", ".join(shared)
        cursor.execute(
            f"INSERT INTO progress_reports ({col_list}) "
            f"SELECT {col_list} FROM _progress_reports_legacy"
        )
    cursor.execute("DROP TABLE _progress_reports_legacy")  # 连同其旧索引一并删除
    cursor.execute(_PROGRESS_REPORTS_INDEX)


def _init_progress_reports_metadata(cursor) -> None:
    cursor.execute(_PROGRESS_REPORTS_DDL)
    cursor.execute(_PROGRESS_REPORTS_INDEX)
    _ensure_progress_reports_columns(cursor)
    init_updated_at_trigger(cursor, "progress_reports")
    upsert_table_metadata(
        cursor,
        table_name="progress_reports",
        display_name="播报记录",
        description="每次播报的项目执行状态、偏差与三段式摘要；可重建 HTML 简报。",
        grid_help="“严重滞后/支出异常偏快”的行建议由科研秘书或财务处跟进；简报可用顶栏按钮重建。",
        visible=1,
        readonly=1,
        default_sort_json='[{"column":"id","direction":"desc"}]',
        default_visible_columns_json='["id","as_of","project_no","project_name","pi_name","spend_rate","time_progress","deviation","status_tag"]',
    )
    seed_column_metadata_batch(
        cursor,
        table_name="progress_reports",
        columns=_PROGRESS_REPORTS_COLUMNS,
        valid_column_names=[str(c["column_name"]) for c in _PROGRESS_REPORTS_COLUMNS],
    )


def _init_alert_rules_metadata(cursor) -> None:
    cursor.execute(_ALERT_RULES_DDL)
    init_updated_at_trigger(cursor, "alert_rules")
    upsert_table_metadata(
        cursor,
        table_name="alert_rules",
        display_name="预警规则",
        description="偏差诊断与提醒阈值（严重滞后/异常偏快/暂付款账龄/结题预警）。",
        grid_help="比例为小数（0.10=10个百分点），天数为正整数；修改后下一次播报即生效。",
        visible=1,
        readonly=0,
    )
    seed_column_metadata_batch(
        cursor,
        table_name="alert_rules",
        columns=_ALERT_RULES_COLUMNS,
        valid_column_names=[str(c["column_name"]) for c in _ALERT_RULES_COLUMNS],
    )


def init_db() -> dict[str, int]:
    """幂等创建/迁移本地 SQLite、数据管理元数据与业务种子，返回种子统计。"""
    conn = get_conn()
    try:
        cur = conn.cursor()
        init_display_metadata(cur)
        _init_fund_projects_metadata(cur)
        _init_progress_reports_metadata(cur)
        _init_alert_rules_metadata(cur)
        seeded = seed_all(cur)
        conn.commit()
        return seeded
    finally:
        conn.close()