#!/usr/bin/env python3
"""Create a WeCom smart sheet from the approved deployment-intake workbook.

The script calls the official WeCom robot CLI. It never reads or stores robot
credentials: authentication remains in the CLI's local secure configuration.
"""

from __future__ import annotations

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

from openpyxl import load_workbook
from openpyxl.utils.cell import range_boundaries


PRODUCT_SHEETS = ("智慧营养结算", "智慧食安", "智慧采购")
FIELD_SPECS = (
    ("售前锁定项", "FIELD_TYPE_TEXT"),
    ("选择（下拉）", "FIELD_TYPE_SINGLE_SELECT"),
    ("一句话补充 / 量化事实", "FIELD_TYPE_TEXT"),
    ("责任方", "FIELD_TYPE_SINGLE_SELECT"),
    ("完成日期", "FIELD_TYPE_DATE_TIME"),
    ("状态", "FIELD_TYPE_SINGLE_SELECT"),
)


def value_as_text(value: Any) -> str:
    if value is None:
        return ""
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d %H:%M:%S")
    if isinstance(value, date):
        return value.isoformat()
    return str(value).strip()


def validation_options(sheet: Any, column: int, valid_rows: set[int]) -> list[str]:
    options: list[str] = []
    for validation in sheet.data_validations.dataValidation:
        applies = False
        for cell_range in validation.sqref.ranges:
            min_col, min_row, max_col, max_row = range_boundaries(str(cell_range))
            if min_col <= column <= max_col and valid_rows.intersection(range(min_row, max_row + 1)):
                applies = True
                break
        formula = value_as_text(validation.formula1)
        if not applies or not (formula.startswith('"') and formula.endswith('"')):
            continue
        for option in formula[1:-1].split(','):
            option = option.strip()
            if option and option not in options:
                options.append(option)
    return options


def read_product_rows(path: Path) -> tuple[dict[str, list[dict[str, str]]], dict[str, dict[str, list[str]]]]:
    workbook = load_workbook(path, data_only=True, read_only=False)
    missing = [name for name in PRODUCT_SHEETS if name not in workbook.sheetnames]
    if missing:
        raise ValueError(f"workbook is missing sheets: {', '.join(missing)}")

    result: dict[str, list[dict[str, str]]] = {}
    options_by_product: dict[str, dict[str, list[str]]] = {}
    for name in PRODUCT_SHEETS:
        sheet = workbook[name]
        records = []
        for row in range(5, sheet.max_row + 1):
            item = value_as_text(sheet.cell(row, 2).value)
            if not item or item == "售前阶段":
                continue
            records.append(
                {
                    "售前锁定项": item,
                    "选择（下拉）": value_as_text(sheet.cell(row, 3).value),
                    "一句话补充 / 量化事实": value_as_text(sheet.cell(row, 4).value),
                    "责任方": value_as_text(sheet.cell(row, 5).value),
                    "完成日期": value_as_text(sheet.cell(row, 6).value),
                    "状态": value_as_text(sheet.cell(row, 7).value),
                }
            )
        if len(records) != 6:
            raise ValueError(f"{name} should contain exactly six intake rows, found {len(records)}")
        result[name] = records
        valid_rows = {
            row
            for row in range(5, sheet.max_row + 1)
            if value_as_text(sheet.cell(row, 2).value)
            and value_as_text(sheet.cell(row, 2).value) != "售前阶段"
        }
        options_by_product[name] = {
            "选择（下拉）": validation_options(sheet, 3, valid_rows),
            "责任方": validation_options(sheet, 5, valid_rows),
            "状态": validation_options(sheet, 7, valid_rows),
        }
    return result, options_by_product


def unwrap_cli_output(stdout: str) -> dict[str, Any]:
    outer = json.loads(stdout)
    result = outer.get("result", outer)
    if result.get("isError"):
        raise RuntimeError(json.dumps(result, ensure_ascii=False))
    content = result.get("content", [])
    if not content or content[0].get("type") != "text":
        raise RuntimeError(f"unexpected WeCom CLI result: {json.dumps(result, ensure_ascii=False)}")
    payload = json.loads(content[0]["text"])
    if payload.get("errcode", 0) != 0:
        raise RuntimeError(f"WeCom API error {payload.get('errcode')}: {payload.get('errmsg')}")
    return payload


def call_robot(cli: str, command: str, payload: dict[str, Any]) -> dict[str, Any]:
    process = subprocess.run(
        [cli, "doc", command, "--json", json.dumps(payload, ensure_ascii=False)],
        capture_output=True,
        text=True,
        check=False,
    )
    if process.returncode:
        raise RuntimeError(process.stderr.strip() or process.stdout.strip())
    return unwrap_cli_output(process.stdout)


def text_cell(value: str) -> list[dict[str, str]]:
    return [{"type": "text", "text": value}]


def select_cell(value: str) -> list[dict[str, str]]:
    return [{"text": value}]


def seed_select_options(cli: str, docid: str, sheet_id: str, options: dict[str, list[str]]) -> None:
    count = max((len(values) for values in options.values()), default=0)
    if not count:
        return
    seeds = []
    for index in range(count):
        values: dict[str, Any] = {"售前锁定项": text_cell("__机器人下拉选项初始化（自动删除）__")}
        for field_title, field_options in options.items():
            if index < len(field_options):
                values[field_title] = select_cell(field_options[index])
        seeds.append({"values": values})
    seeded = call_robot(cli, "smartsheet_add_records", {"docid": docid, "sheet_id": sheet_id, "records": seeds})
    record_ids = [record["record_id"] for record in seeded.get("records", []) if record.get("record_id")]
    if len(record_ids) != len(seeds):
        raise RuntimeError("robot did not return every option-initialization record id")
    call_robot(cli, "smartsheet_delete_records", {"docid": docid, "sheet_id": sheet_id, "record_ids": record_ids})


def configure_sheet(
    cli: str,
    docid: str,
    sheet_id: str,
    rows: list[dict[str, str]],
    options: dict[str, list[str]],
) -> None:
    current = call_robot(cli, "smartsheet_get_fields", {"docid": docid, "sheet_id": sheet_id})
    fields = current.get("fields", [])
    if len(fields) != 1:
        raise RuntimeError(f"new sheet {sheet_id} should have one default field, found {len(fields)}")
    default = fields[0]
    call_robot(
        cli,
        "smartsheet_update_fields",
        {
            "docid": docid,
            "sheet_id": sheet_id,
            "fields": [
                {
                    "field_id": default["field_id"],
                    "field_type": default["field_type"],
                    "field_title": FIELD_SPECS[0][0],
                }
            ],
        },
    )
    call_robot(
        cli,
        "smartsheet_add_fields",
        {
            "docid": docid,
            "sheet_id": sheet_id,
            "fields": [
                {"field_title": title, "field_type": field_type}
                for title, field_type in FIELD_SPECS[1:]
            ],
        },
    )

    records = []
    for row in rows:
        values: dict[str, Any] = {"售前锁定项": text_cell(row["售前锁定项"])}
        if row["选择（下拉）"]:
            values["选择（下拉）"] = select_cell(row["选择（下拉）"])
        if row["一句话补充 / 量化事实"]:
            values["一句话补充 / 量化事实"] = text_cell(row["一句话补充 / 量化事实"])
        if row["责任方"]:
            values["责任方"] = select_cell(row["责任方"])
        if row["完成日期"]:
            values["完成日期"] = row["完成日期"]
        if row["状态"]:
            values["状态"] = select_cell(row["状态"])
        records.append({"values": values})
    call_robot(cli, "smartsheet_add_records", {"docid": docid, "sheet_id": sheet_id, "records": records})
    seed_select_options(cli, docid, sheet_id, options)


def create_online_table(
    cli: str,
    title: str,
    rows_by_product: dict[str, list[dict[str, str]]],
    options_by_product: dict[str, dict[str, list[str]]],
    checkpoint: Path,
) -> dict[str, Any]:
    created = call_robot(cli, "create_doc", {"doc_type": 10, "doc_name": title})
    docid = created["docid"]
    url = created["url"]
    receipt: dict[str, Any] = {"title": title, "docid": docid, "url": url, "status": "created-not-configured"}
    checkpoint.parent.mkdir(parents=True, exist_ok=True)
    checkpoint.write_text(json.dumps(receipt, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
    initial = call_robot(cli, "smartsheet_get_sheet", {"docid": docid}).get("sheet_list", [])
    if len(initial) != 1:
        raise RuntimeError(f"new smart sheet should contain one sub-sheet, found {len(initial)}")

    sheet_ids: dict[str, str] = {}
    first_id = initial[0]["sheet_id"]
    call_robot(
        cli,
        "smartsheet_update_sheet",
        {"docid": docid, "properties": {"sheet_id": first_id, "title": PRODUCT_SHEETS[0]}},
    )
    sheet_ids[PRODUCT_SHEETS[0]] = first_id
    for product in PRODUCT_SHEETS[1:]:
        added = call_robot(cli, "smartsheet_add_sheet", {"docid": docid, "properties": {"title": product}})
        sheet_id = added.get("sheet_id")
        if not sheet_id:
            sheets = call_robot(cli, "smartsheet_get_sheet", {"docid": docid}).get("sheet_list", [])
            sheet_id = next((sheet["sheet_id"] for sheet in sheets if sheet.get("title") == product), None)
        if not sheet_id:
            raise RuntimeError(f"could not determine the new sub-sheet id for {product}")
        sheet_ids[product] = sheet_id

    for product in PRODUCT_SHEETS:
        configure_sheet(cli, docid, sheet_ids[product], rows_by_product[product], options_by_product[product])
    receipt.update({"sheets": sheet_ids, "status": "configured"})
    checkpoint.write_text(json.dumps(receipt, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
    return receipt


def main() -> None:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--xlsx", type=Path, required=True, help="Approved six-row deployment workbook")
    parser.add_argument("--title", required=True, help="WeCom online smart-sheet title")
    parser.add_argument("--output", type=Path, required=True, help="JSON receipt with the created document URL and id")
    parser.add_argument("--execute", action="store_true", help="Perform the external robot calls")
    parser.add_argument(
        "--cli",
        default=os.environ.get(
            "WECOM_CLI_BIN",
            str(Path.home() / ".cache/zhctprompt/wecom-cli/0.1.8/node_modules/.bin/wecom-cli"),
        ),
        help="Official WeCom CLI path; credentials stay in its own secure config",
    )
    args = parser.parse_args()
    rows, options = read_product_rows(args.xlsx)
    if not args.execute:
        print(json.dumps({"title": args.title, "products": {name: len(rows[name]) for name in PRODUCT_SHEETS}, "would_create": True}, ensure_ascii=False))
        return
    if not Path(args.cli).is_file():
        raise SystemExit(f"WeCom CLI not found: {args.cli}")
    receipt = create_online_table(args.cli, args.title, rows, options, args.output)
    print(json.dumps(receipt, ensure_ascii=False))


if __name__ == "__main__":
    main()