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

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

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS notes (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        category TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    );"#,
    scope = "init",
    order = 2
}

migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS note_tags (
        note_id TEXT NOT NULL,
        tag TEXT NOT NULL,
        PRIMARY KEY (note_id, tag),
        FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
    );"#,
    scope = "init",
    order = 3
}

migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_notes_created_at ON notes(created_at);"#,
    scope = "init",
    order = 4
}

migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_note_tags_tag ON note_tags(tag);"#,
    scope = "init",
    order = 5
}

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 {
            self.conn()
                .await?
                .execute(migration.content, ())
                .await
                .map_err(|e| format!("Migration failed: {e}"))?;
        }

        Ok(())
    }
}