-- ==========================================================
-- SaniBook CONSOLIDATED LIVE DEPLOYMENT SCRIPT (Phases 1-4)
-- Date: Feb 20, 2026
-- Purpose: Safely applies all DB schema changes from recent phases 
--          without causing errors if columns already exist.
-- ==========================================================

-- 1. Helper Procedure to safely add columns if they bypass
DROP PROCEDURE IF EXISTS AddColumnIfNotExists;
DELIMITER //
CREATE PROCEDURE AddColumnIfNotExists(
    IN tableName VARCHAR(255), 
    IN colName VARCHAR(255), 
    IN colDef VARCHAR(255)
)
BEGIN
    DECLARE col_count INT;
    SELECT COUNT(*) INTO col_count 
    FROM information_schema.columns 
    WHERE table_name = tableName 
    AND column_name = colName 
    AND table_schema = DATABASE();
    
    IF col_count = 0 THEN
        SET @s = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', colName, '` ', colDef);
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END //
DELIMITER ;

-- ==========================================================
-- 2. CREATE NEW TABLES (Token & Subscription System)
-- ==========================================================

-- Subscription Packages Table
CREATE TABLE IF NOT EXISTS `subscription_packages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `token_amount` INT NOT NULL,
    `description` TEXT,
    `duration_days` INT DEFAULT 30,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_featured` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Subscriptions Table
CREATE TABLE IF NOT EXISTS `user_subscriptions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `package_id` INT NOT NULL,
    `tokens_remaining` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME NOT NULL,
    `status` ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_user_sub` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

-- Token Transactions
CREATE TABLE IF NOT EXISTS `token_transactions` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` int(11) NOT NULL,
    `subscription_id` int(11) DEFAULT NULL,
    `amount` decimal(10,2) NOT NULL,
    `transaction_type` enum('credit','debit','topup','adjustment') NOT NULL,
    `description` varchar(255) DEFAULT NULL,
    `reference_id` varchar(100) DEFAULT NULL COMMENT 'Internal Order ID or Ref',
    `transaction_ref` varchar(100) DEFAULT NULL COMMENT 'External Payment Ref',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_token_trans_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

-- Token Orders (Purchases)
CREATE TABLE IF NOT EXISTS `token_orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `package_id` INT NULL,
    `amount_paid` DECIMAL(10,2) NOT NULL,
    `tokens_requested` INT NOT NULL,
    `currency` VARCHAR(10) DEFAULT 'KES',
    `payment_method` VARCHAR(50) DEFAULT 'manual',
    `payment_proof` VARCHAR(255) NULL,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `admin_notes` TEXT,
    `reviewed_by` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_token_order_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

-- ==========================================================
-- 3. UPDATE `users` TABLE
-- ==========================================================

CALL AddColumnIfNotExists('users', 'is_public', 'TINYINT(1) DEFAULT 1');
CALL AddColumnIfNotExists('users', 'privacy_settings', 'TEXT NULL');
CALL AddColumnIfNotExists('users', 'user_type', 'ENUM(''individual'', ''organization'') DEFAULT ''organization''');
CALL AddColumnIfNotExists('users', 'salutation', 'VARCHAR(20) NULL');
CALL AddColumnIfNotExists('users', 'personal_email', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('users', 'personal_phone', 'VARCHAR(50) NULL');
CALL AddColumnIfNotExists('users', 'company_email', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('users', 'company_phone', 'VARCHAR(50) NULL');
CALL AddColumnIfNotExists('users', 'designation', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('users', 'website', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('users', 'service_chains', 'JSON NULL');
CALL AddColumnIfNotExists('users', 'enabling_pillars', 'JSON NULL');
CALL AddColumnIfNotExists('users', 'challenges_sector', 'JSON NULL');
CALL AddColumnIfNotExists('users', 'counties', 'JSON NULL');
CALL AddColumnIfNotExists('users', 'bio', 'TEXT NULL');
CALL AddColumnIfNotExists('users', 'county', 'VARCHAR(100) DEFAULT NULL');
CALL AddColumnIfNotExists('users', 'profile_completed', 'TINYINT(1) NOT NULL DEFAULT 1');
CALL AddColumnIfNotExists('users', 'is_verified', 'TINYINT(1) DEFAULT 0');
CALL AddColumnIfNotExists('users', 'verification_code', 'VARCHAR(50) NULL');
CALL AddColumnIfNotExists('users', 'is_enterprise', 'TINYINT(1) DEFAULT 0');
CALL AddColumnIfNotExists('users', 'phone_number', 'VARCHAR(20) NULL');

-- Clean up older unused columns
-- Try/Catch wrapped implicitly, but standard DROP COLUMN lacks IF EXISTS in older MySQL.
-- We will leave DROP sub_county manually if needed or let it be.

-- Mark existing Google-created accounts without phone as incomplete
UPDATE `users` SET `profile_completed` = 0 WHERE `google_id` IS NOT NULL AND (`phone` IS NULL OR `phone` = '') AND `role` != 'admin';

-- ==========================================================
-- 4. UPDATE `submissions` TABLE
-- ==========================================================

CALL AddColumnIfNotExists('submissions', 'title', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('submissions', 'contact_person', 'VARCHAR(255) NULL');
CALL AddColumnIfNotExists('submissions', 'contact_role', 'VARCHAR(100) NULL');
CALL AddColumnIfNotExists('submissions', 'description', 'TEXT NULL');
CALL AddColumnIfNotExists('submissions', 'county', 'VARCHAR(100) NULL');
CALL AddColumnIfNotExists('submissions', 'service_chains', 'JSON NULL');
CALL AddColumnIfNotExists('submissions', 'enabling_pillars', 'JSON NULL');
CALL AddColumnIfNotExists('submissions', 'thematic_categories', 'JSON NULL');
CALL AddColumnIfNotExists('submissions', 'status', 'VARCHAR(50) DEFAULT ''pending''');
CALL AddColumnIfNotExists('submissions', 'admin_notes', 'TEXT NULL');
CALL AddColumnIfNotExists('submissions', 'updated_at', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
CALL AddColumnIfNotExists('submissions', 'edit_count', 'int DEFAULT 0');
CALL AddColumnIfNotExists('submissions', 'last_token_cost', 'int DEFAULT 0');
CALL AddColumnIfNotExists('submissions', 'is_verified', 'TINYINT(1) DEFAULT 0');
CALL AddColumnIfNotExists('submissions', 'verification_code', 'VARCHAR(50) NULL');
CALL AddColumnIfNotExists('submissions', 'password_hash', 'VARCHAR(255) NULL');

-- ==========================================================
-- 5. FINAL SEED DATA / CLEANUP
-- ==========================================================

-- Insert Default Packages if empty
INSERT INTO `subscription_packages` (`name`, `price`, `token_amount`, `description`, `is_active`, `is_featured`)
SELECT * FROM (SELECT 'Starter Bundle', 1000.00, 1500, 'Perfect for getting started with small stories.', 1, 0) AS tmp
WHERE NOT EXISTS (SELECT name FROM `subscription_packages` WHERE name = 'Starter Bundle') LIMIT 1;

INSERT INTO `subscription_packages` (`name`, `price`, `token_amount`, `description`, `is_active`, `is_featured`)
SELECT * FROM (SELECT 'Pro Bundle', 5000.00, 8000, 'Best value for serious contributors.', 1, 1) AS tmp
WHERE NOT EXISTS (SELECT name FROM `subscription_packages` WHERE name = 'Pro Bundle') LIMIT 1;

INSERT INTO `subscription_packages` (`name`, `price`, `token_amount`, `description`, `is_active`, `is_featured`)
SELECT * FROM (SELECT 'Enterprise Pack', 20000.00, 35000, 'Maximum tokens for organizations.', 1, 0) AS tmp
WHERE NOT EXISTS (SELECT name FROM `subscription_packages` WHERE name = 'Enterprise Pack') LIMIT 1;

-- Clean up helper procedure
DROP PROCEDURE IF EXISTS AddColumnIfNotExists;

-- Done!
