# -*- coding: utf-8 -*-
"""Export index tables to markdown for quick review."""
import os
import sys
from datetime import datetime

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

from config.settings import AppConfig
from database.connection import DatabaseConnection
from database.operations import DatabaseOperations


def _fmt(value, digits=2):
    if value is None:
        return "-"
    if isinstance(value, (int, float)):
        return f"{value:.{digits}f}"
    return str(value)


def _fetch(db: DatabaseOperations, sql: str):
    return [dict(r) for r in (db.query(sql) or [])]


def build_markdown(db: DatabaseOperations) -> str:
    lines = []
    lines.append("# Index Tables")
    lines.append("")
    lines.append(f"Generated at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    lines.append("")

    # 老客挽回指数（WBI）
    wbi_sql = """
        SELECT
            COALESCE(m.nickname, CONCAT('member_', r.member_id)) AS member_name,
            r.display_score,
            r.raw_score,
            r.t_v,
            r.visits_14d,
            r.sv_balance
        FROM dws.dws_member_winback_index r
        LEFT JOIN dwd.dim_member m
            ON r.member_id = m.member_id AND m.scd2_is_current = 1
        ORDER BY r.display_score DESC NULLS LAST
    """
    wbi_rows = _fetch(db, wbi_sql)
    lines.append("## 1) WBI")
    lines.append("")
    lines.append("| member_name | wbi | raw_score | t_v | visits_14d | sv_balance |")
    lines.append("|---|---:|---:|---:|---:|---:|")
    for r in wbi_rows:
        lines.append(
            f"| {r.get('member_name') or '-'} | {_fmt(r.get('display_score'))} | {_fmt(r.get('raw_score'), 4)} | "
            f"{_fmt(r.get('t_v'))} | {_fmt(r.get('visits_14d'), 0)} | {_fmt(r.get('sv_balance'))} |"
        )
    lines.append("")
    lines.append(f"Total rows: {len(wbi_rows)}")
    lines.append("")

    # 新客转化指数（NCI）
    nci_sql = """
        SELECT
            COALESCE(m.nickname, CONCAT('member_', r.member_id)) AS member_name,
            r.display_score,
            r.display_score_welcome,
            r.display_score_convert,
            r.raw_score,
            r.raw_score_welcome,
            r.raw_score_convert,
            r.t_v,
            r.visits_14d
        FROM dws.dws_member_newconv_index r
        LEFT JOIN dwd.dim_member m
            ON r.member_id = m.member_id AND m.scd2_is_current = 1
        ORDER BY r.display_score DESC NULLS LAST
    """
    nci_rows = _fetch(db, nci_sql)
    lines.append("## 2) NCI")
    lines.append("")
    lines.append("| member_name | nci | welcome | convert | raw_total | raw_welcome | raw_convert | t_v | visits_14d |")
    lines.append("|---|---:|---:|---:|---:|---:|---:|---:|---:|")
    for r in nci_rows:
        lines.append(
            f"| {r.get('member_name') or '-'} | {_fmt(r.get('display_score'))} | {_fmt(r.get('display_score_welcome'))} | "
            f"{_fmt(r.get('display_score_convert'))} | {_fmt(r.get('raw_score'), 4)} | {_fmt(r.get('raw_score_welcome'), 4)} | "
            f"{_fmt(r.get('raw_score_convert'), 4)} | {_fmt(r.get('t_v'))} | {_fmt(r.get('visits_14d'), 0)} |"
        )
    lines.append("")
    lines.append(f"Total rows: {len(nci_rows)}")
    lines.append("")

    # 亲密指数
    intimacy_sql = """
        SELECT
            COALESCE(a.nickname, CONCAT('assistant_', i.assistant_id)) AS assistant_name,
            COALESCE(m.nickname, CONCAT('member_', i.member_id)) AS member_name,
            i.display_score,
            i.session_count,
            i.attributed_recharge_amount
        FROM dws.dws_member_assistant_intimacy i
        LEFT JOIN dwd.dim_member m
            ON i.member_id = m.member_id AND m.scd2_is_current = 1
        LEFT JOIN dwd.dim_assistant a
            ON i.assistant_id = a.assistant_id AND a.scd2_is_current = 1
        ORDER BY i.display_score DESC NULLS LAST, i.session_count DESC
    """
    intimacy_rows = _fetch(db, intimacy_sql)
    lines.append("## 3) Intimacy")
    lines.append("")
    lines.append("| assistant | member | intimacy | sessions | recharge_amount |")
    lines.append("|---|---|---:|---:|---:|")
    for r in intimacy_rows:
        lines.append(
            f"| {r.get('assistant_name') or '-'} | {r.get('member_name') or '-'} | {_fmt(r.get('display_score'))} | "
            f"{_fmt(r.get('session_count'), 0)} | {_fmt(r.get('attributed_recharge_amount'))} |"
        )
    lines.append("")
    lines.append(f"Total rows: {len(intimacy_rows)}")

    return "\n".join(lines)


def main() -> None:
    config = AppConfig.load()
    db_conn = DatabaseConnection(config.config["db"]["dsn"])
    db = DatabaseOperations(db_conn)
    try:
        markdown = build_markdown(db)
    finally:
        db_conn.close()

    output_path = os.path.join(ROOT, "docs", "index_tables.md")
    with open(output_path, "w", encoding="utf-8-sig") as f:
        f.write(markdown)

    print(f"Exported to {output_path}")


if __name__ == "__main__":
    main()
