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(())
}
}