#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
审核后端服务（薪人薪事 功能调研）。
- 打开 http://localhost:8788/ 进入"一页一个功能"的审核界面
- 每次点击 保留/删除 立即写回 SQLite(features.keep)
- 提供 API: 功能列表 / 更新保留状态 / 备注 / 附件清单 / Excel 预览 / 导出 selection.json
依赖: Flask
用法: python serve.py   然后浏览器打开 http://localhost:8788
"""
import os, json, sqlite3, time, re
from flask import Flask, request, jsonify, send_from_directory, Response

BASE = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DB = os.path.join(BASE, "db", "research.db")
DOCS = os.path.join(BASE, "docs")
DL_ROOT = os.path.join(BASE, "downloads")
REVIEW_HTML = os.path.join(DOCS, "审核.html")
EXPORT_JSON = os.path.join(DOCS, "selection.json")
EXCEL_EXT = (".xlsx", ".xls", ".docx", ".doc", ".pdf")

app = Flask(__name__)


def db():
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row
    return conn


def load_features():
    conn = db()
    rows = [dict(r) for r in conn.execute(
        "SELECT node_key,parent_key,level,name,full_path,url,description,"
        "understanding,screenshot_path,sort_order,keep,note FROM features "
        "ORDER BY sort_order,id").fetchall()]
    kind_cn = {"add": "主操作弹窗/表单", "filter": "筛选面板",
               "columns": "自定义列/选择表头", "detail": "行详情/编辑"}
    deep = {}
    try:
        for r in conn.execute("SELECT node_key,kind,label,path FROM page_shots "
                              "WHERE kind<>'main' ORDER BY node_key,id").fetchall():
            deep.setdefault(r["node_key"], []).append({
                "kind": r["kind"], "kind_cn": kind_cn.get(r["kind"], r["kind"]),
                "label": r["label"] or "", "shot": "/shot/" + r["path"]})
    except sqlite3.OperationalError:
        pass
    dls = {}
    try:
        for r in conn.execute("SELECT node_key,file_path,size,kind FROM downloads "
                              "WHERE node_key IS NOT NULL AND node_key<>'' ORDER BY file_path").fetchall():
            fp = r["file_path"] or ""
            name = fp.rsplit("/", 1)[-1]
            sz = r["size"]
            if not sz:
                ap = os.path.join(BASE, fp)
                sz = os.path.getsize(ap) if os.path.isfile(ap) else 0
            dls.setdefault(r["node_key"], []).append(
                {"name": name, "rel": fp, "size": sz or 0, "kind": r["kind"] or ""})
    except sqlite3.OperationalError:
        pass
    conn.close()
    by_parent = {}
    for r in rows:
        by_parent.setdefault(r["parent_key"] or "", []).append(r)

    leaves = []

    def dfs(pk, module, group):
        for r in by_parent.get(pk, []):
            m = r["name"] if r["level"] == 0 else module
            g = r["name"] if r["level"] == 1 else group
            children = by_parent.get(r["node_key"])
            if children:
                dfs(r["node_key"], m, g)
            else:
                shot = ("/shot/" + r["screenshot_path"]) if r.get("screenshot_path") else ""
                leaves.append({
                    "node_key": r["node_key"], "name": r["name"], "module": m,
                    "group": g if g and g != r["name"] else "",
                    "full_path": r["full_path"], "url": r["url"] or "",
                    "description": r["description"] or "",
                    "understanding": r["understanding"] or "",
                    "shot": shot, "deep_shots": deep.get(r["node_key"], []),
                    "downloads": dls.get(r["node_key"], []),
                    "keep": 1 if (r["keep"] is None or r["keep"] == 1) else 0,
                    "note": r.get("note") or "",
                })

    dfs("", "", "")
    return leaves


@app.route("/")
def index():
    with open(REVIEW_HTML, "r", encoding="utf-8") as f:
        return Response(f.read(), mimetype="text/html")


@app.route("/api/features")
def api_features():
    feats = load_features()
    keep = sum(1 for f in feats if f["keep"])
    return jsonify({"total": len(feats), "keep": keep, "items": feats})


@app.route("/api/keep", methods=["POST"])
def api_keep():
    data = request.get_json(force=True)
    node_key = data.get("node_key")
    keep = 1 if data.get("keep") else 0
    if not node_key:
        return jsonify({"ok": False, "err": "missing node_key"}), 400
    now = time.strftime("%Y-%m-%d %H:%M:%S")
    conn = db()
    conn.execute("UPDATE features SET keep=?, updated_at=? WHERE node_key=?", (keep, now, node_key))
    conn.commit(); conn.close()
    return jsonify({"ok": True, "node_key": node_key, "keep": keep})


@app.route("/api/keep_batch", methods=["POST"])
def api_keep_batch():
    data = request.get_json(force=True)
    keys = data.get("node_keys") or []
    keep = 1 if data.get("keep") else 0
    now = time.strftime("%Y-%m-%d %H:%M:%S")
    conn = db()
    conn.executemany("UPDATE features SET keep=?, updated_at=? WHERE node_key=?",
                     [(keep, now, k) for k in keys])
    conn.commit(); conn.close()
    return jsonify({"ok": True, "updated": len(keys), "keep": keep})


@app.route("/api/note", methods=["POST"])
def api_note():
    data = request.get_json(force=True)
    node_key = data.get("node_key")
    note = data.get("note") or ""
    if not node_key:
        return jsonify({"ok": False, "err": "missing node_key"}), 400
    now = time.strftime("%Y-%m-%d %H:%M:%S")
    conn = db()
    conn.execute("UPDATE features SET note=?, updated_at=? WHERE node_key=?", (note, now, node_key))
    conn.commit(); conn.close()
    return jsonify({"ok": True, "node_key": node_key})


@app.route("/api/export", methods=["POST", "GET"])
def api_export():
    feats = load_features()
    kept = sum(1 for f in feats if f["keep"])
    mods = {}
    for f in feats:
        m = mods.setdefault(f["module"], {"module": f["module"], "total": 0, "keep": 0})
        m["total"] += 1
        m["keep"] += f["keep"]

    def item(f):
        return {
            "module": f["module"], "group": f["group"], "name": f["name"],
            "node_key": f["node_key"], "full_path": f["full_path"], "url": f["url"],
            "keep": f["keep"], "description": f["description"],
            "understanding": f["understanding"], "note": f["note"],
            "screenshot": f["shot"].replace("/shot/", "") if f["shot"] else "",
        }

    out = {
        "generated_at": time.strftime("%Y-%m-%dT%H:%M:%S"),
        "source": "薪人薪事 https://s-demob.xinrenxinshi.show 功能调研",
        "total": len(feats), "keep": kept, "dropped": len(feats) - kept,
        "module_summary": sorted(mods.values(), key=lambda x: -x["keep"]),
        "items": [item(f) for f in feats],
        "kept_items": [item(f) for f in feats if f["keep"]],
    }
    os.makedirs(DOCS, exist_ok=True)
    with open(EXPORT_JSON, "w", encoding="utf-8") as f:
        json.dump(out, f, ensure_ascii=False, indent=2)
    return jsonify({"ok": True, "path": "docs/selection.json",
                    "total": out["total"], "keep": out["keep"], "dropped": out["dropped"]})


@app.route("/shot/<path:relpath>")
def shot(relpath):
    return send_from_directory(BASE, relpath)


def _strip_num(s):
    return re.sub(r"^\d+_", "", s or "").strip()


def scan_attachments():
    groups = []
    if not os.path.isdir(DL_ROOT):
        return groups
    for root, _, files in os.walk(DL_ROOT):
        fs = [f for f in files if f.lower().endswith(EXCEL_EXT)]
        if not fs:
            continue
        rel = os.path.relpath(root, DL_ROOT)
        segs = [] if rel == "." else rel.split(os.sep)
        seg0 = segs[0] if segs else ""
        module = "任务中心" if seg0.startswith("_任务") else _strip_num(seg0)
        group = segs[1] if len(segs) > 1 else ""
        items = []
        for f in sorted(fs):
            p = os.path.join(root, f)
            items.append({"name": f, "size": os.path.getsize(p),
                          "rel": os.path.relpath(p, BASE)})
        groups.append({"dir": rel.replace(os.sep, " / "), "module": module,
                       "group": group, "files": items})
    groups.sort(key=lambda g: g["dir"])
    return groups


@app.route("/api/attachments")
def api_attachments():
    gs = scan_attachments()
    total = sum(len(g["files"]) for g in gs)
    return jsonify({"total": total, "groups": gs})


@app.route("/dl/<path:relpath>")
def dl(relpath):
    d = os.path.dirname(relpath)
    fn = os.path.basename(relpath)
    return send_from_directory(os.path.join(BASE, d), fn, as_attachment=True)


SHEET_EXT = (".xlsx", ".xls")
MAX_ROWS = 500
MAX_COLS = 60


def _parse_xlsx(path):
    import openpyxl
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    sheets = []
    for ws in wb.worksheets:
        rows = []
        for i, row in enumerate(ws.iter_rows(values_only=True)):
            if i >= MAX_ROWS:
                break
            cells = [("" if v is None else str(v)) for v in row[:MAX_COLS]]
            rows.append(cells)
        sheets.append({"name": ws.title, "rows": rows,
                       "total_rows": ws.max_row, "total_cols": ws.max_column})
    wb.close()
    return sheets


def _parse_xls(path):
    import xlrd, warnings
    warnings.filterwarnings("ignore")
    wb = xlrd.open_workbook(path)
    sheets = []
    for ws in wb.sheets():
        rows = []
        for r in range(min(MAX_ROWS, ws.nrows)):
            cells = []
            for cc in range(min(MAX_COLS, ws.ncols)):
                v = ws.cell_value(r, cc)
                if isinstance(v, float) and v == int(v):
                    v = int(v)
                cells.append("" if v == "" else str(v))
            rows.append(cells)
        sheets.append({"name": ws.name, "rows": rows,
                       "total_rows": ws.nrows, "total_cols": ws.ncols})
    return sheets


@app.route("/api/preview")
def api_preview():
    rel = request.args.get("rel", "")
    abspath = os.path.abspath(os.path.join(BASE, rel))
    if not abspath.startswith(DL_ROOT + os.sep) or not os.path.isfile(abspath):
        return jsonify({"ok": False, "err": "file not found or forbidden"}), 404
    low = abspath.lower()
    if not low.endswith(SHEET_EXT):
        return jsonify({"ok": False, "err": "not a spreadsheet", "kind": "other"}), 200
    try:
        sheets = _parse_xls(abspath) if low.endswith(".xls") else _parse_xlsx(abspath)
    except Exception as e:
        return jsonify({"ok": False, "err": str(e)[:200]}), 200
    return jsonify({"ok": True, "name": os.path.basename(abspath),
                    "rel": rel, "sheets": sheets,
                    "max_rows": MAX_ROWS, "max_cols": MAX_COLS})


if __name__ == "__main__":
    app.run(host="127.0.0.1", port=8788, debug=False, threaded=True)