"""
SQLite storage for tasks and settings.
The DB file lives at DATA_DIR/uploader.db so it persists across
requests in the same running container.

DATA_DIR defaults to a "data" folder next to this file, which is
writable in an ordinary Docker container. If you attach a persistent
storage volume on Hugging Face, set the DATA_DIR env var / secret to
its mount path (e.g. /data) so the database survives a Space rebuild.
"""
import sqlite3
import os
import time
from contextlib import contextmanager

_DEFAULT_DATA_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data")
DATA_DIR = os.environ.get("DATA_DIR") or _DEFAULT_DATA_DIR

try:
    os.makedirs(DATA_DIR, exist_ok=True)
except PermissionError:
    DATA_DIR = "/tmp/fb-uploader-data"
    os.makedirs(DATA_DIR, exist_ok=True)

DB_PATH = os.path.join(DATA_DIR, "uploader.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_url TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    privacy TEXT NOT NULL DEFAULT 'SELF',
    status TEXT NOT NULL DEFAULT 'pending',
    video_id TEXT,
    cdn_url TEXT,
    error_message TEXT,
    attempts INTEGER NOT NULL DEFAULT 0,
    created_at REAL NOT NULL,
    updated_at REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
);
"""

# status values: pending -> uploading -> processing -> completed
#                any of the above -> failed


@contextmanager
def get_conn():
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL;")
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def init_db():
    with get_conn() as conn:
        conn.executescript(SCHEMA)


# ---------- settings ----------

def set_setting(key: str, value: str):
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO settings (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value=excluded.value",
            (key, value),
        )


def get_setting(key: str, default=None):
    with get_conn() as conn:
        row = conn.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
        return row["value"] if row else default


# ---------- tasks ----------

def create_task(video_url: str, title: str, description: str, privacy: str = "SELF") -> int:
    now = time.time()
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO tasks (video_url, title, description, privacy, status, "
            "created_at, updated_at) VALUES (?, ?, ?, ?, 'pending', ?, ?)",
            (video_url, title, description, privacy, now, now),
        )
        return cur.lastrowid


def list_tasks():
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM tasks ORDER BY id DESC").fetchall()
        return [dict(r) for r in rows]


def get_task(task_id: int):
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
        return dict(row) if row else None


def get_tasks_by_status(status: str):
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM tasks WHERE status=?", (status,)).fetchall()
        return [dict(r) for r in rows]


def update_task(task_id: int, **fields):
    if not fields:
        return
    fields["updated_at"] = time.time()
    cols = ", ".join(f"{k}=?" for k in fields)
    values = list(fields.values()) + [task_id]
    with get_conn() as conn:
        conn.execute(f"UPDATE tasks SET {cols} WHERE id=?", values)


def delete_task(task_id: int):
    with get_conn() as conn:
        conn.execute("DELETE FROM tasks WHERE id=?", (task_id,))