use crate::db::Database;
use migs::{collect, migs};

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        username TEXT UNIQUE NOT NULL,
        email TEXT,
        password_hash TEXT NOT NULL,
        created_at TEXT NOT NULL
    );"#,
    scope = "init",
    order = 1
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS tokens (
        token TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        created_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    );"#,
    scope = "init",
    order = 2
}

migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_tokens_created_at ON tokens(created_at);"#,
    scope = "init",
    order = 3
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS invite_links (
        code TEXT PRIMARY KEY,
        created_at TEXT NOT NULL
    );"#,
    scope = "v2",
    order = 4
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS exercises (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        description TEXT NOT NULL DEFAULT '',
        unit TEXT NOT NULL DEFAULT 'reps',
        points_per_unit REAL NOT NULL DEFAULT 1.0,
        created_at TEXT NOT NULL
    );"#,
    scope = "v2",
    order = 5
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS goals (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        target_points REAL NOT NULL,
        created_at TEXT NOT NULL
    );"#,
    scope = "v2",
    order = 6
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS exercise_logs (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        exercise_id TEXT NOT NULL,
        amount REAL NOT NULL,
        points_earned REAL NOT NULL,
        created_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (exercise_id) REFERENCES exercises(id)
    );"#,
    scope = "v2",
    order = 7
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS favorite_exercises (
        user_id TEXT NOT NULL,
        exercise_id TEXT NOT NULL,
        PRIMARY KEY (user_id, exercise_id),
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (exercise_id) REFERENCES exercises(id)
    );"#,
    scope = "v2",
    order = 8
}

migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_exercise_logs_user ON exercise_logs(user_id);"#,
    scope = "v2",
    order = 9
}

migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_exercise_logs_exercise ON exercise_logs(exercise_id);"#,
    scope = "v2",
    order = 10
}

migs! {
    sql = r#"DROP TABLE IF EXISTS team_members;"#,
    scope = "v2",
    order = 11
}

migs! {
    sql = r#"DROP TABLE IF EXISTS teams;"#,
    scope = "v2",
    order = 12
}

migs! {
    sql = r#"ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0;"#,
    scope = "v3",
    order = 13
}

impl Database {
    pub async fn create_tables(&self) -> Result<(), String> {
        let _ = self
            .conn()
            .await?
            .query("PRAGMA journal_mode = WAL;", ())
            .await
            .map_err(|e| e.to_string())?;

        let migrations = collect!();

        let mut migrations: Vec<_> = migrations;
        migrations.sort_by_key(|m| m.order.unwrap_or(u32::MAX));

        for migration in &migrations {
            let result = self.conn().await?.execute(migration.content, ()).await;

            match result {
                Ok(_) => {}
                Err(e)
                    if migration.content.trim().starts_with("ALTER TABLE")
                        && (e.to_string().contains("duplicate column name")
                            || e.to_string().contains("no such table")) =>
                {
                    log::debug!("Skipping already-applied migration: {e}");
                }
                Err(e) => return Err(format!("Migration failed: {e}")),
            }
        }

        Ok(())
    }
}