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

/// When `tasks` still holds lifecycle columns (`status`, …), copy them into `task_specs` and drop
/// them from `tasks`. No-op if `tasks.status` is already absent (new schema).
#[derive(DeriveMigrationName)]
pub struct Migration;

#[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 has_task_specs = Statement::from_string(
            backend,
            "SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='task_specs' LIMIT 1"
                .to_string(),
        );
        if conn.query_one(has_task_specs).await?.is_none() {
            return Ok(());
        }

        let stmt = Statement::from_string(
            backend,
            "SELECT 1 AS x FROM pragma_table_info('tasks') WHERE name = 'status' LIMIT 1"
                .to_string(),
        );
        if conn.query_one(stmt).await?.is_none() {
            return Ok(());
        }

        // task_specs may exist from an older migration without these columns — add if missing.
        for sql in [
            "ALTER TABLE task_specs ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'",
            "ALTER TABLE task_specs ADD COLUMN branch_name TEXT",
            "ALTER TABLE task_specs ADD COLUMN pr_url TEXT",
            "ALTER TABLE task_specs ADD COLUMN error_message TEXT",
            "ALTER TABLE task_specs ADD COLUMN fetched_at TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE task_specs ADD COLUMN processed_at TEXT",
        ] {
            let _ = conn.execute_unprepared(sql).await;
        }

        let _ = conn
            .execute_unprepared(
                r#"UPDATE task_specs SET
                    status = (SELECT t.status FROM tasks t WHERE t.id = task_specs.task_id),
                    branch_name = (SELECT t.branch_name FROM tasks t WHERE t.id = task_specs.task_id),
                    pr_url = (SELECT t.pr_url FROM tasks t WHERE t.id = task_specs.task_id),
                    error_message = (SELECT t.error_message FROM tasks t WHERE t.id = task_specs.task_id),
                    fetched_at = (SELECT t.fetched_at FROM tasks t WHERE t.id = task_specs.task_id),
                    processed_at = (SELECT t.processed_at FROM tasks t WHERE t.id = task_specs.task_id)"#,
            )
            .await;

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

        Ok(())
    }

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