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> {
        manager
            .get_connection()
            .execute_unprepared(
                r#"
-- Add ap_remote_note_id column
ALTER TABLE attachments ADD COLUMN ap_remote_note_id INTEGER;

-- Recreate table with FK constraints and updated CHECK
CREATE TABLE attachments_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT NOT NULL UNIQUE,
    owner_kind TEXT NOT NULL,
    owner_id INTEGER NOT NULL,
    filename TEXT NOT NULL,
    content_type TEXT NOT NULL,
    size INTEGER NOT NULL,
    remote_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ticket_id INTEGER,
    comment_id INTEGER,
    ap_note_id INTEGER,
    ap_remote_note_id INTEGER,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    FOREIGN KEY (ap_note_id) REFERENCES ap_notes(id) ON DELETE CASCADE,
    FOREIGN KEY (ap_remote_note_id) REFERENCES ap_remote_notes(id) ON DELETE CASCADE,
    CHECK (
        (owner_kind = 'ticket' AND ticket_id IS NOT NULL AND comment_id IS NULL AND ap_note_id IS NULL AND ap_remote_note_id IS NULL) OR
        (owner_kind = 'comment' AND ticket_id IS NULL AND comment_id IS NOT NULL AND ap_note_id IS NULL AND ap_remote_note_id IS NULL) OR
        (owner_kind = 'ap_note' AND ticket_id IS NULL AND comment_id IS NULL AND ap_note_id IS NOT NULL AND ap_remote_note_id IS NULL) OR
        (owner_kind = 'ap_remote_note' AND ticket_id IS NULL AND comment_id IS NULL AND ap_note_id IS NULL AND ap_remote_note_id IS NOT NULL)
    )
);

-- Copy data, fixing mislinked remote note attachments
INSERT INTO attachments_new (
    id, uuid, owner_kind, owner_id, filename, content_type, size, remote_url, created_at,
    ticket_id, comment_id, ap_note_id, ap_remote_note_id
)
SELECT
    id, uuid,
    CASE
        WHEN owner_kind = 'ap_note' AND ap_note_id IS NOT NULL AND remote_url IS NOT NULL
             AND EXISTS (SELECT 1 FROM ap_remote_notes WHERE id = owner_id)
        THEN 'ap_remote_note'
        ELSE owner_kind
    END,
    owner_id, filename, content_type, size, remote_url, created_at,
    ticket_id,
    comment_id,
    CASE
        WHEN owner_kind = 'ap_note' AND ap_note_id IS NOT NULL AND remote_url IS NOT NULL
             AND EXISTS (SELECT 1 FROM ap_remote_notes WHERE id = owner_id)
        THEN NULL
        ELSE ap_note_id
    END,
    CASE
        WHEN owner_kind = 'ap_note' AND ap_note_id IS NOT NULL AND remote_url IS NOT NULL
             AND EXISTS (SELECT 1 FROM ap_remote_notes WHERE id = owner_id)
        THEN owner_id
        ELSE NULL
    END
FROM attachments;

DROP TABLE attachments;
ALTER TABLE attachments_new RENAME TO attachments;

-- Recreate indexes
CREATE INDEX idx_attachments_ticket ON attachments (ticket_id, created_at);
CREATE INDEX idx_attachments_comment ON attachments (comment_id, created_at);
CREATE INDEX idx_attachments_ap_note ON attachments (ap_note_id, created_at);
CREATE INDEX idx_attachments_ap_remote_note ON attachments (ap_remote_note_id, created_at);
"#,
            )
            .await?;
        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .get_connection()
            .execute_unprepared(
                r#"
CREATE TABLE attachments_old (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT NOT NULL UNIQUE,
    owner_kind TEXT NOT NULL,
    owner_id INTEGER NOT NULL,
    filename TEXT NOT NULL,
    content_type TEXT NOT NULL,
    size INTEGER NOT NULL,
    remote_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ticket_id INTEGER,
    comment_id INTEGER,
    ap_note_id INTEGER,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    FOREIGN KEY (ap_note_id) REFERENCES ap_notes(id) ON DELETE CASCADE,
    CHECK (
        (owner_kind = 'ticket' AND ticket_id IS NOT NULL AND comment_id IS NULL AND ap_note_id IS NULL) OR
        (owner_kind = 'comment' AND ticket_id IS NULL AND comment_id IS NOT NULL AND ap_note_id IS NULL) OR
        (owner_kind = 'ap_note' AND ticket_id IS NULL AND comment_id IS NULL AND ap_note_id IS NOT NULL)
    )
);

INSERT INTO attachments_old (
    id, uuid, owner_kind, owner_id, filename, content_type, size, remote_url, created_at,
    ticket_id, comment_id, ap_note_id
)
SELECT
    id, uuid,
    CASE WHEN owner_kind = 'ap_remote_note' THEN 'ap_note' ELSE owner_kind END,
    owner_id, filename, content_type, size, remote_url, created_at,
    ticket_id,
    comment_id,
    COALESCE(ap_note_id, ap_remote_note_id)
FROM attachments
WHERE owner_kind != 'ap_remote_note' OR ap_remote_note_id IS NOT NULL;

DROP TABLE attachments;
ALTER TABLE attachments_old RENAME TO attachments;

CREATE INDEX idx_attachments_ticket ON attachments (ticket_id, created_at);
CREATE INDEX idx_attachments_comment ON attachments (comment_id, created_at);
CREATE INDEX idx_attachments_ap_note ON attachments (ap_note_id, created_at);
"#,
            )
            .await?;
        Ok(())
    }
}