"""从飞书「自动化账户」表同步待投放账户配置到 DB。

表格:
  https://w42nne6hzg.feishu.cn/sheets/D8f4sKEb2hfBnNttX1ucZTklnHf

读取 tab:
  自动化账户 (sheet_id=y3uCcz)

规则:
  - 表头在第 2 行。
  - 仅当「是否自动化执行」为「是」时启用/更新账户。
  - 「否」会禁用已有的 ad_creation_account_config,不删除历史配置。
  - 「初始出价」支持固定值 0.35 或范围 0.28-0.31。
  - 「预算（单广告）」为空时使用模板默认预算;不限制/不限/- 按腾讯不限预算 0 传递;数字按元转换为分。
  - 「素材来源」为空默认历史素材;支持历史素材、AI生成素材、外部合作素材。
  - 「生成失败是否回退历史素材」为空/否默认不回退。
  - 「出价方式」为空默认稳定成本;填「最大转化量」时使用「最大转化量出价」作为控制成本。
  - 「投放版位」为空使用投放模板默认;填 AIM+ 时使用智能版位。
  - 「年龄」支持 30-66+ 或 45-66;为空使用 45-66。
  - 「地域」支持省市中文名逗号分隔;填 不限 时不传地域定向;为空使用投放模板。
  - 「日期」用于同账户配置版本排序；默认每个账户取日期最新的一行。
  - 显式传入 config_date 时才只同步该日期，用于历史复现。
"""

from __future__ import annotations

import argparse
import json
import os
import re
import sys
from datetime import date, datetime
from pathlib import Path
from typing import Any

import httpx
from dotenv import load_dotenv

_HERE = Path(__file__).parent
load_dotenv(_HERE / ".env")
sys.path.insert(0, str(_HERE.parent.parent))
sys.path.insert(0, str(_HERE))

from logging_setup import setup_logging  # noqa: E402

from configure_creation_accounts import (  # noqa: E402
    AccountConfigInput,
    DEFAULT_AGE_MAX,
    DEFAULT_AGE_MIN,
    parse_bid,
    parse_budget_fen,
    set_account_automation_enabled,
    upsert_account_config,
)
from tools.account_material_strategy import (  # noqa: E402
    normalize_material_source,
    parse_bool_flag,
)
from tools.delivery_config import (  # noqa: E402
    BID_MODE_MAX_CONVERSION,
    parse_bid_scene,
    parse_geo_location_config,
    parse_placement_config,
)
from tools.feishu_doc import (  # noqa: E402
    FEISHU_BASE_URL,
    _auth_headers,
    _get_tenant_token,
)


DEFAULT_SPREADSHEET_TOKEN = "D8f4sKEb2hfBnNttX1ucZTklnHf"
DEFAULT_SHEET_ID = "y3uCcz"
DEFAULT_RANGE = "A1:N300"

HEADER_ROW_INDEX = 1
DATA_START_INDEX = 2

REQUIRED_COLUMNS = {
    "日期": "config_date",
    "账户id": "account_id",
    "人群包": "audience_name",
    "初始出价": "bid",
    "预算（单广告）": "budget",
    "是否自动化执行": "enabled",
}

OPTIONAL_COLUMNS = {
    "素材来源": "material_source",
    "生成失败是否回退历史素材": "ai_fallback_to_history",
    "出价方式": "bid_scene",
    "最大转化量出价": "max_conversion_bid",
    "投放版位": "placement",
    "版位": "placement",
    "年龄": "age",
    "地域": "geo_location",
    "投放地域": "geo_location",
}


def _plain_value(value: Any) -> str:
    """将飞书单元格值（可能是富文本 list/dict）转为纯文本字符串"""
    if value is None:
        return ""
    if isinstance(value, list):
        parts: list[str] = []
        for item in value:
            if isinstance(item, dict):
                parts.append(str(item.get("text") or item.get("link") or ""))
            else:
                parts.append(str(item))
        return "".join(parts).strip()
    return str(value).strip()


def _geo_cell_value(value: Any) -> str:
    """地域列专用：多选下拉单元格可能返回逗号拼接字符串或数组，统一成逗号分隔文本。

    _plain_value 对 list 是无分隔符拼接（富文本场景），而多选下拉的 list 是多个
    独立选项，必须用逗号分隔才能被 parse_geo_location_config 正确拆分。
    """
    if isinstance(value, list):
        parts = [
            _plain_value(item).strip()
            for item in value
            if _plain_value(item).strip()
        ]
        return ",".join(parts)
    return _plain_value(value)


def _fetch_rows(spreadsheet_token: str, sheet_id: str, cell_range: str) -> list[list[Any]]:
    """读取飞书表格指定范围的原始行数据"""
    token = _get_tenant_token()
    url = (
        f"{FEISHU_BASE_URL}/sheets/v2/spreadsheets/"
        f"{spreadsheet_token}/values/{sheet_id}!{cell_range}"
    )
    resp = httpx.get(url, headers=_auth_headers(token), timeout=30)
    resp.raise_for_status()
    data = resp.json()
    if data.get("code") != 0:
        raise RuntimeError(f"读取飞书配置表失败:{json.dumps(data, ensure_ascii=False)[:500]}")
    return ((data.get("data") or {}).get("valueRange") or {}).get("values") or []


def _header_map(rows: list[list[Any]]) -> dict[str, int]:
    """解析表头行，返回 列名→列索引 映射；缺少必需列时抛异常"""
    if len(rows) <= HEADER_ROW_INDEX:
        raise RuntimeError("飞书配置表缺少表头行")
    headers = [_plain_value(v) for v in rows[HEADER_ROW_INDEX]]
    mapping = {name: idx for idx, name in enumerate(headers) if name}
    missing = [name for name in REQUIRED_COLUMNS if name not in mapping]
    if missing:
        raise RuntimeError(f"飞书配置表缺少列:{missing}; 当前表头={headers}")
    return mapping


def _cell(row: list[Any], idx: int) -> str:
    """安全取行内指定列的纯文本值，越界返回空字符串"""
    return _plain_value(row[idx]) if idx < len(row) else ""


def _parse_account_id(raw: str) -> int | None:
    """解析账户 ID 文本为 int；空值返回 None，非法值抛 ValueError"""
    text = raw.strip()
    if not text:
        return None
    # 飞书数字可能读成 "84545647.0"。
    try:
        return int(float(text))
    except Exception as e:
        raise ValueError(f"账户id非法:{raw}") from e


def _parse_config_date(raw: str) -> date:
    text = raw.strip().replace("/", "-")
    for fmt in ("%Y%m%d", "%Y-%m-%d"):
        try:
            return datetime.strptime(text, fmt).date()
        except ValueError:
            continue
    raise ValueError(f"日期非法:{raw}; 请输入 YYYYMMDD 或 YYYY-MM-DD")


def _parse_age_range(raw: str) -> tuple[int, int]:
    text = raw.strip()
    if not text:
        return DEFAULT_AGE_MIN, DEFAULT_AGE_MAX
    open_ended = re.fullmatch(r"(\d{1,3})\s*\+", text)
    if open_ended:
        age_min = int(open_ended.group(1))
        if age_min > DEFAULT_AGE_MAX:
            raise ValueError(f"年龄范围非法:{raw}")
        return age_min, DEFAULT_AGE_MAX
    matched = re.fullmatch(r"(\d{1,3})\s*[-~至]\s*(\d{1,3})\+?", text)
    if not matched:
        raise ValueError(f"年龄格式非法:{raw}; 示例 30-66+")
    age_min, age_max = (int(value) for value in matched.groups())
    if age_min > age_max:
        raise ValueError(f"年龄范围非法:{raw}")
    return age_min, age_max


def sync_from_feishu(
    spreadsheet_token: str = DEFAULT_SPREADSHEET_TOKEN,
    sheet_id: str = DEFAULT_SHEET_ID,
    cell_range: str = DEFAULT_RANGE,
    config_date: str | None = None,
    account_ids: set[int] | None = None,
    dry_run: bool = False,
    strict: bool = True,
) -> dict[str, Any]:
    """同步飞书配置；默认每账户取最新日期行，显式日期用于历史复现。"""
    rows = _fetch_rows(spreadsheet_token, sheet_id, cell_range)
    mapping = _header_map(rows)
    target_config_date = _parse_config_date(config_date) if config_date else None
    requested_accounts = set(account_ids) if account_ids is not None else None

    latest_row_by_account: dict[int, tuple[date, int]] = {}
    if target_config_date is None:
        for row_num, row in enumerate(rows[DATA_START_INDEX:], start=DATA_START_INDEX + 1):
            account_id = _parse_account_id(_cell(row, mapping["账户id"]))
            if account_id is None:
                continue
            if requested_accounts is not None and account_id not in requested_accounts:
                continue
            row_config_date = _parse_config_date(_cell(row, mapping["日期"]))
            current = latest_row_by_account.get(account_id)
            candidate = (row_config_date, row_num)
            if current is None or candidate > current:
                latest_row_by_account[account_id] = candidate

    stats = {
        "config_date": (
            target_config_date.isoformat() if target_config_date is not None else None
        ),
        "selection_mode": "exact_date" if target_config_date is not None else "latest_per_account",
        "enabled": 0,
        "disabled": 0,
        "skipped": 0,
        "date_skipped": 0,
        "errors": 0,
    }
    enabled_records: list[AccountConfigInput] = []
    disabled_accounts: list[int] = []
    for row_num, row in enumerate(rows[DATA_START_INDEX:], start=DATA_START_INDEX + 1):
        try:
            account_id = _parse_account_id(_cell(row, mapping["账户id"]))
            if account_id is None:
                stats["skipped"] += 1
                continue
            if requested_accounts is not None and account_id not in requested_accounts:
                stats["skipped"] += 1
                continue

            row_config_date = _parse_config_date(_cell(row, mapping["日期"]))
            if target_config_date is not None and row_config_date != target_config_date:
                stats["date_skipped"] += 1
                continue
            if (
                target_config_date is None
                and latest_row_by_account.get(account_id) != (row_config_date, row_num)
            ):
                stats["date_skipped"] += 1
                continue

            enabled_text = _cell(row, mapping["是否自动化执行"])
            enabled = enabled_text == "是"
            if not enabled:
                disabled_accounts.append(account_id)
                stats["disabled"] += 1
                continue

            audience_name = _cell(row, mapping["人群包"])
            bid_text = _cell(row, mapping["初始出价"])
            budget_text = _cell(row, mapping["预算（单广告）"])
            material_source_text = (
                _cell(row, mapping["素材来源"])
                if "素材来源" in mapping else ""
            )
            fallback_text = (
                _cell(row, mapping["生成失败是否回退历史素材"])
                if "生成失败是否回退历史素材" in mapping else ""
            )
            bid_scene_text = (
                _cell(row, mapping["出价方式"])
                if "出价方式" in mapping else ""
            )
            max_conversion_bid_text = (
                _cell(row, mapping["最大转化量出价"])
                if "最大转化量出价" in mapping else ""
            )
            placement_col = "投放版位" if "投放版位" in mapping else "版位"
            placement_text = (
                _cell(row, mapping[placement_col])
                if placement_col in mapping else ""
            )
            age_text = _cell(row, mapping["年龄"]) if "年龄" in mapping else ""
            geo_col = "地域" if "地域" in mapping else "投放地域"
            if geo_col in mapping:
                geo_idx = mapping[geo_col]
                geo_raw = row[geo_idx] if geo_idx < len(row) else None
                geo_text = _geo_cell_value(geo_raw)
            else:
                geo_text = ""
            bid_min, bid_max, bid_amount = parse_bid(bid_text)
            bid_scene = parse_bid_scene(bid_scene_text)
            custom_cost_cap_fen = None
            if bid_scene == BID_MODE_MAX_CONVERSION:
                if not max_conversion_bid_text:
                    raise ValueError("最大转化量出价不能为空")
                max_bid_min, max_bid_max, max_bid_amount = parse_bid(max_conversion_bid_text)
                if max_bid_min != max_bid_max or max_bid_amount is None:
                    raise ValueError("最大转化量出价必须是固定值")
                custom_cost_cap_fen = max_bid_amount
            placement = parse_placement_config(placement_text)
            age_min, age_max = _parse_age_range(age_text)
            geo_location = parse_geo_location_config(geo_text)
            enabled_records.append(AccountConfigInput(
                account_id=account_id,
                audience_name=audience_name,
                bid_min_fen=bid_min,
                bid_max_fen=bid_max,
                bid_amount_fen=bid_amount,
                daily_budget_fen=parse_budget_fen(budget_text),
                enabled=True,
                material_source=normalize_material_source(material_source_text),
                ai_fallback_to_history=parse_bool_flag(fallback_text),
                bid_scene=bid_scene,
                custom_cost_cap_fen=custom_cost_cap_fen,
                automatic_site_enabled=(
                    placement.automatic_site_enabled if placement is not None else None
                ),
                site_set=(placement.site_set if placement is not None else None),
                age_min=age_min,
                age_max=age_max,
                location_types=(
                    geo_location.location_types if geo_location is not None else None
                ),
                region_ids=(geo_location.region_ids if geo_location is not None else None),
                config_date=row_config_date,
            ))
            stats["enabled"] += 1
        except Exception as e:
            stats["errors"] += 1
            print(f"row={row_num} ERROR {e}")

    if strict and stats["errors"]:
        print("sync summary:", json.dumps(stats, ensure_ascii=False))
        raise RuntimeError(f"飞书配置表存在 {stats['errors']} 行错误,停止本轮自动化")

    for account_id in disabled_accounts:
        set_account_automation_enabled(
            account_id,
            False,
            config_date=target_config_date,
            dry_run=dry_run,
        )
    for record in enabled_records:
        upsert_account_config(record, dry_run=dry_run)

    print("sync summary:", json.dumps(stats, ensure_ascii=False))
    return stats


def main() -> int:
    """命令行入口：解析参数并执行同步，存在错误行且未放行时返回 1"""
    parser = argparse.ArgumentParser()
    parser.add_argument("--spreadsheet-token", default=DEFAULT_SPREADSHEET_TOKEN)
    parser.add_argument("--sheet-id", default=DEFAULT_SHEET_ID)
    parser.add_argument("--range", default=DEFAULT_RANGE)
    parser.add_argument("--config-date", help="历史复现时只同步该日期；默认每账户取最新一行")
    parser.add_argument("--dry-run", action="store_true")
    parser.add_argument("--allow-row-errors", action="store_true")
    args = parser.parse_args()

    setup_logging(level=os.getenv("LOG_LEVEL", "INFO"))
    stats = sync_from_feishu(
        spreadsheet_token=args.spreadsheet_token,
        sheet_id=args.sheet_id,
        cell_range=args.range,
        config_date=args.config_date,
        dry_run=args.dry_run,
        strict=not args.allow_row_errors,
    )
    return 1 if stats["errors"] and not args.allow_row_errors else 0


if __name__ == "__main__":
    raise SystemExit(main())
