use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::SqlitePool;
use std::str::FromStr;

pub async fn init_pool(database_url: &str) -> SqlitePool {
    let options = SqliteConnectOptions::from_str(database_url)
        .expect("Invalid DATABASE_URL")
        .create_if_missing(true);

    SqlitePoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await
        .expect("Failed to create database pool")
}

pub async fn run_migrations(pool: &SqlitePool) {
    let migrations = [
        include_str!("../migrations/001_create_users.sql"),
        include_str!("../migrations/002_create_progress.sql"),
        include_str!("../migrations/003_create_lesson_state.sql"),
        include_str!("../migrations/004_add_streak_freezes.sql"),
        include_str!("../migrations/005_push_notifications.sql"),
    ];

    for sql in &migrations {
        // Each migration file may contain multiple statements separated by semicolons.
        for statement in sql.split(';') {
            let trimmed = statement.trim();
            if trimmed.is_empty() {
                continue;
            }
            if let Err(e) = sqlx::query(trimmed).execute(pool).await {
                // ALTER TABLE ADD COLUMN is not idempotent in SQLite —
                // ignore "duplicate column" errors on restart.
                let msg = e.to_string();
                if msg.contains("duplicate column") {
                    continue;
                }
                panic!("Failed to run migration: {e}");
            }
        }
    }
}