-- ===============================================
-- SaniBook Submissions Table Migration
-- Date: 2026-02-13
-- Adds ALL missing columns to submissions table
-- Fully idempotent — safe to re-run
-- ===============================================

-- 1. Add 'title' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'title' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `title` VARCHAR(255) DEFAULT NULL AFTER `org_name`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. Add 'description' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'description' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `description` TEXT DEFAULT NULL AFTER `story`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Add 'enabling_pillars' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'enabling_pillars' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `enabling_pillars` TEXT DEFAULT NULL AFTER `service_chains`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Add 'thematic_categories' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'thematic_categories' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `thematic_categories` TEXT DEFAULT NULL AFTER `enabling_pillars`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Add 'challenge_area' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'challenge_area' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `challenge_area` VARCHAR(100) DEFAULT NULL AFTER `thematic_categories`', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. Add 'status' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'status' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `status` ENUM(''pending'',''approved'',''rejected'') DEFAULT ''pending''', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. Add 'category_id' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'category_id' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `category_id` INT(11) DEFAULT 0', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 8. Add 'user_id' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'user_id' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `user_id` INT(11) DEFAULT NULL', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. Add 'last_token_cost' column
SET @col = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'submissions' AND COLUMN_NAME = 'last_token_cost' AND TABLE_SCHEMA = DATABASE());
SET @sql = IF(@col = 0, 
    'ALTER TABLE `submissions` ADD COLUMN `last_token_cost` INT(11) DEFAULT 0', 
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 10. Make password_hash nullable (logged-in users don't provide one)
ALTER TABLE `submissions` 
MODIFY COLUMN `password_hash` VARCHAR(255) DEFAULT NULL;

-- 11. Make contact_role nullable
ALTER TABLE `submissions` 
MODIFY COLUMN `contact_role` VARCHAR(100) DEFAULT NULL;

-- 12. Make contact_person nullable
ALTER TABLE `submissions` 
MODIFY COLUMN `contact_person` VARCHAR(255) DEFAULT NULL;

-- 13. Make mobile nullable
ALTER TABLE `submissions` 
MODIFY COLUMN `mobile` VARCHAR(20) DEFAULT NULL;

-- ===============================================
-- Migration Complete
-- Run this BEFORE migrate_media_system.sql
-- ===============================================
