use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // SQLite cannot drop NOT NULL constraints, so we rebuild the table.
        //
        // Key changes:
        // - creator_id becomes nullable (for remote-created patch proposals)
        // - Add remote_actor_id (FK to ap_remote_actors, nullable)
        // - Add remote_offer_id (unique, for idempotency on inbound Offers)
        // - Add resolve_activity_url (for Undo(Resolve) correlation)
        // - CHECK constraint: exactly one of creator_id or remote_actor_id
        manager
            .get_connection()
            .execute_unprepared(
                r#"
-- Create new table with federation columns
CREATE TABLE patch_proposals_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT NOT NULL UNIQUE,
    repository_id INTEGER NOT NULL,
    creator_id INTEGER,
    remote_actor_id INTEGER,
    remote_offer_id TEXT UNIQUE,
    resolve_activity_url TEXT,
    number INTEGER NOT NULL,
    summary TEXT NOT NULL,
    description TEXT,
    source_ref TEXT NOT NULL,
    target_branch TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft',
    merge_policy TEXT NOT NULL DEFAULT 'prefer-fast-forward',
    change_id TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (remote_actor_id) REFERENCES ap_remote_actors(id) ON DELETE CASCADE,
    CHECK ((creator_id IS NOT NULL) != (remote_actor_id IS NOT NULL))
);

-- Copy data from old table
INSERT INTO patch_proposals_new (
    id, uuid, repository_id, creator_id, number, summary, description,
    source_ref, target_branch, status, merge_policy, change_id,
    created_at, updated_at
)
SELECT id, uuid, repository_id, creator_id, number, summary, description,
       source_ref, target_branch, status, merge_policy, change_id,
       created_at, updated_at
FROM patch_proposals;

-- Drop old table
DROP TABLE patch_proposals;

-- Rename new table
ALTER TABLE patch_proposals_new RENAME TO patch_proposals;

-- Recreate indexes
CREATE UNIQUE INDEX uq_patch_proposals_repository_number
    ON patch_proposals (repository_id, number);
CREATE UNIQUE INDEX uq_patch_proposals_repository_source_ref
    ON patch_proposals (repository_id, source_ref);
CREATE UNIQUE INDEX uq_patch_proposals_repository_change_id
    ON patch_proposals (repository_id, change_id)
    WHERE change_id IS NOT NULL;
CREATE INDEX idx_patch_proposals_repository_id
    ON patch_proposals (repository_id);
CREATE INDEX idx_patch_proposals_creator_id
    ON patch_proposals (creator_id);
CREATE INDEX idx_patch_proposals_status
    ON patch_proposals (repository_id, status);
"#,
            )
            .await?;

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // Revert to original schema
        manager
            .get_connection()
            .execute_unprepared(
                r#"
-- Recreate original table
CREATE TABLE patch_proposals_old (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT NOT NULL UNIQUE,
    repository_id INTEGER NOT NULL,
    creator_id INTEGER NOT NULL,
    number INTEGER NOT NULL,
    summary TEXT NOT NULL,
    description TEXT,
    source_ref TEXT NOT NULL,
    target_branch TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft',
    merge_policy TEXT NOT NULL DEFAULT 'prefer-fast-forward',
    change_id TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Copy only locally-created patch proposals (creator_id IS NOT NULL)
INSERT INTO patch_proposals_old (
    id, uuid, repository_id, creator_id, number, summary, description,
    source_ref, target_branch, status, merge_policy, change_id,
    created_at, updated_at
)
SELECT id, uuid, repository_id, creator_id, number, summary, description,
       source_ref, target_branch, status, merge_policy, change_id,
       created_at, updated_at
FROM patch_proposals
WHERE creator_id IS NOT NULL;

-- Drop new table
DROP TABLE patch_proposals;

-- Rename back
ALTER TABLE patch_proposals_old RENAME TO patch_proposals;

-- Recreate indexes
CREATE UNIQUE INDEX uq_patch_proposals_repository_number
    ON patch_proposals (repository_id, number);
CREATE UNIQUE INDEX uq_patch_proposals_repository_source_ref
    ON patch_proposals (repository_id, source_ref);
CREATE UNIQUE INDEX uq_patch_proposals_repository_change_id
    ON patch_proposals (repository_id, change_id)
    WHERE change_id IS NOT NULL;
CREATE INDEX idx_patch_proposals_repository_id
    ON patch_proposals (repository_id);
CREATE INDEX idx_patch_proposals_creator_id
    ON patch_proposals (creator_id);
CREATE INDEX idx_patch_proposals_status
    ON patch_proposals (repository_id, status);
"#,
            )
            .await?;

        Ok(())
    }
}