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

/// Migrates pre–task_specs databases: copies summary/description/assignee/repo into
/// `task_specs`, then drops legacy columns from `tasks`.
#[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();

        // Only the legacy wide `tasks` layout (with `repo_url`) is migrated into `task_specs`.
        // New schemas store work on `tasks` directly and have `repo_id` instead.
        let has_repo_url = Statement::from_string(
            backend,
            "SELECT 1 AS x FROM pragma_table_info('tasks') WHERE name = 'repo_url' LIMIT 1"
                .to_string(),
        );
        if conn.query_one(has_repo_url).await?.is_none() {
            return Ok(());
        }

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

        let _ = conn
            .execute_unprepared(
                r#"CREATE TABLE IF NOT EXISTS task_specs (
                    task_id INTEGER NOT NULL PRIMARY KEY,
                    summary TEXT NOT NULL,
                    description TEXT,
                    assignee TEXT,
                    repo_id INTEGER NOT NULL,
                    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                    FOREIGN KEY (repo_id) REFERENCES repos(id)
                )"#,
            )
            .await;

        let _ = conn
            .execute_unprepared(
                r#"INSERT OR IGNORE INTO task_specs (task_id, summary, description, assignee, repo_id)
                   SELECT t.id, t.summary, t.description, t.assignee, r.id
                   FROM tasks t
                   INNER JOIN repos r ON r.slug = t.repo_url
                   WHERE TRIM(COALESCE(t.repo_url, '')) != ''"#,
            )
            .await;

        for col in ["summary", "description", "assignee", "project", "repo_url"] {
            let _ = conn
                .execute_unprepared(&format!("ALTER TABLE tasks DROP COLUMN {col}"))
                .await;
        }

        Ok(())
    }

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