-- ===============================================
-- ALTER SCRIPTS FOR SSCI OFFICE DATABASE
-- Profile & Settings Tables Schema Updates
-- Date: 2025-12-09
-- ===============================================

-- ===============================================
-- 1. ALTER USERS TABLE - Add Missing Profile Fields
-- ===============================================
-- Profile fields being saved: name, phone, whatsapp, address, photo
-- Preferences fields being saved: language, email_notifications, in_app_notifications, sms_notifications

ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20) AFTER email;
ALTER TABLE users ADD COLUMN IF NOT EXISTS whatsapp VARCHAR(20) AFTER phone;
ALTER TABLE users ADD COLUMN IF NOT EXISTS address TEXT AFTER whatsapp;
ALTER TABLE users ADD COLUMN IF NOT EXISTS photo LONGTEXT AFTER address;
ALTER TABLE users ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'en' AFTER photo;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_notifications TINYINT(1) DEFAULT 1 AFTER language;
ALTER TABLE users ADD COLUMN IF NOT EXISTS in_app_notifications TINYINT(1) DEFAULT 1 AFTER email_notifications;
ALTER TABLE users ADD COLUMN IF NOT EXISTS sms_notifications TINYINT(1) DEFAULT 0 AFTER in_app_notifications;

-- ===============================================
-- 2. ALTER SETTINGS TABLE - Add Missing Fields
-- ===============================================
-- General settings fields: site_name, site_acronym, email, phone, address
-- Appearance settings fields: primary_color, logo, letterhead
-- Email settings fields: email_enabled, smtp_host, smtp_port, smtp_user, smtp_password, smtp_from
-- Notification settings fields: sms_enabled, sms_gateway_token, whatsapp_api_token, push_enabled
-- App settings fields: app_debug, app_url, app_key (stored in .env)
-- Database settings fields: db_host, db_port, db_name, db_user, db_password (stored in .env)

-- General Settings
ALTER TABLE settings ADD COLUMN IF NOT EXISTS site_name VARCHAR(255) AFTER id;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS site_acronym VARCHAR(50) AFTER site_name;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS email VARCHAR(255) AFTER site_acronym;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS phone VARCHAR(20) AFTER email;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS address TEXT AFTER phone;

-- Appearance Settings
ALTER TABLE settings ADD COLUMN IF NOT EXISTS primary_color VARCHAR(7) DEFAULT '#2563eb' AFTER address;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS logo LONGTEXT AFTER primary_color;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS letterhead LONGTEXT AFTER logo;

-- Email Settings
ALTER TABLE settings ADD COLUMN IF NOT EXISTS email_enabled TINYINT(1) DEFAULT 0 AFTER letterhead;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_host VARCHAR(255) AFTER email_enabled;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_port INT DEFAULT 587 AFTER smtp_host;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_user VARCHAR(255) AFTER smtp_port;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_password VARCHAR(255) AFTER smtp_user;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_from VARCHAR(255) AFTER smtp_password;

-- SMS & WhatsApp Settings
ALTER TABLE settings ADD COLUMN IF NOT EXISTS sms_enabled TINYINT(1) DEFAULT 0 AFTER smtp_from;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS sms_gateway_token VARCHAR(500) AFTER sms_enabled;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS whatsapp_api_token VARCHAR(500) AFTER sms_gateway_token;

-- Push Notifications
ALTER TABLE settings ADD COLUMN IF NOT EXISTS push_enabled TINYINT(1) DEFAULT 0 AFTER whatsapp_api_token;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS vapid_public_key LONGTEXT AFTER push_enabled;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS vapid_private_key LONGTEXT AFTER vapid_public_key;

-- Timestamps
ALTER TABLE settings ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER vapid_private_key;

-- ===============================================
-- 3. VERIFY TABLE STRUCTURES (Check these)
-- ===============================================
-- DESCRIBE users;
-- DESCRIBE settings;

-- ===============================================
-- 4. INSERT DEFAULT SETTINGS IF NOT EXISTS
-- ===============================================
-- Ensure there's at least one settings record
INSERT INTO settings (id, site_name, site_acronym, email_enabled, sms_enabled, push_enabled, updated_at) 
VALUES (1, 'SSCI Office', 'SSCI', 0, 0, 0, NOW()) 
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- ===============================================
-- NOTES:
-- ===============================================
-- 1. All ALTER statements use "IF NOT EXISTS" to prevent errors if columns already exist
-- 2. Fields like app_debug, app_url, app_key, db_host, db_port, db_name, db_user, db_password
--    are stored in .env file, NOT in the database (managed by app/api.php updateEnvFile() function)
-- 3. Photo and logo/letterhead fields use LONGTEXT to store base64 encoded images
-- 4. All boolean fields use TINYINT(1) (0 or 1) for MySQL compatibility
-- 5. Default values are set appropriately (disabled services default to 0, English defaults to 'en')
-- 6. The updated_at timestamp is automatically managed by MySQL

