# -*- coding: utf-8 -*-
"""SQLite-backed prompt-template library.

Prompts are the kind of thing you tune once and reuse across rides, so they
live in a shared library (app/data.db) rather than being copied into every
project's JSON. Projects just reference a template by id.

Built-in templates are seeded on first run and kept read-only — editing one
creates a copy instead, so there is always a known-good starting point to
fall back to.
"""
import sqlite3
import threading
from datetime import datetime
from pathlib import Path

APP_ROOT = Path(__file__).resolve().parent.parent
DB_PATH = APP_ROOT / "data.db"

_lock = threading.Lock()
_conn = None

SCHEMA = """
CREATE TABLE IF NOT EXISTS prompts (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  kind       TEXT NOT NULL,          -- 'scene' | 'script'
  name       TEXT NOT NULL,
  content    TEXT NOT NULL,
  builtin    INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_prompts_kind ON prompts(kind);

-- Dashboard layout presets. Same reasoning as prompts: a layout you have
-- tuned (positions, sizes, colours) is worth reusing across rides, so it
-- lives in the shared library rather than only inside one project.
CREATE TABLE IF NOT EXISTS dash_presets (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  layout     TEXT NOT NULL,          -- JSON: {metric: {on,x,y,scale,color}}
  updated_at TEXT NOT NULL
);
"""


def conn() -> sqlite3.Connection:
    global _conn
    if _conn is None:
        # jobs run on worker threads, so the connection must not be pinned to
        # the thread that created it; all access goes through _lock
        _conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
        _conn.row_factory = sqlite3.Row
        _conn.executescript(SCHEMA)
        _conn.commit()
    return _conn


def init(defaults: dict):
    """defaults: {'scene': text, 'script': text} — seeded once."""
    with _lock:
        c = conn()
        for kind, content in defaults.items():
            row = c.execute(
                "SELECT id FROM prompts WHERE kind=? AND builtin=1",
                (kind,)).fetchone()
            if row:
                # keep the built-in row in step with the code's default
                c.execute("UPDATE prompts SET content=?, updated_at=? WHERE id=?",
                          (content, _now(), row["id"]))
            else:
                c.execute(
                    "INSERT INTO prompts(kind,name,content,builtin,updated_at)"
                    " VALUES(?,?,?,1,?)",
                    (kind, "內建預設", content, _now()))
        c.commit()


def _now():
    return datetime.now().isoformat(timespec="seconds")


def list_prompts(kind: str | None = None) -> list:
    with _lock:
        c = conn()
        if kind:
            rows = c.execute(
                "SELECT * FROM prompts WHERE kind=? ORDER BY builtin DESC, name",
                (kind,)).fetchall()
        else:
            rows = c.execute(
                "SELECT * FROM prompts ORDER BY kind, builtin DESC, name").fetchall()
        return [dict(r) for r in rows]


def get_prompt(pid: int) -> dict | None:
    with _lock:
        r = conn().execute("SELECT * FROM prompts WHERE id=?", (pid,)).fetchone()
        return dict(r) if r else None


def get_content(pid: int | None, kind: str, fallback: str) -> str:
    """Resolve a template id to text, tolerating a deleted/missing row."""
    if pid:
        row = get_prompt(int(pid))
        if row and row["kind"] == kind and row["content"].strip():
            return row["content"]
    return fallback


def create_prompt(kind: str, name: str, content: str) -> int:
    with _lock:
        c = conn()
        cur = c.execute(
            "INSERT INTO prompts(kind,name,content,builtin,updated_at)"
            " VALUES(?,?,?,0,?)", (kind, name, content, _now()))
        c.commit()
        return cur.lastrowid


def update_prompt(pid: int, name: str, content: str):
    with _lock:
        c = conn()
        row = c.execute("SELECT builtin FROM prompts WHERE id=?",
                        (pid,)).fetchone()
        if not row:
            raise KeyError("找不到這組範本")
        if row["builtin"]:
            raise PermissionError("內建預設不可修改，請用「另存新範本」")
        c.execute("UPDATE prompts SET name=?, content=?, updated_at=? WHERE id=?",
                  (name, content, _now(), pid))
        c.commit()


def delete_prompt(pid: int):
    with _lock:
        c = conn()
        row = c.execute("SELECT builtin FROM prompts WHERE id=?",
                        (pid,)).fetchone()
        if not row:
            raise KeyError("找不到這組範本")
        if row["builtin"]:
            raise PermissionError("內建預設不可刪除")
        c.execute("DELETE FROM prompts WHERE id=?", (pid,))
        c.commit()


# ---- dashboard layout presets ----------------------------------------

def list_dash() -> list:
    with _lock:
        rows = conn().execute(
            "SELECT id,name,layout,updated_at FROM dash_presets "
            "ORDER BY name").fetchall()
        return [dict(r) for r in rows]


def save_dash(name: str, layout_json: str, pid: int | None = None) -> int:
    """Insert, or overwrite when `pid` names an existing preset."""
    now = datetime.now().isoformat(timespec="seconds")
    with _lock:
        c = conn()
        if pid:
            c.execute("UPDATE dash_presets SET name=?,layout=?,updated_at=? "
                      "WHERE id=?", (name, layout_json, now, pid))
            c.commit()
            return pid
        cur = c.execute(
            "INSERT INTO dash_presets(name,layout,updated_at) VALUES(?,?,?)",
            (name, layout_json, now))
        c.commit()
        return cur.lastrowid


def delete_dash(pid: int):
    with _lock:
        c = conn()
        c.execute("DELETE FROM dash_presets WHERE id=?", (pid,))
        c.commit()
