import argparse
import json
import sqlite3
from pathlib import Path

import httpx
import psycopg


FIELDS = [
    "id",
    "title",
    "subtitle",
    "author",
    "translator",
    "publisher",
    "year",
    "edition",
    "language",
    "series",
    "volume",
    "condition",
    "special_features",
    "other_text",
    "source_folder",
    "source_filename",
    "raw_ocr_text",
    "description",
    "review_status",
    "review_updated_at",
    "image_url",
    "thumb_url",
    "storage_path",
    "thumb_storage_path",
]


def main():
    parser = argparse.ArgumentParser(description="Migrate Bibliothek SQLite data into Postgres")
    parser.add_argument("--db", type=Path, default=Path(__file__).resolve().parents[1] / "books.db")
    parser.add_argument("--database-url")
    parser.add_argument("--supabase-url")
    parser.add_argument("--service-key")
    parser.add_argument("--chunk-size", type=int, default=100)
    args = parser.parse_args()

    sqlite_conn = sqlite3.connect(str(args.db))
    sqlite_conn.row_factory = sqlite3.Row
    rows = sqlite_conn.execute(f"SELECT {', '.join(FIELDS)} FROM book_images ORDER BY id").fetchall()
    sqlite_conn.close()

    insert_columns = ", ".join(FIELDS)
    placeholders = ", ".join(["%s"] * len(FIELDS))
    update_columns = ", ".join(f"{field} = EXCLUDED.{field}" for field in FIELDS if field != "id")
    query = f"""
        INSERT INTO public.book_images ({insert_columns})
        VALUES ({placeholders})
        ON CONFLICT (id) DO UPDATE SET {update_columns}
    """

    payload = []
    for row in rows:
        values = []
        for field in FIELDS:
            value = row[field]
            if field == "review_updated_at" and (value is None or str(value).strip() == ""):
                value = None
            elif value is None:
                value = ""
            values.append(value)
        payload.append(values)

    if args.supabase_url and args.service_key:
        base_url = args.supabase_url.rstrip("/")
        headers = {
            "Authorization": f"Bearer {args.service_key}",
            "apikey": args.service_key,
            "Content-Type": "application/json",
            "Prefer": "resolution=merge-duplicates,return=minimal",
        }
        with httpx.Client(base_url=base_url, headers=headers, timeout=120.0) as client:
            for start in range(0, len(payload), args.chunk_size):
                chunk = payload[start:start + args.chunk_size]
                records = [dict(zip(FIELDS, values)) for values in chunk]
                response = client.post("/rest/v1/book_images?on_conflict=id", json=records)
                response.raise_for_status()
    elif args.database_url:
        with psycopg.connect(args.database_url) as conn:
            with conn.cursor() as cur:
                cur.executemany(query, payload)
            conn.commit()
    else:
        raise SystemExit("Provide either --database-url or --supabase-url plus --service-key")

    print(json.dumps({"migrated": len(payload)}, indent=2))


if __name__ == "__main__":
    main()