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

/// Moves workflow columns from `tasks` onto `task_executions`, then drops them from `tasks`.
/// No-op when `tasks.summary` is already absent (new schema from `m20240101`).
#[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_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();

        let exec_additions: [(&str, &str); 10] = [
            (
                "summary",
                "ALTER TABLE task_executions ADD COLUMN summary TEXT NOT NULL DEFAULT ''",
            ),
            (
                "description",
                "ALTER TABLE task_executions ADD COLUMN description TEXT",
            ),
            (
                "assignee",
                "ALTER TABLE task_executions ADD COLUMN assignee TEXT",
            ),
            (
                "status",
                "ALTER TABLE task_executions ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'",
            ),
            (
                "branch_name",
                "ALTER TABLE task_executions ADD COLUMN branch_name TEXT",
            ),
            (
                "pr_url",
                "ALTER TABLE task_executions ADD COLUMN pr_url TEXT",
            ),
            (
                "fetched_at",
                "ALTER TABLE task_executions ADD COLUMN fetched_at TEXT NOT NULL DEFAULT ''",
            ),
            (
                "processed_at",
                "ALTER TABLE task_executions ADD COLUMN processed_at TEXT",
            ),
            (
                "started_at",
                "ALTER TABLE task_executions ADD COLUMN started_at TEXT",
            ),
            (
                "finished_at",
                "ALTER TABLE task_executions ADD COLUMN finished_at TEXT",
            ),
        ];
        for (col, sql) in exec_additions {
            if !column_exists(conn, &backend, "task_executions", col).await? {
                let _ = conn.execute_unprepared(sql).await;
            }
        }

        if !column_exists(conn, &backend, "tasks", "summary").await? {
            return Ok(());
        }

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

        // Prefer task-level error_message when task_executions row has none
        let _ = conn
            .execute_unprepared(
                r#"UPDATE task_executions SET
                    error_message = (SELECT t.error_message FROM tasks t WHERE t.id = task_executions.task_id)
                WHERE EXISTS (SELECT 1 FROM tasks t WHERE t.id = task_executions.task_id)
                  AND (task_executions.error_message IS NULL OR task_executions.error_message = '')"#,
            )
            .await;

        for col in [
            "processed_at",
            "fetched_at",
            "error_message",
            "pr_url",
            "branch_name",
            "status",
            "repo_id",
            "assignee",
            "description",
            "summary",
        ] {
            let _ = conn
                .execute_unprepared(&format!("ALTER TABLE tasks DROP COLUMN {col}"))
                .await;
        }

        Ok(())
    }

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