import os
import json
import sqlite3
import threading
from pathlib import Path
from typing import Optional

DB_PATH = Path(os.environ.get("DEMO_DB_PATH", "/data/demos/.meta/sites.db"))
_lock = threading.Lock()
_conn: Optional[sqlite3.Connection] = None


def get_conn() -> sqlite3.Connection:
    global _conn
    if _conn is None:
        DB_PATH.parent.mkdir(parents=True, exist_ok=True)
        _conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
        _conn.row_factory = sqlite3.Row
        _init_schema(_conn)
    return _conn


def _init_schema(conn: sqlite3.Connection):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS sites (
            name TEXT PRIMARY KEY,
            enabled INTEGER NOT NULL DEFAULT 1,
            published INTEGER NOT NULL DEFAULT 0,
            description TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
    """)
    conn.commit()


def migrate_from_json():
    """One-time migration from old sites.json to SQLite DB."""
    json_path = DB_PATH.parent / "sites.json"
    if not json_path.exists():
        return False
    conn = get_conn()
    cur = conn.execute("SELECT COUNT(*) FROM sites")
    if cur.fetchone()[0] > 0:
        # Already migrated
        return False
    try:
        data = json.loads(json_path.read_text())
        for name, info in data.get("sites", {}).items():
            # Backward compat: missing published defaults to True (existing sites stay public)
            published = 1 if info.get("published", True) else 0
            enabled = 1 if info.get("enabled", True) else 0
            conn.execute(
                """INSERT OR REPLACE INTO sites
                   (name, enabled, published, description, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?)""",
                (name, enabled, published,
                 info.get("description", ""),
                 info.get("created_at", ""),
                 info.get("updated_at", "")),
            )
        conn.commit()
        # Rename old file so we don't re-migrate
        json_path.rename(json_path.with_suffix(".json.migrated"))
        return True
    except Exception as e:
        print(f"Migration failed: {e}")
        return False


def load_all() -> dict:
    """Return sites as {name: info_dict} for backward compat."""
    conn = get_conn()
    rows = conn.execute(
        "SELECT name, enabled, published, description, created_at, updated_at FROM sites"
    ).fetchall()
    sites = {}
    for r in rows:
        sites[r["name"]] = {
            "name": r["name"],
            "enabled": bool(r["enabled"]),
            "published": bool(r["published"]),
            "description": r["description"],
            "created_at": r["created_at"],
            "updated_at": r["updated_at"],
        }
    return {"sites": sites}


def get(name: str) -> Optional[dict]:
    conn = get_conn()
    r = conn.execute(
        "SELECT name, enabled, published, description, created_at, updated_at FROM sites WHERE name = ?",
        (name,),
    ).fetchone()
    if not r:
        return None
    return {
        "name": r["name"],
        "enabled": bool(r["enabled"]),
        "published": bool(r["published"]),
        "description": r["description"],
        "created_at": r["created_at"],
        "updated_at": r["updated_at"],
    }


def insert(name: str, enabled: bool, published: bool, description: str, now: str):
    with _lock:
        conn = get_conn()
        conn.execute(
            """INSERT INTO sites (name, enabled, published, description, created_at, updated_at)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (name, 1 if enabled else 0, 1 if published else 0, description, now, now),
        )
        conn.commit()


def update(name: str, **kwargs):
    if not kwargs:
        return
    cols = []
    vals = []
    for k, v in kwargs.items():
        if k == "enabled":
            cols.append("enabled = ?")
            vals.append(1 if v else 0)
        elif k == "published":
            cols.append("published = ?")
            vals.append(1 if v else 0)
        else:
            cols.append(f"{k} = ?")
            vals.append(v)
    vals.append(name)
    with _lock:
        conn = get_conn()
        conn.execute(f"UPDATE sites SET {', '.join(cols)} WHERE name = ?", vals)
        conn.commit()


def delete(name: str):
    with _lock:
        conn = get_conn()
        conn.execute("DELETE FROM sites WHERE name = ?", (name,))
        conn.commit()


# Run migration on import
migrate_from_json()
