"""
DEPRECATED: This SQLite database module is deprecated.
Use database_pg.py (PostgreSQL) instead.
This file is kept for reference only and will be removed in a future version.
"""

"""
SQLite data access layer for the Video Production Pipeline.

Tables:
  projects – one row per pipeline run
  images   – one row per generated image (linked to project + scene)
"""

from __future__ import annotations

import json
from datetime import datetime, timezone
from pathlib import Path
from typing import Any

import aiosqlite

_DB_PATH: Path | None = None
_db: aiosqlite.Connection | None = None

SCHEMA = """
CREATE TABLE IF NOT EXISTS projects (
    id            TEXT PRIMARY KEY,
    status        TEXT NOT NULL DEFAULT 'pending',
    mode          TEXT NOT NULL DEFAULT 'plan',
    platform      TEXT NOT NULL DEFAULT 'tiktok',
    character     TEXT NOT NULL DEFAULT '',
    story_preview TEXT NOT NULL DEFAULT '',
    title         TEXT NOT NULL DEFAULT '',
    created_at    TEXT NOT NULL,
    result_json   TEXT
);

CREATE TABLE IF NOT EXISTS images (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id    TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    scene_number  INTEGER NOT NULL,
    image_prompt  TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL DEFAULT '',
    status        TEXT NOT NULL DEFAULT 'pending',
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL,
    UNIQUE(project_id, scene_number)
);

CREATE TABLE IF NOT EXISTS videos (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id    TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    scene_number  INTEGER NOT NULL,
    video_prompt  TEXT NOT NULL DEFAULT '',
    file_path     TEXT NOT NULL DEFAULT '',
    status        TEXT NOT NULL DEFAULT 'pending',
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL,
    UNIQUE(project_id, scene_number)
);

CREATE TABLE IF NOT EXISTS characters (
    id                       TEXT PRIMARY KEY,
    name                     TEXT NOT NULL UNIQUE,
    physical_description     TEXT NOT NULL DEFAULT '',
    style_keywords           TEXT NOT NULL DEFAULT '[]',
    negative_keywords        TEXT NOT NULL DEFAULT '[]',
    hard_prohibitions        TEXT NOT NULL DEFAULT '[]',
    camera_settings          TEXT NOT NULL DEFAULT '',
    lighting_settings        TEXT NOT NULL DEFAULT '',
    pose_rule                TEXT NOT NULL DEFAULT '',
    environment_rule         TEXT NOT NULL DEFAULT '',
    clothing_rule            TEXT NOT NULL DEFAULT '',
    anatomical_highlight_rules TEXT NOT NULL DEFAULT '',
    thumbnail_path           TEXT NOT NULL DEFAULT '',
    is_default               INTEGER NOT NULL DEFAULT 0,
    created_at               TEXT NOT NULL,
    updated_at               TEXT NOT NULL
);
"""


async def init_db(db_path: str | Path | None = None) -> None:
    """Initialise the database (create tables, enable WAL + FK)."""
    global _DB_PATH, _db

    if db_path is None:
        project_root = Path(__file__).resolve().parent.parent
        data_dir = project_root / "data"
        data_dir.mkdir(parents=True, exist_ok=True)
        db_path = data_dir / "pipeline.db"

    _DB_PATH = Path(db_path)
    _DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    _db = await aiosqlite.connect(str(_DB_PATH))
    _db.row_factory = aiosqlite.Row
    await _db.execute("PRAGMA journal_mode=WAL")
    await _db.execute("PRAGMA foreign_keys=ON")
    await _db.executescript(SCHEMA)
    await _db.commit()


async def get_db() -> aiosqlite.Connection:
    """Return the shared database connection."""
    if _db is None:
        raise RuntimeError("Database not initialised – call init_db() first")
    return _db


async def close_db() -> None:
    """Close the database connection."""
    global _db
    if _db is not None:
        await _db.close()
        _db = None


# ---------------------------------------------------------------------------
# Projects
# ---------------------------------------------------------------------------


async def insert_project(
    *,
    id: str,
    status: str = "running",
    mode: str = "plan",
    platform: str = "tiktok",
    character: str = "",
    story_preview: str = "",
    title: str = "",
) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """INSERT INTO projects (id, status, mode, platform, character,
                                 story_preview, title, created_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
        (id, status, mode, platform, character, story_preview, title, now),
    )
    await db.commit()


async def update_project_status(project_id: str, status: str) -> None:
    db = await get_db()
    await db.execute(
        "UPDATE projects SET status = ? WHERE id = ?", (status, project_id)
    )
    await db.commit()


async def update_project_result(
    project_id: str, result: dict[str, Any], title: str = ""
) -> None:
    db = await get_db()
    params: list[Any] = [json.dumps(result)]
    sql = "UPDATE projects SET result_json = ?"
    if title:
        sql += ", title = ?"
        params.append(title)
    sql += " WHERE id = ?"
    params.append(project_id)
    await db.execute(sql, params)
    await db.commit()


async def get_project(project_id: str) -> dict[str, Any] | None:
    db = await get_db()
    cur = await db.execute("SELECT * FROM projects WHERE id = ?", (project_id,))
    row = await cur.fetchone()
    if row is None:
        return None
    proj = dict(row)
    if proj.get("result_json"):
        proj["result"] = json.loads(proj["result_json"])
    else:
        proj["result"] = None
    del proj["result_json"]
    return proj


async def delete_project(project_id: str) -> bool:
    db = await get_db()
    cur = await db.execute("DELETE FROM projects WHERE id = ?", (project_id,))
    await db.commit()
    return cur.rowcount > 0


async def list_projects() -> list[dict[str, Any]]:
    db = await get_db()
    cur = await db.execute(
        """SELECT id, status, mode, platform, character,
                  story_preview, title, created_at
           FROM projects ORDER BY created_at DESC"""
    )
    rows = await cur.fetchall()
    return [dict(r) for r in rows]


# ---------------------------------------------------------------------------
# Images
# ---------------------------------------------------------------------------


async def upsert_image(
    *,
    project_id: str,
    scene_number: int,
    image_prompt: str = "",
    file_path: str = "",
    status: str = "generated",
) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """INSERT INTO images (project_id, scene_number, image_prompt,
                               file_path, status, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON CONFLICT(project_id, scene_number)
           DO UPDATE SET image_prompt = excluded.image_prompt,
                         file_path = excluded.file_path,
                         status = excluded.status,
                         updated_at = excluded.updated_at""",
        (project_id, scene_number, image_prompt, file_path, status, now, now),
    )
    await db.commit()


async def update_image_status(project_id: str, scene_number: int, status: str) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """UPDATE images SET status = ?, updated_at = ?
           WHERE project_id = ? AND scene_number = ?""",
        (status, now, project_id, scene_number),
    )
    await db.commit()


async def update_image_path(project_id: str, scene_number: int, file_path: str) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """UPDATE images SET file_path = ?, status = 'generated', updated_at = ?
           WHERE project_id = ? AND scene_number = ?""",
        (file_path, now, project_id, scene_number),
    )
    await db.commit()


async def get_project_images(project_id: str) -> list[dict[str, Any]]:
    db = await get_db()
    cur = await db.execute(
        """SELECT id, project_id, scene_number, image_prompt,
                  file_path, status, created_at, updated_at
           FROM images WHERE project_id = ?
           ORDER BY scene_number""",
        (project_id,),
    )
    rows = await cur.fetchall()
    return [dict(r) for r in rows]


# ---------------------------------------------------------------------------
# Videos
# ---------------------------------------------------------------------------


async def upsert_video(
    *,
    project_id: str,
    scene_number: int,
    video_prompt: str = "",
    file_path: str = "",
    status: str = "generated",
) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """INSERT INTO videos (project_id, scene_number, video_prompt,
                               file_path, status, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?)
           ON CONFLICT(project_id, scene_number)
           DO UPDATE SET video_prompt = excluded.video_prompt,
                         file_path = excluded.file_path,
                         status = excluded.status,
                         updated_at = excluded.updated_at""",
        (project_id, scene_number, video_prompt, file_path, status, now, now),
    )
    await db.commit()


async def get_project_videos(project_id: str) -> list[dict[str, Any]]:
    db = await get_db()
    cur = await db.execute(
        """SELECT id, project_id, scene_number, video_prompt,
                  file_path, status, created_at, updated_at
           FROM videos WHERE project_id = ?
           ORDER BY scene_number""",
        (project_id,),
    )
    rows = await cur.fetchall()
    return [dict(r) for r in rows]


async def update_video_status(project_id: str, scene_number: int, status: str) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """UPDATE videos SET status = ?, updated_at = ?
           WHERE project_id = ? AND scene_number = ?""",
        (status, now, project_id, scene_number),
    )
    await db.commit()


# ---------------------------------------------------------------------------
# Characters
# ---------------------------------------------------------------------------


def _char_row_to_dict(row: dict[str, Any]) -> dict[str, Any]:
    """Convert a character DB row to a dict with parsed JSON fields."""
    d = dict(row)
    for key in ("style_keywords", "negative_keywords", "hard_prohibitions"):
        val = d.get(key, "[]")
        try:
            d[key] = json.loads(val) if isinstance(val, str) else val
        except (json.JSONDecodeError, TypeError):
            d[key] = []
    return d


async def insert_character(
    *,
    id: str,
    name: str,
    physical_description: str = "",
    style_keywords: list[str] | None = None,
    negative_keywords: list[str] | None = None,
    hard_prohibitions: list[str] | None = None,
    camera_settings: str = "",
    lighting_settings: str = "",
    pose_rule: str = "",
    environment_rule: str = "",
    clothing_rule: str = "",
    anatomical_highlight_rules: str = "",
    thumbnail_path: str = "",
    is_default: bool = False,
) -> None:
    db = await get_db()
    now = datetime.now(timezone.utc).isoformat()
    await db.execute(
        """INSERT OR IGNORE INTO characters
           (id, name, physical_description, style_keywords, negative_keywords,
            hard_prohibitions, camera_settings, lighting_settings, pose_rule,
            environment_rule, clothing_rule, anatomical_highlight_rules,
            thumbnail_path, is_default, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (
            id,
            name,
            physical_description,
            json.dumps(style_keywords or []),
            json.dumps(negative_keywords or []),
            json.dumps(hard_prohibitions or []),
            camera_settings,
            lighting_settings,
            pose_rule,
            environment_rule,
            clothing_rule,
            anatomical_highlight_rules,
            thumbnail_path,
            1 if is_default else 0,
            now,
            now,
        ),
    )
    await db.commit()


async def update_character(char_id: str, updates: dict[str, Any]) -> bool:
    """Update one or more fields on a character."""
    db = await get_db()
    # JSON-encode list fields
    for key in ("style_keywords", "negative_keywords", "hard_prohibitions"):
        if key in updates and isinstance(updates[key], list):
            updates[key] = json.dumps(updates[key])
    updates["updated_at"] = datetime.now(timezone.utc).isoformat()
    set_clause = ", ".join(f"{k} = ?" for k in updates)
    vals = list(updates.values()) + [char_id]
    cur = await db.execute(f"UPDATE characters SET {set_clause} WHERE id = ?", vals)
    await db.commit()
    return cur.rowcount > 0


async def delete_character(char_id: str) -> bool:
    db = await get_db()
    cur = await db.execute("DELETE FROM characters WHERE id = ?", (char_id,))
    await db.commit()
    return cur.rowcount > 0


async def get_character(char_id: str) -> dict[str, Any] | None:
    db = await get_db()
    cur = await db.execute("SELECT * FROM characters WHERE id = ?", (char_id,))
    row = await cur.fetchone()
    return _char_row_to_dict(row) if row else None


async def get_character_by_name(name: str) -> dict[str, Any] | None:
    db = await get_db()
    cur = await db.execute("SELECT * FROM characters WHERE name = ?", (name,))
    row = await cur.fetchone()
    return _char_row_to_dict(row) if row else None


async def list_characters() -> list[dict[str, Any]]:
    db = await get_db()
    cur = await db.execute(
        "SELECT * FROM characters ORDER BY is_default DESC, name ASC"
    )
    rows = await cur.fetchall()
    return [_char_row_to_dict(r) for r in rows]


async def character_count() -> int:
    db = await get_db()
    cur = await db.execute("SELECT COUNT(*) FROM characters")
    row = await cur.fetchone()
    return row[0] if row else 0