﻿from lk import *
import csv, io, re, datetime as dt
URL = "https://hisense.feishu.cn/sheets/NYdOsiusVhawmitwPsGcPKv5n5b"
SHEETS = ["W45完成情况", "W46完成情况", "完成情况填报", "W46活动提报", "W47活动提报", "活动提报"]
OFF = set(__import__("schema").OFFICE_NAMES)
TMAP = {"门店主推活动":"门店主推活动","门店主推":"门店主推活动","门店活动":"门店主推活动","抖音直播活动":"抖音直播活动","抖音活动":"抖音直播活动",
        "异业":"异业联动","异业联动":"异业联动","政企活动":"政企活动","其他":"其他","小区运营":"小区运营","家博会":"家博会",
        "璀璨新风品鉴会":"璀璨新风品鉴会","品鉴会":"璀璨新风品鉴会","培训订货会":"培训订货会","订货会":"培训订货会"}
OMAP = {"五星": "京东五星"}

def read_sheet(name):
    out = f"sheet_{len(name)}_{abs(hash(name))%10000}.json"
    lk("sheets", "+csv-get", "--url", URL, "--sheet-name", name, "--output-path", "./" + out)
    j = json.load(open(out, encoding="utf-8-sig")); os.remove(out)
    j = j.get("data", j)
    lines = [re.sub(r"^\[row=\d+\] ", "", l) for l in j["annotated_csv"].split("\n")]
    rows = list(csv.reader(io.StringIO("\n".join(lines))))
    head = rows[0]
    return [dict(zip(head, r)) for r in rows[1:] if any(c.strip() for c in r)]

def d(md, ref_year=2026):
    m, dd = [int(x) for x in md.strip().split(".")]
    return dt.date(2027 if m < 6 else ref_year, m, dd)

def dates(s):
    s = s.replace("—", "-").replace("~", "-").strip()
    a = s.split("-")
    st = d(a[0]); en = d(a[1]) if len(a) > 1 and a[1].strip() else st
    return st.isoformat(), en.isoformat()

def numv(x):
    x = (x or "").strip()
    if x == "": return None
    return float(x)

def g(r, *keys):
    for k in keys:
        if k in r and r[k].strip() != "": return r[k].strip()
    return ""

def sysref(x):
    x = (x or "").strip()
    if not x: return None
    m = re.match(r"^\s*(-?[\d.]+)万?(?:/(-?\d+)套)?(?:/(-?\d+)挂)?\s*$", x)
    if not m: return ("UNPARSED", x)
    a, b, c = m.groups()
    return (float(a), int(b) if b else None, int(c) if c else None)

plan = []
seen = set()
for sh in SHEETS:
    rows = read_sheet(sh)
    wk_default = re.match(r"^(W\d+)", sh).group(1) if re.match(r"^W\d+", sh) else ""
    for r in rows:
        name = g(r, "活动名称")
        if not name: continue
        wk = g(r, "周次") or wk_default
        if not wk:
            plan.append(("SKIP-no-week", sh, name)); continue
        if int(wk[1:]) < 45:
            continue
        key = (wk, name)
        if key in seen: continue
        seen.add(key)
        off = OMAP.get(g(r, "办事处"), g(r, "办事处"))
        typ = TMAP.get(g(r, "活动类型"))
        st, en = dates(g(r, "活动日期"))
        act = {"办事处": [off], "周次": [wk], "活动名称": name, "活动类型": [typ], "开始日期": st, "结束日期": en,
               "门店全称": g(r, "门店全称"), "商家全称": g(r, "商家全称"), "填报人": g(r, "负责人"), "数据来源": ["历史导入"]}
        for src, dst in [(("销额目标(万)",), "销额目标（万）"), (("销量目标(套)",), "销量目标（套）"), (("新风三柜三挂目标(套)", "三挂目标(套)"), "新风三柜三挂目标（套）")]:
            v = numv(g(r, *src))
            if v is not None: act[dst] = v
        if g(r, "备注"): act["备注"] = g(r, "备注")
        actual = {}
        for src, dst in [(("销额实际(万)",), "销额实际（万）"), (("销量实际(套)",), "销量实际（套）"), (("新风三柜三挂完成(套)", "三挂实际(套)"), "新风三柜三挂完成（套）")]:
            v = numv(g(r, *src))
            if v is not None: actual[dst] = v
        sr = sysref(g(r, "系统参考(万/套/挂)"))
        problems = [p for p, ok in [("office:" + off, off in OFF), ("type:" + g(r, "活动类型"), typ is not None)] if not ok]
        plan.append(("OK" if not problems else "BAD:" + ",".join(problems), sh, act, actual, sr))
json.dump(plan, open("import_plan.json", "w", encoding="utf-8"), ensure_ascii=False, indent=1)
for p in plan:
    if p[0] != "OK" or True:
        a = p[2] if isinstance(p[2], dict) else {}
        print(p[0], p[1], a.get("周次"), a.get("办事处"), a.get("活动名称"), a.get("活动类型"), a.get("开始日期"), a.get("结束日期"), "| 实际:", p[3] if len(p) > 3 else "", "| 系统:", p[4] if len(p) > 4 else "")
print("total", len(plan))
