use sea_orm::{ConnectionTrait, DatabaseBackend, Statement};
use sea_orm_migration::prelude::*;

/// Drops `task_specs` by merging its columns into `tasks`, and adds `engine_type` to `task_executions`
/// when missing. No-op pieces are skipped defensively for each target schema.
#[derive(DeriveMigrationName)]
pub struct Migration;

async fn column_exists(
    conn: &impl ConnectionTrait,
    backend: &DatabaseBackend,
    table: &str,
    col: &str,
) -> Result<bool, DbErr> {
    let stmt = Statement::from_string(
        *backend,
        format!("SELECT 1 AS x FROM pragma_table_info('{table}') WHERE name = '{col}' LIMIT 1"),
    );
    Ok(conn.query_one(stmt).await?.is_some())
}

async fn table_exists(
    conn: &impl ConnectionTrait,
    backend: &DatabaseBackend,
    name: &str,
) -> Result<bool, DbErr> {
    let stmt = Statement::from_string(
        *backend,
        format!("SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='{name}' LIMIT 1"),
    );
    Ok(conn.query_one(stmt).await?.is_some())
}

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let conn = manager.get_connection();
        let backend = manager.get_database_backend();

        if table_exists(conn, &backend, "task_specs").await? {
            let additions: [(&str, &str); 10] = [
                (
                    "summary",
                    "ALTER TABLE tasks ADD COLUMN summary TEXT NOT NULL DEFAULT ''",
                ),
                (
                    "description",
                    "ALTER TABLE tasks ADD COLUMN description TEXT",
                ),
                ("assignee", "ALTER TABLE tasks ADD COLUMN assignee TEXT"),
                (
                    "repo_id",
                    "ALTER TABLE tasks ADD COLUMN repo_id INTEGER NOT NULL DEFAULT 0",
                ),
                (
                    "status",
                    "ALTER TABLE tasks ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'",
                ),
                (
                    "branch_name",
                    "ALTER TABLE tasks ADD COLUMN branch_name TEXT",
                ),
                ("pr_url", "ALTER TABLE tasks ADD COLUMN pr_url TEXT"),
                (
                    "error_message",
                    "ALTER TABLE tasks ADD COLUMN error_message TEXT",
                ),
                (
                    "fetched_at",
                    "ALTER TABLE tasks ADD COLUMN fetched_at TEXT NOT NULL DEFAULT ''",
                ),
                (
                    "processed_at",
                    "ALTER TABLE tasks ADD COLUMN processed_at TEXT",
                ),
            ];
            for (col, sql) in additions {
                if !column_exists(conn, &backend, "tasks", col).await? {
                    let _ = conn.execute_unprepared(sql).await;
                }
            }

            let _ = conn
                .execute_unprepared(
                    r#"UPDATE tasks SET
                        summary = (SELECT ts.summary FROM task_specs ts WHERE ts.task_id = tasks.id),
                        description = (SELECT ts.description FROM task_specs ts WHERE ts.task_id = tasks.id),
                        assignee = (SELECT ts.assignee FROM task_specs ts WHERE ts.task_id = tasks.id),
                        repo_id = (SELECT ts.repo_id FROM task_specs ts WHERE ts.task_id = tasks.id),
                        status = (SELECT ts.status FROM task_specs ts WHERE ts.task_id = tasks.id),
                        branch_name = (SELECT ts.branch_name FROM task_specs ts WHERE ts.task_id = tasks.id),
                        pr_url = (SELECT ts.pr_url FROM task_specs ts WHERE ts.task_id = tasks.id),
                        error_message = (SELECT ts.error_message FROM task_specs ts WHERE ts.task_id = tasks.id),
                        fetched_at = (SELECT ts.fetched_at FROM task_specs ts WHERE ts.task_id = tasks.id),
                        processed_at = (SELECT ts.processed_at FROM task_specs ts WHERE ts.task_id = tasks.id)
                    WHERE EXISTS (SELECT 1 FROM task_specs ts WHERE ts.task_id = tasks.id)"#,
                )
                .await;

            let _ = conn.execute_unprepared("DROP TABLE task_specs").await?;
        }

        if table_exists(conn, &backend, "task_executions").await?
            && !column_exists(conn, &backend, "task_executions", "engine_type").await?
        {
            let _ = conn
                .execute_unprepared(
                    "ALTER TABLE task_executions ADD COLUMN engine_type TEXT NOT NULL DEFAULT 'claude_code'",
                )
                .await;
        }

        Ok(())
    }

    async fn down(&self, _manager: &SchemaManager) -> Result<(), DbErr> {
        Ok(())
    }
}
