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

Uses asyncpg for high-performance async database operations.
Supports storing assets (images, videos, audios) as BYTEA.

Tables:
  projects   – one row per pipeline run
  images     – one row per generated image (with optional BYTEA data)
  videos     – one row per generated video (with optional BYTEA data)
  audios     – one row per generated audio (with optional BYTEA data)
  characters – character templates
"""

from __future__ import annotations

import json
import os
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Optional
import base64

import asyncpg

_pool: Optional[asyncpg.Pool] = None


DATABASE_URL = os.environ.get("DATABASE_URL")
if not DATABASE_URL:
    raise EnvironmentError(
        "DATABASE_URL environment variable is required. "
        "Set it in .env or docker-compose.yml. "
        "Example: postgresql://user:password@localhost:5432/dbname"
    )


async def init_db() -> None:
    """Initialize the database connection pool and ensure tables exist."""
    global _pool

    if _pool is not None:
        return

    database_url = DATABASE_URL

    _pool = await asyncpg.create_pool(
        database_url,
        min_size=2,
        max_size=10,
        command_timeout=60,
    )

    async with _pool.acquire() as conn:
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS grok_accounts (
                id SERIAL PRIMARY KEY,
                label VARCHAR(100) NOT NULL DEFAULT '',
                sso_token TEXT NOT NULL,
                sso_rw_token TEXT NOT NULL,
                user_id VARCHAR(100) NOT NULL,
                is_active BOOLEAN NOT NULL DEFAULT TRUE,
                last_used_at TIMESTAMPTZ,
                usage_count INTEGER NOT NULL DEFAULT 0,
                daily_usage_count INTEGER NOT NULL DEFAULT 0,
                daily_usage_reset_at DATE,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
        """)
        await conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_grok_accounts_rotation
            ON grok_accounts(is_active, last_used_at NULLS FIRST, usage_count ASC)
        """)
        
        # World Styles table
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS world_styles (
                id VARCHAR(100) PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                preset_type VARCHAR(50) NOT NULL DEFAULT 'custom',
                background_rules TEXT NOT NULL DEFAULT '',
                floor_rules TEXT NOT NULL DEFAULT '',
                secondary_characters_rules TEXT NOT NULL DEFAULT '',
                lighting_rules TEXT NOT NULL DEFAULT '',
                props_rules TEXT NOT NULL DEFAULT '',
                architecture_allowed BOOLEAN NOT NULL DEFAULT TRUE,
                is_preset BOOLEAN NOT NULL DEFAULT FALSE,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
        """)
        
        # Clothing Styles table
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS clothing_styles (
                id VARCHAR(100) PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                preset_type VARCHAR(50) NOT NULL DEFAULT 'custom',
                clothing_rules TEXT NOT NULL DEFAULT '',
                opacity_rules TEXT NOT NULL DEFAULT '',
                max_pieces INTEGER NOT NULL DEFAULT 2,
                is_preset BOOLEAN NOT NULL DEFAULT FALSE,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
        """)
        
        # Production Rules table
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS production_rules (
                id VARCHAR(100) PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                camera_settings TEXT NOT NULL DEFAULT '',
                pose_rule TEXT NOT NULL DEFAULT '',
                shot_intercalation_rules TEXT NOT NULL DEFAULT '',
                death_scene_rules TEXT NOT NULL DEFAULT '',
                child_rules TEXT NOT NULL DEFAULT '',
                zombie_rules TEXT NOT NULL DEFAULT '',
                consequence_philosophy TEXT NOT NULL DEFAULT '',
                scale_rule TEXT NOT NULL DEFAULT '',
                is_preset BOOLEAN NOT NULL DEFAULT FALSE,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
        """)


async def get_pool() -> asyncpg.Pool:
    """Return the shared connection pool."""
    if _pool is None:
        await init_db()
    return _pool


async def close_db() -> None:
    """Close the database connection pool."""
    global _pool
    if _pool is not None:
        await _pool.close()
        _pool = 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:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO projects (id, status, mode, platform, character,
                                     story_preview, title, created_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8)""",
            id,
            status,
            mode,
            platform,
            character,
            story_preview,
            title,
            datetime.now(timezone.utc),
        )


async def update_project_status(project_id: str, status: str) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            "UPDATE projects SET status = $1 WHERE id = $2", status, project_id
        )


async def update_project_result(
    project_id: str, result: dict[str, Any], title: str = ""
) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        if title:
            await conn.execute(
                "UPDATE projects SET result_json = $1, title = $2 WHERE id = $3",
                json.dumps(result),
                title,
                project_id,
            )
        else:
            await conn.execute(
                "UPDATE projects SET result_json = $1 WHERE id = $2",
                json.dumps(result),
                project_id,
            )


async def get_project(project_id: str) -> dict[str, Any] | None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM projects WHERE id = $1", project_id)
        if row is None:
            return None
        proj = dict(row)
        # Parse result_json
        if proj.get("result_json"):
            if isinstance(proj["result_json"], str):
                proj["result"] = json.loads(proj["result_json"])
            else:
                proj["result"] = proj["result_json"]
        else:
            proj["result"] = None
        del proj["result_json"]
        # Convert UUID to string
        proj["id"] = str(proj["id"])
        # Convert datetime to ISO string
        if proj.get("created_at"):
            proj["created_at"] = proj["created_at"].isoformat()
        return proj


async def delete_project(project_id: str) -> bool:
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute("DELETE FROM projects WHERE id = $1", project_id)
        return result == "DELETE 1"


async def list_projects() -> list[dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """SELECT id, status, mode, platform, character,
                      story_preview, title, created_at
               FROM projects ORDER BY created_at DESC"""
        )
        result = []
        for r in rows:
            d = dict(r)
            d["id"] = str(d["id"])
            if d.get("created_at"):
                d["created_at"] = d["created_at"].isoformat()
            result.append(d)
        return result


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


async def upsert_image(
    *,
    project_id: str,
    scene_number: int,
    image_prompt: str = "",
    file_path: str = "",
    image_data: bytes | None = None,
    mime_type: str = "image/png",
    status: str = "generated",
) -> None:
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    file_size = len(image_data) if image_data else None

    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO images (project_id, scene_number, image_prompt,
                                   file_path, image_data, mime_type, file_size_bytes,
                                   status, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
               ON CONFLICT(project_id, scene_number)
               DO UPDATE SET image_prompt = EXCLUDED.image_prompt,
                             file_path = EXCLUDED.file_path,
                             image_data = COALESCE(EXCLUDED.image_data, images.image_data),
                             mime_type = EXCLUDED.mime_type,
                             file_size_bytes = COALESCE(EXCLUDED.file_size_bytes, images.file_size_bytes),
                             status = EXCLUDED.status,
                             updated_at = EXCLUDED.updated_at""",
            project_id,
            scene_number,
            image_prompt,
            file_path,
            image_data,
            mime_type,
            file_size,
            status,
            now,
            now,
        )


async def update_image_status(project_id: str, scene_number: int, status: str) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """UPDATE images SET status = $1, updated_at = $2
               WHERE project_id = $3 AND scene_number = $4""",
            status,
            datetime.now(timezone.utc),
            project_id,
            scene_number,
        )


async def update_image_path(project_id: str, scene_number: int, file_path: str) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """UPDATE images SET file_path = $1, status = 'generated', updated_at = $2
               WHERE project_id = $3 AND scene_number = $4""",
            file_path,
            datetime.now(timezone.utc),
            project_id,
            scene_number,
        )


async def update_image_data(
    project_id: str, scene_number: int, image_data: bytes, mime_type: str = "image/png"
) -> None:
    """Store image binary data in the database."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """UPDATE images SET image_data = $1, mime_type = $2, 
               file_size_bytes = $3, updated_at = $4
               WHERE project_id = $5 AND scene_number = $6""",
            image_data,
            mime_type,
            len(image_data),
            datetime.now(timezone.utc),
            project_id,
            scene_number,
        )


async def get_project_images(project_id: str) -> list[dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """SELECT id, project_id, scene_number, image_prompt,
                      file_path, mime_type, file_size_bytes, status, 
                      created_at, updated_at
               FROM images WHERE project_id = $1
               ORDER BY scene_number""",
            project_id,
        )
        result = []
        for r in rows:
            d = dict(r)
            d["project_id"] = str(d["project_id"])
            if d.get("created_at"):
                d["created_at"] = d["created_at"].isoformat()
            if d.get("updated_at"):
                d["updated_at"] = d["updated_at"].isoformat()
            result.append(d)
        return result


async def get_image_data(
    project_id: str, scene_number: int
) -> tuple[bytes | None, str]:
    """Get image binary data and mime type."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """SELECT image_data, mime_type FROM images 
               WHERE project_id = $1 AND scene_number = $2""",
            project_id,
            scene_number,
        )
        if row:
            return row["image_data"], row["mime_type"] or "image/png"
        return None, "image/png"


async def get_image_data_base64(
    project_id: str, scene_number: int
) -> tuple[str | None, str]:
    """Get image as Base64 string."""
    data, mime_type = await get_image_data(project_id, scene_number)
    if data:
        return base64.b64encode(data).decode(), mime_type
    return None, mime_type


async def delete_image(project_id: str, scene_number: int) -> bool:
    """Delete an image record for a given project and scene."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute(
            """DELETE FROM images WHERE project_id = $1 AND scene_number = $2""",
            project_id,
            scene_number,
        )
        return result == "DELETE 1"


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


async def upsert_video(
    *,
    project_id: str,
    scene_number: int,
    video_prompt: str = "",
    file_path: str = "",
    video_data: bytes | None = None,
    mime_type: str = "video/mp4",
    duration_seconds: float | None = None,
    status: str = "generated",
) -> None:
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    file_size = len(video_data) if video_data else None

    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO videos (project_id, scene_number, video_prompt,
                                   file_path, video_data, mime_type, file_size_bytes,
                                   duration_seconds, status, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
               ON CONFLICT(project_id, scene_number)
               DO UPDATE SET video_prompt = EXCLUDED.video_prompt,
                             file_path = EXCLUDED.file_path,
                             video_data = COALESCE(EXCLUDED.video_data, videos.video_data),
                             mime_type = EXCLUDED.mime_type,
                             file_size_bytes = COALESCE(EXCLUDED.file_size_bytes, videos.file_size_bytes),
                             duration_seconds = COALESCE(EXCLUDED.duration_seconds, videos.duration_seconds),
                             status = EXCLUDED.status,
                             updated_at = EXCLUDED.updated_at""",
            project_id,
            scene_number,
            video_prompt,
            file_path,
            video_data,
            mime_type,
            file_size,
            duration_seconds,
            status,
            now,
            now,
        )


async def get_project_videos(project_id: str) -> list[dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """SELECT id, project_id, scene_number, video_prompt,
                      file_path, mime_type, file_size_bytes, duration_seconds,
                      status, created_at, updated_at
               FROM videos WHERE project_id = $1
               ORDER BY scene_number""",
            project_id,
        )
        result = []
        for r in rows:
            d = dict(r)
            d["project_id"] = str(d["project_id"])
            if d.get("created_at"):
                d["created_at"] = d["created_at"].isoformat()
            if d.get("updated_at"):
                d["updated_at"] = d["updated_at"].isoformat()
            result.append(d)
        return result


async def update_video_status(project_id: str, scene_number: int, status: str) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """UPDATE videos SET status = $1, updated_at = $2
               WHERE project_id = $3 AND scene_number = $4""",
            status,
            datetime.now(timezone.utc),
            project_id,
            scene_number,
        )


async def get_video_data(
    project_id: str, scene_number: int
) -> tuple[bytes | None, str]:
    """Get video binary data and mime type."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """SELECT video_data, mime_type FROM videos 
               WHERE project_id = $1 AND scene_number = $2""",
            project_id,
            scene_number,
        )
        if row:
            return row["video_data"], row["mime_type"] or "video/mp4"
        return None, "video/mp4"


# ---------------------------------------------------------------------------
# Audios (NEW)
# ---------------------------------------------------------------------------


async def upsert_audio(
    *,
    project_id: str,
    scene_number: int,
    tts_text: str = "",
    tts_provider: str = "elevenlabs",
    file_path: str = "",
    audio_data: bytes | None = None,
    mime_type: str = "audio/wav",
    duration_seconds: float | None = None,
    status: str = "generated",
) -> None:
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    file_size = len(audio_data) if audio_data else None

    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO audios (project_id, scene_number, tts_text, tts_provider,
                                   file_path, audio_data, mime_type, file_size_bytes,
                                   duration_seconds, status, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
               ON CONFLICT(project_id, scene_number)
               DO UPDATE SET tts_text = EXCLUDED.tts_text,
                             tts_provider = EXCLUDED.tts_provider,
                             file_path = EXCLUDED.file_path,
                             audio_data = COALESCE(EXCLUDED.audio_data, audios.audio_data),
                             mime_type = EXCLUDED.mime_type,
                             file_size_bytes = COALESCE(EXCLUDED.file_size_bytes, audios.file_size_bytes),
                             duration_seconds = COALESCE(EXCLUDED.duration_seconds, audios.duration_seconds),
                             status = EXCLUDED.status,
                             updated_at = EXCLUDED.updated_at""",
            project_id,
            scene_number,
            tts_text,
            tts_provider,
            file_path,
            audio_data,
            mime_type,
            file_size,
            duration_seconds,
            status,
            now,
            now,
        )


async def get_project_audios(project_id: str) -> list[dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """SELECT id, project_id, scene_number, tts_text, tts_provider,
                      file_path, mime_type, file_size_bytes, duration_seconds,
                      status, created_at, updated_at
               FROM audios WHERE project_id = $1
               ORDER BY scene_number""",
            project_id,
        )
        result = []
        for r in rows:
            d = dict(r)
            d["project_id"] = str(d["project_id"])
            if d.get("created_at"):
                d["created_at"] = d["created_at"].isoformat()
            if d.get("updated_at"):
                d["updated_at"] = d["updated_at"].isoformat()
            result.append(d)
        return result


# ---------------------------------------------------------------------------
# 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)
    d["id"] = str(d["id"])

    # Handle JSONB fields (already parsed by asyncpg)
    for key in ("style_keywords", "negative_keywords", "hard_prohibitions"):
        val = d.get(key)
        if val is None:
            d[key] = []
        elif isinstance(val, str):
            try:
                d[key] = json.loads(val)
            except (json.JSONDecodeError, TypeError):
                d[key] = []
        # If already a list, keep as is

    # Convert timestamps
    if d.get("created_at"):
        d["created_at"] = d["created_at"].isoformat()
    if d.get("updated_at"):
        d["updated_at"] = d["updated_at"].isoformat()

    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 = "",
    consequence_philosophy: str = "",
    shot_variety_rules: str = "",
    thumbnail_path: str = "",
    is_default: bool = False,
) -> None:
    pool = await get_pool()
    now = datetime.now(timezone.utc)

    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT 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,
                consequence_philosophy, shot_variety_rules,
                thumbnail_path, is_default, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
               ON CONFLICT (id) DO NOTHING""",
            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,
            consequence_philosophy,
            shot_variety_rules,
            thumbnail_path,
            is_default,
            now,
            now,
        )


async def update_character(char_id: str, updates: dict[str, Any]) -> bool:
    """Update one or more fields on a character."""
    pool = await get_pool()

    # 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)

    # Build SET clause with numbered parameters
    set_parts = []
    values = []
    for i, (key, value) in enumerate(updates.items(), start=1):
        set_parts.append(f"{key} = ${i}")
        values.append(value)

    set_clause = ", ".join(set_parts)
    values.append(char_id)  # For WHERE clause

    async with pool.acquire() as conn:
        result = await conn.execute(
            f"UPDATE characters SET {set_clause} WHERE id = ${len(values)}", *values
        )
        return result == "UPDATE 1"


async def delete_character(char_id: str) -> bool:
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute("DELETE FROM characters WHERE id = $1", char_id)
        return result == "DELETE 1"


async def get_character(char_id: str) -> dict[str, Any] | None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM characters WHERE id = $1", char_id)
        return _char_row_to_dict(row) if row else None


async def get_character_by_name(name: str) -> dict[str, Any] | None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM characters WHERE name = $1", name)
        return _char_row_to_dict(row) if row else None


async def list_characters() -> list[dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT * FROM characters ORDER BY is_default DESC, name ASC"
        )
        return [_char_row_to_dict(r) for r in rows]


async def character_count() -> int:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT COUNT(*) as count FROM characters")
        return row["count"] if row else 0


# ---------------------------------------------------------------------------
# Settings CRUD
# ---------------------------------------------------------------------------


async def get_setting(key: str) -> str | None:
    """Get a setting value by key."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT value FROM settings WHERE key = $1", key)
        return row["value"] if row else None


async def set_setting(key: str, value: str, description: str | None = None) -> None:
    """Set a setting value (upsert)."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        if description:
            await conn.execute(
                """
                INSERT INTO settings (key, value, description)
                VALUES ($1, $2, $3)
                ON CONFLICT (key) DO UPDATE SET value = $2, description = $3
                """,
                key,
                value,
                description,
            )
        else:
            await conn.execute(
                """
                INSERT INTO settings (key, value)
                VALUES ($1, $2)
                ON CONFLICT (key) DO UPDATE SET value = $2
                """,
                key,
                value,
            )


async def get_all_settings() -> dict[str, str]:
    """Get all settings as a dict."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch("SELECT key, value FROM settings")
        return {row["key"]: row["value"] for row in rows}


async def delete_setting(key: str) -> None:
    """Delete a setting by key."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute("DELETE FROM settings WHERE key = $1", key)


# ---------------------------------------------------------------------------
# Grok Accounts (multi-cookie rotation)
# ---------------------------------------------------------------------------


async def insert_grok_account(*, label: str, sso_token: str, sso_rw_token: str, user_id: str) -> dict[str, Any]:
    """Insert a new Grok account and return it."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """INSERT INTO grok_accounts (label, sso_token, sso_rw_token, user_id)
               VALUES ($1, $2, $3, $4)
               RETURNING *""",
            label, sso_token, sso_rw_token, user_id,
        )
        return dict(row)


async def list_grok_accounts() -> list[dict[str, Any]]:
    """List all Grok accounts."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT * FROM grok_accounts ORDER BY created_at"
        )
        return [dict(r) for r in rows]


async def get_next_grok_account() -> dict[str, Any] | None:
    """Pick the least-recently-used active Grok account (atomic rotation).

    Uses FOR UPDATE SKIP LOCKED to handle concurrent batch runs safely.
    Resets daily_usage_count when date has changed.
    """
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("""
            UPDATE grok_accounts
            SET last_used_at = NOW(),
                usage_count = usage_count + 1,
                daily_usage_count = CASE
                    WHEN daily_usage_reset_at IS NULL OR daily_usage_reset_at < CURRENT_DATE THEN 1
                    ELSE daily_usage_count + 1
                END,
                daily_usage_reset_at = CURRENT_DATE
            WHERE id = (
                SELECT id FROM grok_accounts
                WHERE is_active = TRUE
                ORDER BY last_used_at NULLS FIRST, usage_count ASC
                LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
            RETURNING *
        """)
        return dict(row) if row else None


async def update_grok_account(account_id: int, **fields) -> dict[str, Any] | None:
    """Update a Grok account. Accepts: label, sso_token, sso_rw_token, user_id, is_active."""
    allowed = {"label", "sso_token", "sso_rw_token", "user_id", "is_active"}
    updates = {k: v for k, v in fields.items() if k in allowed}
    if not updates:
        return None
    pool = await get_pool()
    set_clauses = ", ".join(f"{k} = ${i+2}" for i, k in enumerate(updates))
    values = [account_id] + list(updates.values())
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            f"UPDATE grok_accounts SET {set_clauses} WHERE id = $1 RETURNING *",
            *values,
        )
        return dict(row) if row else None


async def delete_grok_account(account_id: int) -> bool:
    """Delete a Grok account. Returns True if deleted."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute(
            "DELETE FROM grok_accounts WHERE id = $1", account_id
        )
        return result == "DELETE 1"


# ---------------------------------------------------------------------------
# World Styles CRUD
# ---------------------------------------------------------------------------


async def list_world_styles() -> list[dict[str, Any]]:
    """List all world styles (custom only, presets come from presets.py)."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT * FROM world_styles WHERE is_preset = FALSE ORDER BY name"
        )
        return [dict(r) for r in rows]


async def get_world_style(style_id: str) -> dict[str, Any] | None:
    """Get a world style by ID."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM world_styles WHERE id = $1", style_id)
        return dict(row) if row else None


async def insert_world_style(
    *,
    id: str,
    name: str,
    preset_type: str = "custom",
    background_rules: str = "",
    floor_rules: str = "",
    secondary_characters_rules: str = "",
    lighting_rules: str = "",
    props_rules: str = "",
    architecture_allowed: bool = True,
) -> None:
    """Insert a new world style."""
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO world_styles (id, name, preset_type, background_rules, floor_rules,
                                         secondary_characters_rules, lighting_rules, props_rules,
                                         architecture_allowed, is_preset, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, FALSE, $10, $11)""",
            id, name, preset_type, background_rules, floor_rules,
            secondary_characters_rules, lighting_rules, props_rules,
            architecture_allowed, now, now,
        )


async def delete_world_style(style_id: str) -> bool:
    """Delete a world style."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute(
            "DELETE FROM world_styles WHERE id = $1 AND is_preset = FALSE", style_id
        )
        return result == "DELETE 1"


# ---------------------------------------------------------------------------
# Clothing Styles CRUD
# ---------------------------------------------------------------------------


async def list_clothing_styles() -> list[dict[str, Any]]:
    """List all clothing styles (custom only, presets come from presets.py)."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT * FROM clothing_styles WHERE is_preset = FALSE ORDER BY name"
        )
        return [dict(r) for r in rows]


async def get_clothing_style(style_id: str) -> dict[str, Any] | None:
    """Get a clothing style by ID."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM clothing_styles WHERE id = $1", style_id)
        return dict(row) if row else None


async def insert_clothing_style(
    *,
    id: str,
    name: str,
    preset_type: str = "custom",
    clothing_rules: str = "",
    opacity_rules: str = "",
    max_pieces: int = 2,
) -> None:
    """Insert a new clothing style."""
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO clothing_styles (id, name, preset_type, clothing_rules, opacity_rules,
                                            max_pieces, is_preset, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, FALSE, $7, $8)""",
            id, name, preset_type, clothing_rules, opacity_rules, max_pieces, now, now,
        )


async def delete_clothing_style(style_id: str) -> bool:
    """Delete a clothing style."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute(
            "DELETE FROM clothing_styles WHERE id = $1 AND is_preset = FALSE", style_id
        )
        return result == "DELETE 1"


# ---------------------------------------------------------------------------
# Production Rules CRUD
# ---------------------------------------------------------------------------


async def list_production_rules() -> list[dict[str, Any]]:
    """List all production rules (custom only, presets come from presets.py)."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT * FROM production_rules WHERE is_preset = FALSE ORDER BY name"
        )
        return [dict(r) for r in rows]


async def get_production_rules(rules_id: str) -> dict[str, Any] | None:
    """Get production rules by ID."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT * FROM production_rules WHERE id = $1", rules_id)
        return dict(row) if row else None


async def insert_production_rules(
    *,
    id: str,
    name: str,
    camera_settings: str = "",
    pose_rule: str = "",
    shot_intercalation_rules: str = "",
    death_scene_rules: str = "",
    child_rules: str = "",
    zombie_rules: str = "",
    consequence_philosophy: str = "",
    scale_rule: str = "",
) -> None:
    """Insert new production rules."""
    pool = await get_pool()
    now = datetime.now(timezone.utc)
    async with pool.acquire() as conn:
        await conn.execute(
            """INSERT INTO production_rules (id, name, camera_settings, pose_rule,
                                             shot_intercalation_rules, death_scene_rules,
                                             child_rules, zombie_rules, consequence_philosophy,
                                             scale_rule, is_preset, created_at, updated_at)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, FALSE, $11, $12)""",
            id, name, camera_settings, pose_rule, shot_intercalation_rules,
            death_scene_rules, child_rules, zombie_rules, consequence_philosophy,
            scale_rule, now, now,
        )


async def delete_production_rules(rules_id: str) -> bool:
    """Delete production rules."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute(
            "DELETE FROM production_rules WHERE id = $1 AND is_preset = FALSE", rules_id
        )
        return result == "DELETE 1"