# db/bulk.py
import sqlite3
from typing import Optional


def bulk_db(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.row_factory = sqlite3.Row
    return conn


def _table_has_column(conn: sqlite3.Connection, table: str, col: str) -> bool:
    cur = conn.cursor()
    cur.execute(f"PRAGMA table_info({table})")
    cols = [r[1] for r in cur.fetchall()]
    return col in cols


def init_bulk_db(db_path: str) -> None:
    """
    Crea la tabla bulk_jobs si no existe.
    Nota: mantenemos access_token NOT NULL por compatibilidad con tu versión anterior.
    Añadimos account_alias opcional (para refrescar tokens en el cron).
    """
    conn = bulk_db(db_path)
    cur = conn.cursor()

    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS bulk_jobs (
          id TEXT PRIMARY KEY,
          created_at_ts INTEGER NOT NULL,
          publish_at_ts INTEGER NOT NULL,
          next_attempt_ts INTEGER NOT NULL,
          status TEXT NOT NULL,              -- scheduled | submitting | submitted | complete | failed
          attempts INTEGER NOT NULL DEFAULT 0,
          last_error TEXT,

          -- token actual (puede caducar). Se mantiene por compatibilidad.
          access_token TEXT NOT NULL,

          -- alias opcional para refrescar token en cron (recomendado)
          account_alias TEXT,

          title TEXT,
          privacy_level TEXT NOT NULL,
          allow_comment INTEGER NOT NULL,
          allow_duet INTEGER NOT NULL,
          allow_stitch INTEGER NOT NULL,
          commercial_toggle INTEGER NOT NULL,
          brand_organic_toggle INTEGER NOT NULL,
          brand_content_toggle INTEGER NOT NULL,
          is_aigc INTEGER NOT NULL,

          stored_filename TEXT NOT NULL,
          original_filename TEXT,
          video_url TEXT,

          publish_id TEXT,
          last_status TEXT,
          last_fail_reason TEXT,
          updated_at_ts INTEGER NOT NULL
        )
        """
    )

    # Migración suave: añadir account_alias si vienes de una tabla antigua
    if not _table_has_column(conn, "bulk_jobs", "account_alias"):
        cur.execute("ALTER TABLE bulk_jobs ADD COLUMN account_alias TEXT")

    conn.commit()
    conn.close()