-- Add salary_income transaction type to wallet_transactions table
-- This migration adds the new transaction type for salary income system

-- Update wallet_transactions table to include salary_income transaction type
ALTER TABLE wallet_transactions 
MODIFY COLUMN transaction_type ENUM(
    'deposit', 
    'withdrawal', 
    'package_purchase', 
    'add_funds', 
    'deduct_funds', 
    'transfer', 
    'commission', 
    'daily_income', 
    'level_income', 
    'direct_commission', 
    'team_reward', 
    'refund',
    'salary_income'
) NOT NULL;

-- Update income_transactions table to include salary_income transaction type
ALTER TABLE income_transactions 
MODIFY COLUMN transaction_type ENUM(
    'daily_income', 
    'direct_commission', 
    'level_income', 
    'team_reward', 
    'withdrawal', 
    'topup',
    'salary_income'
) NOT NULL;

-- Add salary income settings to system_settings table
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('salary_income_enabled', '1', 'Enable salary income system (0=disabled, 1=enabled)'),
('salary_income_auto_process', '1', 'Auto process salary income (0=manual, 1=auto)'),
('salary_income_check_interval', '24', 'Hours between salary income checks');

-- Create salary income tiers table for easier management
CREATE TABLE IF NOT EXISTS salary_income_tiers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tier_name VARCHAR(50) NOT NULL,
    required_members INT NOT NULL,
    required_business_volume DECIMAL(10,2) NOT NULL,
    salary_amount DECIMAL(10,2) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tier_requirements (required_members, required_business_volume)
);

-- Insert salary income tiers
INSERT INTO salary_income_tiers (tier_name, required_members, required_business_volume, salary_amount) VALUES
('Bronze', 5, 1000.00, 10.00),
('Silver', 25, 5000.00, 50.00),
('Gold', 75, 15000.00, 100.00),
('Platinum', 125, 25000.00, 1000.00),
('Diamond', 500, 50000.00, 2500.00);

-- Create salary income processing log table
CREATE TABLE IF NOT EXISTS salary_income_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tier_id INT NOT NULL,
    team_members INT NOT NULL,
    business_volume DECIMAL(10,2) NOT NULL,
    salary_amount DECIMAL(10,2) NOT NULL,
    processed_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processed', 'capped', 'failed') DEFAULT 'pending',
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (tier_id) REFERENCES salary_income_tiers(id) ON DELETE CASCADE,
    INDEX idx_user_tier (user_id, tier_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);