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?;
    // path 正規化（\ → /、末尾 / 除去）。照合を一貫させるため。
    migrate_normalize_document_paths(pool).await?;
    // documents に category カラムを追加（モニター先フォルダのカテゴリ分類用）
    migrate_add_documents_category(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(())
}

/// documents に category カラムを追加（既存DBへの後方互換マイグレーション）
async fn migrate_add_documents_category(pool: &SqlitePool) -> Result<(), String> {
    // 新規DBでは run_migrations が init_schema の CREATE TABLE より先に実行されるため、
    // この時点で documents がまだ存在しない場合がある。その場合はスキップ（後で CREATE に category 含む）
    let has_docs = sqlx::query_scalar::<_, i64>("SELECT 1 FROM sqlite_master WHERE type='table' AND name='documents'")
        .fetch_optional(pool)
        .await
        .map_err(|e| e.to_string())?;
    if has_docs.is_none() {
        return Ok(());
    }

    let cols = sqlx::query("PRAGMA table_info(documents)")
        .fetch_all(pool)
        .await
        .map_err(|e| e.to_string())?;
    let has_category = cols.iter().any(|r| {
        let name: String = r.get(1);
        name == "category"
    });
    if has_category {
        return Ok(());
    }
    sqlx::query("ALTER TABLE documents ADD COLUMN category TEXT DEFAULT ''")
        .execute(pool)
        .await
        .map_err(|e| e.to_string())?;
    log::info!("Migration: added 'category' column to documents table.");
    Ok(())
}

/// documents.path を正規化（\ → /、末尾 / 除去）。既存DBの path を吸収用の一形式に揃える。
async fn migrate_normalize_document_paths(pool: &SqlitePool) -> Result<(), String> {
    use std::collections::HashMap;

    let has_docs = sqlx::query_scalar::<_, i64>("SELECT 1 FROM sqlite_master WHERE type='table' AND name='documents'")
        .fetch_optional(pool)
        .await
        .map_err(|e| e.to_string())?;
    if has_docs.is_none() {
        return Ok(()); // 新規DBでは documents はまだ無い（init_schema で後から作られる）
    }

    let rows: Vec<(i64, String)> = sqlx::query_as("SELECT id, path FROM documents WHERE path IS NOT NULL")
        .fetch_all(pool)
        .await
        .map_err(|e| e.to_string())?;

    if rows.is_empty() {
        return Ok(());
    }

    fn norm(p: &str) -> String {
        let t = p.trim().replace('\\', "/");
        let t = t.trim_end_matches('/');
        t.to_string()
    }

    let normalized: Vec<(i64, String)> = rows.iter().map(|(id, p)| (*id, norm(p))).collect();
    let mut by_norm: HashMap<String, Vec<i64>> = HashMap::new();
    for (id, n) in &normalized {
        by_norm.entry(n.clone()).or_default().push(*id);
    }

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

    // 同一正規化 path が複数ある場合は 1 件だけ残し他は削除（CASCADE で items も消える）
    for (_npath, ids) in by_norm.iter().filter(|(_, ids)| ids.len() > 1) {
        let (_keep, remove) = (ids[0], &ids[1..]);
        for id in remove.iter().copied() {
            sqlx::query("DELETE FROM documents WHERE id = ?")
                .bind(id)
                .execute(&mut *tx)
                .await
                .map_err(|e| e.to_string())?;
        }
    }

    // 全件 path を正規化で更新（重複は上で解消済み）
    for (id, npath) in normalized {
        sqlx::query("UPDATE documents SET path = ? WHERE id = ?")
            .bind(&npath)
            .bind(id)
            .execute(&mut *tx)
            .await
            .map_err(|e| e.to_string())?;
    }

    tx.commit().await.map_err(|e| e.to_string())?;
    log::info!("Migration: normalized document paths (backslash → forward slash).");
    Ok(())
}