"""
database.py — Tiny Civilization persistent storage layer.
SQLite-backed, safe for concurrent Gradio calls.
"""
import sqlite3
import json
import os
from datetime import datetime
from typing import Optional

# ── DB path: prefer HF /data (persistent volume), fallback to cwd ──
_DATA_DIRS = ["/data", "."]
DB_PATH = os.getenv("TINY_DB_PATH", "")
if not DB_PATH:
    for _d in _DATA_DIRS:
        try:
            os.makedirs(_d, exist_ok=True)
            _t = os.path.join(_d, ".wtest"); open(_t,"w").write("ok"); os.remove(_t)
            DB_PATH = os.path.join(_d, "tiny_civilization.db"); break
        except Exception: continue
    if not DB_PATH: DB_PATH = "tiny_civilization.db"


def _conn() -> sqlite3.Connection:
    c = sqlite3.connect(DB_PATH, check_same_thread=False, timeout=10)
    c.row_factory = sqlite3.Row
    return c


# ─────────────────────────────────────────────────────────────────
# Schema + seed
# ─────────────────────────────────────────────────────────────────
def init_db() -> None:
    with _conn() as con:
        cur = con.cursor()
        cur.execute("""CREATE TABLE IF NOT EXISTS days (
            day_number INTEGER PRIMARY KEY,
            headline TEXT NOT NULL,
            full_newspaper_text TEXT NOT NULL,
            timestamp TEXT NOT NULL
        )""")
        cur.execute("""CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            day_number INTEGER NOT NULL,
            actor TEXT NOT NULL,
            action TEXT NOT NULL,
            target TEXT NOT NULL,
            description TEXT NOT NULL
        )""")
        cur.execute("""CREATE TABLE IF NOT EXISTS creatures (
            name TEXT PRIMARY KEY,
            relationship_scores TEXT NOT NULL DEFAULT '{}',
            inventory TEXT NOT NULL DEFAULT '[]'
        )""")
        cur.execute("""CREATE TABLE IF NOT EXISTS nudges (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            day_number INTEGER NOT NULL,
            nudge_type TEXT NOT NULL,
            nudge_value TEXT NOT NULL
        )""")
        con.commit()

        # ── Seed creatures ─────────────────────────────────────────
        _SEED = {
            "fox": {
                "relationships": {"badger": 42, "squirrel": 61, "mole": 55},
                "inventory": ["forged certificate of merit", "silk scarf (dubious origin)"],
            },
            "badger": {
                "relationships": {"fox": 28, "squirrel": 67, "mole": 72},
                "inventory": ["ancient grudge (well-preserved)", "favourite grey stone"],
            },
            "squirrel": {
                "relationships": {"fox": 63, "badger": 70, "mole": 48},
                "inventory": ["seven-and-a-half acorns", "borrowed umbrella (decade old)"],
            },
            "mole": {
                "relationships": {"fox": 51, "badger": 76, "squirrel": 53},
                "inventory": ["map of secret tunnels", "crystal monocle", "lost button"],
            },
        }
        for name, data in _SEED.items():
            if not cur.execute("SELECT 1 FROM creatures WHERE name=?", (name,)).fetchone():
                cur.execute(
                    "INSERT INTO creatures (name,relationship_scores,inventory) VALUES (?,?,?)",
                    (name, json.dumps(data["relationships"]), json.dumps(data["inventory"])),
                )
        con.commit()

        # ── Seed Day 0: the Founding ───────────────────────────────
        if not cur.execute("SELECT 1 FROM days WHERE day_number=0").fetchone():
            founding_text = (
                "TINYWICK HOLLOW DECLARES ITSELF A CIVILISATION TODAY\n\n"
                "In a development that surprised absolutely no one who knows these four, Tinywick "
                "Hollow has formally declared itself a civilisation. The founding document was "
                "signed by Beatrice Badger (who insists it must be legally binding), Reginald "
                "Fox (who has already forged three certified copies), Cornelius Squirrel (who "
                "invented a device to sign it faster, then signed it twice by mistake), and "
                "Millicent Mole (who observed that the document was, in a sense, already signed "
                "underground, and then descended). The future of Tinywick Hollow remains, as "
                "always, magnificently uncertain.\n\n"
                "WEATHER: Portentous, with scattered significance.\n"
                "FOX: Forged three certificates before breakfast.\n"
                "BADGER: Insisted on thirteen constitutional amendments before lunch.\n"
                "SQUIRREL: Invented a signing machine! It signed the wrong document!\n"
                "MOLE: Something is already happening underground."
            )
            cur.execute(
                "INSERT INTO days (day_number,headline,full_newspaper_text,timestamp) VALUES (?,?,?,?)",
                (0, "TINYWICK HOLLOW DECLARES ITSELF A CIVILISATION TODAY",
                 founding_text, datetime.now().isoformat())
            )
        con.commit()


# ─────────────────────────────────────────────────────────────────
# Days
# ─────────────────────────────────────────────────────────────────
def save_day(day_number: int, headline: str, full_newspaper_text: str) -> None:
    with _conn() as con:
        con.execute(
            "INSERT OR REPLACE INTO days (day_number,headline,full_newspaper_text,timestamp) VALUES (?,?,?,?)",
            (day_number, headline, full_newspaper_text, datetime.now().isoformat()),
        ); con.commit()

def get_latest_day() -> Optional[dict]:
    with _conn() as con:
        row = con.execute("SELECT * FROM days ORDER BY day_number DESC LIMIT 1").fetchone()
    return dict(row) if row else None

def get_day(day_number: int) -> Optional[dict]:
    with _conn() as con:
        row = con.execute("SELECT * FROM days WHERE day_number=?", (day_number,)).fetchone()
    return dict(row) if row else None

def get_all_headlines() -> list[tuple[int, str]]:
    with _conn() as con:
        rows = con.execute("SELECT day_number,headline FROM days ORDER BY day_number DESC").fetchall()
    return [(r["day_number"], r["headline"]) for r in rows]

def get_next_day_number() -> int:
    with _conn() as con:
        row = con.execute("SELECT MAX(day_number) AS m FROM days").fetchone()
    return (row["m"] or 0) + 1


# ─────────────────────────────────────────────────────────────────
# Events
# ─────────────────────────────────────────────────────────────────
def save_event(day_number: int, actor: str, action: str, target: str, description: str) -> None:
    with _conn() as con:
        con.execute(
            "INSERT INTO events (day_number,actor,action,target,description) VALUES (?,?,?,?,?)",
            (day_number, actor, action, target, description),
        ); con.commit()

def get_events_for_day(day_number: int) -> list[dict]:
    with _conn() as con:
        rows = con.execute(
            "SELECT actor,action,target,description FROM events WHERE day_number=?", (day_number,)
        ).fetchall()
    return [dict(r) for r in rows]


# ─────────────────────────────────────────────────────────────────
# Nudges
# ─────────────────────────────────────────────────────────────────
def save_nudge(day_number: int, nudge_type: str, nudge_value: str) -> None:
    with _conn() as con:
        con.execute("INSERT INTO nudges (day_number,nudge_type,nudge_value) VALUES (?,?,?)",
                    (day_number, nudge_type, nudge_value)); con.commit()

def get_recent_nudges(limit: int = 4) -> list[dict]:
    with _conn() as con:
        rows = con.execute(
            "SELECT day_number,nudge_type,nudge_value FROM nudges ORDER BY id DESC LIMIT ?", (limit,)
        ).fetchall()
    return [dict(r) for r in rows]


# ─────────────────────────────────────────────────────────────────
# Creatures
# ─────────────────────────────────────────────────────────────────
def _parse_creature(row: sqlite3.Row) -> dict:
    d = dict(row)
    d["relationship_scores"] = json.loads(d["relationship_scores"])
    d["inventory"] = json.loads(d["inventory"])
    return d

def get_creature(name: str) -> Optional[dict]:
    with _conn() as con:
        row = con.execute("SELECT * FROM creatures WHERE name=?", (name,)).fetchone()
    return _parse_creature(row) if row else None

def get_all_creatures() -> list[dict]:
    with _conn() as con:
        rows = con.execute("SELECT * FROM creatures").fetchall()
    return [_parse_creature(r) for r in rows]

def update_creature(name: str, relationship_scores: Optional[dict]=None, inventory: Optional[list]=None) -> None:
    with _conn() as con:
        if relationship_scores is not None and inventory is not None:
            con.execute("UPDATE creatures SET relationship_scores=?,inventory=? WHERE name=?",
                        (json.dumps(relationship_scores), json.dumps(inventory), name))
        elif relationship_scores is not None:
            con.execute("UPDATE creatures SET relationship_scores=? WHERE name=?",
                        (json.dumps(relationship_scores), name))
        elif inventory is not None:
            con.execute("UPDATE creatures SET inventory=? WHERE name=?",
                        (json.dumps(inventory), name))
        con.commit()


# ─────────────────────────────────────────────────────────────────
# Civilisation stats
# ─────────────────────────────────────────────────────────────────
def get_civ_stats() -> dict:
    with _conn() as con:
        total_days   = con.execute("SELECT COUNT(*) FROM days").fetchone()[0]
        total_events = con.execute("SELECT COUNT(*) FROM events").fetchone()[0]
        total_nudges = con.execute("SELECT COUNT(*) FROM nudges").fetchone()[0]
    creatures = get_all_creatures()
    best_pair = ("?", "?", 0); worst_pair = ("?", "?", 100)
    for c in creatures:
        for other, score in c["relationship_scores"].items():
            pair = tuple(sorted([c["name"], other]))
            if score > best_pair[2]:  best_pair  = (*pair, score)
            if score < worst_pair[2]: worst_pair = (*pair, score)
    dominant = max(creatures, key=lambda c: sum(c["relationship_scores"].values()), default={"name":"?"})
    return {
        "total_days": total_days,
        "total_events": total_events,
        "total_nudges": total_nudges,
        "best_pair": best_pair,
        "worst_pair": worst_pair,
        "dominant": dominant["name"],
    }