# -*- coding: utf-8 -*-
"""Export groupbuy orders that used assistant services."""

from __future__ import annotations

import argparse
import csv
import os
import sys
from pathlib import Path
from typing import Any, Dict, List, Optional, Sequence, Tuple

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 _as_int(v: Any) -> Optional[int]:
    if v is None or str(v).strip() == "":
        return None
    return int(v)


def _resolve_site_id(config: AppConfig, db: DatabaseOperations, cli_site_id: Optional[int]) -> int:
    if cli_site_id is not None:
        return int(cli_site_id)

    from_cfg = _as_int(config.get("app.store_id"))
    if from_cfg is not None:
        return from_cfg

    rows = db.query(
        """
        SELECT site_id
        FROM dwd.dwd_settlement_head
        WHERE site_id IS NOT NULL
        GROUP BY site_id
        ORDER BY COUNT(*) DESC
        LIMIT 1
        """
    )
    if rows:
        return int(dict(rows[0])["site_id"])

    raise RuntimeError("Unable to resolve site_id; pass --site-id explicitly.")


FIELD_ORDER: List[str] = [
    "site_id",
    "order_settle_id",
    "order_trade_no",
    "pay_time",
    "settle_type",
    "member_id",
    "member_name",
    "member_phone",
    "table_id",
    "table_name",
    "table_area_name",
    "settle_consume_money",
    "settle_pay_amount",
    "settle_coupon_amount",
    "pl_coupon_sale_amount",
    "groupbuy_item_count",
    "groupbuy_pay_amount",
    "groupbuy_ledger_amount",
    "groupbuy_coupon_money",
    "coupon_codes",
    "groupbuy_items",
    "assistant_service_count",
    "assistant_count",
    "assistant_nicknames",
    "assistant_skills",
    "assistant_real_use_seconds",
    "assistant_projected_income",
    "assistant_real_service_money",
]

ZH_HEADER_MAP: Dict[str, str] = {
    "site_id": "门店ID",
    "order_settle_id": "结账单ID",
    "order_trade_no": "订单交易号",
    "pay_time": "结账时间",
    "settle_type": "结账类型",
    "member_id": "会员ID",
    "member_name": "会员姓名",
    "member_phone": "会员手机号",
    "table_id": "台桌ID",
    "table_name": "台桌名称",
    "table_area_name": "台区名称",
    "settle_consume_money": "结算消费金额",
    "settle_pay_amount": "结算实付金额",
    "settle_coupon_amount": "结算团购抵扣金额",
    "pl_coupon_sale_amount": "平台团购实付金额",
    "groupbuy_item_count": "团购核销条目数",
    "groupbuy_pay_amount": "团购实付合计",
    "groupbuy_ledger_amount": "团购标价合计",
    "groupbuy_coupon_money": "团购券面额合计",
    "coupon_codes": "团购券码列表",
    "groupbuy_items": "团购项目列表",
    "assistant_service_count": "助教服务条目数",
    "assistant_count": "助教人数",
    "assistant_nicknames": "助教昵称列表",
    "assistant_skills": "助教技能列表",
    "assistant_real_use_seconds": "助教实际服务秒数",
    "assistant_projected_income": "助教预计收入合计",
    "assistant_real_service_money": "助教实收服务费合计",
}


def _fetch_rows_current(
    db: DatabaseOperations,
    site_id: int,
    start_date: Optional[str],
    end_date: Optional[str],
) -> List[Dict[str, Any]]:
    sql = """
    WITH gb AS (
        SELECT
            site_id,
            order_settle_id,
            COUNT(*) AS groupbuy_item_count,
            ROUND(SUM(COALESCE(ledger_unit_price, 0))::numeric, 2) AS groupbuy_pay_amount,
            ROUND(SUM(COALESCE(ledger_amount, 0))::numeric, 2) AS groupbuy_ledger_amount,
            ROUND(SUM(COALESCE(coupon_money, 0))::numeric, 2) AS groupbuy_coupon_money,
            STRING_AGG(DISTINCT NULLIF(coupon_code, ''), '?' ORDER BY NULLIF(coupon_code, '')) AS coupon_codes,
            STRING_AGG(DISTINCT NULLIF(ledger_name, ''), '?' ORDER BY NULLIF(ledger_name, '')) AS groupbuy_items
        FROM dwd.dwd_groupbuy_redemption
        WHERE site_id = %s
          AND is_delete = 0
        GROUP BY site_id, order_settle_id
    ),
    asv AS (
        SELECT
            site_id,
            order_settle_id,
            COUNT(*) AS assistant_service_count,
            COUNT(DISTINCT NULLIF(assistant_no, '')) AS assistant_count,
            STRING_AGG(DISTINCT NULLIF(nickname, ''), '?' ORDER BY NULLIF(nickname, '')) AS assistant_nicknames,
            STRING_AGG(DISTINCT NULLIF(skill_name, ''), '?' ORDER BY NULLIF(skill_name, '')) AS assistant_skills,
            ROUND(SUM(COALESCE(real_use_seconds, 0))::numeric, 0) AS assistant_real_use_seconds,
            ROUND(SUM(COALESCE(projected_income, 0))::numeric, 2) AS assistant_projected_income,
            ROUND(SUM(COALESCE(real_service_money, 0))::numeric, 2) AS assistant_real_service_money
        FROM dwd.dwd_assistant_service_log
        WHERE site_id = %s
          AND is_delete = 0
        GROUP BY site_id, order_settle_id
    )
    SELECT
        sh.site_id,
        sh.order_settle_id,
        sh.order_trade_no,
        sh.pay_time,
        sh.settle_type,
        sh.member_id,
        COALESCE(dm.nickname, sh.member_name) AS member_name,
        COALESCE(dm.mobile, sh.member_phone) AS member_phone,
        sh.table_id,
        dt.table_name,
        dt.site_table_area_name AS table_area_name,
        ROUND(COALESCE(sh.consume_money, 0)::numeric, 2) AS settle_consume_money,
        ROUND(COALESCE(sh.pay_amount, 0)::numeric, 2) AS settle_pay_amount,
        ROUND(COALESCE(sh.coupon_amount, 0)::numeric, 2) AS settle_coupon_amount,
        ROUND(COALESCE(sh.pl_coupon_sale_amount, 0)::numeric, 2) AS pl_coupon_sale_amount,
        gb.groupbuy_item_count,
        gb.groupbuy_pay_amount,
        gb.groupbuy_ledger_amount,
        gb.groupbuy_coupon_money,
        gb.coupon_codes,
        gb.groupbuy_items,
        asv.assistant_service_count,
        asv.assistant_count,
        asv.assistant_nicknames,
        asv.assistant_skills,
        asv.assistant_real_use_seconds,
        asv.assistant_projected_income,
        asv.assistant_real_service_money
    FROM gb
    JOIN asv
      ON asv.site_id = gb.site_id
     AND asv.order_settle_id = gb.order_settle_id
    LEFT JOIN dwd.dwd_settlement_head sh
      ON sh.site_id = gb.site_id
     AND sh.order_settle_id = gb.order_settle_id
    LEFT JOIN dwd.dim_member dm
      ON dm.register_site_id = sh.site_id
     AND dm.member_id = sh.member_id
     AND dm.scd2_is_current = 1
    LEFT JOIN dwd.dim_table dt
      ON dt.site_id = sh.site_id
     AND dt.table_id = sh.table_id
     AND dt.scd2_is_current = 1
    WHERE (%s::date IS NULL OR sh.pay_time::date >= %s::date)
      AND (%s::date IS NULL OR sh.pay_time::date <= %s::date)
    ORDER BY sh.pay_time DESC, sh.order_settle_id DESC
    """
    rows = db.query(
        sql,
        (
            site_id,
            site_id,
            start_date,
            start_date,
            end_date,
            end_date,
        ),
    )
    return [dict(r) for r in (rows or [])]


def _fetch_rows_optimized(
    db: DatabaseOperations,
    site_id: int,
    start_date: Optional[str],
    end_date: Optional[str],
) -> List[Dict[str, Any]]:
    """
    Optimized export strategy:
    - Deduplicate groupbuy rows by (order_settle_id, coupon_key) to handle retry noise.
    - Deduplicate assistant rows by assistant_service_id.
    - Keep output schema identical to current export for direct comparison.
    """
    sql = """
    WITH gb_raw AS (
        SELECT
            redemption_id,
            site_id,
            order_settle_id,
            order_coupon_id,
            coupon_code,
            ledger_name,
            COALESCE(ledger_unit_price, 0) AS ledger_unit_price,
            COALESCE(ledger_amount, 0) AS ledger_amount,
            COALESCE(coupon_money, 0) AS coupon_money,
            create_time,
            COALESCE(NULLIF(coupon_code, ''), CAST(order_coupon_id AS varchar), CAST(redemption_id AS varchar)) AS coupon_key,
            ROW_NUMBER() OVER (
                PARTITION BY site_id, order_settle_id,
                             COALESCE(NULLIF(coupon_code, ''), CAST(order_coupon_id AS varchar), CAST(redemption_id AS varchar))
                ORDER BY create_time DESC NULLS LAST, redemption_id DESC
            ) AS rn
        FROM dwd.dwd_groupbuy_redemption
        WHERE site_id = %s
          AND is_delete = 0
    ),
    gb AS (
        SELECT
            site_id,
            order_settle_id,
            COUNT(*) AS groupbuy_item_count,
            ROUND(SUM(ledger_unit_price)::numeric, 2) AS groupbuy_pay_amount,
            ROUND(SUM(ledger_amount)::numeric, 2) AS groupbuy_ledger_amount,
            ROUND(SUM(coupon_money)::numeric, 2) AS groupbuy_coupon_money,
            STRING_AGG(DISTINCT NULLIF(coupon_code, ''), '?' ORDER BY NULLIF(coupon_code, '')) AS coupon_codes,
            STRING_AGG(DISTINCT NULLIF(ledger_name, ''), '?' ORDER BY NULLIF(ledger_name, '')) AS groupbuy_items
        FROM gb_raw
        WHERE rn = 1
        GROUP BY site_id, order_settle_id
    ),
    asv_raw AS (
        SELECT DISTINCT ON (assistant_service_id)
            assistant_service_id,
            site_id,
            order_settle_id,
            assistant_no,
            nickname,
            skill_name,
            COALESCE(real_use_seconds, 0) AS real_use_seconds,
            COALESCE(projected_income, 0) AS projected_income,
            COALESCE(real_service_money, 0) AS real_service_money
        FROM dwd.dwd_assistant_service_log
        WHERE site_id = %s
          AND is_delete = 0
        ORDER BY assistant_service_id
    ),
    asv AS (
        SELECT
            site_id,
            order_settle_id,
            COUNT(*) AS assistant_service_count,
            COUNT(DISTINCT NULLIF(assistant_no, '')) AS assistant_count,
            STRING_AGG(DISTINCT NULLIF(nickname, ''), '?' ORDER BY NULLIF(nickname, '')) AS assistant_nicknames,
            STRING_AGG(DISTINCT NULLIF(skill_name, ''), '?' ORDER BY NULLIF(skill_name, '')) AS assistant_skills,
            ROUND(SUM(real_use_seconds)::numeric, 0) AS assistant_real_use_seconds,
            ROUND(SUM(projected_income)::numeric, 2) AS assistant_projected_income,
            ROUND(SUM(real_service_money)::numeric, 2) AS assistant_real_service_money
        FROM asv_raw
        GROUP BY site_id, order_settle_id
    )
    SELECT
        sh.site_id,
        sh.order_settle_id,
        sh.order_trade_no,
        sh.pay_time,
        sh.settle_type,
        sh.member_id,
        COALESCE(dm.nickname, sh.member_name) AS member_name,
        COALESCE(dm.mobile, sh.member_phone) AS member_phone,
        sh.table_id,
        dt.table_name,
        dt.site_table_area_name AS table_area_name,
        ROUND(COALESCE(sh.consume_money, 0)::numeric, 2) AS settle_consume_money,
        ROUND(COALESCE(sh.pay_amount, 0)::numeric, 2) AS settle_pay_amount,
        ROUND(COALESCE(sh.coupon_amount, 0)::numeric, 2) AS settle_coupon_amount,
        ROUND(COALESCE(sh.pl_coupon_sale_amount, 0)::numeric, 2) AS pl_coupon_sale_amount,
        gb.groupbuy_item_count,
        gb.groupbuy_pay_amount,
        gb.groupbuy_ledger_amount,
        gb.groupbuy_coupon_money,
        gb.coupon_codes,
        gb.groupbuy_items,
        asv.assistant_service_count,
        asv.assistant_count,
        asv.assistant_nicknames,
        asv.assistant_skills,
        asv.assistant_real_use_seconds,
        asv.assistant_projected_income,
        asv.assistant_real_service_money
    FROM gb
    JOIN asv
      ON asv.site_id = gb.site_id
     AND asv.order_settle_id = gb.order_settle_id
    LEFT JOIN dwd.dwd_settlement_head sh
      ON sh.site_id = gb.site_id
     AND sh.order_settle_id = gb.order_settle_id
    LEFT JOIN dwd.dim_member dm
      ON dm.register_site_id = sh.site_id
     AND dm.member_id = sh.member_id
     AND dm.scd2_is_current = 1
    LEFT JOIN dwd.dim_table dt
      ON dt.site_id = sh.site_id
     AND dt.table_id = sh.table_id
     AND dt.scd2_is_current = 1
    WHERE (%s::date IS NULL OR sh.pay_time::date >= %s::date)
      AND (%s::date IS NULL OR sh.pay_time::date <= %s::date)
    ORDER BY sh.pay_time DESC, sh.order_settle_id DESC
    """
    rows = db.query(
        sql,
        (
            site_id,
            site_id,
            start_date,
            start_date,
            end_date,
            end_date,
        ),
    )
    return [dict(r) for r in (rows or [])]


def _write_csv(
    rows: List[Dict[str, Any]],
    out_csv: Path,
    fields: Sequence[str],
    header_map: Optional[Dict[str, str]] = None,
) -> None:
    out_csv.parent.mkdir(parents=True, exist_ok=True)
    if header_map:
        file_headers = [header_map.get(f, f) for f in fields]
    else:
        file_headers = list(fields)
    with out_csv.open("w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f)
        writer.writerow(file_headers)
        for row in rows:
            writer.writerow([row.get(k) for k in fields])


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Export groupbuy orders that used assistant services."
    )
    parser.add_argument("--site-id", type=int, default=None, help="Site id to export")
    parser.add_argument("--start-date", default=None, help="Filter start date: YYYY-MM-DD")
    parser.add_argument("--end-date", default=None, help="Filter end date: YYYY-MM-DD")
    parser.add_argument(
        "--scheme",
        choices=["current", "optimized"],
        default="current",
        help="Export scheme",
    )
    parser.add_argument(
        "--header-lang",
        choices=["zh", "en"],
        default="zh",
        help="CSV header language",
    )
    parser.add_argument(
        "--output-csv",
        default=os.path.join(ROOT, "docs", "groupbuy_orders_with_assistant_service.csv"),
        help="Output CSV path",
    )
    args = parser.parse_args()

    config = AppConfig.load()
    db_conn = DatabaseConnection(config.config["db"]["dsn"])
    db = DatabaseOperations(db_conn)
    try:
        site_id = _resolve_site_id(config, db, args.site_id)
        if args.scheme == "optimized":
            rows = _fetch_rows_optimized(db, site_id, args.start_date, args.end_date)
        else:
            rows = _fetch_rows_current(db, site_id, args.start_date, args.end_date)
    finally:
        db_conn.close()

    out_csv = Path(args.output_csv)
    header_map = ZH_HEADER_MAP if args.header_lang == "zh" else None
    _write_csv(rows, out_csv, fields=FIELD_ORDER, header_map=header_map)

    print(f"site_id={site_id}")
    print(f"scheme={args.scheme}")
    print(f"rows={len(rows)}")
    print(f"csv={out_csv}")


if __name__ == "__main__":
    main()
