210 lines
9.7 KiB
SQL
210 lines
9.7 KiB
SQL
-- Migration: Split mailbox into separate tables for each module
|
|
-- This addresses the architectural concern of having all modules in a single table
|
|
|
|
-- 1. Create INBOUND tables
|
|
CREATE TABLE IF NOT EXISTS inbound_mail (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ref_no VARCHAR(50) NOT NULL UNIQUE,
|
|
date_registered DATE NOT NULL,
|
|
due_date DATE NULL,
|
|
sender VARCHAR(255),
|
|
recipient VARCHAR(255),
|
|
subject VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status_id INT,
|
|
assigned_to INT,
|
|
created_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS inbound_attachments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
display_name VARCHAR(255),
|
|
file_path VARCHAR(255) NOT NULL,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_size INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES inbound_mail(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS inbound_comments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
user_id INT,
|
|
comment TEXT NOT NULL,
|
|
referred_user_id INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES inbound_mail(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. Create OUTBOUND tables
|
|
CREATE TABLE IF NOT EXISTS outbound_mail (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ref_no VARCHAR(50) NOT NULL UNIQUE,
|
|
date_registered DATE NOT NULL,
|
|
due_date DATE NULL,
|
|
sender VARCHAR(255),
|
|
recipient VARCHAR(255),
|
|
subject VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status_id INT,
|
|
assigned_to INT,
|
|
created_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS outbound_attachments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
display_name VARCHAR(255),
|
|
file_path VARCHAR(255) NOT NULL,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_size INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES outbound_mail(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS outbound_comments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
user_id INT,
|
|
comment TEXT NOT NULL,
|
|
referred_user_id INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES outbound_mail(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. Create INTERNAL tables
|
|
CREATE TABLE IF NOT EXISTS internal_mail (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ref_no VARCHAR(50) NOT NULL UNIQUE,
|
|
date_registered DATE NOT NULL,
|
|
due_date DATE NULL,
|
|
sender VARCHAR(255),
|
|
recipient VARCHAR(255),
|
|
subject VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status_id INT,
|
|
assigned_to INT,
|
|
created_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS internal_attachments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
display_name VARCHAR(255),
|
|
file_path VARCHAR(255) NOT NULL,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_size INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES internal_mail(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS internal_comments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
mail_id INT NOT NULL,
|
|
user_id INT,
|
|
comment TEXT NOT NULL,
|
|
referred_user_id INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (mail_id) REFERENCES internal_mail(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. Migrate data (using INSERT IGNORE to allow re-running partially failed migrations)
|
|
-- We wrap these in conditional checks to ensure they only run if 'mailbox' exists.
|
|
|
|
SET @mailbox_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'mailbox');
|
|
|
|
-- Inbound Mail
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO inbound_mail (id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at) SELECT id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at FROM mailbox WHERE type = 'inbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO inbound_attachments (id, mail_id, display_name, file_path, file_name, file_size, created_at) SELECT a.id, a.mail_id, a.display_name, a.file_path, a.file_name, a.file_size, a.created_at FROM attachments a JOIN mailbox m ON a.mail_id = m.id WHERE m.type = 'inbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO inbound_comments (id, mail_id, user_id, comment, referred_user_id, created_at) SELECT c.id, c.mail_id, c.user_id, c.comment, c.referred_user_id, c.created_at FROM comments c JOIN mailbox m ON c.mail_id = m.id WHERE m.type = 'inbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Outbound Mail
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO outbound_mail (id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at) SELECT id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at FROM mailbox WHERE type = 'outbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO outbound_attachments (id, mail_id, display_name, file_path, file_name, file_size, created_at) SELECT a.id, a.mail_id, a.display_name, a.file_path, a.file_name, a.file_size, a.created_at FROM attachments a JOIN mailbox m ON a.mail_id = m.id WHERE m.type = 'outbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO outbound_comments (id, mail_id, user_id, comment, referred_user_id, created_at) SELECT c.id, c.mail_id, c.user_id, c.comment, c.referred_user_id, c.created_at FROM comments c JOIN mailbox m ON c.mail_id = m.id WHERE m.type = 'outbound'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Internal Mail
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO internal_mail (id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at) SELECT id, ref_no, date_registered, due_date, sender, recipient, subject, description, status_id, assigned_to, created_by, created_at, updated_at FROM mailbox WHERE type = 'internal'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO internal_attachments (id, mail_id, display_name, file_path, file_name, file_size, created_at) SELECT a.id, a.mail_id, a.display_name, a.file_path, a.file_name, a.file_size, a.created_at FROM attachments a JOIN mailbox m ON a.mail_id = m.id WHERE m.type = 'internal'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(@mailbox_exists > 0,
|
|
"INSERT IGNORE INTO internal_comments (id, mail_id, user_id, comment, referred_user_id, created_at) SELECT c.id, c.mail_id, c.user_id, c.comment, c.referred_user_id, c.created_at FROM comments c JOIN mailbox m ON c.mail_id = m.id WHERE m.type = 'internal'",
|
|
"SELECT 1");
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 5. Rename old tables instead of dropping for safety
|
|
-- Only rename if 'mailbox' exists and 'mailbox_old' does not
|
|
SET @old_mailbox = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'mailbox_old');
|
|
SET @mailbox_exists = (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'mailbox');
|
|
|
|
SET @sql_rename = IF(@old_mailbox = 0 AND @mailbox_exists > 0, 'RENAME TABLE mailbox TO mailbox_old, attachments TO attachments_old, comments TO comments_old', 'SELECT 1');
|
|
PREPARE stmt FROM @sql_rename;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|