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

/// When a legacy `task_repos` table exists, copy rows into `task_executions` and drop the old table.
/// No-op if `task_repos` is absent (fresh DBs already have `task_executions` from `m20240101`).
#[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_old = Statement::from_string(
            backend,
            "SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='task_repos' LIMIT 1"
                .to_string(),
        );
        if conn.query_one(has_old).await?.is_none() {
            return Ok(());
        }

        let has_new = Statement::from_string(
            backend,
            "SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='task_executions' LIMIT 1".to_string(),
        );
        if conn.query_one(has_new).await?.is_some() {
            let _ = conn.execute_unprepared("DROP TABLE task_repos").await;
            return Ok(());
        }

        let _ = conn
            .execute_unprepared(
                r#"CREATE TABLE task_executions (
                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    task_id INTEGER NOT NULL,
                    repo_id INTEGER NOT NULL,
                    engine_type TEXT NOT NULL DEFAULT 'claude_code',
                    summary TEXT NOT NULL DEFAULT '',
                    description TEXT,
                    assignee TEXT,
                    status TEXT NOT NULL DEFAULT 'pending',
                    branch_name TEXT,
                    pr_url TEXT,
                    error_message TEXT,
                    fetched_at TEXT NOT NULL DEFAULT '',
                    processed_at TEXT,
                    started_at TEXT,
                    finished_at TEXT,
                    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                    FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
                )"#,
            )
            .await;

        let _ = conn
            .execute_unprepared(
                r#"INSERT INTO task_executions (task_id, repo_id, engine_type, summary, status, fetched_at, started_at, finished_at, error_message)
                   SELECT task_id, repo_id, 'claude_code', '', 'pending', COALESCE(started_at, ''), started_at, completed_at, error_message FROM task_repos"#,
            )
            .await;

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

        let _ = conn
            .execute_unprepared(
                "CREATE INDEX IF NOT EXISTS idx_task_executions_task_id ON task_executions (task_id)",
            )
            .await;

        Ok(())
    }

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