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

/// Drops `task_infos` after copying its columns back onto `tasks`. No-op when `task_infos` is absent.
#[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_infos").await? {
            return Ok(());
        }

        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 i.summary FROM task_infos i WHERE i.task_id = tasks.id),
                    description = (SELECT i.description FROM task_infos i WHERE i.task_id = tasks.id),
                    assignee = (SELECT i.assignee FROM task_infos i WHERE i.task_id = tasks.id),
                    repo_id = (SELECT i.repo_id FROM task_infos i WHERE i.task_id = tasks.id),
                    status = (SELECT i.status FROM task_infos i WHERE i.task_id = tasks.id),
                    branch_name = (SELECT i.branch_name FROM task_infos i WHERE i.task_id = tasks.id),
                    pr_url = (SELECT i.pr_url FROM task_infos i WHERE i.task_id = tasks.id),
                    error_message = (SELECT i.error_message FROM task_infos i WHERE i.task_id = tasks.id),
                    fetched_at = (SELECT i.fetched_at FROM task_infos i WHERE i.task_id = tasks.id),
                    processed_at = (SELECT i.processed_at FROM task_infos i WHERE i.task_id = tasks.id)
                WHERE EXISTS (SELECT 1 FROM task_infos i WHERE i.task_id = tasks.id)"#,
            )
            .await;

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

        Ok(())
    }

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