use anyhow::{Context, Result};
use rusqlite::Connection;

pub async fn migrate(database: &std::path::Path) -> Result<()> {
    let conn = Connection::open(database).context("Failed to open database")?;

    conn.execute_batch(
        r#"
        CREATE TABLE IF NOT EXISTS developers (
            id TEXT PRIMARY KEY,
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            name TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS games (
            id TEXT PRIMARY KEY,
            developer_id TEXT NOT NULL REFERENCES developers(id),
            name TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS api_keys (
            id TEXT PRIMARY KEY,
            developer_id TEXT NOT NULL REFERENCES developers(id),
            game_id TEXT REFERENCES games(id),
            key_hash TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            revoked_at TEXT
        );

        CREATE TABLE IF NOT EXISTS gamers (
            id TEXT PRIMARY KEY,
            itch_user_id INTEGER NOT NULL UNIQUE,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS sessions (
            id TEXT PRIMARY KEY,
            gamer_id TEXT NOT NULL REFERENCES gamers(id),
            expires_at TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS invites (
            code TEXT PRIMARY KEY,
            used_by TEXT REFERENCES gamers(id),
            used_at TEXT,
            created_by TEXT NOT NULL,
            expires_at TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS saves (
            id TEXT PRIMARY KEY,
            gamer_id TEXT NOT NULL REFERENCES gamers(id),
            game_id TEXT NOT NULL REFERENCES games(id),
            slot_id TEXT NOT NULL,
            current_cid TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(gamer_id, game_id, slot_id)
        );

        CREATE TABLE IF NOT EXISTS save_versions (
            id TEXT PRIMARY KEY,
            save_id TEXT NOT NULL REFERENCES saves(id),
            version_number INTEGER NOT NULL,
            cid TEXT NOT NULL,
            milestone INTEGER DEFAULT 0,
            size_bytes INTEGER NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS dpop_tokens (
            token_id TEXT PRIMARY KEY,
            gamer_id TEXT NOT NULL REFERENCES gamers(id),
            nonce TEXT NOT NULL,
            expires_at TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_games_developer ON games(developer_id);
        CREATE INDEX IF NOT EXISTS idx_saves_gamer ON saves(gamer_id);
        CREATE INDEX IF NOT EXISTS idx_saves_game ON saves(game_id);
        CREATE INDEX IF NOT EXISTS idx_save_versions_save ON save_versions(save_id);

        CREATE TABLE IF NOT EXISTS gamer_quotas (
            gamer_id TEXT PRIMARY KEY,
            used_bytes INTEGER NOT NULL DEFAULT 0,
            limit_bytes INTEGER NOT NULL DEFAULT 2147483648,
            warning_sent INTEGER NOT NULL DEFAULT 0
        );
        "#,
    )?;

    println!("Migration completed successfully");
    Ok(())
}