

-- =====================================================
-- TEAM COMMISSIONS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS team_commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT 'User who earned commission',
    from_user_id BIGINT UNSIGNED NOT NULL COMMENT 'User whose activity triggered commission',
    from_transaction_id BIGINT UNSIGNED NULL COMMENT 'Related transaction',
    
    -- Commission Details
    level ENUM('TA1', 'TB1', 'TC1') NOT NULL COMMENT 'Team level',
    rate DECIMAL(5, 2) NOT NULL COMMENT 'Commission rate applied',
    base_amount DECIMAL(15, 2) NOT NULL COMMENT 'Amount commission calculated on',
    commission_amount DECIMAL(15, 2) NOT NULL COMMENT 'Actual commission earned',
    
    -- Status
    status ENUM('pending', 'credited', 'cancelled') DEFAULT 'credited',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_commissions (user_id),
    INDEX idx_from_user (from_user_id),
    INDEX idx_level (level),
    INDEX idx_date (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update referral_settings to percentage-based
UPDATE referral_settings SET 
    referral_bonus_type = 'percentage',
    referral_bonus_amount = 0
WHERE id = 1;

-- Add team commission rates to system_settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('team_commission_ta1', '7', 'decimal', 'Team A1 commission rate - 7%', TRUE),
('team_commission_tb1', '4', 'decimal', 'Team B1 commission rate - 4%', TRUE),
('team_commission_tc1', '1', 'decimal', 'Team C1 commission rate - 1%', TRUE),
('team_commission_enabled', 'true', 'boolean', 'Enable team commission system', TRUE)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);



-- =====================================================
-- UPDATE REFERRAL SETTINGS TO TEAM LEVELS
-- =====================================================

-- Remove fixed 1000 and update to team level commissions
UPDATE referral_settings SET 
    referral_bonus_type = 'percentage',
    referral_bonus_amount = 0,
    is_active = TRUE
WHERE id = 1;

-- If no row exists, insert
INSERT INTO referral_settings (referral_bonus_type, referral_bonus_amount, is_active)
SELECT 'percentage', 0, TRUE
WHERE NOT EXISTS (SELECT 1 FROM referral_settings);

-- Ensure team commission settings exist
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('team_commission_ta1', '7', 'decimal', 'Team A1 commission rate (Level 1) - 7%', TRUE),
('team_commission_tb1', '4', 'decimal', 'Team B1 commission rate (Level 2) - 4%', TRUE),
('team_commission_tc1', '1', 'decimal', 'Team C1 commission rate (Level 3) - 1%', TRUE),
('team_commission_enabled', 'true', 'boolean', 'Enable team commission system', TRUE)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- =====================================================
-- TEAM COMMISSION SETTINGS
-- =====================================================

-- Add team commission rates to system_settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('team_commission_ta1', '7', 'decimal', 'Team A1 commission rate (Level 1) - 7%', TRUE),
('team_commission_tb1', '4', 'decimal', 'Team B1 commission rate (Level 2) - 4%', TRUE),
('team_commission_tc1', '1', 'decimal', 'Team C1 commission rate (Level 3) - 1%', TRUE),
('team_commission_enabled', 'true', 'boolean', 'Enable team commission system', TRUE),
('team_commission_calculation', 'on_investment', 'string', 'When to calculate: on_investment or on_earning', FALSE);

-- Create team_commissions table for tracking
CREATE TABLE IF NOT EXISTS team_commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT 'User who earned commission',
    from_user_id BIGINT UNSIGNED NOT NULL COMMENT 'User whose activity triggered commission',
    from_transaction_id BIGINT UNSIGNED NULL COMMENT 'Related transaction',
    
    -- Commission Details
    level ENUM('TA1', 'TB1', 'TC1') NOT NULL COMMENT 'Team level',
    rate DECIMAL(5, 2) NOT NULL COMMENT 'Commission rate applied',
    base_amount DECIMAL(15, 2) NOT NULL COMMENT 'Amount commission calculated on',
    commission_amount DECIMAL(15, 2) NOT NULL COMMENT 'Actual commission earned',
    
    -- Status
    status ENUM('pending', 'credited', 'cancelled') DEFAULT 'credited',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_commissions (user_id),
    INDEX idx_from_user (from_user_id),
    INDEX idx_level (level),
    INDEX idx_date (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add team_level column to users table (if not exists)
ALTER TABLE users ADD COLUMN IF NOT EXISTS team_level VARCHAR(10) NULL COMMENT 'Team level: TA1, TB1, TC1' AFTER referral_code;

-- Update referral_settings to include team commission
ALTER TABLE referral_settings ADD COLUMN IF NOT EXISTS team_commission_enabled BOOLEAN DEFAULT TRUE AFTER is_active;



-- Add logo settings to system_settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('platform_logo', '/uploads/logo.png', 'string', 'Site logo image URL', TRUE),
('platform_logo_dark', '/uploads/logo-dark.png', 'string', 'Dark mode logo URL', TRUE),
('platform_favicon', '/uploads/favicon.ico', 'string', 'Site favicon URL', TRUE);

- =====================================================
-- AIRTIME DISTRIBUTION INVESTMENT PLATFORM
-- Database Schema - MySQL 8.0+
-- =====================================================

-- Drop existing tables if they exist (in reverse dependency order)

-- =====================================================
-- 1. USERS TABLE
-- =====================================================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100) NULL,
    pin_hash VARCHAR(255) NOT NULL COMMENT 'Encrypted 5-digit PIN',
    pin_reset_token VARCHAR(100) NULL,
    pin_reset_expires TIMESTAMP NULL,
    profile_image VARCHAR(255) NULL,
    
    -- Account Status
    status ENUM('pending', 'active', 'suspended', 'banned') DEFAULT 'pending' COMMENT 'pending=awaiting verification',
    is_phone_verified BOOLEAN DEFAULT FALSE,
    is_email_verified BOOLEAN DEFAULT FALSE,
    
    -- Referral System
    referral_code VARCHAR(20) UNIQUE,
    referred_by BIGINT UNSIGNED NULL,
    
    -- Timestamps
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45) NULL,
    login_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_phone (phone_number),
    INDEX idx_referral (referral_code),
    INDEX idx_status (status),
    INDEX idx_referred_by (referred_by),
    
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 2. OTP VERIFICATIONS TABLE
-- =====================================================
CREATE TABLE otp_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    phone_number VARCHAR(20) NOT NULL,
    otp_code VARCHAR(6) NOT NULL,
    purpose ENUM('registration', 'login', 'withdrawal', 'pin_reset', 'phone_change') NOT NULL,
    
    is_used BOOLEAN DEFAULT FALSE,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_phone_purpose (phone_number, purpose),
    INDEX idx_expires (expires_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 3. USER DEVICES TABLE (Device Tracking & Remember Device)
-- =====================================================
CREATE TABLE user_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    device_id VARCHAR(255) NOT NULL COMMENT 'Unique device identifier',
    device_name VARCHAR(100) NULL COMMENT 'e.g., iPhone 14, Samsung Galaxy',
    device_type ENUM('mobile', 'tablet', 'desktop', 'other') DEFAULT 'mobile',
    platform VARCHAR(50) NULL COMMENT 'iOS, Android, Windows, etc.',
    browser VARCHAR(50) NULL,
    
    -- Security
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    is_trusted BOOLEAN DEFAULT FALSE COMMENT 'Remember device',
    is_biometric_enabled BOOLEAN DEFAULT FALSE,
    
    -- Tracking
    last_used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    login_count INT DEFAULT 1,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_device_user (user_id, device_id),
    INDEX idx_user_devices (user_id),
    INDEX idx_device_id (device_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 4. WALLETS TABLE
-- =====================================================
CREATE TABLE wallets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    
    -- Balance Types
    main_balance DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Total balance including active investments',
    withdrawable_balance DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Available for withdrawal',
    investment_balance DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Currently invested amount',
    
    -- Totals
    total_deposited DECIMAL(15, 2) DEFAULT 0.00,
    total_withdrawn DECIMAL(15, 2) DEFAULT 0.00,
    total_earned DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Total earnings received',
    total_invested DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Total amount ever invested',
    
    -- Security
    wallet_pin_hash VARCHAR(255) NULL COMMENT 'Separate wallet PIN if needed',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_wallet (user_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 5. AIRTIME PACKAGES TABLE
-- =====================================================
CREATE TABLE packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT 'e.g., Starter Airtime',
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT NULL,
    
    -- Package Details (RWF Currency)
    purchase_price DECIMAL(15, 2) NOT NULL,
    daily_return DECIMAL(15, 2) NOT NULL COMMENT 'Daily earnings in RWF',
    total_return DECIMAL(15, 2) NOT NULL COMMENT 'Total return after duration',
    duration_days INT NOT NULL DEFAULT 360,
    
    -- Display Settings
    display_order INT DEFAULT 0,
    is_popular BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    color_code VARCHAR(7) DEFAULT '#2563eb' COMMENT 'Hex color for UI',
    icon VARCHAR(50) DEFAULT 'phone',
    
    -- ROI Percentage (for admin reference only)
    roi_percentage DECIMAL(5, 2) NULL COMMENT 'Calculated ROI % (hidden from users)',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 6. USER PACKAGES TABLE (Active Investments)
-- =====================================================
CREATE TABLE user_packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    package_id BIGINT UNSIGNED NOT NULL,
    
    -- Investment Details
    purchase_amount DECIMAL(15, 2) NOT NULL,
    daily_return DECIMAL(15, 2) NOT NULL COMMENT 'Daily earning amount',
    total_expected_return DECIMAL(15, 2) NOT NULL COMMENT 'Total return after duration',
    total_earned DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Amount earned so far',
    
    -- Duration Tracking
    duration_days INT NOT NULL DEFAULT 360,
    days_completed INT DEFAULT 0,
    days_remaining INT DEFAULT 360,
    
    -- Status
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    
    -- Timestamps
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_earning_at TIMESTAMP NULL,
    ends_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_packages (user_id),
    INDEX idx_status (status),
    INDEX idx_active_packages (user_id, status),
    INDEX idx_earning_schedule (status, last_earning_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 7. TRANSACTIONS TABLE (Deposits & Withdrawals)
-- =====================================================
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    
    -- Transaction Details
    transaction_type ENUM('deposit', 'withdrawal', 'investment', 'earning', 'referral_bonus', 'penalty') NOT NULL,
    reference_number VARCHAR(50) UNIQUE NOT NULL,
    external_reference VARCHAR(100) NULL COMMENT 'Payment gateway reference',
    
    -- Amount Details
    amount DECIMAL(15, 2) NOT NULL,
    fee DECIMAL(15, 2) DEFAULT 0.00,
    net_amount DECIMAL(15, 2) NOT NULL COMMENT 'Amount after fees',
    
    -- Payment Method
    payment_method ENUM('mobile_money', 'bank_transfer', 'wallet', 'system') DEFAULT 'mobile_money',
    payment_provider VARCHAR(50) NULL COMMENT 'MTN, Airtel, Bank name, etc.',
    payment_account VARCHAR(100) NULL COMMENT 'Phone number or account number',
    
    -- Status
    status ENUM('pending', 'processing', 'approved', 'rejected', 'cancelled', 'failed') DEFAULT 'pending',
    rejection_reason VARCHAR(255) NULL,
    admin_notes TEXT NULL,
    
    -- For Investments
    user_package_id BIGINT UNSIGNED NULL,
    
    -- Timestamps
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_transactions (user_id),
    INDEX idx_type_status (transaction_type, status),
    INDEX idx_reference (reference_number),
    INDEX idx_date (created_at),
    INDEX idx_pending_withdrawals (transaction_type, status, created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_package_id) REFERENCES user_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 8. EARNINGS TABLE (Daily Profit Distribution)
-- =====================================================
CREATE TABLE earnings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    user_package_id BIGINT UNSIGNED NOT NULL,
    transaction_id BIGINT UNSIGNED NULL,
    
    -- Earning Details
    amount DECIMAL(15, 2) NOT NULL,
    earning_type ENUM('daily_return', 'completion_bonus', 'referral_commission') DEFAULT 'daily_return',
    
    -- Day Tracking
    earning_date DATE NOT NULL,
    day_number INT NOT NULL COMMENT 'Day X of 360',
    
    -- Status
    status ENUM('credited', 'pending', 'failed') DEFAULT 'credited',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_daily_earning (user_package_id, earning_date),
    INDEX idx_user_earnings (user_id),
    INDEX idx_date (earning_date),
    INDEX idx_package_earnings (user_package_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_package_id) REFERENCES user_packages(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 9. ADMIN USERS TABLE
-- =====================================================
CREATE TABLE admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NULL,
    profile_image VARCHAR(255) NULL,
    
    -- Role & Permissions
    role ENUM('super_admin', 'admin', 'manager', 'support', 'viewer') DEFAULT 'admin',
    permissions JSON NULL COMMENT 'Custom permissions array',
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    
    -- Security
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45) NULL,
    login_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255) NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 10. SYSTEM SETTINGS TABLE
-- =====================================================
CREATE TABLE system_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    setting_type ENUM('string', 'integer', 'boolean', 'json', 'decimal') DEFAULT 'string',
    description VARCHAR(255) NULL,
    is_public BOOLEAN DEFAULT FALSE COMMENT 'Visible to frontend',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_key (setting_key),
    INDEX idx_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 11. NOTIFICATIONS TABLE
-- =====================================================
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL COMMENT 'NULL for broadcast notifications',
    
    -- Notification Details
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('earning', 'deposit', 'withdrawal', 'investment', 'system', 'promo', 'alert') DEFAULT 'system',
    
    -- Targeting
    target_type ENUM('user', 'all', 'group') DEFAULT 'user',
    target_group VARCHAR(50) NULL COMMENT 'active_users, investors, etc.',
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    
    -- Delivery
    send_sms BOOLEAN DEFAULT FALSE,
    sms_sent BOOLEAN DEFAULT FALSE,
    send_email BOOLEAN DEFAULT FALSE,
    email_sent BOOLEAN DEFAULT FALSE,
    send_push BOOLEAN DEFAULT FALSE,
    push_sent BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_notifications (user_id),
    INDEX idx_unread (user_id, is_read),
    INDEX idx_type (type),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 12. SMS LOGS TABLE
-- =====================================================
CREATE TABLE sms_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    message_type ENUM('otp', 'notification', 'alert', 'promo') DEFAULT 'notification',
    
    -- Provider Details
    provider VARCHAR(50) NULL COMMENT 'Twilio, Africa\'s Talking, etc.',
    provider_message_id VARCHAR(100) NULL,
    
    -- Status
    status ENUM('queued', 'sent', 'delivered', 'failed') DEFAULT 'queued',
    error_message TEXT NULL,
    cost DECIMAL(10, 4) DEFAULT 0.0000,
    
    sent_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_phone (phone_number),
    INDEX idx_status (status),
    INDEX idx_date (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 13. ACTIVITY LOGS TABLE (Audit Trail)
-- =====================================================
CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    admin_id BIGINT UNSIGNED NULL,
    
    -- Activity Details
    activity_type VARCHAR(50) NOT NULL COMMENT 'login, logout, deposit, withdrawal, investment, etc.',
    description TEXT NOT NULL,
    
    -- Context
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    device_id VARCHAR(255) NULL,
    
    -- Additional Data
    metadata JSON NULL COMMENT 'Additional context data',
    
    -- Affected Resources
    resource_type VARCHAR(50) NULL COMMENT 'user, transaction, package, etc.',
    resource_id BIGINT UNSIGNED NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_activity (user_id),
    INDEX idx_admin_activity (admin_id),
    INDEX idx_type (activity_type),
    INDEX idx_date (created_at),
    INDEX idx_resource (resource_type, resource_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 14. REFERRAL SETTINGS TABLE
-- =====================================================
CREATE TABLE referral_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referral_bonus_type ENUM('fixed', 'percentage') DEFAULT 'fixed',
    referral_bonus_amount DECIMAL(10, 2) DEFAULT 0.00,
    min_withdrawal_referral_earnings DECIMAL(10, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 15. WITHDRAWAL SETTINGS TABLE
-- =====================================================
CREATE TABLE withdrawal_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    minimum_withdrawal DECIMAL(10, 2) DEFAULT 1000.00,
    maximum_withdrawal DECIMAL(15, 2) DEFAULT 1000000.00,
    withdrawal_fee_type ENUM('fixed', 'percentage') DEFAULT 'fixed',
    withdrawal_fee_amount DECIMAL(10, 2) DEFAULT 0.00,
    processing_time_hours INT DEFAULT 24,
    auto_approve_enabled BOOLEAN DEFAULT FALSE,
    auto_approve_max_amount DECIMAL(15, 2) DEFAULT 0.00,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 16. FAQ TABLE
-- =====================================================
CREATE TABLE faqs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer TEXT NOT NULL,
    category VARCHAR(50) DEFAULT 'general',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_active (is_active),
    INDEX idx_category (category),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 17. CONTACT MESSAGES TABLE
-- =====================================================
CREATE TABLE contact_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NULL,
    phone VARCHAR(20) NULL,
    subject VARCHAR(150) NULL,
    message TEXT NOT NULL,
    
    status ENUM('unread', 'read', 'replied', 'closed') DEFAULT 'unread',
    admin_notes TEXT NULL,
    replied_at TIMESTAMP NULL,
    replied_by BIGINT UNSIGNED NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_status (status),
    INDEX idx_date (created_at),
    
    FOREIGN KEY (replied_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 18. PAGES TABLE (CMS for About, Terms, etc.)
-- =====================================================
CREATE TABLE pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    content LONGTEXT NOT NULL,
    meta_description VARCHAR(255) NULL,
    meta_keywords VARCHAR(255) NULL,
    
    is_active BOOLEAN DEFAULT TRUE,
    show_in_menu BOOLEAN DEFAULT FALSE,
    menu_order INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_slug (slug),
    INDEX idx_active (is_active),
    INDEX idx_menu (show_in_menu, menu_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 19. AIRTIME OPERATIONS TABLE (For authenticity)
-- =====================================================
CREATE TABLE airtime_operations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    operation_date DATE NOT NULL,
    telecom_provider VARCHAR(50) NOT NULL COMMENT 'MTN, Airtel, etc.',
    
    -- Volume Stats
    airtime_purchased DECIMAL(18, 2) DEFAULT 0.00 COMMENT 'Total airtime purchased (RWF)',
    airtime_distributed DECIMAL(18, 2) DEFAULT 0.00 COMMENT 'Total airtime distributed',
    transactions_count INT DEFAULT 0,
    
    -- Revenue
    revenue_generated DECIMAL(15, 2) DEFAULT 0.00,
    margin_earned DECIMAL(15, 2) DEFAULT 0.00,
    
    -- Agent Network
    active_agents INT DEFAULT 0,
    active_retailers INT DEFAULT 0,
    
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_operation (operation_date, telecom_provider),
    INDEX idx_date (operation_date),
    INDEX idx_provider (telecom_provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 20. CRON JOBS LOG TABLE
-- =====================================================
CREATE TABLE cron_job_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_name VARCHAR(100) NOT NULL,
    started_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP NULL,
    
    status ENUM('running', 'completed', 'failed') DEFAULT 'running',
    records_processed INT DEFAULT 0,
    error_message TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_job_name (job_name),
    INDEX idx_status (status),
    INDEX idx_date (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- INSERT DEFAULT DATA
-- =====================================================

-- Insert Default Packages
INSERT INTO packages (name, slug, description, purchase_price, daily_return, total_return, duration_days, display_order, is_popular, color_code, roi_percentage) VALUES
('Starter Airtime', 'starter-airtime', 'Entry-level airtime distribution package perfect for beginners looking to start their telecom investment journey.', 60000.00, 350.00, 126000.00, 360, 1, FALSE, '#22c55e', 110.00),
('Basic Airtime', 'basic-airtime', 'A solid foundation for airtime distribution with steady daily returns and reliable performance.', 150000.00, 900.00, 324000.00, 360, 2, FALSE, '#3b82f6', 116.00),
('Standard Airtime', 'standard-airtime', 'Our most popular package offering excellent balance between investment and returns.', 210000.00, 1350.00, 486000.00, 360, 3, TRUE, '#8b5cf6', 131.43),
('Advanced Airtime', 'advanced-airtime', 'Premium airtime distribution package for serious investors seeking higher daily yields.', 300000.00, 2000.00, 720000.00, 360, 4, FALSE, '#f59e0b', 140.00),
('Premium Airtime', 'premium-airtime', 'High-yield airtime distribution package with maximum daily returns for committed investors.', 530000.00, 3700.00, 1332000.00, 360, 5, FALSE, '#ef4444', 151.32),
('Enterprise Airtime', 'enterprise-airtime', 'Our flagship enterprise-level package for maximum airtime distribution returns.', 900000.00, 6600.00, 2376000.00, 360, 6, FALSE, '#ec4899', 164.00);

-- Insert Default System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
-- Platform Information
('platform_name', 'AirTime Distribution Hub', 'string', 'Platform display name', TRUE),
('platform_tagline', 'Powering Telecom Airtime Distribution Across Africa', 'string', 'Platform tagline', TRUE),
('platform_email', 'support@airtimehub.rw', 'string', 'Support email address', TRUE),
('platform_phone', '+250788000000', 'string', 'Support phone number', TRUE),
('platform_address', 'Kigali, Rwanda', 'string', 'Platform address', TRUE),
('currency', 'RWF', 'string', 'Default currency', TRUE),
('currency_symbol', 'RWF', 'string', 'Currency symbol', TRUE),

-- OTP Settings
('otp_length', '6', 'integer', 'Number of digits in OTP', FALSE),
('otp_expiry_minutes', '10', 'integer', 'OTP expiry time in minutes', FALSE),
('otp_max_attempts', '3', 'integer', 'Maximum OTP verification attempts', FALSE),

-- Registration Settings
('registration_enabled', 'true', 'boolean', 'Allow new user registration', TRUE),
('phone_verification_required', 'true', 'boolean', 'Require phone verification', TRUE),
('min_phone_length', '10', 'integer', 'Minimum phone number length', FALSE),

-- Investment Settings
('max_active_packages', '5', 'integer', 'Maximum active packages per user', TRUE),
('earning_time', '00:00', 'string', 'Daily earning processing time (HH:MM)', FALSE),
('auto_complete_packages', 'true', 'boolean', 'Auto-complete packages after duration', FALSE),

-- Security Settings
('login_max_attempts', '5', 'integer', 'Maximum login attempts before lockout', FALSE),
('lockout_duration_minutes', '30', 'integer', 'Account lockout duration in minutes', FALSE),
('session_timeout_minutes', '60', 'integer', 'Session timeout duration', FALSE),
('max_devices_per_user', '3', 'integer', 'Maximum devices per user account', FALSE),

-- SMS Settings
('sms_provider', 'africastalking', 'string', 'SMS gateway provider', FALSE),
('sms_sender_id', 'AirTimeHub', 'string', 'SMS sender ID', FALSE),
('sms_enabled', 'true', 'boolean', 'Enable SMS notifications', FALSE),

-- Email Settings
('email_enabled', 'false', 'boolean', 'Enable email notifications', FALSE),

-- Maintenance
('maintenance_mode', 'false', 'boolean', 'Enable maintenance mode', FALSE),
('maintenance_message', 'System under maintenance. Please try again later.', 'string', 'Maintenance mode message', TRUE);

-- Insert Default Withdrawal Settings
INSERT INTO withdrawal_settings (minimum_withdrawal, maximum_withdrawal, withdrawal_fee_type, withdrawal_fee_amount, processing_time_hours, auto_approve_enabled, auto_approve_max_amount) VALUES
(1000.00, 5000000.00, 'fixed', 0.00, 24, FALSE, 0.00);

-- Insert Default Referral Settings
INSERT INTO referral_settings (referral_bonus_type, referral_bonus_amount, min_withdrawal_referral_earnings, is_active) VALUES
('fixed', 1000.00, 5000.00, TRUE);

-- Insert Default Admin User (Password: Admin@123456)
INSERT INTO admin_users (username, email, password_hash, full_name, role, is_active, email_verified) VALUES
('admin', 'admin@airtimehub.rw', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'super_admin', TRUE, TRUE);

-- Insert Default FAQs
INSERT INTO faqs (question, answer, category, display_order, is_active) VALUES
('How does airtime distribution work?', 'Our platform purchases large volumes of telecom airtime from providers like MTN and Airtel at discounted rates. We then distribute this airtime through our network of agents and retailers, earning margins on each transaction. Members participate by funding airtime packages and receive daily operational returns.', 'general', 1, TRUE),
('What payment methods are accepted?', 'We accept Mobile Money (MTN Mobile Money, Airtel Money) and bank transfers. Deposits are credited to your wallet within minutes after confirmation.', 'payments', 2, TRUE),
('How long do packages last?', 'All airtime distribution packages run for 360 days. During this period, you receive daily returns credited to your withdrawable balance.', 'packages', 3, TRUE),
('How do I withdraw my earnings?', 'Navigate to the Withdraw section in your dashboard, enter the amount you wish to withdraw, provide your payment details, and confirm with your PIN. Withdrawals are processed within 24 hours.', 'withdrawals', 4, TRUE),
('Is my investment secure?', 'Yes, we use bank-grade encryption and security measures to protect your account and investments. All transactions require PIN verification, and we monitor for suspicious activity.', 'security', 5, TRUE),
('Can I have multiple packages?', 'Yes, you can purchase up to 5 active packages simultaneously. Each package operates independently with its own daily returns.', 'packages', 6, TRUE),
('What happens after 360 days?', 'Upon completion, your package is marked as complete and all returns are credited to your withdrawable balance. You can then withdraw or reinvest in new packages.', 'packages', 7, TRUE);

-- Insert Default Pages (CMS)
INSERT INTO pages (title, slug, content, meta_description, is_active, show_in_menu, menu_order) VALUES
('About Us', 'about-us', '<h2>Powering Telecom Airtime Distribution Across Africa</h2><p>AirTime Distribution Hub is a leading digital airtime distribution company specializing in large-scale telecom operations. We purchase airtime in bulk from major telecom providers and distribute through our extensive network of agents and retailers across the region.</p><h3>Our Mission</h3><p>To democratize access to telecom investment opportunities while powering digital connectivity across Africa.</p><h3>How We Generate Returns</h3><ul><li>Airtime resale margins from bulk purchases</li><li>Mobile payment processing fees</li><li>Digital recharge services</li><li>Agent network commissions</li></ul>', 'Learn about AirTime Distribution Hub - your trusted partner in telecom airtime distribution investments.', TRUE, TRUE, 1),
('How It Works', 'how-it-works', '<h2>How Airtime Distribution Works</h2><h3>Step 1: Register</h3><p>Create your account with your phone number and verify with OTP. Set your secure 5-digit PIN for all transactions.</p><h3>Step 2: Fund Your Wallet</h3><p>Deposit funds using Mobile Money or bank transfer. Your balance is available immediately after confirmation.</p><h3>Step 3: Choose a Package</h3><p>Select from our range of airtime distribution packages. Each package shows the exact purchase price, daily return, and total return.</p><h3>Step 4: Earn Daily</h3><p>Receive daily operational returns credited to your withdrawable balance automatically.</p><h3>Step 5: Withdraw Anytime</h3><p>Withdraw your earnings to Mobile Money or bank account. Simple, fast, and secure.</p>', 'Step by step guide on how airtime distribution investment works.', TRUE, TRUE, 2),
('Terms of Service', 'terms-of-service', '<h2>Terms of Service</h2><p>By using AirTime Distribution Hub, you agree to these terms and conditions.</p><h3>Account Security</h3><p>You are responsible for maintaining the confidentiality of your PIN and account credentials. Report any unauthorized access immediately.</p><h3>Investment Terms</h3><p>All packages run for 360 days. Returns are calculated based on package purchase price and credited daily. Returns are not guaranteed and may vary based on operational performance.</p><h3>Withdrawal Policy</h3><p>Withdrawals are processed within 24-48 hours. Minimum withdrawal amount is 1,000 RWF. We reserve the right to verify identity before processing large withdrawals.</p>', 'Terms and conditions for using AirTime Distribution Hub platform.', TRUE, FALSE, 3),
('Privacy Policy', 'privacy-policy', '<h2>Privacy Policy</h2><p>We are committed to protecting your personal information and your right to privacy.</p><h3>Information We Collect</h3><p>We collect your name, phone number, email (optional), and transaction history. This information is used to provide and improve our services.</p><h3>How We Use Your Information</h3><p>Your information is used for account management, transaction processing, customer support, and sending important notifications about your account.</p><h3>Data Security</h3><p>We implement industry-standard security measures including encryption, secure servers, and regular security audits to protect your data.</p>', 'Privacy policy detailing how we collect, use, and protect your information.', TRUE, FALSE, 4);

-- Insert Sample Airtime Operations (For authenticity display)
INSERT INTO airtime_operations (operation_date, telecom_provider, airtime_purchased, airtime_distributed, transactions_count, revenue_generated, margin_earned, active_agents, active_retailers) VALUES
(CURDATE() - INTERVAL 7 DAY, 'MTN Rwanda', 15000000.00, 14850000.00, 2543, 165000.00, 148500.00, 125, 450),
(CURDATE() - INTERVAL 7 DAY, 'Airtel Rwanda', 8500000.00, 8415000.00, 1876, 93500.00, 84150.00, 85, 320),
(CURDATE() - INTERVAL 6 DAY, 'MTN Rwanda', 17500000.00, 17325000.00, 2876, 192500.00, 173250.00, 128, 465),
(CURDATE() - INTERVAL 6 DAY, 'Airtel Rwanda', 9200000.00, 9108000.00, 1934, 101200.00, 91080.00, 87, 328),
(CURDATE() - INTERVAL 5 DAY, 'MTN Rwanda', 16200000.00, 16038000.00, 2654, 178200.00, 160380.00, 130, 478),
(CURDATE() - INTERVAL 5 DAY, 'Airtel Rwanda', 8900000.00, 8811000.00, 1856, 97900.00, 88110.00, 89, 335);

-- =====================================================
-- CREATE VIEWS FOR DASHBOARD STATS
-- =====================================================

-- User Dashboard Stats View
CREATE OR REPLACE VIEW v_user_dashboard_stats AS
SELECT 
    u.id AS user_id,
    u.full_name,
    u.phone_number,
    w.main_balance,
    w.withdrawable_balance,
    w.investment_balance,
    w.total_earned,
    w.total_invested,
    w.total_deposited,
    w.total_withdrawn,
    COUNT(DISTINCT up.id) AS active_packages_count,
    COALESCE(SUM(CASE WHEN up.status = 'active' THEN up.total_expected_return - up.total_earned ELSE 0 END), 0) AS pending_earnings
FROM users u
LEFT JOIN wallets w ON u.id = w.user_id
LEFT JOIN user_packages up ON u.id = up.user_id AND up.status = 'active'
GROUP BY u.id;

-- Active Packages View (For Daily Earnings Cron)
CREATE OR REPLACE VIEW v_active_packages_for_earning AS
SELECT 
    up.id,
    up.user_id,
    up.package_id,
    up.daily_return,
    up.days_completed,
    up.days_remaining,
    up.total_earned,
    up.total_expected_return,
    up.last_earning_at,
    up.status,
    p.name AS package_name
FROM user_packages up
JOIN packages p ON up.package_id = p.id
WHERE up.status = 'active'
  AND up.days_remaining > 0
  AND (up.last_earning_at IS NULL OR DATE(up.last_earning_at) < CURDATE());

-- Platform Stats View (Admin Dashboard)
CREATE OR REPLACE VIEW v_platform_stats AS
SELECT 
    (SELECT COUNT(*) FROM users WHERE status = 'active') AS total_active_users,
    (SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE()) AS new_users_today,
    (SELECT COALESCE(SUM(main_balance), 0) FROM wallets) AS total_wallet_balance,
    (SELECT COALESCE(SUM(investment_balance), 0) FROM wallets) AS total_invested,
    (SELECT COUNT(*) FROM user_packages WHERE status = 'active') AS active_packages_count,
    (SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE transaction_type = 'deposit' AND status = 'approved' AND DATE(created_at) = CURDATE()) AS deposits_today,
    (SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE transaction_type = 'withdrawal' AND status = 'pending') AS pending_withdrawals,
    (SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE transaction_type = 'earning' AND status = 'credited' AND DATE(created_at) = CURDATE()) AS earnings_distributed_today;

-- Transaction History View
CREATE OR REPLACE VIEW v_transaction_history AS
SELECT 
    t.id,
    t.user_id,
    u.full_name,
    u.phone_number,
    t.transaction_type,
    t.reference_number,
    t.amount,
    t.fee,
    t.net_amount,
    t.payment_method,
    t.payment_provider,
    t.status,
    t.created_at,
    t.completed_at
FROM transactions t
JOIN users u ON t.user_id = u.id
ORDER BY t.created_at DESC;

-- =====================================================
-- CREATE STORED PROCEDURES
-- =====================================================

DELIMITER //

-- Procedure: Process Daily Earnings
CREATE PROCEDURE sp_process_daily_earnings()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_package_id BIGINT;
    DECLARE v_user_id BIGINT;
    DECLARE v_daily_return DECIMAL(15,2);
    DECLARE v_days_remaining INT;
    DECLARE v_earning_id BIGINT;
    DECLARE v_processed_count INT DEFAULT 0;
    
    DECLARE cur_packages CURSOR FOR
        SELECT id, user_id, daily_return, days_remaining
        FROM user_packages
        WHERE status = 'active'
          AND days_remaining > 0
          AND (last_earning_at IS NULL OR DATE(last_earning_at) < CURDATE());
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur_packages;
    
    read_loop: LOOP
        FETCH cur_packages INTO v_package_id, v_user_id, v_daily_return, v_days_remaining;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Create earning record
        INSERT INTO earnings (user_id, user_package_id, amount, earning_type, earning_date, day_number, status)
        SELECT v_user_id, v_package_id, v_daily_return, 'daily_return', CURDATE(), 
               (duration_days - v_days_remaining + 1), 'credited'
        FROM user_packages WHERE id = v_package_id;
        
        SET v_earning_id = LAST_INSERT_ID();
        
        -- Create transaction
        INSERT INTO transactions (user_id, transaction_type, reference_number, amount, net_amount, payment_method, status, user_package_id)
        VALUES (v_user_id, 'earning', CONCAT('ERN', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(v_earning_id, 8, '0')), 
                v_daily_return, v_daily_return, 'system', 'approved', v_package_id);
        
        -- Update user package
        UPDATE user_packages 
        SET days_completed = days_completed + 1,
            days_remaining = days_remaining - 1,
            total_earned = total_earned + v_daily_return,
            last_earning_at = NOW(),
            status = CASE WHEN days_remaining - 1 = 0 THEN 'completed' ELSE 'active' END,
            completed_at = CASE WHEN days_remaining - 1 = 0 THEN NOW() ELSE completed_at END
        WHERE id = v_package_id;
        
        -- Update wallet
        UPDATE wallets 
        SET withdrawable_balance = withdrawable_balance + v_daily_return,
            total_earned = total_earned + v_daily_return
        WHERE user_id = v_user_id;
        
        SET v_processed_count = v_processed_count + 1;
    END LOOP;
    
    CLOSE cur_packages;
    
    SELECT v_processed_count AS processed_packages;
END //

-- Procedure: Create User Wallet
CREATE PROCEDURE sp_create_user_wallet(IN p_user_id BIGINT)
BEGIN
    INSERT INTO wallets (user_id, main_balance, withdrawable_balance, investment_balance, total_deposited, total_withdrawn, total_earned, total_invested)
    VALUES (p_user_id, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00)
    ON DUPLICATE KEY UPDATE user_id = user_id;
END //

-- Procedure: Purchase Package
CREATE PROCEDURE sp_purchase_package(
    IN p_user_id BIGINT,
    IN p_package_id BIGINT,
    IN p_pin VARCHAR(5)
)
BEGIN
    DECLARE v_package_price DECIMAL(15,2);
    DECLARE v_daily_return DECIMAL(15,2);
    DECLARE v_total_return DECIMAL(15,2);
    DECLARE v_duration INT;
    DECLARE v_wallet_balance DECIMAL(15,2);
    DECLARE v_user_pin VARCHAR(255);
    DECLARE v_transaction_ref VARCHAR(50);
    DECLARE v_user_package_id BIGINT;
    
    -- Get package details
    SELECT purchase_price, daily_return, total_return, duration_days
    INTO v_package_price, v_daily_return, v_total_return, v_duration
    FROM packages WHERE id = p_package_id AND is_active = TRUE;
    
    IF v_package_price IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Package not found or inactive';
    END IF;
    
    -- Check user balance
    SELECT withdrawable_balance INTO v_wallet_balance
    FROM wallets WHERE user_id = p_user_id;
    
    IF v_wallet_balance < v_package_price THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    
    -- Create user package
    INSERT INTO user_packages (user_id, package_id, purchase_amount, daily_return, total_expected_return, duration_days, days_remaining, ends_at)
    VALUES (p_user_id, p_package_id, v_package_price, v_daily_return, v_total_return, v_duration, v_duration, DATE_ADD(NOW(), INTERVAL v_duration DAY));
    
    SET v_user_package_id = LAST_INSERT_ID();
    
    -- Generate transaction reference
    SET v_transaction_ref = CONCAT('INV', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(v_user_package_id, 8, '0'));
    
    -- Create investment transaction
    INSERT INTO transactions (user_id, transaction_type, reference_number, amount, net_amount, payment_method, status, user_package_id)
    VALUES (p_user_id, 'investment', v_transaction_ref, v_package_price, v_package_price, 'wallet', 'approved', v_user_package_id);
    
    -- Update wallet
    UPDATE wallets 
    SET withdrawable_balance = withdrawable_balance - v_package_price,
        investment_balance = investment_balance + v_package_price,
        total_invested = total_invested + v_package_price
    WHERE user_id = p_user_id;
    
    SELECT v_user_package_id AS package_id, v_transaction_ref AS reference;
END //

DELIMITER ;

-- =====================================================
-- CREATE TRIGGERS
-- =====================================================

DELIMITER //

-- Trigger: Generate referral code on user creation
CREATE TRIGGER tr_generate_referral_code
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.referral_code IS NULL THEN
        SET NEW.referral_code = CONCAT('AT', UPPER(SUBSTRING(MD5(RAND()), 1, 8)));
    END IF;
END //

-- Trigger: Create wallet on user creation
CREATE TRIGGER tr_create_wallet_on_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO wallets (user_id) VALUES (NEW.id);
END //

-- Trigger: Log activity on user login
CREATE TRIGGER tr_log_login
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.last_login_at <> OLD.last_login_at OR (OLD.last_login_at IS NULL AND NEW.last_login_at IS NOT NULL) THEN
        INSERT INTO activity_logs (user_id, activity_type, description, ip_address)
        VALUES (NEW.id, 'login', 'User logged in successfully', NEW.last_login_ip);
    END IF;
END //

DELIMITER ;

-- =====================================================
-- CREATE INDEXES FOR PERFORMANCE
-- =====================================================

-- Additional indexes for common queries
CREATE INDEX idx_transactions_user_type_date ON transactions(user_id, transaction_type, created_at);
CREATE INDEX idx_user_packages_user_status ON user_packages(user_id, status);
CREATE INDEX idx_earnings_user_date ON earnings(user_id, earning_date);
CREATE INDEX idx_users_status_created ON users(status, created_at);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read);

-- =====================================================
-- END OF SQL FILE
-- =====================================================