-- =====================================================
-- TELEGRAM INVESTMENT BOT - DATABASE SCHEMA
-- =====================================================
-- Database: telegram_investment_bot
-- Engine: InnoDB
-- Charset: utf8mb4
-- Collation: utf8mb4_unicode_ci
-- =====================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Create database
CREATE DATABASE IF NOT EXISTS `telegram_investment_bot` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `telegram_investment_bot`;

-- =====================================================
-- TABLE: languages
-- Purpose: Supported languages for multi-language support
-- =====================================================
CREATE TABLE `languages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(5) NOT NULL COMMENT 'Language code (en, es, fr, etc.)',
  `name` VARCHAR(50) NOT NULL COMMENT 'Language name in English',
  `native_name` VARCHAR(50) NOT NULL COMMENT 'Language name in native script',
  `flag_emoji` VARCHAR(10) DEFAULT NULL COMMENT 'Flag emoji for display',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: translations
-- Purpose: Translation strings for all languages
-- =====================================================
CREATE TABLE `translations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `language_code` VARCHAR(5) NOT NULL,
  `trans_key` VARCHAR(100) NOT NULL COMMENT 'Translation key identifier',
  `trans_value` TEXT NOT NULL COMMENT 'Translated text',
  `category` VARCHAR(50) DEFAULT NULL COMMENT 'Grouping category (menu, error, etc.)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lang_key` (`language_code`, `trans_key`),
  KEY `idx_language` (`language_code`),
  KEY `idx_category` (`category`),
  CONSTRAINT `fk_translations_language` FOREIGN KEY (`language_code`) REFERENCES `languages` (`code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: users
-- Purpose: Core user registry
-- =====================================================
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL COMMENT 'Telegram user ID',
  `username` VARCHAR(255) DEFAULT NULL COMMENT 'Telegram username',
  `first_name` VARCHAR(255) DEFAULT NULL,
  `last_name` VARCHAR(255) DEFAULT NULL,
  `language_code` VARCHAR(5) NOT NULL DEFAULT 'en',
  `referrer_id` INT UNSIGNED DEFAULT NULL COMMENT 'User who referred this user',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Account status',
  `is_frozen` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Admin can freeze account',
  `registration_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_telegram_id` (`telegram_id`),
  KEY `idx_username` (`username`),
  KEY `idx_referrer` (`referrer_id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_language` (`language_code`),
  CONSTRAINT `fk_users_language` FOREIGN KEY (`language_code`) REFERENCES `languages` (`code`),
  CONSTRAINT `fk_users_referrer` FOREIGN KEY (`referrer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: user_states
-- Purpose: Track conversation state for each user
-- =====================================================
CREATE TABLE `user_states` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `state` VARCHAR(50) NOT NULL DEFAULT 'idle',
  `context` TEXT DEFAULT NULL COMMENT 'JSON-encoded context data',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user` (`user_id`),
  KEY `idx_state` (`state`),
  CONSTRAINT `fk_states_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: wallets
-- Purpose: Multi-currency wallet balances
-- =====================================================
CREATE TABLE `wallets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `currency` VARCHAR(10) NOT NULL COMMENT 'BTC, ETH, LTC, USDT, TRX, DASH',
  `balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `total_deposited` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `total_withdrawn` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `total_profit` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `total_referral` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_currency` (`user_id`, `currency`),
  KEY `idx_currency` (`currency`),
  KEY `idx_balance` (`balance`),
  CONSTRAINT `fk_wallets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: investment_plans
-- Purpose: Admin-configurable investment plans
-- =====================================================
CREATE TABLE `investment_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL COMMENT 'Capital, Gold, Diamond, Mega',
  `currency` VARCHAR(10) NOT NULL,
  `min_amount` DECIMAL(20, 8) NOT NULL,
  `max_amount` DECIMAL(20, 8) NOT NULL,
  `roi_percentage` DECIMAL(5, 2) NOT NULL COMMENT 'Return on investment %',
  `duration_days` INT NOT NULL COMMENT 'Investment duration in days',
  `description` TEXT DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_currency` (`currency`),
  KEY `idx_active` (`is_active`),
  KEY `idx_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: investments
-- Purpose: User investment records
-- =====================================================
CREATE TABLE `investments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `amount` DECIMAL(20, 8) NOT NULL,
  `roi_percentage` DECIMAL(5, 2) NOT NULL COMMENT 'Snapshot at investment time',
  `expected_profit` DECIMAL(20, 8) NOT NULL,
  `earned_profit` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `status` ENUM('active', 'completed', 'cancelled') NOT NULL DEFAULT 'active',
  `start_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` TIMESTAMP NULL DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_plan` (`plan_id`),
  KEY `idx_status` (`status`),
  KEY `idx_dates` (`start_date`, `end_date`),
  CONSTRAINT `fk_investments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_investments_plan` FOREIGN KEY (`plan_id`) REFERENCES `investment_plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: transactions
-- Purpose: Unified financial transaction ledger
-- =====================================================
CREATE TABLE `transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` ENUM('deposit', 'withdrawal', 'profit', 'referral', 'bonus', 'adjustment') NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `amount` DECIMAL(20, 8) NOT NULL,
  `balance_before` DECIMAL(20, 8) NOT NULL,
  `balance_after` DECIMAL(20, 8) NOT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL COMMENT 'investments, withdrawals, etc.',
  `reference_id` INT UNSIGNED DEFAULT NULL COMMENT 'Related record ID',
  `description` TEXT DEFAULT NULL,
  `tx_hash` VARCHAR(255) DEFAULT NULL COMMENT 'Blockchain transaction hash',
  `status` ENUM('pending', 'completed', 'failed') NOT NULL DEFAULT 'completed',
  `created_by` INT UNSIGNED DEFAULT NULL COMMENT 'Admin user ID if manual',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_currency` (`currency`),
  KEY `idx_status` (`status`),
  KEY `idx_reference` (`reference_type`, `reference_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: deposit_addresses
-- Purpose: System-owned deposit addresses pool
-- =====================================================
CREATE TABLE `deposit_addresses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `currency` VARCHAR(10) NOT NULL,
  `address` VARCHAR(255) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `label` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_currency_address` (`currency`, `address`),
  KEY `idx_currency` (`currency`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: deposit_requests
-- Purpose: User deposit requests awaiting admin approval
-- =====================================================
CREATE TABLE `deposit_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `amount` DECIMAL(20, 8) NOT NULL,
  `deposit_address_id` INT UNSIGNED NOT NULL COMMENT 'Assigned address',
  `user_tx_hash` VARCHAR(255) DEFAULT NULL COMMENT 'User-provided tx hash',
  `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  `admin_note` TEXT DEFAULT NULL,
  `approved_by` INT UNSIGNED DEFAULT NULL COMMENT 'Admin user ID',
  `approved_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_currency` (`currency`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_deposit_requests_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deposit_requests_plan` FOREIGN KEY (`plan_id`) REFERENCES `investment_plans` (`id`),
  CONSTRAINT `fk_deposit_requests_address` FOREIGN KEY (`deposit_address_id`) REFERENCES `deposit_addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: withdrawal_addresses
-- Purpose: User-registered withdrawal addresses
-- =====================================================
CREATE TABLE `withdrawal_addresses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `address` VARCHAR(255) NOT NULL,
  `label` VARCHAR(100) DEFAULT NULL,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_currency` (`user_id`, `currency`),
  KEY `idx_currency` (`currency`),
  CONSTRAINT `fk_withdrawal_addresses_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: withdrawal_requests
-- Purpose: User withdrawal requests
-- =====================================================
CREATE TABLE `withdrawal_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `amount` DECIMAL(20, 8) NOT NULL,
  `withdrawal_address_id` INT UNSIGNED NOT NULL,
  `fee` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `net_amount` DECIMAL(20, 8) NOT NULL COMMENT 'Amount after fee',
  `status` ENUM('pending', 'approved', 'rejected', 'paid') NOT NULL DEFAULT 'pending',
  `tx_hash` VARCHAR(255) DEFAULT NULL COMMENT 'Actual blockchain tx hash',
  `admin_note` TEXT DEFAULT NULL,
  `approved_by` INT UNSIGNED DEFAULT NULL,
  `approved_at` TIMESTAMP NULL DEFAULT NULL,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_currency` (`currency`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_withdrawal_requests_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_withdrawal_requests_address` FOREIGN KEY (`withdrawal_address_id`) REFERENCES `withdrawal_addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: referrals
-- Purpose: Referral relationship tracking
-- =====================================================
CREATE TABLE `referrals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `referrer_id` INT UNSIGNED NOT NULL COMMENT 'User who referred',
  `referred_id` INT UNSIGNED NOT NULL COMMENT 'User who was referred',
  `commission_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
  `total_earned` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_referred` (`referred_id`),
  KEY `idx_referrer` (`referrer_id`),
  CONSTRAINT `fk_referrals_referrer` FOREIGN KEY (`referrer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_referrals_referred` FOREIGN KEY (`referred_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: pages
-- Purpose: Static content pages (About, FAQ, etc.)
-- =====================================================
CREATE TABLE `pages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(50) NOT NULL COMMENT 'about, faq, terms',
  `language_code` VARCHAR(5) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_slug_lang` (`slug`, `language_code`),
  KEY `idx_slug` (`slug`),
  KEY `idx_language` (`language_code`),
  CONSTRAINT `fk_pages_language` FOREIGN KEY (`language_code`) REFERENCES `languages` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: admin_users
-- Purpose: Admin panel authentication
-- =====================================================
CREATE TABLE `admin_users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `role` ENUM('super_admin', 'admin', 'moderator') NOT NULL DEFAULT 'admin',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: admin_logs
-- Purpose: Audit trail for admin actions
-- =====================================================
CREATE TABLE `admin_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` INT UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL COMMENT 'approve_deposit, reject_withdrawal, etc.',
  `target_type` VARCHAR(50) DEFAULT NULL COMMENT 'user, deposit, withdrawal',
  `target_id` INT UNSIGNED DEFAULT NULL,
  `old_value` TEXT DEFAULT NULL COMMENT 'JSON of old data',
  `new_value` TEXT DEFAULT NULL COMMENT 'JSON of new data',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_action` (`action`),
  KEY `idx_target` (`target_type`, `target_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_admin_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLE: system_settings
-- Purpose: Application configuration
-- =====================================================
CREATE TABLE `system_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') NOT NULL DEFAULT 'string',
  `description` TEXT DEFAULT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INITIAL DATA INSERTION
-- =====================================================

-- Insert default languages
INSERT INTO `languages` (`code`, `name`, `native_name`, `flag_emoji`, `is_active`) VALUES
('en', 'English', 'English', '🇺🇸', 1),
('es', 'Spanish', 'Español', '🇪🇸', 1),
('fr', 'French', 'Français', '🇫🇷', 1),
('de', 'German', 'Deutsch', '🇩🇪', 1),
('ru', 'Russian', 'Русский', '🇷🇺', 1),
('ar', 'Arabic', 'العربية', '🇸🇦', 1);

-- Insert default admin (username: admin, password: admin123)
-- NOTE: Change this password immediately in production!
INSERT INTO `admin_users` (`username`, `password_hash`, `email`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', 'super_admin');

-- Insert system settings
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('bot_token', '', 'string', 'Telegram Bot API Token'),
('bot_username', '', 'string', 'Telegram Bot Username'),
('support_username', '@support', 'string', 'Support Telegram Username'),
('min_withdrawal_btc', '0.001', 'number', 'Minimum BTC withdrawal'),
('min_withdrawal_eth', '0.01', 'number', 'Minimum ETH withdrawal'),
('min_withdrawal_usdt', '10', 'number', 'Minimum USDT withdrawal'),
('withdrawal_fee_percentage', '2', 'number', 'Withdrawal fee percentage'),
('referral_commission', '5', 'number', 'Referral commission percentage'),
('enable_registration', 'true', 'boolean', 'Allow new user registration');

-- Insert sample deposit addresses (REPLACE WITH REAL ADDRESSES)
INSERT INTO `deposit_addresses` (`currency`, `address`, `label`) VALUES
('BTC', 'bc1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh', 'BTC Wallet 1'),
('ETH', '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb', 'ETH Wallet 1'),
('USDT', 'TYDzsYUEpvnYmQk4zGP9sWWcTEd2MiAtW6', 'USDT Wallet 1'),
('LTC', 'LTC1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh', 'LTC Wallet 1'),
('TRX', 'TYDzsYUEpvnYmQk4zGP9sWWcTEd2MiAtW6', 'TRX Wallet 1'),
('DASH', 'Xxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh', 'DASH Wallet 1');

-- Insert investment plans
INSERT INTO `investment_plans` (`name`, `currency`, `min_amount`, `max_amount`, `roi_percentage`, `duration_days`, `description`, `display_order`) VALUES
('Capital', 'BTC', 0.001, 0.01, 15.00, 7, 'Basic investment plan with 15% ROI in 7 days', 1),
('Gold', 'BTC', 0.01, 0.1, 25.00, 14, 'Gold plan with 25% ROI in 14 days', 2),
('Diamond', 'BTC', 0.1, 1.0, 40.00, 30, 'Diamond plan with 40% ROI in 30 days', 3),
('Mega', 'BTC', 1.0, 10.0, 75.00, 60, 'Mega plan with 75% ROI in 60 days', 4),
('Capital', 'ETH', 0.01, 0.1, 15.00, 7, 'Basic investment plan with 15% ROI in 7 days', 1),
('Gold', 'ETH', 0.1, 1.0, 25.00, 14, 'Gold plan with 25% ROI in 14 days', 2),
('Diamond', 'ETH', 1.0, 10.0, 40.00, 30, 'Diamond plan with 40% ROI in 30 days', 3),
('Mega', 'ETH', 10.0, 100.0, 75.00, 60, 'Mega plan with 75% ROI in 60 days', 4),
('Capital', 'USDT', 100, 1000, 15.00, 7, 'Basic investment plan with 15% ROI in 7 days', 1),
('Gold', 'USDT', 1000, 10000, 25.00, 14, 'Gold plan with 25% ROI in 14 days', 2),
('Diamond', 'USDT', 10000, 100000, 40.00, 30, 'Diamond plan with 40% ROI in 30 days', 3),
('Mega', 'USDT', 100000, 1000000, 75.00, 60, 'Mega plan with 75% ROI in 60 days', 4);

-- =====================================================
-- VIEWS FOR REPORTING
-- =====================================================

-- User statistics view
CREATE OR REPLACE VIEW `view_user_stats` AS
SELECT 
    u.id,
    u.telegram_id,
    u.username,
    u.first_name,
    u.language_code,
    u.is_active,
    u.registration_date,
    COUNT(DISTINCT w.id) as wallet_count,
    COUNT(DISTINCT i.id) as investment_count,
    COALESCE(SUM(CASE WHEN i.status = 'active' THEN i.amount ELSE 0 END), 0) as active_investments,
    COALESCE(SUM(w.balance), 0) as total_balance,
    (SELECT COUNT(*) FROM referrals WHERE referrer_id = u.id) as referral_count
FROM users u
LEFT JOIN wallets w ON u.id = w.user_id
LEFT JOIN investments i ON u.id = i.user_id
GROUP BY u.id;

-- Transaction summary view
CREATE OR REPLACE VIEW `view_transaction_summary` AS
SELECT 
    user_id,
    currency,
    type,
    COUNT(*) as transaction_count,
    SUM(amount) as total_amount,
    MAX(created_at) as last_transaction
FROM transactions
WHERE status = 'completed'
GROUP BY user_id, currency, type;

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

-- Additional composite indexes for common queries
CREATE INDEX idx_investments_user_status ON investments(user_id, status);
CREATE INDEX idx_transactions_user_type ON transactions(user_id, type, created_at);
CREATE INDEX idx_withdrawal_requests_status_created ON withdrawal_requests(status, created_at);
CREATE INDEX idx_deposit_requests_status_created ON deposit_requests(status, created_at);

-- =====================================================
-- STORED PROCEDURES (Optional but recommended)
-- =====================================================

DELIMITER //

-- Procedure to create a new investment
CREATE PROCEDURE `sp_create_investment`(
    IN p_user_id INT UNSIGNED,
    IN p_plan_id INT UNSIGNED,
    IN p_amount DECIMAL(20,8),
    OUT p_investment_id INT UNSIGNED,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_currency VARCHAR(10);
    DECLARE v_roi_percentage DECIMAL(5,2);
    DECLARE v_expected_profit DECIMAL(20,8);
    DECLARE v_balance DECIMAL(20,8);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Database error occurred';
    END;
    
    START TRANSACTION;
    
    -- Get plan details
    SELECT currency, roi_percentage INTO v_currency, v_roi_percentage
    FROM investment_plans WHERE id = p_plan_id AND is_active = 1;
    
    IF v_currency IS NULL THEN
        SET p_success = FALSE;
        SET p_message = 'Invalid or inactive plan';
        ROLLBACK;
    ELSE
        -- Check user balance
        SELECT balance INTO v_balance 
        FROM wallets 
        WHERE user_id = p_user_id AND currency = v_currency
        FOR UPDATE;
        
        IF v_balance < p_amount THEN
            SET p_success = FALSE;
            SET p_message = 'Insufficient balance';
            ROLLBACK;
        ELSE
            -- Calculate expected profit
            SET v_expected_profit = p_amount * (v_roi_percentage / 100);
            
            -- Create investment
            INSERT INTO investments (user_id, plan_id, currency, amount, roi_percentage, expected_profit)
            VALUES (p_user_id, p_plan_id, v_currency, p_amount, v_roi_percentage, v_expected_profit);
            
            SET p_investment_id = LAST_INSERT_ID();
            SET p_success = TRUE;
            SET p_message = 'Investment created successfully';
            
            COMMIT;
        END IF;
    END IF;
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS FOR DATA INTEGRITY
-- =====================================================

DELIMITER //

-- Trigger to update wallet totals on transaction insert
CREATE TRIGGER `tr_transactions_after_insert`
AFTER INSERT ON `transactions`
FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' THEN
        UPDATE wallets 
        SET 
            balance = NEW.balance_after,
            total_deposited = total_deposited + IF(NEW.type = 'deposit', NEW.amount, 0),
            total_withdrawn = total_withdrawn + IF(NEW.type = 'withdrawal', NEW.amount, 0),
            total_profit = total_profit + IF(NEW.type = 'profit', NEW.amount, 0),
            total_referral = total_referral + IF(NEW.type = 'referral', NEW.amount, 0)
        WHERE user_id = NEW.user_id AND currency = NEW.currency;
    END IF;
END//

DELIMITER ;

-- =====================================================
-- SECURITY NOTES
-- =====================================================
-- 1. Change default admin password immediately
-- 2. Replace sample deposit addresses with real ones
-- 3. Ensure proper database user permissions (no SUPER privilege)
-- 4. Regular backups of transactions and admin_logs tables
-- 5. Enable binary logging for point-in-time recovery
-- 6. Monitor for unusual transaction patterns
-- =====================================================

-- End of schema