"""SQLite 数据层（兼容原 mock 后端 JSON store，迁移用）
位于 server-core/app/training/，数据落在 server-core/data/opc.db（与平台库 app.db 并列）。
"""
import json
import os
import sqlite3
import time
import random

BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))  # server-core 根
DB_PATH = os.path.join(BASE_DIR, "serverdata", "data", "opc.db")
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)


def get_conn():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def gen_id(prefix):
    ts = time.time()
    s = int(ts)
    # base36 大写，模拟 JS genId
    def b36(n):
        if n == 0:
            return "0"
        d = "0123456789abcdefghijklmnopqrstuvwxyz"
        out = ""
        while n:
            out = d[n % 36] + out
            n //= 36
        return out
    return prefix + b36(s).upper() + b36(random.randint(0, 35)).upper()


SCHEMA = """
CREATE TABLE IF NOT EXISTS accounts (
  id TEXT PRIMARY KEY,
  username TEXT UNIQUE,
  wxid TEXT,
  phone TEXT,
  contact TEXT,
  name TEXT,
  avatar TEXT,
  password TEXT,
  phone_bound INTEGER DEFAULT 0,
  identities TEXT,
  created_at TEXT
);
CREATE TABLE IF NOT EXISTS events (
  id TEXT PRIMARY KEY,
  type TEXT,
  mode TEXT DEFAULT 'offline',
  title TEXT,
  subtitle TEXT,
  desc TEXT,
  location TEXT,
  host TEXT,
  image TEXT,
  link TEXT,
  start_at TEXT,
  duration_min INTEGER DEFAULT 90,
  capacity INTEGER DEFAULT 0,
  status TEXT DEFAULT 'open'
);
CREATE TABLE IF NOT EXISTS bookings (
  id TEXT PRIMARY KEY,
  created_at TEXT,
  status TEXT DEFAULT 'pending',
  username TEXT,
  name TEXT,
  contact TEXT,
  status_label TEXT,
  want TEXT,
  event_id TEXT,
  event_title TEXT,
  event_start TEXT,
  topics TEXT,
  question TEXT,
  source TEXT,
  checkin_at TEXT,
  audit_status TEXT DEFAULT 'pending'
);
CREATE TABLE IF NOT EXISTS tests (
  id TEXT PRIMARY KEY,
  created_at TEXT,
  username TEXT,
  type_code TEXT,
  persona TEXT,
  adapt_index INTEGER,
  adapt_level TEXT,
  tracks TEXT,
  version TEXT
);
CREATE TABLE IF NOT EXISTS courses (
  id TEXT PRIMARY KEY,
  category TEXT,
  level TEXT,
  title TEXT,
  subtitle TEXT,
  desc TEXT,
  price REAL DEFAULT 0,
  status TEXT DEFAULT 'draft',
  start_at TEXT,
  end_at TEXT,
  venue TEXT,
  quota INTEGER DEFAULT 0,
  image TEXT,
  host TEXT,
  created_at TEXT
);
CREATE TABLE IF NOT EXISTS policy_logs (
  id TEXT PRIMARY KEY,
  created_at TEXT,
  username TEXT,
  answers TEXT,
  policies_count INTEGER,
  subsidies_count INTEGER,
  loans_count INTEGER,
  summary TEXT
);
CREATE TABLE IF NOT EXISTS plan_logs (
  id TEXT PRIMARY KEY,
  created_at TEXT,
  username TEXT,
  region TEXT,
  status TEXT,
  need_park INTEGER,
  has_staff INTEGER,
  steps_count INTEGER
);
CREATE TABLE IF NOT EXISTS survey_logs (
  id TEXT PRIMARY KEY,
  created_at TEXT,
  username TEXT,
  source TEXT,
  answers TEXT
);
"""

SEED_EVENTS = [
  {"id": "E-F001", "type": "free", "mode": "online", "title": "AI 让民宿主每月省 10 小时", "subtitle": "民宿 / 文旅创业 · 公益课", "desc": "拆解民宿经营里最耗时的重复劳动——接单、排房、回复咨询。现场演示 AI 怎么把每周 10 小时的杂活接住，让你把时间还给体验和口碑。不卖课，点到即止。", "location": "视频号直播", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-f1/600/400", "link": "", "start_at": "2026-09-04T19:30:00+08:00", "duration_min": 90, "capacity": 0, "status": "open"},
  {"id": "E-S001", "type": "salon", "mode": "offline", "title": "民宿 AI 化改造实战", "subtitle": "交流沙龙 · 12–30 人小场", "desc": "带上你的民宿，现场用 AI 搭建一套内容与客服小助手：从组标题、生成海报到自动回复，走通一遍。1 对 1 快诊你的具体场景，带走可复用的模板。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-s1/600/400", "link": "", "start_at": "2026-09-05T14:00:00+08:00", "duration_min": 180, "capacity": 30, "status": "invite"},
  {"id": "E-F002", "type": "free", "mode": "offline", "title": "用 AI 做云南咖啡品牌", "subtitle": "咖啡 / 农特产创业 · 公益课", "desc": "从云南豆讲到品牌：怎么用一个好故事、一套 AI 内容打法，把咖啡从产地卖到全国。现场看一条内容从 0 到发布的完整流程。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-f2/600/400", "link": "", "start_at": "2026-09-06T19:30:00+08:00", "duration_min": 90, "capacity": 60, "status": "open"},
  {"id": "E-S002", "type": "salon", "mode": "offline", "title": "技能打包：把手艺变成产品", "subtitle": "交流沙龙 · 副业转型专场", "desc": "工作坊：把你的技能、经验和手艺，改成能标价、能交付、能复制的产品。现场分组拆解你的卡点，产出一版可落地的产品草图。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-s2/600/400", "link": "", "start_at": "2026-09-07T14:00:00+08:00", "duration_min": 180, "capacity": 24, "status": "open"},
  {"id": "E-F003", "type": "free", "mode": "online", "title": "一人公司财税避坑入门", "subtitle": "自由职业 / 小老板 · 公益课", "desc": "个体户、一人公司最容易踩的财税坑：发票、成本票、申报节点、核定与查账。用大白话讲清楚，让你不再为报税发愁。", "location": "视频号直播", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-f3/600/400", "link": "", "start_at": "2026-09-09T19:30:00+08:00", "duration_min": 90, "capacity": 0, "status": "pending"},
  {"id": "E-F004", "type": "free", "mode": "offline", "title": "你适不适合创业？先做一次 OPC 测评", "subtitle": "大众 · 公益课", "desc": "现场 30 题速测你的 OPC 适配度：独立、风险、自驱、单兵、AI 意愿、安全垫六维速览，带你读懂自己的类型码与适合的赛道。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-f4/600/400", "link": "", "start_at": "2026-09-10T19:30:00+08:00", "duration_min": 90, "capacity": 60, "status": "open"},
  {"id": "E-S003", "type": "salon", "mode": "offline", "title": "OPC 测评报告 1 对 1 解读专场", "subtitle": "交流沙龙 · 测评者组场", "desc": "每人 15 分钟快诊：读懂你的 4 字母类型码、适配 6 维和推荐赛道，现场把报告讲到能落地，并给出你的起点方向。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-s3/600/400", "link": "", "start_at": "2026-09-11T14:00:00+08:00", "duration_min": 180, "capacity": 20, "status": "open"},
  {"id": "E-S004", "type": "salon", "mode": "offline", "title": "资源对接夜：能提供 × 需要", "subtitle": "交流沙龙 · 综合场", "desc": "一张桌子，把你的「能提供」和「需要」摆上台面，现场匹配同行与本地资源。可能认识下一个合作伙伴，也可能接住第一单。", "location": "昆明市大学生创业园", "host": "云超服 · OPC 培训", "image": "https://picsum.photos/seed/opc-s4/600/400", "link": "", "start_at": "2026-09-12T14:00:00+08:00", "duration_min": 180, "capacity": 30, "status": "done"},
]


def now_iso():
    from datetime import datetime, timezone
    return datetime.now(timezone.utc).isoformat().replace("+00:00", "Z")


SEED_COURSES = [
  {"id": "C-001", "category": "AI应用", "level": "入门", "title": "AI 内容一键产出：从标题到海报", "subtitle": "公益课回放", "desc": "把组标题、写正文、配图、做海报这一条链路用 AI 跑通。", "price": 0, "status": "published", "start_at": "2026-09-04T19:30:00+08:00", "end_at": "", "venue": "视频号直播", "quota": 0, "image": "", "host": "云超服 · OPC 培训", "created_at": "2026-08-24T00:00:00Z"},
  {"id": "C-002", "category": "创业", "level": "进阶", "title": "一人公司财税避坑进阶", "subtitle": "线上标准课", "desc": "个体户 / 一人公司：发票、成本票、申报节点、核定与查账。", "price": 499, "status": "draft", "start_at": "2026-09-20T19:30:00+08:00", "end_at": "", "venue": "视频号直播", "quota": 200, "image": "", "host": "云超服 · OPC 培训", "created_at": "2026-08-24T00:00:00Z"},
]


def init_db():
    conn = get_conn()
    conn.executescript(SCHEMA)
    # 种子：内置超级账号（pine/123456，兼容原 mock）
    if conn.execute("SELECT COUNT(*) AS c FROM accounts").fetchone()["c"] == 0:
        from .auth import hash_password
        conn.execute(
            "INSERT INTO accounts (id,username,password,name,identities,created_at) VALUES (?,?,?,?,?,?)",
            ("U-PINE", "pine", hash_password("123456"), "Pine", json.dumps(["admin"]), now_iso()),
        )
    # 种子：排期
    if conn.execute("SELECT COUNT(*) AS c FROM events").fetchone()["c"] == 0:
        for e in SEED_EVENTS:
            conn.execute(
                "INSERT INTO events (id,type,mode,title,subtitle,desc,location,host,image,link,start_at,duration_min,capacity,status) "
                "VALUES (:id,:type,:mode,:title,:subtitle,:desc,:location,:host,:image,:link,:start_at,:duration_min,:capacity,:status)",
                e,
            )
    # 种子：在线课程（运营端课程管理）
    if conn.execute("SELECT COUNT(*) AS c FROM courses").fetchone()["c"] == 0:
        for c in SEED_COURSES:
            conn.execute(
                "INSERT INTO courses (id,category,level,title,subtitle,desc,price,status,start_at,end_at,venue,quota,image,host,created_at) "
                "VALUES (:id,:category,:level,:title,:subtitle,:desc,:price,:status,:start_at,:end_at,:venue,:quota,:image,:host,:created_at)",
                c,
            )
    # -- 幂等迁移：为已有 bookings 表补 audit_status 列 --
    cols = [r["name"] for r in conn.execute("PRAGMA table_info(bookings)").fetchall()]
    if "audit_status" not in cols:
        conn.execute("ALTER TABLE bookings ADD COLUMN audit_status TEXT DEFAULT 'pending'")
    # -- 幂等迁移：为已有 accounts 表补 wxid / phone / phone_bound / contact / avatar 列（旧库自愈，避免微信登录报缺列或每次新建账号） --
    acols = [r["name"] for r in conn.execute("PRAGMA table_info(accounts)").fetchall()]
    for col, ddl in [
        ("wxid", "ALTER TABLE accounts ADD COLUMN wxid TEXT"),
        ("phone", "ALTER TABLE accounts ADD COLUMN phone TEXT"),
        ("contact", "ALTER TABLE accounts ADD COLUMN contact TEXT"),
        ("avatar", "ALTER TABLE accounts ADD COLUMN avatar TEXT"),
        ("phone_bound", "ALTER TABLE accounts ADD COLUMN phone_bound INTEGER DEFAULT 0"),
        # 报名资料（个人中心设置，报名时自动带入）
        ("status_label", "ALTER TABLE accounts ADD COLUMN status_label TEXT"),
        ("topics", "ALTER TABLE accounts ADD COLUMN topics TEXT"),
        ("source", "ALTER TABLE accounts ADD COLUMN source TEXT"),
    ]:
        if col not in acols:
            conn.execute(ddl)
    # -- 幂等迁移：为已有 events 表补 结束时间 / 签到开放时间 列 --
    ecols = [r["name"] for r in conn.execute("PRAGMA table_info(events)").fetchall()]
    for col, ddl in [
        ("end_at", "ALTER TABLE events ADD COLUMN end_at TEXT"),
        ("checkin_at", "ALTER TABLE events ADD COLUMN checkin_at TEXT"),
        ("audit_mode", "ALTER TABLE events ADD COLUMN audit_mode TEXT DEFAULT 'auto'"),
        ("show_capacity", "ALTER TABLE events ADD COLUMN show_capacity INTEGER DEFAULT 0"),
    ]:
        if col not in ecols:
            conn.execute(ddl)
    conn.commit()
    conn.close()


# ---------------- 通用读写 helpers ----------------

def rows_to_list(rows):
    return [dict(r) for r in rows]


def fetch_one(table, **kw):
    conn = get_conn()
    where = " AND ".join([f"{k}=?" for k in kw])
    row = conn.execute(f"SELECT * FROM {table} WHERE {where}", list(kw.values())).fetchone()
    conn.close()
    return dict(row) if row else None


def fetch_by_id(table, rid):
    return fetch_one(table, id=rid)


def list_all(table, order_by=None):
    conn = get_conn()
    sql = f"SELECT * FROM {table}"
    if order_by:
        sql += f" ORDER BY {order_by}"
    rows = conn.execute(sql).fetchall()
    conn.close()
    return rows_to_list(rows)


def insert(table, data):
    conn = get_conn()
    keys = list(data.keys())
    conn.execute(
        f"INSERT INTO {table} ({','.join(keys)}) VALUES ({','.join(['?']*len(keys))})",
        [data[k] for k in keys],
    )
    conn.commit()
    conn.close()


def update_row(table, rid, patch):
    conn = get_conn()
    keys = list(patch.keys())
    conn.execute(
        f"UPDATE {table} SET {','.join([f'{k}=?' for k in keys])} WHERE id=?",
        [patch[k] for k in keys] + [rid],
    )
    conn.commit()
    conn.close()


def delete_row(table, rid):
    conn = get_conn()
    conn.execute(f"DELETE FROM {table} WHERE id=?", (rid,))
    conn.commit()
    conn.close()
