"""SQLite persistence for saved stories (revisit; sharing is a post-MVP add-on).

Kept tiny and dependency-free. On HF Spaces storage is ephemeral unless
persistent storage is enabled — saved stories survive a session regardless.
"""
from __future__ import annotations

import json
import os
import sqlite3
import time

import config


def _conn() -> sqlite3.Connection:
    os.makedirs(os.path.dirname(config.DB_PATH) or ".", exist_ok=True)
    conn = sqlite3.connect(config.DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    with _conn() as c:
        c.execute(
            "CREATE TABLE IF NOT EXISTS stories ("
            " id INTEGER PRIMARY KEY AUTOINCREMENT,"
            " name TEXT, style TEXT, difficulty TEXT,"
            " story_json TEXT, created REAL)")


def save_story(name: str, style: str, difficulty: str, story_dict: dict) -> int:
    with _conn() as c:
        cur = c.execute(
            "INSERT INTO stories(name, style, difficulty, story_json, created) "
            "VALUES(?,?,?,?,?)",
            (name, style, difficulty, json.dumps(story_dict), time.time()))
        return int(cur.lastrowid)


def list_stories(limit: int = 20) -> list[dict]:
    with _conn() as c:
        rows = c.execute(
            "SELECT id, name, style, difficulty, created FROM stories "
            "ORDER BY created DESC LIMIT ?", (limit,)).fetchall()
        return [dict(r) for r in rows]


def get_story(story_id: int) -> dict | None:
    with _conn() as c:
        row = c.execute("SELECT * FROM stories WHERE id=?", (story_id,)).fetchone()
        if not row:
            return None
        d = dict(row)
        d["story"] = json.loads(d.pop("story_json"))
        return d