-- Migration 010: CMS Pages, SMS Password Reset, New Settings

-- 1. CMS Pages table
CREATE TABLE IF NOT EXISTS `cms_pages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `title` VARCHAR(255) NOT NULL,
    `content` LONGTEXT,
    `meta_title` VARCHAR(255) DEFAULT NULL,
    `meta_description` TEXT,
    `status` ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    `created_by` INT DEFAULT NULL,
    `updated_by` INT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default home page
INSERT IGNORE INTO `cms_pages` (`slug`, `title`, `status`) VALUES ('home', 'Home Page', 'draft');

-- 2. Add SMS/OTP columns to password_resets
CALL AddColumnIfNotExists('password_resets', 'reset_method', "ENUM('email','sms') DEFAULT 'email' AFTER `token`");
CALL AddColumnIfNotExists('password_resets', 'phone', 'VARCHAR(20) DEFAULT NULL AFTER `reset_method`');
CALL AddColumnIfNotExists('password_resets', 'otp', 'VARCHAR(10) DEFAULT NULL AFTER `phone`');

-- 3. New settings
INSERT IGNORE INTO `settings` (`key`, `value`, `type`, `group`) VALUES
('default_user_role', 'viewer', 'select', 'general'),
('auto_approve_registration', 'false', 'boolean', 'general'),
('enable_registration', 'true', 'boolean', 'general'),
('company_address', '', 'text', 'general'),
('company_phone', '', 'text', 'general'),
('sms_provider', 'hubtel', 'select', 'mail'),
('sms_api_key', '', 'text', 'mail'),
('sms_api_secret', '', 'text', 'mail'),
('sms_sender_id', 'GAS', 'text', 'mail'),
('home_hero_title', 'Advancing Anaesthesiology in Ghana', 'text', 'appearance'),
('home_hero_subtitle', 'The Ghana Anaesthesiologists Society is the professional body uniting anaesthesiologists across the nation.', 'textarea', 'appearance'),
('home_show_announcements', 'true', 'boolean', 'appearance'),
('home_show_events', 'true', 'boolean', 'appearance');