-- Apply once to the existing SLPS database before opening Project Collaborations.
CREATE TABLE IF NOT EXISTS collaboration_projects (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(160) NOT NULL,
 description TEXT NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 storage_key CHAR(48) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS collaboration_members (
 project_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 added_by BIGINT UNSIGNED NOT NULL,
 added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (project_id,user_id),
 KEY idx_collaboration_member (user_id,project_id),
 FOREIGN KEY (project_id) REFERENCES collaboration_projects(id) ON DELETE CASCADE,
 FOREIGN KEY (user_id) REFERENCES users(id),
 FOREIGN KEY (added_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
