-- 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
);

-- 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,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Helper procedure to add columns if they don't exist (simplifies re-running)
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 ;

-- Update Users Table
CALL AddColumnIfNotExists('users', 'is_enterprise', 'TINYINT(1) DEFAULT 0');
CALL AddColumnIfNotExists('users', 'phone_number', 'VARCHAR(20) NULL');

-- Update Token Transactions Table for better audit
CALL AddColumnIfNotExists('token_transactions', 'reference_id', 'VARCHAR(100) NULL COMMENT "Internal Order ID or Ref"');
CALL AddColumnIfNotExists('token_transactions', 'transaction_ref', 'VARCHAR(100) NULL COMMENT "External Payment Ref"');

-- 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;
