#!/usr/bin/env python3
"""把模糊到货文本（"5月中旬"/"6月下旬"等）解析成真实日期区间，回填
purchase_orders.expected_arrival_start/end 与 order_lines.expected_arrival_start/end。

规则：
  X月上旬/初 -> (X-01, X-10)；X月中旬 -> (X-11, X-20)；X月下旬/底/末 -> (X-21, 月末)；
  只写 X月 -> 整月。年份取订单年份；到货月份 < 订单月份则视为跨年 +1。
  解析不了的文本（"待确认"等）跳过，原文本保留不动。
  只填空，不覆盖已有日期。--apply 落库并写审计，默认 dry-run。
"""
from __future__ import annotations

import calendar
import json
import re
import sys
from datetime import date
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "backend"))
from app import db  # noqa: E402

APPLY = "--apply" in sys.argv
ACTOR = "backfill-script/arrival-dates"
PAT = re.compile(r"(\d{1,2})\s*月\s*(上旬|中旬|下旬|初|底|末)?")


def parse_arrival(text: str | None, order_date: date | None) -> tuple[date, date] | None:
    if not text or not order_date:
        return None
    m = PAT.search(text)
    if not m:
        return None
    month = int(m.group(1))
    part = m.group(2)
    if not 1 <= month <= 12:
        return None
    year = order_date.year + (1 if month < order_date.month else 0)
    eom = calendar.monthrange(year, month)[1]
    if part in ("上旬", "初"):
        rng = (1, 10)
    elif part == "中旬":
        rng = (11, 20)
    elif part in ("下旬", "底", "末"):
        rng = (21, eom)
    else:
        rng = (1, eom)
    return date(year, month, rng[0]), date(year, month, min(rng[1], eom))


def main() -> None:
    conn = db.connect()
    cur = conn.cursor()
    plans: list[tuple[str, str, str, date, date, str]] = []  # (table, id, text, start, end, label)

    cur.execute("""
        select id, order_no, order_code, order_date, expected_arrival_text, expected_arrival_start
        from purchase_orders
        where expected_arrival_text is not null and expected_arrival_start is null
    """)
    for r in cur.fetchall():
        parsed = parse_arrival(r["expected_arrival_text"], r["order_date"])
        label = r["order_no"] or r["order_code"]
        if parsed:
            plans.append(("purchase_orders", str(r["id"]), r["expected_arrival_text"], parsed[0], parsed[1], label))
            print(f"  [po  ] {label}: {r['expected_arrival_text']!r} -> {parsed[0]} ~ {parsed[1]}")
        else:
            print(f"  [skip] {label}: {r['expected_arrival_text']!r} 无法解析，保留原文本")

    cur.execute("""
        select ol.id, ol.expected_arrival_text, ol.expected_arrival_start, po.order_date,
               coalesce(po.order_no, po.order_code) as order_no, ol.line_no
        from order_lines ol join purchase_orders po on po.id = ol.order_id
        where ol.expected_arrival_text is not null and ol.expected_arrival_start is null
    """)
    for r in cur.fetchall():
        parsed = parse_arrival(r["expected_arrival_text"], r["order_date"])
        label = f"{r['order_no']} line{r['line_no']}"
        if parsed:
            plans.append(("order_lines", str(r["id"]), r["expected_arrival_text"], parsed[0], parsed[1], label))
            print(f"  [line] {label}: {r['expected_arrival_text']!r} -> {parsed[0]} ~ {parsed[1]}")
        else:
            print(f"  [skip] {label}: {r['expected_arrival_text']!r} 无法解析")

    po_n = sum(1 for p in plans if p[0] == "purchase_orders")
    print(f"\n计划: purchase_orders {po_n} 行, order_lines {len(plans) - po_n} 行")
    if not APPLY:
        print("[DRY-RUN] 未落库。加 --apply 执行。")
        conn.rollback()
        return
    for table, row_id, _text, start, end, _label in plans:
        cur.execute(
            f"update {table} set expected_arrival_start=%s, expected_arrival_end=%s where id=%s"
            " and expected_arrival_start is null",
            (start, end, row_id),
        )
    cur.execute(
        """
        insert into agent_write_audit(agent_profile, tool_name, operation, target_table, request_payload, result_status)
        values (%s,%s,%s,%s,%s,%s)
        """,
        (ACTOR, "backfill_arrival_dates", "update", "purchase_orders+order_lines",
         json.dumps({"po_rows": po_n, "line_rows": len(plans) - po_n}, ensure_ascii=False), "success"),
    )
    conn.commit()
    print(f"已提交并写审计: po={po_n} lines={len(plans) - po_n}")


if __name__ == "__main__":
    main()