-- Run once against the existing SLPS database before opening Practical Guidelines.
CREATE TABLE IF NOT EXISTS course_guidelines (
 course_id BIGINT UNSIGNED NOT NULL,
 draft_delta MEDIUMTEXT NOT NULL,
 published_delta MEDIUMTEXT NULL,
 draft_attachment_ids TEXT NOT NULL,
 published_attachment_ids TEXT NULL,
 revision INT UNSIGNED NOT NULL DEFAULT 1,
 published_revision INT UNSIGNED NULL,
 updated_by BIGINT UNSIGNED NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 published_at DATETIME NULL,
 PRIMARY KEY(course_id),
 CONSTRAINT fk_cg_course FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
 CONSTRAINT fk_cg_editor FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS guideline_assets (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 course_id BIGINT UNSIGNED NOT NULL,
 file_name VARCHAR(255) NOT NULL,
 storage_key VARCHAR(80) NOT NULL,
 media_type VARCHAR(100) NOT NULL,
 file_size_bytes BIGINT UNSIGNED NOT NULL,
 asset_kind VARCHAR(12) NOT NULL,
 is_published TINYINT(1) NOT NULL DEFAULT 0,
 uploaded_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_ga_storage(storage_key), KEY idx_ga_course(course_id,is_published),
 CONSTRAINT fk_ga_course FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
 CONSTRAINT fk_ga_uploader FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
