from __future__ import annotations

import sqlite3
from pathlib import Path
from typing import Any

ROOT = Path(__file__).resolve().parents[1]
DB_PATH = ROOT / 'data' / 'db' / 'youtube_automation.db'

SCHEMA = """
PRAGMA journal_mode=WAL;
CREATE TABLE IF NOT EXISTS projects (
  project_id TEXT PRIMARY KEY,
  title TEXT,
  topic TEXT,
  script_version TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS scenes (
  scene_id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  ordering INTEGER NOT NULL,
  narration TEXT,
  visual_prompt TEXT,
  target_duration INTEGER,
  continuity_group TEXT,
  scene_type TEXT NOT NULL,
  extend_flag INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY(project_id) REFERENCES projects(project_id)
);
CREATE TABLE IF NOT EXISTS tasks (
  task_id_internal TEXT PRIMARY KEY,
  scene_id TEXT,
  endpoint_type TEXT NOT NULL,
  endpoint_path TEXT,
  model_name TEXT,
  kling_task_id TEXT,
  request_id TEXT,
  external_task_id TEXT,
  task_status TEXT NOT NULL,
  task_status_msg TEXT,
  request_payload_json TEXT NOT NULL,
  response_json TEXT,
  callback_payload_json TEXT,
  callback_received_at INTEGER,
  final_unit_deduction TEXT,
  parent_video_id TEXT,
  parent_video_url TEXT,
  parent_video_duration TEXT,
  retry_count INTEGER NOT NULL DEFAULT 0,
  retry_reason TEXT,
  error_category TEXT,
  raw_error TEXT,
  submitted_at INTEGER,
  finished_at INTEGER,
  callback_auth_mode TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY(scene_id) REFERENCES scenes(scene_id)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_kling_task_id ON tasks(kling_task_id);
CREATE INDEX IF NOT EXISTS idx_tasks_scene_id ON tasks(scene_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(task_status);
CREATE TABLE IF NOT EXISTS assets (
  asset_id TEXT PRIMARY KEY,
  scene_id TEXT,
  source_task_id TEXT,
  asset_type TEXT NOT NULL,
  original_url TEXT,
  watermark_url TEXT,
  local_path TEXT,
  downloaded_at INTEGER,
  duration TEXT,
  metadata_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY(scene_id) REFERENCES scenes(scene_id),
  FOREIGN KEY(source_task_id) REFERENCES tasks(task_id_internal)
);
CREATE INDEX IF NOT EXISTS idx_assets_scene_id ON assets(scene_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_assets_source_url_unique ON assets(source_task_id, asset_type, original_url);
CREATE TABLE IF NOT EXISTS task_events (
  event_id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id_internal TEXT,
  event_type TEXT NOT NULL,
  payload_json TEXT,
  dedupe_key TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY(task_id_internal) REFERENCES tasks(task_id_internal)
);CREATE UNIQUE INDEX IF NOT EXISTS idx_task_events_dedupe ON task_events(task_id_internal, event_type, dedupe_key);
"""


def connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys=ON;')
    return conn


def init_db() -> None:
    conn = connect()
    try:
        conn.executescript(SCHEMA)
        conn.commit()
    finally:
        conn.close()


def fetch_one(query: str, params: tuple[Any, ...] = ()):
    conn = connect()
    try:
        row = conn.execute(query, params).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def execute(query: str, params: tuple[Any, ...] = ()) -> None:
    conn = connect()
    try:
        conn.execute(query, params)
        conn.commit()
    finally:
        conn.close()


def reset_db() -> None:
    for suffix in ('', '-wal', '-shm'):
        p = Path(str(DB_PATH) + suffix)
        if p.exists():
            p.unlink()
    init_db()