# -*- coding: utf-8 -*-
"""构建「辖区周排班」单文件网页工具：读两张源表 -> 注入数据 -> 输出 HTML。"""
import json, os, sys, io, re, datetime
import openpyxl

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')
BASE = r"D:\workbbbbbb\v2"
SRC_ORG = os.path.join(BASE, r"workb\杭州机构表.xlsx")
SRC_PPL = os.path.join(BASE, r"workb\人员信息表.xlsx")
B = os.path.join(BASE, "build")
OUT = os.path.join(BASE, "排班工具_就近版.html")

# ---------- 机构表 ----------
wb = openpyxl.load_workbook(SRC_ORG, data_only=True)
ws = wb.worksheets[0]
orgs, seen, areas = [], set(), []
for code, name, r1, r2, note in ws.iter_rows(min_row=2, values_only=True):
    if not name:
        continue
    o = {"c": str(code or "").strip(), "n": str(name).strip(),
         "r1": str(r1 or "").strip(), "r2": str(r2 or "").strip()}
    orgs.append(o)
    k = (o["r1"], o["r2"])
    if k not in seen:
        seen.add(k)
        areas.append({"r1": o["r1"], "r2": o["r2"]})

# ---------- 小区 -> (行政区, 归属辖区, 备注) ----------
HOME_MAP = {
    "崇盛里":     ("拱墅区", "拱墅辖区", "拱宸桥街道 崇盛里（OSM 实测坐标）"),
    "风来旭和府": ("临平区", "临平辖区", "乔司板块，街道待确认（OSM 查不到，坐标为估算）"),
    "如玺华院":   ("临平区", "临平辖区", "乔司板块，街道待确认（OSM 查不到，坐标为估算）"),
    "桦枫居":     ("拱墅区", "下城辖区", "石桥街道杭玻街（OSM 实测），石桥支行归下城辖区"),
    "吉鸿家园":   ("西湖区", "城西辖区", "三墩镇周坪路（OSM 实测），三墩/厚仁路支行归城西辖区"),
    "盛世钱塘":   ("上城区", "江干辖区", "钱江路（OSM 实测），四季青一带归江干辖区"),
    "海上海":     ("余杭区", "余杭辖区", "良渚街道（OSM 实测），良渚/勾庄/瓶窑一带归余杭辖区"),
    "慧仁家园":   ("西湖区", "城西辖区", "三墩镇荡鱼街（OSM 实测）"),
    "圣苑":       ("西湖区", "西湖辖区", "街道待确认（OSM 查不到，坐标为估算）"),
    "圣苑小区":   ("西湖区", "西湖辖区", "街道待确认（OSM 查不到，坐标为估算）"),
    "水韵康桥康泽苑": ("拱墅区", "拱墅辖区", "康桥街道康桥路（OSM 命中康盛苑），最近的拱宸桥支行归拱墅辖区"),
}

# ---------- 人员表（按表头取列，支持「负责辖区」多列） ----------
wb2 = openpyxl.load_workbook(SRC_PPL, data_only=True)
ws2 = wb2.worksheets[0]
hdr = {}
for c in ws2[1]:
    v = str(c.value or "").strip()
    if v:
        hdr.setdefault(v, []).append(c.column)
def col(names, default=None):
    for n in names:
        if n in hdr:
            return hdr[n][0]
    return default
C_NAME = col(["名称", "姓名"], 1)
C_STAT = col(["状态"], 2)
C_DUR = col(["状态持续时间", "请假时间"], 3)
C_HOME = col(["住址"], 4)
C_ADDR = col(["详细地址"], 5)
C_NOTE = col(["解析备注"], None)
AREA_COLS = sorted(hdr.get("负责辖区", []))
people, i = [], 0
for r in range(2, ws2.max_row + 1):
    nm = ws2.cell(r, C_NAME).value
    if not nm:
        continue
    i += 1
    home = str(ws2.cell(r, C_HOME).value or "").strip()
    d = HOME_MAP.get(home, ("", "", "未识别住址"))
    pAr = []
    for c in AREA_COLS:
        v = str(ws2.cell(r, c).value or "").strip()
        if v:
            pAr.append(v)
    addr = str(ws2.cell(r, C_ADDR).value or "").strip() if C_ADDR else ""
    note_cell = str(ws2.cell(r, C_NOTE).value or "").strip() if C_NOTE else ""
    NOTE_MARKS = ("已录小区：", "未识别：", "网点地名：", "区县中心：")
    addr_is_note = any(addr.startswith(k) for k in NOTE_MARKS)
    addr_full = (d[2] if d else "") if (addr_is_note or not addr) else addr
    addr_note = note_cell or (addr if addr_is_note else (d[2] if d else ""))
    people.append({
        "id": "p%d" % i,
        "n": str(nm).strip(),
        "home": home,
        "district": d[0],
        "autoArea": d[1],
        "area": pAr[0] if pAr else d[1],
        "areas": pAr if pAr else ([d[1]] if d[1] else []),
        "manual": bool(pAr),
        "addrFull": addr_full,
        "addrNote": addr_note,
        "status": str(ws2.cell(r, C_STAT).value or "在岗").strip(),
        "leaveText": str(ws2.cell(r, C_DUR).value or "").strip(),
        "leaveStart": "",
        "leaveEnd": "",
        "conf": "infer",
    })

YEAR = 2026
def parse_leave(txt):
    if not txt:
        return "", ""
    s = txt.replace("—", "-").replace("～", "-").replace("~", "-").replace("到", "-").replace("至", "-")
    md = []
    for p in re.split(r"[-\s]+", s):
        m = re.match(r"(\d{4})?年?(\d{1,2})月(\d{1,2})日?", p.strip())
        if m:
            md.append((int(m.group(1)) if m.group(1) else YEAR, int(m.group(2)), int(m.group(3))))
    if len(md) >= 2:
        a, b = md[0], md[1]
        if b[0] == a[0] and (b[1], b[2]) < (a[1], a[2]):
            b = (b[0] + 1, b[1], b[2])
        return "%04d-%02d-%02d" % a, "%04d-%02d-%02d" % b
    return "", ""

for p in people:
    p["leaveStart"], p["leaveEnd"] = parse_leave(p["leaveText"])

today = datetime.date.today()
week_start = today + datetime.timedelta(days=(7 - today.weekday()) % 7 or 7)

geo = {}
_gp = os.path.join(B, "geodb.json")
if os.path.exists(_gp):
    with open(_gp, "r", encoding="utf-8") as f:
        geo = json.load(f)

data = {
    "orgs": orgs,
    "areas": areas,
    "homes": {k: list(v) for k, v in HOME_MAP.items()},
    "people": people,
    "geo": geo,
    "weekStart": week_start.isoformat(),
    "builtAt": datetime.datetime.now().strftime("%Y-%m-%d %H:%M"),
}

def rd(name):
    with open(os.path.join(B, name), "r", encoding="utf-8") as f:
        return f.read()

html = rd("shell.html")
html = html.replace("/*__CSS__*/", rd("app.css"))
html = html.replace("<!--__BODY__-->", rd("body.html"))
html = html.replace("/*__JS__*/", rd("app.js"))
html = html.replace("/*__DATA__*/null", json.dumps(data, ensure_ascii=False, separators=(",", ":")))

# 占位符必须全部替换掉
for ph in ("/*__CSS__*/", "/*__JS__*/", "/*__DATA__*/", "<!--__BODY__-->"):
    if ph in html:
        raise SystemExit("占位符未替换: " + ph)

with open(OUT, "w", encoding="utf-8") as f:
    f.write(html)

print("orgs=%d areas=%d people=%d weekStart=%s" % (len(orgs), len(areas), len(people), data["weekStart"]))
print("geo=%d orgs / %d homes / %d distPairs / drive=%d" % (
    len(geo.get("orgs", [])), len(geo.get("homes", [])),
    sum(len(v) for v in geo.get("dist", {}).values()),
    sum(len(v) for v in geo.get("drive", {}).values())))
print("out=%s size=%.1fKB" % (OUT, os.path.getsize(OUT) / 1024.0))
for p in people:
    print("  %-4s %-6s %-4s %-8s %-3s %s..%s" % (
        p["n"], p["home"], p["district"], p["area"], p["status"],
        p["leaveStart"] or "-", p["leaveEnd"] or "-"))