# -*- coding: utf-8 -*-
"""园区租户（多租户）—— SQLite 持久化（serverdata/park/park.db，后续可迁 MySQL/等）。

表：
  park_tenants(id, name, intro_json, username, salt, password_hash, data_json, agent_json, created_at)
  park_companies(id, tenant_id, name, zone, room, industry, bio, founder, status, employees, created_at)
  park_kb_docs(id, tenant_id, grp, title, content_md, created_at)

data_json（大屏锚点：as_of/token/projects/jobs/revenue/cumulative/park/zones/industryMix/feed）
与 agent_json 用 JSON 列（灵活、易迁移）；companies/kb 用表（可直接查询/CRUD）。
"""
from __future__ import annotations

import hashlib
import json
import secrets
import sqlite3
import time
import uuid
from pathlib import Path

from .config import PARK_DIR
from .sim_engine import DEFAULT_DATA, refresh_engine

DB_PATH = Path(PARK_DIR) / "park.db"
_DEFAULT_TENANT_ID = "T001"

SCHEMA = """
CREATE TABLE IF NOT EXISTS park_tenants (
  id TEXT PRIMARY KEY,
  name TEXT,
  intro_json TEXT,
  username TEXT,
  salt TEXT,
  password_hash TEXT,
  admin_username TEXT DEFAULT '',   -- 绑定的平台账号（园区管理员，园区端登录用）
  status TEXT DEFAULT 'active',     -- active | disabled（禁用则不删、禁止登录/使用）
  data_json TEXT,
  agent_json TEXT,
  created_at TEXT
);
CREATE TABLE IF NOT EXISTS park_companies (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,
  name TEXT,
  zone TEXT DEFAULT '',
  room TEXT DEFAULT '',
  industry TEXT DEFAULT '',
  bio TEXT DEFAULT '',
  founder TEXT DEFAULT '',
  status TEXT DEFAULT 'applying',
  employees INTEGER,
  created_at TEXT
);
CREATE TABLE IF NOT EXISTS park_kb_docs (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,
  grp TEXT DEFAULT 'general',
  title TEXT,
  content_md TEXT DEFAULT '',
  created_at TEXT
);
CREATE TABLE IF NOT EXISTS park_screens (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,
  device_id TEXT,
  name TEXT DEFAULT '',
  role TEXT DEFAULT 'main',
  location TEXT DEFAULT '',
  created_at TEXT
);
"""


def _conn() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def init_db():
    conn = _conn()
    conn.executescript(SCHEMA)
    # 迁移：为旧 park_tenants 表补 admin_username（绑定平台账号作园区管理员）
    cols = [r["name"] for r in conn.execute("PRAGMA table_info(park_tenants)").fetchall()]
    if "admin_username" not in cols:
        conn.execute("ALTER TABLE park_tenants ADD COLUMN admin_username TEXT DEFAULT ''")
    if "status" not in cols:
        conn.execute("ALTER TABLE park_tenants ADD COLUMN status TEXT DEFAULT 'active'")
    conn.commit()
    conn.close()
    ensure_default_tenant()


def _hash_password(password: str, salt: str | None = None) -> tuple[str, str]:
    salt = salt or secrets.token_hex(16)
    h = hashlib.pbkdf2_hmac("sha256", password.encode(), salt.encode(), 100_000).hex()
    return salt, h


def _verify_password(password: str, salt: str, expected: str) -> bool:
    return _hash_password(password, salt)[1] == expected


def _now() -> str:
    return time.strftime("%Y-%m-%d %H:%M:%S")


def _new_id(prefix: str) -> str:
    return f"{prefix}{int(uuid.uuid4().int % 1000000000):09d}"


# ---------------- 租户 ----------------

def _row_to_tenant(row) -> dict:
    return {
        "id": row["id"],
        "name": row["name"],
        "intro": json.loads(row["intro_json"] or "[]"),
        "auth": {"username": row["username"], "salt": row["salt"], "password_hash": row["password_hash"]},
        "admin_username": row["admin_username"] or "",
        "status": row["status"] if "status" in row.keys() else "active",
        "data": json.loads(row["data_json"] or "{}"),
        "agent": json.loads(row["agent_json"] or "{}"),
    }


def list_tenants() -> list[dict]:
    conn = _conn()
    rows = conn.execute("SELECT * FROM park_tenants ORDER BY created_at DESC").fetchall()
    conn.close()
    return [_row_to_tenant(r) for r in rows]


def get_tenant(tenant_id: str) -> dict | None:
    conn = _conn()
    row = conn.execute("SELECT * FROM park_tenants WHERE id=?", (tenant_id,)).fetchone()
    conn.close()
    return _row_to_tenant(row) if row else None


def _get_data(tenant_id: str) -> dict:
    t = get_tenant(tenant_id)
    return (t or {}).get("data", {})


def get_tenant_data(tenant_id: str) -> dict:
    """给 sim_engine 用：data 锚点 + 注入 companies（来自 park_companies 表）。"""
    data = _get_data(tenant_id)
    comps = list_companies(tenant_id)
    if comps:
        data = dict(data)
        data["companies"] = comps
    return data or dict(DEFAULT_DATA)


def create_tenant(name: str, intro: list[str], username: str = "", password: str = "") -> dict:
    """创建园区：不创建账号（admin 留空），由平台端绑定已有账号作园区管理员。"""
    tid = _new_id("T")
    data = dict(DEFAULT_DATA)
    conn = _conn()
    conn.execute(
        "INSERT INTO park_tenants (id,name,intro_json,username,salt,password_hash,admin_username,data_json,agent_json,created_at) VALUES (?,?,?,?,?,?,?,?,?,?)",
        (tid, name, json.dumps(intro or ["", ""]), username, "", "", "", json.dumps(data, ensure_ascii=False), json.dumps({}, ensure_ascii=False), _now()),
    )
    conn.commit()
    conn.close()
    _seed_companies(tid, DEFAULT_DATA["companies"])
    refresh_engine(tid)
    return _summary(tid, name, intro, username)


def bind_admin(tenant_id: str, username: str) -> bool:
    conn = _conn()
    cur = conn.execute("UPDATE park_tenants SET admin_username=? WHERE id=?", (username.strip(), tenant_id))
    conn.commit()
    conn.close()
    return cur.rowcount > 0


def unbind_admin(tenant_id: str) -> bool:
    return bind_admin(tenant_id, "")


def find_by_admin(username: str) -> dict | None:
    """返回绑定为该平台账号管理员的园区（供园区端登录）。"""
    conn = _conn()
    row = conn.execute("SELECT * FROM park_tenants WHERE admin_username=? LIMIT 1", (username.strip(),)).fetchone()
    conn.close()
    return _row_to_tenant(row) if row else None


def update_tenant(tenant_id: str, patch: dict) -> dict | None:
    conn = _conn()
    t = get_tenant(tenant_id)
    if t is None:
        conn.close()
        return None
    sets, args = [], []
    if patch.get("name") is not None:
        sets.append("name=?"); args.append(patch["name"])
    if patch.get("intro") is not None:
        sets.append("intro_json=?"); args.append(json.dumps(patch["intro"], ensure_ascii=False))
    if patch.get("data") is not None:
        sets.append("data_json=?"); args.append(json.dumps(patch["data"], ensure_ascii=False))
    if patch.get("agent") is not None:
        sets.append("agent_json=?"); args.append(json.dumps(patch["agent"], ensure_ascii=False))
    if patch.get("username") is not None:
        sets.append("username=?"); args.append(patch["username"])
    if patch.get("password"):
        salt, h = _hash_password(patch["password"])
        sets.append("salt=?"); sets.append("password_hash=?"); args += [salt, h]
    if not sets:
        conn.close()
        return _summary(tenant_id, t["name"], t["intro"], t["auth"]["username"])
    args.append(tenant_id)
    conn.execute(f"UPDATE park_tenants SET {', '.join(sets)} WHERE id=?", args)
    conn.commit()
    conn.close()
    if patch.get("data"):
        refresh_engine(tenant_id)
    t2 = get_tenant(tenant_id)
    return _summary(tenant_id, t2["name"], t2["intro"], t2["auth"]["username"])


def delete_tenant(tenant_id: str) -> bool:
    """联级删除园区：租户行 + 全部资源（企业/知识库/屏幕/引擎）。"""
    conn = _conn()
    cur = conn.execute("DELETE FROM park_tenants WHERE id=?", (tenant_id,))
    conn.execute("DELETE FROM park_companies WHERE tenant_id=?", (tenant_id,))
    conn.execute("DELETE FROM park_kb_docs WHERE tenant_id=?", (tenant_id,))
    conn.execute("DELETE FROM park_screens WHERE tenant_id=?", (tenant_id,))
    for cid in [c["id"] for c in conn.execute("SELECT id FROM park_tenants").fetchall()]:
        pass  # 引擎缓存后续统一清理
    conn.commit()
    conn.close()
    from .sim_engine import _engines
    _engines.pop(tenant_id, None)  # 淘汰缓存引擎
    return cur.rowcount > 0


def set_status(tenant_id: str, status: str) -> bool:
    """禁用/启用园区（disabled 不删除，但禁止登录/使用）。"""
    if status not in ("active", "disabled"):
        return False
    conn = _conn()
    cur = conn.execute("UPDATE park_tenants SET status=? WHERE id=?", (status, tenant_id))
    conn.commit()
    conn.close()
    return cur.rowcount > 0


def verify_login(username: str, password: str) -> dict | None:
    conn = _conn()
    row = conn.execute("SELECT * FROM park_tenants WHERE username=?", (username,)).fetchone()
    conn.close()
    if row and _verify_password(password, row["salt"], row["password_hash"]):
        return _summary(row["id"], row["name"], json.loads(row["intro_json"] or "[]"), row["username"])
    return None


def ensure_default_tenant() -> str:
    """确保至少存在一个默认园区（昆明市大学生创业园）。已有则补种企业（自愈）。"""
    conn = _conn()
    if conn.execute("SELECT 1 FROM park_tenants WHERE id=?", (_DEFAULT_TENANT_ID,)).fetchone():
        conn.close()
        if not list_companies(_DEFAULT_TENANT_ID):
            _seed_companies(_DEFAULT_TENANT_ID, DEFAULT_DATA["companies"])
        return _DEFAULT_TENANT_ID
    conn.close()
    salt, h = _hash_password("123456")
    data = dict(DEFAULT_DATA)
    conn = _conn()
    conn.execute(
        "INSERT INTO park_tenants (id,name,intro_json,username,salt,password_hash,data_json,agent_json,created_at) VALUES (?,?,?,?,?,?,?,?,?)",
        (_DEFAULT_TENANT_ID, "昆明市大学生创业园", json.dumps(["云南省首家政府主办大学生创业孵化园区", "空间 + 孵化 + 融资 + 政策 + AI 赋能 + 综合服务"], ensure_ascii=False), "admin", salt, h, json.dumps(data, ensure_ascii=False), json.dumps({}, ensure_ascii=False), _now()),
    )
    conn.commit()
    conn.close()
    _seed_companies(_DEFAULT_TENANT_ID, DEFAULT_DATA["companies"])
    refresh_engine(_DEFAULT_TENANT_ID)
    return _DEFAULT_TENANT_ID


def _summary(tid: str, name: str, intro: list, username: str, admin_username: str = "", status: str = "active") -> dict:
    return {"id": tid, "name": name, "intro": intro, "username": username, "admin_username": admin_username, "status": status}


def _seed_companies(tenant_id: str, companies: list) -> None:
    """把默认企业灌入 park_companies 表（新园区初始名录），管理端在其上改。"""
    conn = _conn()
    for c in companies:
        name = c["name"] if isinstance(c, dict) else c
        conn.execute(
            "INSERT OR IGNORE INTO park_companies (id,tenant_id,name,zone,room,industry,bio,founder,status,employees,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            (_new_id("PC"), tenant_id, name, c.get("zone", "") if isinstance(c, dict) else "", c.get("room", "") if isinstance(c, dict) else "", c.get("industry", "") if isinstance(c, dict) else "", c.get("bio", "") if isinstance(c, dict) else "", c.get("founder", "") if isinstance(c, dict) else "", "active", None, _now()),
        )
    conn.commit()
    conn.close()


def iter_tenants():
    for t in list_tenants():
        yield t["id"], t


# ---------------- 入驻企业（表级 CRUD） ----------------

def list_companies(tenant_id: str) -> list[dict]:
    conn = _conn()
    rows = conn.execute("SELECT * FROM park_companies WHERE tenant_id=? ORDER BY created_at DESC", (tenant_id,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def create_company(tenant_id: str, payload: dict) -> dict:
    row = {
        "id": _new_id("PC"), "tenant_id": tenant_id, "name": payload.get("name", ""),
        "zone": payload.get("zone", ""), "room": payload.get("room", ""), "industry": payload.get("industry", ""),
        "bio": payload.get("bio", ""), "founder": payload.get("founder", ""),
        "status": payload.get("status", "applying"), "employees": payload.get("employees"), "created_at": _now(),
    }
    conn = _conn()
    conn.execute(
        "INSERT INTO park_companies (id,tenant_id,name,zone,room,industry,bio,founder,status,employees,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        (row["id"], row["tenant_id"], row["name"], row["zone"], row["room"], row["industry"], row["bio"], row["founder"], row["status"], row["employees"], row["created_at"]),
    )
    conn.commit()
    conn.close()
    refresh_engine(tenant_id)
    return {k: v for k, v in row.items() if k != "tenant_id"}


def update_company(tenant_id: str, cid: str, payload: dict) -> dict | None:
    fields = {k: v for k, v in payload.items() if v is not None and k in ("name", "zone", "room", "industry", "bio", "founder", "status", "employees")}
    if not fields:
        return None
    sets = ", ".join(f"{k}=?" for k in fields)
    conn = _conn()
    cur = conn.execute(f"UPDATE park_companies SET {sets} WHERE id=? AND tenant_id=?", list(fields.values()) + [cid, tenant_id])
    conn.commit()
    conn.close()
    refresh_engine(tenant_id)
    if cur.rowcount == 0:
        return None
    conn = _conn()
    row = conn.execute("SELECT * FROM park_companies WHERE id=? AND tenant_id=?", (cid, tenant_id)).fetchone()
    conn.close()
    return {k: v for k, v in dict(row).items() if k != "tenant_id"}


def delete_company(tenant_id: str, cid: str) -> bool:
    conn = _conn()
    cur = conn.execute("DELETE FROM park_companies WHERE id=? AND tenant_id=?", (cid, tenant_id))
    conn.commit()
    conn.close()
    refresh_engine(tenant_id)
    return cur.rowcount > 0


# ---------------- 园区智能体 ----------------

def get_agent(tenant_id: str) -> dict:
    return get_tenant(tenant_id).get("agent", {})


def update_agent(tenant_id: str, payload: dict) -> dict:
    t = get_tenant(tenant_id)
    agent = t.get("agent", {})
    for k, v in payload.items():
        if v is not None:
            agent[k] = v
    conn = _conn()
    conn.execute("UPDATE park_tenants SET agent_json=? WHERE id=?", (json.dumps(agent, ensure_ascii=False), tenant_id))
    conn.commit()
    conn.close()
    return agent


# ---------------- 园区知识库（表级 CRUD） ----------------

def kb_docs(tenant_id: str) -> list[dict]:
    conn = _conn()
    rows = conn.execute("SELECT * FROM park_kb_docs WHERE tenant_id=? ORDER BY created_at DESC", (tenant_id,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def create_kb_doc(tenant_id: str, payload: dict) -> dict:
    row = {"id": _new_id("KB"), "tenant_id": tenant_id, "grp": payload.get("group", "general"), "title": payload.get("title", ""), "content_md": payload.get("content_md", ""), "created_at": _now()}
    conn = _conn()
    conn.execute("INSERT INTO park_kb_docs (id,tenant_id,grp,title,content_md,created_at) VALUES (?,?,?,?,?,?)", (row["id"], row["tenant_id"], row["grp"], row["title"], row["content_md"], row["created_at"]))
    conn.commit()
    conn.close()
    return {k: v for k, v in row.items() if k != "tenant_id"}


def update_kb_doc(tenant_id: str, did: str, payload: dict) -> dict | None:
    fields = {k: v for k, v in payload.items() if v is not None and k in ("grp", "title", "content_md")}
    if not fields:
        return get_kb_doc(tenant_id, did)
    sets = ", ".join(f"{k}=?" for k in fields)
    conn = _conn()
    conn.execute(f"UPDATE park_kb_docs SET {sets} WHERE id=? AND tenant_id=?", list(fields.values()) + [did, tenant_id])
    conn.commit()
    conn.close()
    return get_kb_doc(tenant_id, did)


def delete_kb_doc(tenant_id: str, did: str) -> bool:
    conn = _conn()
    cur = conn.execute("DELETE FROM park_kb_docs WHERE id=? AND tenant_id=?", (did, tenant_id))
    conn.commit()
    conn.close()
    return cur.rowcount > 0


def get_kb_doc(tenant_id: str, did: str) -> dict | None:
    conn = _conn()
    row = conn.execute("SELECT * FROM park_kb_docs WHERE id=? AND tenant_id=?", (did, tenant_id)).fetchone()
    conn.close()
    return {k: v for k, v in dict(row).items() if k != "tenant_id"} if row else None


# ---------------- 屏幕（园区端创建/管理大屏设备） ----------------

def list_screens(tenant_id: str) -> list[dict]:
    conn = _conn()
    rows = conn.execute("SELECT * FROM park_screens WHERE tenant_id=? ORDER BY created_at DESC", (tenant_id,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def create_screen(tenant_id: str, payload: dict) -> dict:
    row = {"id": _new_id("SCR"), "tenant_id": tenant_id, "device_id": payload.get("device_id", ""), "name": payload.get("name", ""), "role": payload.get("role", "main"), "location": payload.get("location", ""), "created_at": _now()}
    conn = _conn()
    conn.execute("INSERT INTO park_screens (id,tenant_id,device_id,name,role,location,created_at) VALUES (?,?,?,?,?,?,?)", (row["id"], row["tenant_id"], row["device_id"], row["name"], row["role"], row["location"], row["created_at"]))
    conn.commit()
    conn.close()
    return {k: v for k, v in row.items() if k != "tenant_id"}


def delete_screen(tenant_id: str, sid: str) -> bool:
    conn = _conn()
    cur = conn.execute("DELETE FROM park_screens WHERE id=? AND tenant_id=?", (sid, tenant_id))
    conn.commit()
    conn.close()
    return cur.rowcount > 0


# ---------------- 大屏数据（名称/简介 + 锚点） ----------------

def screen_view(tenant_id: str) -> dict:
    t = get_tenant(tenant_id) or {}
    d = t.get("data", {})
    park = d.get("park", {}) or {}
    return {
        "tenant_id": tenant_id,
        "name": t.get("name", ""),
        "intro": t.get("intro", []),
        "founded": park.get("founded"), "province_level": park.get("province_level") or park.get("provinceLevel"), "area": park.get("area"),
        "region": park.get("region", ""), "address": park.get("address"), "phone": park.get("phone"), "email": park.get("email"),
        "capacity": d.get("projects", {}).get("capacity"), "invested": d.get("projects", {}).get("invested"),
        "jobs": d.get("jobs", {}).get("total"), "revenue_total": d.get("revenue", {}).get("total"), "revenue_tax": d.get("revenue", {}).get("tax"),
        "zones": d.get("zones", []), "industryMix": d.get("industryMix", []), "feed": d.get("feed", []),
        "as_of": d.get("as_of", ""),
    }


def update_screen(tenant_id: str, payload: dict) -> dict:
    t = get_tenant(tenant_id) or {}
    d = dict(t.get("data", {}))
    if payload.get("name"):
        d["_name"] = payload["name"]  # 名称存租户 name 列，见下
    park = dict(d.get("park", {}))
    for k in ("founded", "area", "address", "phone", "email", "region"):
        if payload.get(k) is not None:
            park[k] = payload[k]
    d["park"] = park
    proj = dict(d.get("projects", {}))
    if payload.get("capacity") is not None:
        proj["capacity"] = payload["capacity"]
    if payload.get("invested") is not None:
        proj["invested"] = payload["invested"]
    d["projects"] = proj
    jobs = dict(d.get("jobs", {}))
    if payload.get("jobs") is not None:
        jobs["total"] = payload["jobs"]
    d["jobs"] = jobs
    rev = dict(d.get("revenue", {}))
    if payload.get("revenue_total") is not None:
        rev["total"] = payload["revenue_total"]
    if payload.get("revenue_tax") is not None:
        rev["tax"] = payload["revenue_tax"]
    d["revenue"] = rev
    # 动态 / 区域 / 产业构成（整表替换，可全量编辑）
    for k in ("feed", "zones", "industryMix"):
        if payload.get(k) is not None:
            d[k] = payload[k]
    conn = _conn()
    name = payload.get("name") or t["name"]
    intro = payload.get("intro") or t.get("intro", [])
    conn.execute("UPDATE park_tenants SET name=?, intro_json=?, data_json=? WHERE id=?",
                 (name, json.dumps(intro, ensure_ascii=False), json.dumps(d, ensure_ascii=False), tenant_id))
    conn.commit()
    conn.close()
    refresh_engine(tenant_id)
    return screen_view(tenant_id)
