use sqlx::SqlitePool;
use sqlx::Row;

/// 全てのマイグレーションを実行する
pub async fn run_migrations(pool: &SqlitePool) -> Result<(), String> {
    // v0.2.5 -> v0.3.0
    migrate_025_to_030(pool).await?;
    
    // 今後、バージョンが増えるごとにここに追加
    // migrate_030_to_040(pool).await?;

    Ok(())
}

async fn migrate_025_to_030(pool: &SqlitePool) -> Result<(), String> {
    // 1. internal_metadata テーブルが存在するか確認
    let row = sqlx::query("SELECT 1 FROM sqlite_master WHERE type='table' AND name='internal_metadata'")
        .fetch_optional(pool)
        .await
        .map_err(|e| e.to_string())?;

    if row.is_some() {
        // すでにメタデータテーブルがある場合は、0.3.0以降とみなす
        return Ok(());
    }

    // 2. items テーブルの構造を確認（v0.2.5以前の判定）
    let rows = sqlx::query("PRAGMA table_info(items)")
        .fetch_all(pool)
        .await
        .map_err(|e| e.to_string())?;

    if rows.is_empty() {
        // テーブル自体がない（新規導入）の場合はマイグレーション不要
        return Ok(());
    }

    let has_path = rows.iter().any(|row| {
        let name: String = row.get(1);
        name == "path"
    });

    if !has_path {
        return Ok(());
    }

    log::info!("Migrating database from v0.2.5 to v0.3.0 (Dedicated Migrator)...");

    // 外部キー制約を一時的に無効化
    sqlx::query("PRAGMA foreign_keys = OFF")
        .execute(pool)
        .await
        .map_err(|e| e.to_string())?;

    let mut tx = pool.begin().await.map_err(|e| e.to_string())?;

    // documents テーブルを作成
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS documents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT UNIQUE,
            mime TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT DEFAULT (datetime('now', 'localtime'))
        )",
    )
    .execute(&mut *tx)
    .await
    .map_err(|e| e.to_string())?;

    sqlx::query("INSERT OR IGNORE INTO documents (path) SELECT DISTINCT path FROM items")
        .execute(&mut *tx)
        .await
        .map_err(|e| e.to_string())?;

    sqlx::query(
        "CREATE TABLE items_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            document_id INTEGER NOT NULL,
            chunk_index INTEGER NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT,
            updated_at TEXT,
            FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE
        )",
    )
    .execute(&mut *tx)
    .await
    .map_err(|e| e.to_string())?;

    sqlx::query(
        "INSERT INTO items_new (id, document_id, chunk_index, content, created_at, updated_at)
         SELECT 
            i.id, 
            d.id, 
            (SELECT COUNT(*) FROM items i2 WHERE i2.path = i.path AND i2.id < i.id),
            i.content, 
            i.created_at, 
            i.updated_at
         FROM items i
         JOIN documents d ON i.path = d.path",
    )
    .execute(&mut *tx)
    .await
    .map_err(|e| e.to_string())?;

    sqlx::query("DROP TABLE items")
        .execute(&mut *tx)
        .await
        .map_err(|e| e.to_string())?;

    sqlx::query("ALTER TABLE items_new RENAME TO items")
        .execute(&mut *tx)
        .await
        .map_err(|e| e.to_string())?;

    tx.commit().await.map_err(|e| e.to_string())?;

    sqlx::query("PRAGMA foreign_keys = ON")
        .execute(pool)
        .await
        .map_err(|e| e.to_string())?;

    log::info!("Migration v0.2.5 to v0.3.0 completed successfully.");

    Ok(())
}
