-- ===============================================
-- SaniBook Media System Migration
-- Date: 2026-02-13
-- Description: Adds PDF support, captions, and original filenames to media_uploads
-- Fully idempotent — safe to re-run
-- ===============================================

-- 1. Expand media_type enum to include 'pdf'
ALTER TABLE `media_uploads` 
MODIFY COLUMN `media_type` ENUM('image','video','audio','text','pdf') NOT NULL;

-- 2. Add caption column if it doesn't exist
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'media_uploads' AND COLUMN_NAME = 'caption' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `media_uploads` ADD COLUMN `caption` VARCHAR(500) DEFAULT NULL AFTER `description`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Add original_filename column if it doesn't exist
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'media_uploads' AND COLUMN_NAME = 'original_filename' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `media_uploads` ADD COLUMN `original_filename` VARCHAR(255) DEFAULT NULL AFTER `caption`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Add submission_id column if it doesn't exist
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'media_uploads' AND COLUMN_NAME = 'submission_id' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `media_uploads` ADD COLUMN `submission_id` INT(11) DEFAULT NULL AFTER `user_id`, ADD KEY `submission_id` (`submission_id`)', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Drop the foreign key on subscription_id if it exists
SET @fk = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'fk_media_upload_subscription' 
    AND TABLE_NAME = 'media_uploads' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@fk > 0, 
    'ALTER TABLE `media_uploads` DROP FOREIGN KEY `fk_media_upload_subscription`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. Make subscription_id nullable (it's NOT NULL but submit.php doesn't always have one)
ALTER TABLE `media_uploads` 
MODIFY COLUMN `subscription_id` INT(11) DEFAULT NULL;

-- 7. Fix any existing records with wrong media_type
UPDATE `media_uploads` SET `media_type` = 'pdf'   WHERE `mime_type` = 'application/pdf';
UPDATE `media_uploads` SET `media_type` = 'video'  WHERE `mime_type` LIKE 'video/%' AND `media_type` NOT IN ('video');
UPDATE `media_uploads` SET `media_type` = 'audio'  WHERE `mime_type` LIKE 'audio/%' AND `media_type` NOT IN ('audio');

-- ===============================================
-- Migration Complete
-- ===============================================
