use anyhow::Result;
use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
use std::path::Path;
use std::str::FromStr;

pub async fn init_pool(db_path: &Path) -> Result<SqlitePool> {
    let db_url = format!("sqlite://{}?mode=rwc", db_path.display());
    let options = SqliteConnectOptions::from_str(&db_url)?
        .create_if_missing(true)
        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
        .foreign_keys(false);

    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await?;

    Ok(pool)
}

pub async fn create_tables(pool: &SqlitePool) -> Result<()> {
    // 1. teams — API-provided PKs (no autoincrement)
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS teams (
            id INTEGER PRIMARY KEY,
            abbrev TEXT NOT NULL,
            short_name TEXT NOT NULL,
            long_name TEXT NOT NULL,
            season INTEGER NOT NULL,
            manager1_name TEXT,
            manager2_name TEXT,
            gm_discord_id TEXT,
            gm2_discord_id TEXT,
            division_id INTEGER,
            division_name TEXT,
            league_abbrev TEXT,
            thumbnail TEXT,
            color TEXT,
            dice_color TEXT,
            stadium TEXT,
            salary_cap REAL,
            synced_at TEXT,
            UNIQUE(abbrev, season)
        )",
    )
    .execute(pool)
    .await?;

    // 2. players — API-provided PKs (no autoincrement)
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS players (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            season INTEGER NOT NULL,
            team_id INTEGER REFERENCES teams(id),
            swar REAL DEFAULT 0,
            card_image TEXT,
            card_image_alt TEXT,
            headshot TEXT,
            vanity_card TEXT,
            pos_1 TEXT,
            pos_2 TEXT,
            pos_3 TEXT,
            pos_4 TEXT,
            pos_5 TEXT,
            pos_6 TEXT,
            pos_7 TEXT,
            pos_8 TEXT,
            hand TEXT,
            injury_rating TEXT,
            il_return TEXT,
            demotion_week INTEGER,
            strat_code TEXT,
            bbref_id TEXT,
            sbaplayer_id INTEGER,
            last_game TEXT,
            last_game2 TEXT,
            synced_at TEXT
        )",
    )
    .execute(pool)
    .await?;

    // 3. batter_cards
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS batter_cards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL UNIQUE REFERENCES players(id),
            so_vlhp REAL DEFAULT 0,
            bb_vlhp REAL DEFAULT 0,
            hit_vlhp REAL DEFAULT 0,
            ob_vlhp REAL DEFAULT 0,
            tb_vlhp REAL DEFAULT 0,
            hr_vlhp REAL DEFAULT 0,
            dp_vlhp REAL DEFAULT 0,
            so_vrhp REAL DEFAULT 0,
            bb_vrhp REAL DEFAULT 0,
            hit_vrhp REAL DEFAULT 0,
            ob_vrhp REAL DEFAULT 0,
            tb_vrhp REAL DEFAULT 0,
            hr_vrhp REAL DEFAULT 0,
            dp_vrhp REAL DEFAULT 0,
            bphr_vlhp REAL DEFAULT 0,
            bphr_vrhp REAL DEFAULT 0,
            bp1b_vlhp REAL DEFAULT 0,
            bp1b_vrhp REAL DEFAULT 0,
            stealing TEXT,
            steal_rating TEXT,
            speed INTEGER DEFAULT 10,
            bunt TEXT,
            hit_run TEXT,
            fielding TEXT,
            catcher_arm INTEGER,
            catcher_pb INTEGER,
            catcher_t INTEGER,
            rating_vl REAL,
            rating_vr REAL,
            rating_overall REAL,
            imported_at TEXT,
            source TEXT
        )",
    )
    .execute(pool)
    .await?;

    // 4. pitcher_cards
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS pitcher_cards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL UNIQUE REFERENCES players(id),
            so_vlhb REAL DEFAULT 0,
            bb_vlhb REAL DEFAULT 0,
            hit_vlhb REAL DEFAULT 0,
            ob_vlhb REAL DEFAULT 0,
            tb_vlhb REAL DEFAULT 0,
            hr_vlhb REAL DEFAULT 0,
            dp_vlhb REAL DEFAULT 0,
            bphr_vlhb REAL DEFAULT 0,
            bp1b_vlhb REAL DEFAULT 0,
            so_vrhb REAL DEFAULT 0,
            bb_vrhb REAL DEFAULT 0,
            hit_vrhb REAL DEFAULT 0,
            ob_vrhb REAL DEFAULT 0,
            tb_vrhb REAL DEFAULT 0,
            hr_vrhb REAL DEFAULT 0,
            dp_vrhb REAL DEFAULT 0,
            bphr_vrhb REAL DEFAULT 0,
            bp1b_vrhb REAL DEFAULT 0,
            hold_rating INTEGER DEFAULT 0,
            endurance_start INTEGER,
            endurance_relief INTEGER,
            endurance_close INTEGER,
            fielding_range INTEGER,
            fielding_error INTEGER,
            wild_pitch INTEGER DEFAULT 0,
            balk INTEGER DEFAULT 0,
            batting_rating TEXT,
            rating_vlhb REAL,
            rating_vrhb REAL,
            rating_overall REAL,
            imported_at TEXT,
            source TEXT
        )",
    )
    .execute(pool)
    .await?;

    // 5. transactions
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            season INTEGER NOT NULL,
            week INTEGER NOT NULL,
            move_id TEXT NOT NULL,
            player_id INTEGER NOT NULL,
            from_team_id INTEGER NOT NULL,
            to_team_id INTEGER NOT NULL,
            cancelled INTEGER DEFAULT 0,
            frozen INTEGER DEFAULT 0,
            synced_at TEXT,
            UNIQUE(move_id, player_id)
        )",
    )
    .execute(pool)
    .await?;

    // 6. lineups
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS lineups (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            description TEXT,
            lineup_type TEXT DEFAULT 'standard',
            batting_order TEXT,
            positions TEXT,
            starting_pitcher_id INTEGER REFERENCES players(id),
            created_at TEXT,
            updated_at TEXT
        )",
    )
    .execute(pool)
    .await?;

    // 7. matchup_cache
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS matchup_cache (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            batter_id INTEGER NOT NULL REFERENCES players(id),
            pitcher_id INTEGER NOT NULL REFERENCES players(id),
            rating REAL NOT NULL,
            tier TEXT,
            details TEXT,
            computed_at TEXT,
            weights_hash TEXT,
            UNIQUE(batter_id, pitcher_id)
        )",
    )
    .execute(pool)
    .await?;

    // 8. standardized_score_cache
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS standardized_score_cache (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            batter_card_id INTEGER REFERENCES batter_cards(id),
            pitcher_card_id INTEGER REFERENCES pitcher_cards(id),
            split TEXT NOT NULL,
            total_score REAL NOT NULL,
            stat_scores TEXT NOT NULL,
            computed_at TEXT,
            weights_hash TEXT,
            league_stats_hash TEXT,
            UNIQUE(batter_card_id, split),
            UNIQUE(pitcher_card_id, split)
        )",
    )
    .execute(pool)
    .await?;

    // 9. sync_status
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS sync_status (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            entity_type TEXT NOT NULL UNIQUE,
            last_sync TEXT,
            last_sync_count INTEGER DEFAULT 0,
            last_error TEXT
        )",
    )
    .execute(pool)
    .await?;

    Ok(())
}

pub async fn reset_database(pool: &SqlitePool) -> Result<()> {
    // Drop in reverse dependency order to satisfy foreign key constraints
    for table in &[
        "standardized_score_cache",
        "matchup_cache",
        "lineups",
        "transactions",
        "pitcher_cards",
        "batter_cards",
        "players",
        "teams",
        "sync_status",
    ] {
        sqlx::query(&format!("DROP TABLE IF EXISTS {}", table))
            .execute(pool)
            .await?;
    }

    create_tables(pool).await
}
