"""Persistent, multi-process round-robin state for Gemini provider rotation.

Passenger/cPanel can run several WSGI workers. Python globals are isolated per
worker, so an in-memory rotation cursor can make every worker start at key #1.
This module stores only the rotation order/cursor in a tiny SQLite database.

No API keys or provider secrets are stored here.
"""
from __future__ import annotations

import json
import sqlite3
import threading
import time
from pathlib import Path
from typing import Callable, Iterable

import app_config

_DB_PATH = app_config.CONFIG_DIR / "rotation.sqlite3"
_INIT_LOCK = threading.Lock()
_LOCAL_LOCK = threading.Lock()


def _connect() -> sqlite3.Connection:
    app_config.CONFIG_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(_DB_PATH), timeout=30, isolation_level=None)
    conn.execute("PRAGMA busy_timeout=30000")
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS rotation_state (
            pool_id TEXT PRIMARY KEY,
            queue_json TEXT NOT NULL,
            updated_at REAL NOT NULL
        )
        """
    )
    return conn


def reserve(
    pool_id: str,
    provider_ids: Iterable[str],
    eligible: Callable[[str], bool],
    *,
    advance: bool = True,
) -> tuple[str | None, list[str]]:
    """Atomically reserve the next eligible provider for a pool.

    Returns (selected_id, ordered_eligible_ids). The selected provider is moved
    to the end of the persistent queue when advance=True. Provider IDs that
    disappeared are removed; newly added IDs are appended, so adding a key does
    not reset the existing serial position.
    """
    ids = []
    seen = set()
    for raw in provider_ids:
        pid = str(raw or "")
        if pid and pid not in seen:
            seen.add(pid)
            ids.append(pid)
    if not ids:
        return None, []

    with _LOCAL_LOCK:
        conn = _connect()
        try:
            conn.execute("BEGIN IMMEDIATE")
            row = conn.execute(
                "SELECT queue_json FROM rotation_state WHERE pool_id = ?",
                (str(pool_id),),
            ).fetchone()

            try:
                old_queue = json.loads(row[0]) if row else []
            except Exception:
                old_queue = []

            valid = set(ids)
            queue = [pid for pid in old_queue if pid in valid]
            queue.extend(pid for pid in ids if pid not in queue)

            eligible_ids = [pid for pid in queue if eligible(pid)]
            if not eligible_ids:
                conn.execute("ROLLBACK")
                return None, []

            selected = eligible_ids[0]
            if advance:
                queue.remove(selected)
                queue.append(selected)

            conn.execute(
                """
                INSERT INTO rotation_state(pool_id, queue_json, updated_at)
                VALUES (?, ?, ?)
                ON CONFLICT(pool_id) DO UPDATE SET
                    queue_json=excluded.queue_json,
                    updated_at=excluded.updated_at
                """,
                (str(pool_id), json.dumps(queue), time.time()),
            )
            conn.execute("COMMIT")

            # Return the selected key first, then the remaining eligible keys
            # in their current queue order. This preserves strict serial
            # rotation while allowing fallback to continue after a failure.
            ordered = [selected] + [
                pid for pid in queue if pid != selected and pid in eligible_ids
            ]
            return selected, ordered
        except Exception:
            try:
                conn.execute("ROLLBACK")
            except Exception:
                pass
            raise
        finally:
            conn.close()


def clear_all() -> None:
    """Clear only rotation cursors; API keys and health/history are untouched."""
    with _LOCAL_LOCK:
        conn = _connect()
        try:
            conn.execute("BEGIN IMMEDIATE")
            conn.execute("DELETE FROM rotation_state")
            conn.execute("COMMIT")
        except Exception:
            try:
                conn.execute("ROLLBACK")
            except Exception:
                pass
            raise
        finally:
            conn.close()


def clear_pool(pool_id: str) -> None:
    with _LOCAL_LOCK:
        conn = _connect()
        try:
            conn.execute("BEGIN IMMEDIATE")
            conn.execute("DELETE FROM rotation_state WHERE pool_id = ?", (str(pool_id),))
            conn.execute("COMMIT")
        except Exception:
            try:
                conn.execute("ROLLBACK")
            except Exception:
                pass
            raise
        finally:
            conn.close()