"""创建统一性状值视图 v_trait_value，幂等。

背景：波次6.3 统一性状值视图，归一 bre_trait_observation（未来 bre_observation 接入时
UNION 扩展），供 ABLUP / 配合力 / 选择指数等统计查询直接消费，作为 L3 统计地基。

视图输出与 bre_trait_observation 同构（长表），另加 source_table 标注数据来源。

用法（backend/ 目录）：
    ENVIRONMENT=dev "C:/ai/miniconda3/envs/dpb/python.exe" scripts/create_v_trait_value.py --check
    ENVIRONMENT=dev "C:/ai/miniconda3/envs/dpb/python.exe" scripts/create_v_trait_value.py

幂等性：CREATE OR REPLACE VIEW 天然幂等，可重复执行。
"""
import argparse
import os
import sys

BACKEND_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if BACKEND_DIR not in sys.path:
    sys.path.insert(0, BACKEND_DIR)

from sqlalchemy import create_engine, text  # noqa: E402

from app.config.setting import settings  # noqa: E402

EXPECTED_COLUMNS = {
    "id", "evaluation_id", "tree_id", "combination_id", "trait_id",
    "evaluate_year", "value_numeric", "value_text", "value_date",
    "is_deleted", "source_table", "created_time",
}


def main() -> None:
    parser = argparse.ArgumentParser(description="创建 v_trait_value 统一性状值视图")
    parser.add_argument("--check", action="store_true", help="仅校验视图存在性与列结构，不落库")
    args = parser.parse_args()

    engine = create_engine(settings.DB_URI)
    with engine.connect() as conn:
        exists = conn.execute(
            text("SELECT 1 FROM information_schema.views WHERE table_name = 'v_trait_value'")
        ).first() is not None

        if args.check:
            if not exists:
                print("!! 视图 v_trait_value 不存在")
                sys.exit(1)
            cols = {
                r[0]
                for r in conn.execute(
                    text(
                        "SELECT column_name FROM information_schema.columns "
                        "WHERE table_name = 'v_trait_value'"
                    )
                )
            }
            missing = sorted(EXPECTED_COLUMNS - cols)
            print(f"# 视图 v_trait_value 存在，列数 {len(cols)}")
            print(f"# 期望列缺失: {missing if missing else '无，结构完备 OK'}")
            if missing:
                sys.exit(1)
            return

        sql = text(
            "CREATE OR REPLACE VIEW v_trait_value AS "
            "SELECT id, evaluation_id, tree_id, combination_id, trait_id, evaluate_year, "
            "value_numeric, value_text, value_date, is_deleted, "
            "'trait_observation'::text AS source_table, created_time "
            "FROM bre_trait_observation"
        )
        conn.execute(sql)
        conn.commit()
        print("# 视图 v_trait_value 已创建/刷新 OK")


if __name__ == "__main__":
    main()