#!/usr/bin/env -S uv run --script --quiet
# /// script
# requires-python = ">=3.12"
# dependencies = ["httpx", "pydantic-settings"]
# ///
"""
Recreate the documents table with clean schema (F32_BLOB(1024) for voyage-4-lite).

This script:
  1. Drops corrupted DiskANN shadow tables
  2. Creates documents_new with clean schema (F32_BLOB(1024))
  3. Copies all data (excluding embeddings — those will be re-generated by backfill-embeddings)
  4. Drops old table, renames new one
  5. Recreates FTS and indexes (no DiskANN — we use brute-force for /similar)

Usage:
    ./scripts/rebuild-documents-table
"""

import os
import sys
import time

import httpx
from pydantic_settings import BaseSettings, SettingsConfigDict

TIMEOUT = 300


class Settings(BaseSettings):
    model_config = SettingsConfigDict(
        env_file=os.environ.get("ENV_FILE", ".env"), extra="ignore"
    )
    turso_url: str
    turso_token: str

    @property
    def turso_host(self) -> str:
        url = self.turso_url
        if url.startswith("libsql://"):
            url = url[len("libsql://") :]
        return url


def pipeline(settings: Settings, statements: list[str], timeout: int = TIMEOUT) -> list[dict]:
    requests = [{"type": "execute", "stmt": {"sql": sql}} for sql in statements]
    requests.append({"type": "close"})

    response = httpx.post(
        f"https://{settings.turso_host}/v2/pipeline",
        headers={
            "Authorization": f"Bearer {settings.turso_token}",
            "Content-Type": "application/json",
        },
        json={"requests": requests},
        timeout=timeout,
    )
    response.raise_for_status()
    data = response.json()

    results = []
    for i, result in enumerate(data["results"][:-1]):
        if result["type"] == "error":
            raise Exception(f"statement {i} failed: {result['error']}")
        results.append(result["response"]["result"])
    return results


def scalar(settings: Settings, sql: str) -> int:
    results = pipeline(settings, [sql], timeout=30)
    cell = results[0]["rows"][0][0]
    return int(cell["value"] if isinstance(cell, dict) else cell)


def step(msg: str):
    print(f"  {msg}...", end="", flush=True)


def done(detail: str = ""):
    suffix = f" ({detail})" if detail else ""
    print(f" ok{suffix}", flush=True)


def main():
    try:
        settings = Settings()  # type: ignore
    except Exception as e:
        print(f"error: {e}", file=sys.stderr)
        sys.exit(1)

    total = scalar(settings, "SELECT count(*) FROM documents")
    print(f"documents table: {total} rows")

    t0 = time.time()

    # step 0: drop corrupted DiskANN shadow tables
    step("dropping corrupted vector index tables")
    pipeline(settings, [
        "DROP TABLE IF EXISTS documents_embedding_idx_shadow",
        "DROP TABLE IF EXISTS libsql_vector_meta_shadow",
    ])
    done()

    # step 1: create new table with clean schema
    step("creating documents_new")
    pipeline(settings, ["""
        CREATE TABLE documents_new (
            uri TEXT PRIMARY KEY,
            did TEXT NOT NULL,
            rkey TEXT NOT NULL,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT,
            publication_uri TEXT,
            platform TEXT DEFAULT 'leaflet',
            source_collection TEXT DEFAULT 'pub.leaflet.document',
            path TEXT,
            base_path TEXT DEFAULT '',
            has_publication INTEGER DEFAULT 0,
            indexed_at TEXT,
            embedding F32_BLOB(1024)
        )
    """])
    done()

    # step 2: copy all data (skip embeddings — wrong dims, will be re-generated)
    step(f"copying {total} rows (without embeddings)")
    pipeline(settings, ["""
        INSERT INTO documents_new
            (uri, did, rkey, title, content, created_at, publication_uri,
             platform, source_collection, path, base_path, has_publication, indexed_at)
        SELECT uri, did, rkey, title, content, created_at, publication_uri,
               platform, source_collection, path, base_path, has_publication, indexed_at
        FROM documents
    """])
    done()

    # step 3: drop old table + FTS triggers, rename new
    step("swapping tables")
    pipeline(settings, [
        "DROP TABLE documents",
        "ALTER TABLE documents_new RENAME TO documents",
    ])
    done()

    # step 4: recreate indexes
    step("creating indexes")
    pipeline(settings, [
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_documents_did_rkey ON documents(did, rkey)",
    ])
    done()

    # step 5: recreate FTS
    step("rebuilding FTS index")
    pipeline(settings, [
        "DROP TABLE IF EXISTS documents_fts",
        "CREATE VIRTUAL TABLE documents_fts USING fts5(uri UNINDEXED, title, content)",
        "INSERT INTO documents_fts (uri, title, content) SELECT uri, title, content FROM documents",
    ])
    done()

    # verify
    new_total = scalar(settings, "SELECT count(*) FROM documents")
    fts_count = scalar(settings, "SELECT count(*) FROM documents_fts")
    embedded = scalar(settings, "SELECT count(*) FROM documents WHERE embedding IS NOT NULL")
    elapsed = time.time() - t0

    print(f"\ndone in {elapsed:.1f}s")
    print(f"  documents: {new_total}")
    print(f"  FTS indexed: {fts_count}")
    print(f"  embeddings: {embedded} (will be re-generated by backfill-embeddings)")


if __name__ == "__main__":
    main()
