-- ============================================================================
-- TSB Bank - External Banking Integration & Settlement Logic
-- Adds support for saving user bank accounts and routing funds to local banks
-- Based on User Location (Country Code)
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- SECTION 1: BANK DIRECTORY (Master List of Supported Banks)
-- This table allows the frontend to show only relevant banks based on user location
-- ============================================================================

CREATE TABLE IF NOT EXISTS `bank_directory` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_code` CHAR(2) NOT NULL COMMENT 'ISO Country Code (e.g., RW for Rwanda)',
  `bank_name` VARCHAR(100) NOT NULL,
  `bank_code` VARCHAR(20) DEFAULT NULL COMMENT 'Internal or SWIFT/BIC code if available',
  `logo_url` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `requires_iban` TINYINT(1) DEFAULT 0 COMMENT 'Does this bank require IBAN format?',
  `min_transfer_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  `max_transfer_amount` DECIMAL(18, 4) DEFAULT 999999999.0000,
  `settlement_days_business` INT UNSIGNED DEFAULT 3 COMMENT 'Number of business days for clearing',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_bank_country` (`country_code`, `bank_name`),
  KEY `idx_country` (`country_code`),
  FOREIGN KEY (`country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Data for Rwanda (Based on your request)
INSERT IGNORE INTO `bank_directory` 
(`country_code`, `bank_name`, `bank_code`, `is_active`, `requires_iban`, `settlement_days_business`) VALUES
('RW', 'Bank of Kigali (BK)', 'BKRWRW', 1, 0, 3),
('RW', 'BPR Bank Rwanda', 'BPRBRW', 1, 0, 3),
('RW', 'I&M Bank Rwanda', 'IMRWRW', 1, 0, 3),
('RW', 'Equity Bank Rwanda', 'EQBLRW', 1, 0, 3),
('RW', 'Ecobank Rwanda', 'ECOWRW', 1, 0, 3),
('RW', 'Access Bank Rwanda', 'ACCRRW', 1, 0, 3),
('RW', 'Bank of Africa Rwanda', 'BOARRW', 1, 0, 3),
('RW', 'GT Bank Rwanda', 'GTBARN', 1, 0, 3),
('RW', 'NCBA Bank Rwanda', 'NCBARW', 1, 0, 3),
('RW', 'Rwanda Development Bank (BRD)', 'BRDRRW', 1, 0, 3),
('RW', 'Zigama Credit and Savings Bank (Zigama CSS)', 'ZIGARW', 1, 0, 3);

-- ============================================================================
-- SECTION 2: USER LINKED BANKS (Saved Beneficiaries)
-- Users can add multiple bank accounts. These are used for withdrawals/transfers.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `user_linked_banks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `bank_directory_id` INT UNSIGNED NOT NULL COMMENT 'Links to master bank list',
  `account_holder_name` VARCHAR(100) NOT NULL,
  `account_number` VARCHAR(50) NOT NULL COMMENT 'Local account number or IBAN',
  `phone_number` VARCHAR(20) DEFAULT NULL COMMENT 'Optional phone linked to bank account',
  `location_address` VARCHAR(255) DEFAULT NULL COMMENT 'Physical branch or city info',
  `currency_code` CHAR(3) NOT NULL DEFAULT 'RWF',
  
  -- Verification Status (Crucial for fraud prevention)
  `verification_status` ENUM('PENDING_REVIEW', 'VERIFIED', 'REJECTED') DEFAULT 'PENDING_REVIEW',
  `verified_by_admin_id` INT UNSIGNED DEFAULT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `rejection_reason` TEXT DEFAULT NULL,
  
  `is_primary` TINYINT(1) DEFAULT 0 COMMENT 'Set as default withdrawal method',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_bank_dir` (`bank_directory_id`),
  KEY `idx_verification` (`verification_status`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`bank_directory_id`) REFERENCES `bank_directory`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 3: EXTERNAL TRANSFER LEDGER (Tracking Settlement)
-- We create a specific tracking table for external bank transfers because they 
-- take longer than internal wallet-to-wallet transfers.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `external_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` BIGINT UNSIGNED NOT NULL COMMENT 'Link to main transactions table',
  `user_id` INT UNSIGNED NOT NULL,
  `linked_bank_id` BIGINT UNSIGNED NOT NULL COMMENT 'The saved bank account being sent to',
  
  -- Financial Details
  `amount` DECIMAL(18, 4) NOT NULL,
  `fee_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  `total_debit` DECIMAL(18, 4) NOT NULL COMMENT 'Amount deducted from user wallet',
  `currency_code` CHAR(3) NOT NULL,
  
  -- Settlement Timeline
  `initiated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `scheduled_settlement_date` DATE NOT NULL COMMENT 'Calculated date: Initiated + 3 Business Days',
  `settled_at` TIMESTAMP NULL DEFAULT NULL,
  `failed_at` TIMESTAMP NULL DEFAULT NULL,
  
  -- Status Workflow
  `status` ENUM('QUEUED', 'PROCESSING_BANK_NETWORK', 'PENDING_SETTLEMENT', 'SETTLED', 'FAILED', 'REVERSED') DEFAULT 'QUEUED',
  `reference_number_external` VARCHAR(64) DEFAULT NULL COMMENT 'Reference returned by banking network/API',
  `admin_notes` TEXT DEFAULT NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_transaction` (`transaction_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_settlement_date` (`scheduled_settlement_date`),
  
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`linked_bank_id`) REFERENCES `user_linked_banks`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 4: SYSTEM SETTINGS FOR EXTERNAL TRANSFERS
-- Add configuration keys to control behavior globally
-- ============================================================================

INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('external_transfer_enabled', '1'),
('external_transfer_settlement_days', '3'),
('external_transfer_min_amount', '1000.00'),
('external_transfer_max_amount', '5000000.00'),
('require_bank_account_verification', '1')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

-- ============================================================================
-- SECTION 5: HELPER FUNCTION TO CALCULATE BUSINESS DAYS (MySQL Native)
-- Since MySQL doesn't have a built-in "Add 3 Business Days" function easily,
-- we rely on the application layer (PHP) to calculate `scheduled_settlement_date`.
-- However, here is a View to help admins monitor stuck transfers.
-- ============================================================================

CREATE OR REPLACE VIEW `v_pending_external_transfers` AS
SELECT 
    et.id AS transfer_id,
    t.transaction_reference,
    u.full_name AS user_name,
    u.phone_number,
    bd.bank_name,
    ulb.account_holder_name,
    ulb.account_number,
    et.amount,
    et.currency_code,
    et.initiated_at,
    et.scheduled_settlement_date,
    DATEDIFF(CURDATE(), et.scheduled_settlement_date) AS days_overdue,
    CASE 
        WHEN CURDATE() > et.scheduled_settlement_date THEN 'OVERDUE'
        ELSE 'WAITING'
    END AS settlement_status
FROM `external_transfers` et
JOIN `transactions` t ON et.transaction_id = t.id
JOIN `users` u ON et.user_id = u.id
JOIN `user_linked_banks` ulb ON et.linked_bank_id = ulb.id
JOIN `bank_directory` bd ON ulb.bank_directory_id = bd.id
WHERE et.status IN ('QUEUED', 'PROCESSING_BANK_NETWORK', 'PENDING_SETTLEMENT');

SET FOREIGN_KEY_CHECKS = 1;

SELECT 'External Banking Schema Added Successfully!' AS Status;



-- ============================================================================
-- TSB Bank - Fee Configuration & PawaPay Restriction Update
-- Database: asktbpks_tsb bank
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Update System Settings for Fees and PawaPay Restrictions
INSERT INTO `system_settings` (`setting_key`, `setting_value`) 
VALUES 
('deposit_fee_percentage', '5.00'),
('withdrawal_fee_percentage', '4.00'),
('total_combined_fee_percentage', '9.00'),
('pawapay_allowed_for_deposit', '1'),
('pawapay_allowed_for_withdrawal', '0')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

-- 2. Create Fee Configurations Table (Simplified for maximum compatibility)
CREATE TABLE IF NOT EXISTS `fee_configurations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_type` ENUM('DEPOSIT', 'WITHDRAWAL', 'TRANSFER', 'EXCHANGE') NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'ALL',
  `fee_type` ENUM('PERCENTAGE', 'FIXED') NOT NULL DEFAULT 'PERCENTAGE',
  `fee_value` DECIMAL(10, 4) NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_type_method` (`transaction_type`, `payment_method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Seed the Fee Configurations (INSERT IGNORE prevents duplicate errors safely)
INSERT IGNORE INTO `fee_configurations` (`transaction_type`, `payment_method`, `fee_type`, `fee_value`, `is_active`) VALUES
('DEPOSIT', 'pawapay', 'PERCENTAGE', 5.0000, 1),
('DEPOSIT', 'ALL', 'PERCENTAGE', 5.0000, 1),
('WITHDRAWAL', 'ALL', 'PERCENTAGE', 4.0000, 1);

SET FOREIGN_KEY_CHECKS = 1;

-- Success Message
SELECT 'Fee configurations and PawaPay restrictions updated successfully!' AS Status;


-- ============================================================================
-- TSB Bank - Multi-Currency Digital Banking Platform Database Schema
-- Database: asktbpks_tsb bank
-- User: asktbpks_tsbbank
-- Version: 3.0 - MM Products, Loans, Multi-Language, Referral System
-- ============================================================================

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

-- Disable foreign key checks to guarantee zero errors during import
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `asktbpks_tsb bank` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `asktbpks_tsb bank`;

-- ============================================================================
-- SECTION 1: LANGUAGES & TRANSLATIONS (Default: Chinese)
-- ============================================================================

CREATE TABLE `languages` (
  `code` CHAR(2) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `native_name` VARCHAR(64) NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `is_default` TINYINT(1) DEFAULT 0,
  `text_direction` ENUM('LTR', 'RTL') DEFAULT 'LTR',
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `languages` (`code`, `name`, `native_name`, `is_active`, `is_default`) VALUES
('zh', 'Chinese', '中文', 1, 1),
('en', 'English', 'English', 1, 0),
('rw', 'Kinyarwanda', 'Ikinyarwanda', 1, 0);

CREATE TABLE `translations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `translation_key` VARCHAR(128) NOT NULL,
  `language_code` CHAR(2) NOT NULL,
  `translation_value` TEXT NOT NULL,
  `category` VARCHAR(50) DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_key_lang` (`translation_key`, `language_code`),
  KEY `idx_language` (`language_code`),
  KEY `idx_category` (`category`),
  FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- English translations
INSERT INTO `translations` (`translation_key`, `language_code`, `translation_value`, `category`) VALUES
('welcome_message', 'en', 'Welcome to TSB Bank', 'general'),
('sign_in', 'en', 'Sign In', 'auth'),
('create_account', 'en', 'Create Account', 'auth'),
('phone_number', 'en', 'Phone Number', 'auth'),
('password', 'en', 'Password', 'auth'),
('full_name', 'en', 'Full Name', 'auth'),
('account_restricted', 'en', 'Account Restricted — Customer Support Required', 'account'),
('account_restricted_message', 'en', 'Your loan repayment deadline and five-day grace period have expired. Please contact customer support to review your account.', 'account'),
('mm_product', 'en', 'MM Membership Product', 'mm'),
('locked', 'en', 'Locked', 'mm'),
('coming_soon', 'en', 'Coming Soon', 'mm'),
('currently_unavailable', 'en', 'Currently Unavailable', 'mm'),
('daily_earning', 'en', 'Daily Earning', 'mm'),
('repayment_deadline', 'en', 'Repayment Deadline', 'loan'),
('grace_period', 'en', 'Grace Period', 'loan'),
('grace_period_charge', 'en', 'Grace Period Charge (6%)', 'loan'),
('loan_fully_repaid', 'en', 'Loan Fully Repaid', 'loan'),
('select_earning_period', 'en', 'Select Earning Period', 'mm'),
('principal_locked', 'en', 'Principal is locked and cannot be withdrawn', 'mm'),
('withdrawable_earnings', 'en', 'Withdrawable Earnings', 'mm'),
('referral_bonus', 'en', 'Referral Bonus', 'referral'),
('contact_support', 'en', 'Contact Support', 'general');

-- Chinese translations (Default)
INSERT INTO `translations` (`translation_key`, `language_code`, `translation_value`, `category`) VALUES
('welcome_message', 'zh', '欢迎使用TSB银行', 'general'),
('sign_in', 'zh', '登录', 'auth'),
('create_account', 'zh', '创建账户', 'auth'),
('phone_number', 'zh', '电话号码', 'auth'),
('password', 'zh', '密码', 'auth'),
('full_name', 'zh', '全名', 'auth'),
('account_restricted', 'zh', '账户受限 — 需要客户支持', 'account'),
('account_restricted_message', 'zh', '您的贷款还款期限和五天宽限期已到期。请联系客户支持审核您的账户。', 'account'),
('mm_product', 'zh', 'MM会员产品', 'mm'),
('locked', 'zh', '已锁定', 'mm'),
('coming_soon', 'zh', '即将推出', 'mm'),
('currently_unavailable', 'zh', '暂不可用', 'mm'),
('daily_earning', 'zh', '每日收益', 'mm'),
('repayment_deadline', 'zh', '还款截止日期', 'loan'),
('grace_period', 'zh', '宽限期', 'loan'),
('grace_period_charge', 'zh', '宽限期费用 (6%)', 'loan'),
('loan_fully_repaid', 'zh', '贷款已全额还清', 'loan'),
('select_earning_period', 'zh', '选择收益期限', 'mm'),
('principal_locked', 'zh', '本金已锁定,无法提取', 'mm'),
('withdrawable_earnings', 'zh', '可提取收益', 'mm'),
('referral_bonus', 'zh', '推荐奖金', 'referral'),
('contact_support', 'zh', '联系支持', 'general');

-- Kinyarwanda translations
INSERT INTO `translations` (`translation_key`, `language_code`, `translation_value`, `category`) VALUES
('welcome_message', 'rw', 'Murakaza neza kuri TSB Bank', 'general'),
('sign_in', 'rw', 'Injira', 'auth'),
('create_account', 'rw', 'Fungura Konti', 'auth'),
('phone_number', 'rw', 'Nomero ya Telefoni', 'auth'),
('password', 'rw', 'Ijambo banga', 'auth'),
('full_name', 'rw', 'Amazina Yombi', 'auth'),
('account_restricted', 'rw', 'Konti Ifunze — Ukeneye Ubufasha', 'account'),
('account_restricted_message', 'rw', 'Igihe cyo kwishyura inguzanyo n\'iminsi itanu y\'uburenganzira byarangiye. Nyamuneka vugana n\'ubufasha bw\'abakiriya.', 'account'),
('mm_product', 'rw', 'Ibicuruzwa by\'Ubumwe bwa MM', 'mm'),
('locked', 'rw', 'Byafungiranywe', 'mm'),
('coming_soon', 'rw', 'Biraza Vuba', 'mm'),
('currently_unavailable', 'rw', 'Ntibiboneka Ubu', 'mm'),
('daily_earning', 'rw', 'Inyungu ya Buri Munsi', 'mm'),
('repayment_deadline', 'rw', 'Igihe Nyacyo cyo Kwishyura', 'loan'),
('grace_period', 'rw', 'Igihe cy\'Uburenganzira', 'loan'),
('grace_period_charge', 'rw', 'Igiciro cy\'Igihe cy\'Uburenganzira (6%)', 'loan'),
('loan_fully_repaid', 'rw', 'Inguzanyo Yishyuwe Yose', 'loan'),
('select_earning_period', 'rw', 'Hitamo Igihe cy\'Inyungu', 'mm'),
('principal_locked', 'rw', 'Umutungo W\'ibanze Wafungiranywe, Ntushobora Kuwukuramo', 'mm'),
('withdrawable_earnings', 'rw', 'Inyungu Ishobora Gukurwamo', 'mm'),
('referral_bonus', 'rw', 'Inyungu y\'Ubutumire', 'referral'),
('contact_support', 'rw', 'Vugana n\'Ubufasha', 'general');

-- ============================================================================
-- SECTION 2: REFERENCE TABLES (Currencies & Countries)
-- ============================================================================

CREATE TABLE `currencies` (
  `code` CHAR(3) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `symbol` VARCHAR(8) NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `currencies` (`code`, `name`, `symbol`, `is_active`) VALUES
('AED', 'UAE Dirham', 'د.إ', 1), ('AFN', 'Afghan Afghani', '؋', 1), ('ALL', 'Albanian Lek', 'L', 1),
('AMD', 'Armenian Dram', '֏', 1), ('ANG', 'Netherlands Antillean Guilder', 'ƒ', 1), ('AOA', 'Angolan Kwanza', 'Kz', 1),
('ARS', 'Argentine Peso', '$', 1), ('AUD', 'Australian Dollar', 'A$', 1), ('AWG', 'Aruban Florin', 'ƒ', 1),
('AZN', 'Azerbaijani Manat', '₼', 1), ('BAM', 'Bosnia-Herzegovina Convertible Mark', 'KM', 1), ('BBD', 'Barbadian Dollar', '$', 1),
('BDT', 'Bangladeshi Taka', '৳', 1), ('BGN', 'Bulgarian Lev', 'лв', 1), ('BHD', 'Bahraini Dinar', '.د.ب', 1),
('BIF', 'Burundian Franc', 'FBu', 1), ('BMD', 'Bermudan Dollar', '$', 1), ('BND', 'Brunei Dollar', '$', 1),
('BOB', 'Bolivian Boliviano', 'Bs.', 1), ('BRL', 'Brazilian Real', 'R$', 1), ('BSD', 'Bahamian Dollar', '$', 1),
('BTN', 'Bhutanese Ngultrum', 'Nu.', 1), ('BWP', 'Botswanan Pula', 'P', 1), ('BYN', 'Belarusian Ruble', 'Br', 1),
('BZD', 'Belize Dollar', 'BZ$', 1), ('CAD', 'Canadian Dollar', 'C$', 1), ('CDF', 'Congolese Franc', 'FC', 1),
('CHF', 'Swiss Franc', 'CHF', 1), ('CLF', 'Unidad de Fomento', 'UF', 1), ('CLP', 'Chilean Peso', '$', 1),
('CNH', 'Chinese Yuan Offshore', '¥', 1), ('CNY', 'Chinese Yuan', '¥', 1), ('COP', 'Colombian Peso', '$', 1),
('CRC', 'Costa Rican Colón', '₡', 1), ('CUP', 'Cuban Peso', '₱', 1), ('CVE', 'Cape Verdean Escudo', '$', 1),
('CZK', 'Czech Republic Koruna', 'Kč', 1), ('DJF', 'Djiboutian Franc', 'Fdj', 1), ('DKK', 'Danish Krone', 'kr', 1),
('DOP', 'Dominican Peso', 'RD$', 1), ('DZD', 'Algerian Dinar', 'دج', 1), ('EGP', 'Egyptian Pound', '£', 1),
('ERN', 'Eritrean Nakfa', 'Nfk', 1), ('ETB', 'Ethiopian Birr', 'Br', 1), ('EUR', 'Euro', '€', 1),
('FJD', 'Fijian Dollar', '$', 1), ('FKP', 'Falkland Islands Pound', '£', 1), ('FOK', 'Faroese Króna', 'kr', 1),
('GBP', 'Pound Sterling', '£', 1), ('GEL', 'Georgian Lari', '₾', 1), ('GGP', 'Guernsey Pound', '£', 1),
('GHS', 'Ghanaian Cedi', '₵', 1), ('GIP', 'Gibraltar Pound', '£', 1), ('GMD', 'Gambian Dalasi', 'D', 1),
('GNF', 'Guinean Franc', 'FG', 1), ('GTQ', 'Guatemalan Quetzal', 'Q', 1), ('GYD', 'Guyanaese Dollar', '$', 1),
('HKD', 'Hong Kong Dollar', 'HK$', 1), ('HNL', 'Honduran Lempira', 'L', 1), ('HRK', 'Croatian Kuna', 'kn', 1),
('HTG', 'Haitian Gourde', 'G', 1), ('HUF', 'Hungarian Forint', 'Ft', 1), ('IDR', 'Indonesian Rupiah', 'Rp', 1),
('ILS', 'Israeli New Sheqel', '₪', 1), ('IMP', 'Manx Pound', '£', 1), ('INR', 'Indian Rupee', '₹', 1),
('IQD', 'Iraqi Dinar', 'ع.د', 1), ('IRR', 'Iranian Rial', '﷼', 1), ('ISK', 'Icelandic Króna', 'kr', 1),
('JEP', 'Jersey Pound', '£', 1), ('JMD', 'Jamaican Dollar', 'J$', 1), ('JOD', 'Jordanian Dinar', 'د.ا', 1),
('JPY', 'Japanese Yen', '¥', 1), ('KES', 'Kenyan Shilling', 'KSh', 1), ('KGS', 'Kyrgystani Som', 'с', 1),
('KHR', 'Cambodian Riel', '៛', 1), ('KID', 'Kiribati Dollar', '$', 1), ('KMF', 'Comorian Franc', 'CF', 1),
('KPW', 'North Korean Won', '₩', 1), ('KRW', 'South Korean Won', '₩', 1), ('KWD', 'Kuwaiti Dinar', 'د.ك', 1),
('KYD', 'Cayman Islands Dollar', '$', 1), ('KZT', 'Kazakhstani Tenge', '₸', 1), ('LAK', 'Laotian Kip', '₭', 1),
('LBP', 'Lebanese Pound', 'ل.ل', 1), ('LKR', 'Sri Lankan Rupee', 'Rs', 1), ('LRD', 'Liberian Dollar', '$', 1),
('LSL', 'Lesotho Loti', 'L', 1), ('LYD', 'Libyan Dinar', 'ل.د', 1), ('MAD', 'Moroccan Dirham', 'د.م.', 1),
('MDL', 'Moldovan Leu', 'L', 1), ('MGA', 'Malagasy Ariary', 'Ar', 1), ('MKD', 'Macedonian Denar', 'ден', 1),
('MMK', 'Myanma Kyat', 'K', 1), ('MNT', 'Mongolian Tugrik', '₮', 1), ('MOP', 'Macanese Pataca', 'MOP$', 1),
('MRU', 'Mauritanian Ouguiya', 'UM', 1), ('MUR', 'Mauritian Rupee', '₨', 1), ('MVR', 'Maldivian Rufiyaa', 'Rf', 1),
('MWK', 'Malawian Kwacha', 'MK', 1), ('MXN', 'Mexican Peso', '$', 1), ('MYR', 'Malaysian Ringgit', 'RM', 1),
('MZN', 'Mozambican Metical', 'MT', 1), ('NAD', 'Namibian Dollar', '$', 1), ('NGN', 'Nigerian Naira', '₦', 1),
('NIO', 'Nicaraguan Córdoba', 'C$', 1), ('NOK', 'Norwegian Krone', 'kr', 1), ('NPR', 'Nepalese Rupee', '₨', 1),
('NZD', 'New Zealand Dollar', 'NZ$', 1), ('OMR', 'Omani Rial', 'ر.ع.', 1), ('PAB', 'Panamanian Balboa', 'B/.', 1),
('PEN', 'Peruvian Nuevo Sol', 'S/.', 1), ('PGK', 'Papua New Guinean Kina', 'K', 1), ('PHP', 'Philippine Peso', '₱', 1),
('PKR', 'Pakistani Rupee', '₨', 1), ('PLN', 'Polish Zloty', 'zł', 1), ('PYG', 'Paraguayan Guarani', '₲', 1),
('QAR', 'Qatari Rial', 'ر.ق', 1), ('RON', 'Romanian Leu', 'lei', 1), ('RSD', 'Serbian Dinar', 'дин.', 1),
('RUB', 'Russian Ruble', '₽', 1), ('RWF', 'Rwandan Franc', 'FRw', 1), ('SAR', 'Saudi Riyal', 'ر.س', 1),
('SBD', 'Solomon Islands Dollar', '$', 1), ('SCR', 'Seychellois Rupee', '₨', 1), ('SDG', 'Sudanese Pound', 'ج.س.', 1),
('SEK', 'Swedish Krona', 'kr', 1), ('SGD', 'Singapore Dollar', 'S$', 1), ('SHP', 'Saint Helena Pound', '£', 1),
('SLE', 'Sierra Leonean Leone', 'Le', 1), ('SLL', 'Sierra Leonean Leone (Old)', 'Le', 1), ('SOS', 'Somali Shilling', 'Sh', 1),
('SRD', 'Surinamese Dollar', '$', 1), ('SSP', 'South Sudanese Pound', '£', 1), ('STN', 'São Tomé and Príncipe Dobra', 'Db', 1),
('SYP', 'Syrian Pound', '£', 1), ('SZL', 'Swazi Lilangeni', 'L', 1), ('THB', 'Thai Baht', '฿', 1),
('TJS', 'Tajikistani Somoni', 'ЅМ', 1), ('TMT', 'Turkmenistani Manat', 'm', 1), ('TND', 'Tunisian Dinar', 'د.ت', 1),
('TOP', 'Tongan Paʻanga', 'T$', 1), ('TRY', 'Turkish Lira', '₺', 1), ('TTD', 'Trinidad and Tobago Dollar', 'TT$', 1),
('TVD', 'Tuvaluan Dollar', '$', 1), ('TWD', 'New Taiwan Dollar', 'NT$', 1), ('TZS', 'Tanzanian Shilling', 'TSh', 1),
('UAH', 'Ukrainian Hryvnia', '₴', 1), ('UGX', 'Ugandan Shilling', 'USh', 1), ('USD', 'US Dollar', '$', 1),
('UYU', 'Uruguayan Peso', '$U', 1), ('UZS', 'Uzbekistan Som', 'soʻm', 1), ('VES', 'Venezuelan Bolívar', 'Bs.S', 1),
('VND', 'Vietnamese Dong', '₫', 1), ('VUV', 'Vanuatu Vatu', 'VT', 1), ('WST', 'Samoan Tala', 'T', 1),
('XAF', 'CFA Franc BEAC', 'FCFA', 1), ('XCD', 'East Caribbean Dollar', '$', 1), ('XCG', 'Caribbean Guilder', 'ƒ', 1),
('XDR', 'Special Drawing Rights', 'SDR', 1), ('XOF', 'CFA Franc BCEAO', 'CFA', 1), ('XPF', 'CFP Franc', '₣', 1),
('YER', 'Yemeni Rial', '﷼', 1), ('ZAR', 'South African Rand', 'R', 1), ('ZMW', 'Zambian Kwacha', 'ZK', 1),
('ZWG', 'Zimbabwe Gold', 'ZiG', 1), ('ZWL', 'Zimbabwean Dollar', 'Z$', 1);

CREATE TABLE `countries` (
  `code` CHAR(2) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `emoji_flag` VARCHAR(8) NOT NULL,
  `dial_code` VARCHAR(10) NOT NULL,
  `phone_length` TINYINT UNSIGNED NOT NULL,
  `default_currency_code` CHAR(3) NOT NULL,
  `default_language_code` CHAR(2) DEFAULT 'zh',
  `is_supported` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`code`),
  FOREIGN KEY (`default_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`default_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `countries` (`code`, `name`, `emoji_flag`, `dial_code`, `phone_length`, `default_currency_code`, `default_language_code`, `is_supported`) VALUES
('AF', 'Afghanistan', '🇦🇫', '+93', 9, 'AFN', 'zh', 1), ('AL', 'Albania', '🇦🇱', '+355', 9, 'ALL', 'zh', 1),
('DZ', 'Algeria', '🇩🇿', '+213', 9, 'DZD', 'zh', 1), ('AD', 'Andorra', '🇦🇩', '+376', 6, 'EUR', 'zh', 1),
('AO', 'Angola', '🇦🇴', '+244', 9, 'AOA', 'zh', 1), ('AR', 'Argentina', '🇦🇷', '+54', 10, 'ARS', 'zh', 1),
('AM', 'Armenia', '🇦🇲', '+374', 8, 'AMD', 'zh', 1), ('AU', 'Australia', '🇦🇺', '+61', 9, 'AUD', 'zh', 1),
('AT', 'Austria', '🇦🇹', '+43', 10, 'EUR', 'zh', 1), ('AZ', 'Azerbaijan', '🇦🇿', '+994', 9, 'AZN', 'zh', 1),
('BS', 'Bahamas', '🇧🇸', '+1-242', 10, 'BSD', 'zh', 1), ('BH', 'Bahrain', '🇧🇭', '+973', 8, 'BHD', 'zh', 1),
('BD', 'Bangladesh', '🇧🇩', '+880', 10, 'BDT', 'zh', 1), ('BY', 'Belarus', '🇧🇾', '+375', 9, 'BYN', 'zh', 1),
('BE', 'Belgium', '🇧🇪', '+32', 9, 'EUR', 'zh', 1), ('BZ', 'Belize', '🇧🇿', '+501', 7, 'BZD', 'zh', 1),
('BJ', 'Benin', '🇧🇯', '+229', 8, 'XOF', 'zh', 1), ('BT', 'Bhutan', '🇧🇹', '+975', 8, 'BTN', 'zh', 1),
('BO', 'Bolivia', '🇧🇴', '+591', 8, 'BOB', 'zh', 1), ('BA', 'Bosnia and Herzegovina', '🇧🇦', '+387', 8, 'BAM', 'zh', 1),
('BW', 'Botswana', '🇧🇼', '+267', 8, 'BWP', 'zh', 1), ('BR', 'Brazil', '🇧🇷', '+55', 11, 'BRL', 'zh', 1),
('BN', 'Brunei', '🇧🇳', '+673', 7, 'BND', 'zh', 1), ('BG', 'Bulgaria', '🇧🇬', '+359', 9, 'BGN', 'zh', 1),
('BF', 'Burkina Faso', '🇧🇫', '+226', 8, 'XOF', 'zh', 1), ('BI', 'Burundi', '🇧🇮', '+257', 8, 'BIF', 'zh', 1),
('KH', 'Cambodia', '🇰🇭', '+855', 9, 'KHR', 'zh', 1), ('CM', 'Cameroon', '🇨🇲', '+237', 9, 'XAF', 'zh', 1),
('CA', 'Canada', '🇨🇦', '+1', 10, 'CAD', 'zh', 1), ('CV', 'Cape Verde', '🇨🇻', '+238', 7, 'CVE', 'zh', 1),
('CF', 'Central African Republic', '🇨🇫', '+236', 8, 'XAF', 'zh', 1), ('TD', 'Chad', '🇹🇩', '+235', 8, 'XAF', 'zh', 1),
('CL', 'Chile', '🇨🇱', '+56', 9, 'CLP', 'zh', 1), ('CN', 'China', '🇨🇳', '+86', 11, 'CNY', 'zh', 1),
('CO', 'Colombia', '🇨🇴', '+57', 10, 'COP', 'zh', 1), ('KM', 'Comoros', '🇰🇲', '+269', 7, 'KMF', 'zh', 1),
('CG', 'Congo', '🇨🇬', '+242', 9, 'XAF', 'zh', 1), ('CR', 'Costa Rica', '🇨🇷', '+506', 8, 'CRC', 'zh', 1),
('HR', 'Croatia', '🇭🇷', '+385', 9, 'EUR', 'zh', 1), ('CU', 'Cuba', '🇨🇺', '+53', 8, 'CUP', 'zh', 1),
('CY', 'Cyprus', '🇨🇾', '+357', 8, 'EUR', 'zh', 1), ('CZ', 'Czech Republic', '🇨🇿', '+420', 9, 'CZK', 'zh', 1),
('DK', 'Denmark', '🇩🇰', '+45', 8, 'DKK', 'zh', 1), ('DJ', 'Djibouti', '🇩🇯', '+253', 8, 'DJF', 'zh', 1),
('DM', 'Dominica', '🇩🇲', '+1-767', 10, 'XCD', 'zh', 1), ('DO', 'Dominican Republic', '🇩🇴', '+1-809', 10, 'DOP', 'zh', 1),
('EC', 'Ecuador', '🇪🇨', '+593', 9, 'USD', 'zh', 1), ('EG', 'Egypt', '🇪🇬', '+20', 10, 'EGP', 'zh', 1),
('SV', 'El Salvador', '🇸🇻', '+503', 8, 'USD', 'zh', 1), ('GQ', 'Equatorial Guinea', '🇬🇶', '+240', 9, 'XAF', 'zh', 1),
('ER', 'Eritrea', '🇪🇷', '+291', 7, 'ERN', 'zh', 1), ('EE', 'Estonia', '🇪🇪', '+372', 8, 'EUR', 'zh', 1),
('ET', 'Ethiopia', '🇪🇹', '+251', 9, 'ETB', 'zh', 1), ('FJ', 'Fiji', '🇫🇯', '+679', 7, 'FJD', 'zh', 1),
('FI', 'Finland', '🇫🇮', '+358', 9, 'EUR', 'zh', 1), ('FR', 'France', '🇫🇷', '+33', 9, 'EUR', 'zh', 1),
('GA', 'Gabon', '🇬🇦', '+241', 8, 'XAF', 'zh', 1), ('GM', 'Gambia', '🇬🇲', '+220', 7, 'GMD', 'zh', 1),
('GE', 'Georgia', '🇬🇪', '+995', 9, 'GEL', 'zh', 1), ('DE', 'Germany', '🇩🇪', '+49', 10, 'EUR', 'zh', 1),
('GH', 'Ghana', '🇬🇭', '+233', 9, 'GHS', 'zh', 1), ('GR', 'Greece', '🇬🇷', '+30', 10, 'EUR', 'zh', 1),
('GD', 'Grenada', '🇬🇩', '+1-473', 10, 'XCD', 'zh', 1), ('GT', 'Guatemala', '🇬🇹', '+502', 8, 'GTQ', 'zh', 1),
('GN', 'Guinea', '🇬🇳', '+224', 9, 'GNF', 'zh', 1), ('GW', 'Guinea-Bissau', '🇬🇼', '+245', 7, 'XOF', 'zh', 1),
('GY', 'Guyana', '🇬🇾', '+592', 7, 'GYD', 'zh', 1), ('HT', 'Haiti', '🇭🇹', '+509', 8, 'HTG', 'zh', 1),
('HN', 'Honduras', '🇭🇳', '+504', 8, 'HNL', 'zh', 1), ('HU', 'Hungary', '🇭🇺', '+36', 9, 'HUF', 'zh', 1),
('IS', 'Iceland', '🇮🇸', '+354', 7, 'ISK', 'zh', 1), ('IN', 'India', '🇮🇳', '+91', 10, 'INR', 'zh', 1),
('ID', 'Indonesia', '🇮🇩', '+62', 11, 'IDR', 'zh', 1), ('IR', 'Iran', '🇮🇷', '+98', 10, 'IRR', 'zh', 1),
('IQ', 'Iraq', '🇮🇶', '+964', 10, 'IQD', 'zh', 1), ('IE', 'Ireland', '🇮🇪', '+353', 9, 'EUR', 'zh', 1),
('IL', 'Israel', '🇮🇱', '+972', 9, 'ILS', 'zh', 1), ('IT', 'Italy', '🇮🇹', '+39', 10, 'EUR', 'zh', 1),
('JM', 'Jamaica', '🇯🇲', '+1-876', 10, 'JMD', 'zh', 1), ('JP', 'Japan', '🇯🇵', '+81', 10, 'JPY', 'zh', 1),
('JO', 'Jordan', '🇯🇴', '+962', 9, 'JOD', 'zh', 1), ('KZ', 'Kazakhstan', '🇰🇿', '+7', 10, 'KZT', 'zh', 1),
('KE', 'Kenya', '🇰🇪', '+254', 9, 'KES', 'zh', 1), ('KI', 'Kiribati', '🇰🇮', '+686', 5, 'AUD', 'zh', 1),
('KW', 'Kuwait', '🇰🇼', '+965', 8, 'KWD', 'zh', 1), ('KG', 'Kyrgyzstan', '🇰🇬', '+996', 9, 'KGS', 'zh', 1),
('LA', 'Laos', '🇱🇦', '+856', 9, 'LAK', 'zh', 1), ('LV', 'Latvia', '🇱🇻', '+371', 8, 'EUR', 'zh', 1),
('LB', 'Lebanon', '🇱🇧', '+961', 8, 'LBP', 'zh', 1), ('LS', 'Lesotho', '🇱🇸', '+266', 8, 'LSL', 'zh', 1),
('LR', 'Liberia', '🇱🇷', '+231', 8, 'LRD', 'zh', 1), ('LY', 'Libya', '🇱🇾', '+218', 9, 'LYD', 'zh', 1),
('LI', 'Liechtenstein', '🇱🇮', '+423', 7, 'CHF', 'zh', 1), ('LT', 'Lithuania', '🇱🇹', '+370', 8, 'EUR', 'zh', 1),
('LU', 'Luxembourg', '🇱🇺', '+352', 9, 'EUR', 'zh', 1), ('MG', 'Madagascar', '🇲🇬', '+261', 9, 'MGA', 'zh', 1),
('MW', 'Malawi', '🇲🇼', '+265', 9, 'MWK', 'zh', 1), ('MY', 'Malaysia', '🇲🇾', '+60', 9, 'MYR', 'zh', 1),
('MV', 'Maldives', '🇲🇻', '+960', 7, 'MVR', 'zh', 1), ('ML', 'Mali', '🇲🇱', '+223', 8, 'XOF', 'zh', 1),
('MT', 'Malta', '🇲🇹', '+356', 8, 'EUR', 'zh', 1), ('MH', 'Marshall Islands', '🇲🇭', '+692', 7, 'USD', 'zh', 1),
('MR', 'Mauritania', '🇲🇷', '+222', 8, 'MRU', 'zh', 1), ('MU', 'Mauritius', '🇲🇺', '+230', 8, 'MUR', 'zh', 1),
('MX', 'Mexico', '🇲🇽', '+52', 10, 'MXN', 'zh', 1), ('FM', 'Micronesia', '🇫🇲', '+691', 7, 'USD', 'zh', 1),
('MD', 'Moldova', '🇲🇩', '+373', 8, 'MDL', 'zh', 1), ('MC', 'Monaco', '🇲🇨', '+377', 8, 'EUR', 'zh', 1),
('MN', 'Mongolia', '🇲🇳', '+976', 8, 'MNT', 'zh', 1), ('ME', 'Montenegro', '🇲🇪', '+382', 8, 'EUR', 'zh', 1),
('MA', 'Morocco', '🇲🇦', '+212', 9, 'MAD', 'zh', 1), ('MZ', 'Mozambique', '🇲🇿', '+258', 9, 'MZN', 'zh', 1),
('MM', 'Myanmar', '🇲🇲', '+95', 9, 'MMK', 'zh', 1), ('NA', 'Namibia', '🇳🇦', '+264', 9, 'NAD', 'zh', 1),
('NR', 'Nauru', '🇳🇷', '+674', 7, 'AUD', 'zh', 1), ('NP', 'Nepal', '🇳🇵', '+977', 10, 'NPR', 'zh', 1),
('NL', 'Netherlands', '🇳🇱', '+31', 9, 'EUR', 'zh', 1), ('NZ', 'New Zealand', '🇳🇿', '+64', 9, 'NZD', 'zh', 1),
('NI', 'Nicaragua', '🇳🇮', '+505', 8, 'NIO', 'zh', 1), ('NE', 'Niger', '🇳🇪', '+227', 8, 'XOF', 'zh', 1),
('NG', 'Nigeria', '🇳🇬', '+234', 10, 'NGN', 'zh', 1), ('KP', 'North Korea', '🇰🇵', '+850', 9, 'KPW', 'zh', 1),
('MK', 'North Macedonia', '🇲🇰', '+389', 8, 'MKD', 'zh', 1), ('NO', 'Norway', '🇳🇴', '+47', 8, 'NOK', 'zh', 1),
('OM', 'Oman', '🇴🇲', '+968', 8, 'OMR', 'zh', 1), ('PK', 'Pakistan', '🇵🇰', '+92', 10, 'PKR', 'zh', 1),
('PW', 'Palau', '🇵🇼', '+680', 7, 'USD', 'zh', 1), ('PA', 'Panama', '🇵🇦', '+507', 8, 'PAB', 'zh', 1),
('PG', 'Papua New Guinea', '🇵🇬', '+675', 8, 'PGK', 'zh', 1), ('PY', 'Paraguay', '🇵🇾', '+595', 9, 'PYG', 'zh', 1),
('PE', 'Peru', '🇵🇪', '+51', 9, 'PEN', 'zh', 1), ('PH', 'Philippines', '🇵🇭', '+63', 10, 'PHP', 'zh', 1),
('PL', 'Poland', '🇵🇱', '+48', 9, 'PLN', 'zh', 1), ('PT', 'Portugal', '🇵🇹', '+351', 9, 'EUR', 'zh', 1),
('QA', 'Qatar', '🇶🇦', '+974', 8, 'QAR', 'zh', 1), ('RO', 'Romania', '🇷🇴', '+40', 9, 'RON', 'zh', 1),
('RU', 'Russia', '🇷🇺', '+7', 10, 'RUB', 'zh', 1), ('RW', 'Rwanda', '🇷🇼', '+250', 9, 'RWF', 'rw', 1),
('KN', 'Saint Kitts and Nevis', '🇰🇳', '+1-869', 10, 'XCD', 'zh', 1), ('LC', 'Saint Lucia', '🇱🇨', '+1-758', 10, 'XCD', 'zh', 1),
('VC', 'Saint Vincent and the Grenadines', '🇻🇨', '+1-784', 10, 'XCD', 'zh', 1), ('WS', 'Samoa', '🇼🇸', '+685', 7, 'WST', 'zh', 1),
('SM', 'San Marino', '🇸🇲', '+378', 8, 'EUR', 'zh', 1), ('ST', 'Sao Tome and Principe', '🇸🇹', '+239', 7, 'STN', 'zh', 1),
('SA', 'Saudi Arabia', '🇸🇦', '+966', 9, 'SAR', 'zh', 1), ('SN', 'Senegal', '🇸🇳', '+221', 9, 'XOF', 'zh', 1),
('RS', 'Serbia', '🇷🇸', '+381', 9, 'RSD', 'zh', 1), ('SC', 'Seychelles', '🇸🇨', '+248', 7, 'SCR', 'zh', 1),
('SL', 'Sierra Leone', '🇸🇱', '+232', 8, 'SLL', 'zh', 1), ('SG', 'Singapore', '🇸🇬', '+65', 8, 'SGD', 'zh', 1),
('SK', 'Slovakia', '🇸🇰', '+421', 9, 'EUR', 'zh', 1), ('SI', 'Slovenia', '🇸🇮', '+386', 8, 'EUR', 'zh', 1),
('SB', 'Solomon Islands', '🇸🇧', '+677', 7, 'SBD', 'zh', 1), ('SO', 'Somalia', '🇸🇴', '+252', 9, 'SOS', 'zh', 1),
('ZA', 'South Africa', '🇿🇦', '+27', 9, 'ZAR', 'zh', 1), ('KR', 'South Korea', '🇰🇷', '+82', 10, 'KRW', 'zh', 1),
('SS', 'South Sudan', '🇸🇸', '+211', 9, 'SSP', 'zh', 1), ('ES', 'Spain', '🇪🇸', '+34', 9, 'EUR', 'zh', 1),
('LK', 'Sri Lanka', '🇱🇰', '+94', 9, 'LKR', 'zh', 1), ('SD', 'Sudan', '🇸🇩', '+249', 9, 'SDG', 'zh', 1),
('SR', 'Suriname', '🇸🇷', '+597', 7, 'SRD', 'zh', 1), ('SZ', 'Swaziland', '🇸🇿', '+268', 8, 'SZL', 'zh', 1),
('SE', 'Sweden', '🇸🇪', '+46', 9, 'SEK', 'zh', 1), ('CH', 'Switzerland', '🇨🇭', '+41', 9, 'CHF', 'zh', 1),
('SY', 'Syria', '🇸🇾', '+963', 9, 'SYP', 'zh', 1), ('TW', 'Taiwan', '🇹🇼', '+886', 9, 'TWD', 'zh', 1),
('TJ', 'Tajikistan', '🇹🇯', '+992', 9, 'TJS', 'zh', 1), ('TZ', 'Tanzania', '🇹🇿', '+255', 9, 'TZS', 'zh', 1),
('TH', 'Thailand', '🇹🇭', '+66', 9, 'THB', 'zh', 1), ('TL', 'Timor-Leste', '🇹🇱', '+670', 7, 'USD', 'zh', 1),
('TG', 'Togo', '🇹🇬', '+228', 8, 'XOF', 'zh', 1), ('TO', 'Tonga', '🇹🇴', '+676', 5, 'TOP', 'zh', 1),
('TT', 'Trinidad and Tobago', '🇹🇹', '+1-868', 10, 'TTD', 'zh', 1), ('TN', 'Tunisia', '🇹🇳', '+216', 8, 'TND', 'zh', 1),
('TR', 'Turkey', '🇹🇷', '+90', 10, 'TRY', 'zh', 1), ('TM', 'Turkmenistan', '🇹🇲', '+993', 8, 'TMT', 'zh', 1),
('TV', 'Tuvalu', '🇹🇻', '+688', 5, 'AUD', 'zh', 1), ('UG', 'Uganda', '🇺🇬', '+256', 9, 'UGX', 'zh', 1),
('UA', 'Ukraine', '🇺🇦', '+380', 9, 'UAH', 'zh', 1), ('AE', 'United Arab Emirates', '🇦🇪', '+971', 9, 'AED', 'zh', 1),
('GB', 'United Kingdom', '🇬🇧', '+44', 10, 'GBP', 'en', 1), ('US', 'United States', '🇺🇸', '+1', 10, 'USD', 'en', 1),
('UY', 'Uruguay', '🇺🇾', '+598', 8, 'UYU', 'zh', 1), ('UZ', 'Uzbekistan', '🇺🇿', '+998', 9, 'UZS', 'zh', 1),
('VU', 'Vanuatu', '🇻🇺', '+678', 7, 'VUV', 'zh', 1), ('VA', 'Vatican City', '🇻🇦', '+379', 8, 'EUR', 'zh', 1),
('VE', 'Venezuela', '🇻🇪', '+58', 10, 'VES', 'zh', 1), ('VN', 'Vietnam', '🇻🇳', '+84', 9, 'VND', 'zh', 1),
('YE', 'Yemen', '🇾🇪', '+967', 9, 'YER', 'zh', 1), ('ZM', 'Zambia', '🇿🇲', '+260', 9, 'ZMW', 'zh', 1),
('ZW', 'Zimbabwe', '🇿🇼', '+263', 9, 'ZWL', 'zh', 1);

-- ============================================================================
-- SECTION 3: USERS TABLE (with language preference)
-- ============================================================================

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone_number` VARCHAR(20) NOT NULL,
  `phone_country_code` CHAR(2) DEFAULT 'RW',
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  
  -- Geographic & Currency Preferences
  `detected_country_code` CHAR(2) DEFAULT NULL,
  `verified_country_code` CHAR(2) DEFAULT NULL,
  `display_currency_code` CHAR(3) DEFAULT 'RWF',
  
  -- Language Preference (Default: Chinese)
  `preferred_language_code` CHAR(2) DEFAULT 'zh',
  
  -- Referral Tracking
  `referred_by_user_id` INT UNSIGNED DEFAULT NULL,
  `used_referral_code` CHAR(6) DEFAULT NULL,
  
  -- Audio/Voice Preferences
  `enable_voice_announcements` TINYINT(1) DEFAULT 1,
  `enable_sound_effects` TINYINT(1) DEFAULT 1,
  
  -- Account Status (includes restriction status)
  `status` ENUM('PENDING', 'ACTIVE', 'SUSPENDED', 'REJECTED', 'REPAYMENT_DEFAULT_SUPPORT_REVIEW') DEFAULT 'PENDING',
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `phone_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_username` (`username`),
  UNIQUE KEY `unique_email` (`email`),
  UNIQUE KEY `unique_phone` (`phone_number`),
  KEY `idx_referred_by` (`referred_by_user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_language` (`preferred_language_code`),
  
  FOREIGN KEY (`phone_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`verified_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`display_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`preferred_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 4: MM MEMBERSHIP LEVELS (MM-M1 through MM-M7)
-- ============================================================================

CREATE TABLE `mm_levels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level_code` VARCHAR(10) NOT NULL,
  `level_name` VARCHAR(64) NOT NULL,
  `product_amount` DECIMAL(18, 4) NOT NULL,
  `product_currency_code` CHAR(3) NOT NULL DEFAULT 'RWF',
  `daily_earning_amount` DECIMAL(18, 4) NOT NULL,
  `earning_currency_code` CHAR(3) NOT NULL DEFAULT 'RWF',
  `principal_withdrawable` TINYINT(1) DEFAULT 0,
  `status` ENUM('OPEN', 'LOCKED', 'CONFIGURING', 'RETIRED') DEFAULT 'LOCKED',
  `allowed_earning_periods` JSON DEFAULT NULL,
  `description_en` TEXT DEFAULT NULL,
  `description_zh` TEXT DEFAULT NULL,
  `description_rw` TEXT DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_level_code` (`level_code`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`product_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`earning_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed MM levels per specification
INSERT INTO `mm_levels` (`level_code`, `level_name`, `product_amount`, `product_currency_code`, `daily_earning_amount`, `earning_currency_code`, `principal_withdrawable`, `status`, `allowed_earning_periods`, `description_en`, `description_zh`, `description_rw`) VALUES
('MM-M1', 'MM Membership Level 1', 10000.0000, 'RWF', 275.0000, 'RWF', 0, 'OPEN', 
 '["30", "90", "180", "365"]',
 'Entry-level MM membership with 275 RWF daily earnings.',
 '入门级MM会员,每日收益275卢旺达法郎。',
 'Ubumwe bwa MM rw\'icyiciro cya mbere n\'inyungu ya 275 RWF buri munsi.'),
 
('MM-M2', 'MM Membership Level 2', 22000.0000, 'RWF', 675.0000, 'RWF', 0, 'OPEN',
 '["30", "90", "180", "365"]',
 'Standard MM membership with 675 RWF daily earnings.',
 '标准MM会员,每日收益675卢旺达法郎。',
 'Ubumwe bwa MM busanzwe n\'inyungu ya 675 RWF buri munsi.'),
 
('MM-M3', 'MM Membership Level 3', 90000.0000, 'RWF', 2612.0000, 'RWF', 0, 'LOCKED',
 '["30", "90", "180", "365"]',
 'Premium MM membership - Currently Unavailable.',
 '高级MM会员 - 暂不可用。',
 'Ubumwe bwa MM bwiza - Ntibiboneka ubu.'),
 
('MM-M4', 'MM Membership Level 4', 250000.0000, 'RWF', 7200.0000, 'RWF', 0, 'LOCKED',
 '["30", "90", "180", "365"]',
 'Elite MM membership - Currently Unavailable.',
 '精英MM会员 - 暂不可用。',
 'Ubumwe bwa MM bw\'indashyikirwa - Ntibiboneka ubu.'),
 
('MM-M5', 'MM Membership Level 5', 0.0000, 'RWF', 0.0000, 'RWF', 0, 'LOCKED',
 '["30", "90", "180", "365"]',
 'Coming Soon.', '即将推出。', 'Biraza Vuba.'),
 
('MM-M6', 'MM Membership Level 6', 0.0000, 'RWF', 0.0000, 'RWF', 0, 'LOCKED',
 '["30", "90", "180", "365"]',
 'Coming Soon.', '即将推出。', 'Biraza Vuba.'),
 
('MM-M7', 'MM Membership Level 7', 0.0000, 'RWF', 0.0000, 'RWF', 0, 'LOCKED',
 '["30", "90", "180", "365"]',
 'Coming Soon.', '即将推出。', 'Biraza Vuba.');

-- ============================================================================
-- SECTION 5: MM SUBSCRIPTIONS (Active customer MM products)
-- ============================================================================

CREATE TABLE `mm_subscriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `mm_level_id` INT UNSIGNED NOT NULL,
  `subscription_reference` VARCHAR(32) NOT NULL,
  
  -- Principal (LOCKED - cannot be withdrawn)
  `principal_amount` DECIMAL(18, 4) NOT NULL,
  `principal_currency_code` CHAR(3) NOT NULL,
  
  -- Earnings tracking (separate from principal)
  `daily_earning_amount` DECIMAL(18, 4) NOT NULL,
  `earning_currency_code` CHAR(3) NOT NULL,
  `selected_earning_period_days` INT UNSIGNED NOT NULL,
  
  -- Financial balances (MUST remain separate)
  `locked_principal_balance` DECIMAL(18, 4) NOT NULL,
  `projected_earnings` DECIMAL(18, 4) DEFAULT 0.0000,
  `accrued_earnings` DECIMAL(18, 4) DEFAULT 0.0000,
  `credited_earnings` DECIMAL(18, 4) DEFAULT 0.0000,
  `withdrawn_earnings` DECIMAL(18, 4) DEFAULT 0.0000,
  `withdrawable_earnings` DECIMAL(18, 4) DEFAULT 0.0000,
  
  -- Timeline
  `earning_start_date` TIMESTAMP NULL DEFAULT NULL,
  `earning_end_date` TIMESTAMP NULL DEFAULT NULL,
  `last_earning_posted_date` DATE DEFAULT NULL,
  `total_earning_days_posted` INT UNSIGNED DEFAULT 0,
  
  -- Status
  `status` ENUM('PENDING_ACTIVATION', 'ACTIVE', 'MATURED', 'SUSPENDED', 'CANCELLED', 'SETTLING') DEFAULT 'PENDING_ACTIVATION',
  `activation_conditions_met` TINYINT(1) DEFAULT 0,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_subscription_ref` (`subscription_reference`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_earning_end` (`earning_end_date`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`mm_level_id`) REFERENCES `mm_levels`(`id`) ON UPDATE CASCADE,
  FOREIGN KEY (`principal_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`earning_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 6: DAILY EARNINGS LEDGER
-- ============================================================================

CREATE TABLE `mm_earnings_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subscription_id` BIGINT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `earning_date` DATE NOT NULL,
  `earning_amount` DECIMAL(18, 4) NOT NULL,
  `currency_code` CHAR(3) NOT NULL,
  
  -- Status tracking
  `status` ENUM('PROJECTED', 'ACCRUED', 'CREDITED', 'WITHDRAWN', 'REVERSED') DEFAULT 'PROJECTED',
  `credited_at` TIMESTAMP NULL DEFAULT NULL,
  `withdrawn_at` TIMESTAMP NULL DEFAULT NULL,
  `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_subscription_date` (`subscription_id`, `earning_date`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_earning_date` (`earning_date`),
  KEY `idx_status` (`status`),
  
  FOREIGN KEY (`subscription_id`) REFERENCES `mm_subscriptions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 7: LOANS (13-day repayment + 5-day grace period)
-- ============================================================================

CREATE TABLE `loans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `loan_reference` VARCHAR(32) NOT NULL,
  `mm_subscription_id` BIGINT UNSIGNED DEFAULT NULL,
  
  -- Loan amounts
  `principal_amount` DECIMAL(18, 4) NOT NULL,
  `currency_code` CHAR(3) NOT NULL,
  `outstanding_principal` DECIMAL(18, 4) NOT NULL,
  
  -- 13-day repayment deadline
  `disbursement_date` TIMESTAMP NOT NULL,
  `repayment_start_date` TIMESTAMP NOT NULL,
  `repayment_due_date` TIMESTAMP NOT NULL,
  `repayment_period_days` INT UNSIGNED DEFAULT 13,
  
  -- 5-day grace period with 6% charge
  `grace_period_start_date` TIMESTAMP NULL DEFAULT NULL,
  `grace_period_end_date` TIMESTAMP NULL DEFAULT NULL,
  `grace_period_days` INT UNSIGNED DEFAULT 5,
  `grace_period_charge_rate` DECIMAL(5, 2) DEFAULT 6.00,
  `grace_period_charge_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  `grace_charge_paid_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  
  -- Repayment tracking
  `total_repaid_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  `remaining_balance` DECIMAL(18, 4) NOT NULL,
  
  -- Status
  `status` ENUM('PENDING_DISBURSEMENT', 'ACTIVE', 'IN_GRACE_PERIOD', 'REPAID', 'DEFAULTED', 'UNDER_REVIEW', 'RESTRUCTURED') DEFAULT 'PENDING_DISBURSEMENT',
  `fully_repaid_at` TIMESTAMP NULL DEFAULT NULL,
  `defaulted_at` TIMESTAMP NULL DEFAULT NULL,
  `restriction_case_id` BIGINT UNSIGNED DEFAULT NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_loan_ref` (`loan_reference`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`repayment_due_date`),
  KEY `idx_grace_end` (`grace_period_end_date`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`mm_subscription_id`) REFERENCES `mm_subscriptions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_repayments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_id` BIGINT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `repayment_reference` VARCHAR(32) NOT NULL,
  
  `repayment_amount` DECIMAL(18, 4) NOT NULL,
  `currency_code` CHAR(3) NOT NULL,
  `repayment_type` ENUM('PRINCIPAL', 'GRACE_CHARGE', 'INTEREST', 'FEE', 'PARTIAL_SETTLEMENT', 'FULL_SETTLEMENT') NOT NULL,
  
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `payment_provider` VARCHAR(50) DEFAULT NULL,
  `external_transaction_id` VARCHAR(128) DEFAULT NULL,
  
  `is_confirmed` TINYINT(1) DEFAULT 0,
  `confirmed_at` TIMESTAMP NULL DEFAULT NULL,
  
  `status` ENUM('PENDING', 'CONFIRMED', 'FAILED', 'REVERSED') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_repayment_ref` (`repayment_reference`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  
  FOREIGN KEY (`loan_id`) REFERENCES `loans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 8: ACCOUNT RESTRICTIONS & SUPPORT CASES
-- ============================================================================

CREATE TABLE `account_restrictions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `loan_id` BIGINT UNSIGNED DEFAULT NULL,
  `restriction_type` ENUM('REPAYMENT_DEFAULT', 'FRAUD_SUSPECTED', 'COMPLIANCE_REVIEW', 'SUSPICIOUS_ACTIVITY', 'ADMIN_OVERRIDE') NOT NULL,
  `restriction_code` VARCHAR(32) NOT NULL,
  `reason_code` VARCHAR(100) NOT NULL,
  `reason_details` TEXT DEFAULT NULL,
  
  -- What triggered the restriction
  `triggered_by` ENUM('AUTOMATED_SYSTEM', 'ADMIN_USER', 'COMPLIANCE_OFFICER') DEFAULT 'AUTOMATED_SYSTEM',
  `triggered_by_user_id` INT UNSIGNED DEFAULT NULL,
  
  -- Outstanding amounts at time of restriction
  `outstanding_principal` DECIMAL(18, 4) DEFAULT NULL,
  `outstanding_grace_charge` DECIMAL(18, 4) DEFAULT NULL,
  `total_outstanding` DECIMAL(18, 4) DEFAULT NULL,
  `currency_code` CHAR(3) DEFAULT NULL,
  
  -- Timeline
  `original_due_date` TIMESTAMP NULL DEFAULT NULL,
  `grace_period_end_date` TIMESTAMP NULL DEFAULT NULL,
  `restricted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` TIMESTAMP NULL DEFAULT NULL,
  
  -- Resolution
  `status` ENUM('ACTIVE', 'UNDER_REVIEW', 'RESOLVED', 'LIFTED', 'ESCALATED') DEFAULT 'ACTIVE',
  `resolution_notes` TEXT DEFAULT NULL,
  `resolved_by_user_id` INT UNSIGNED DEFAULT NULL,
  `support_case_id` BIGINT UNSIGNED DEFAULT NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_restriction_code` (`restriction_code`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_status` (`status`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`loan_id`) REFERENCES `loans`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_cases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_reference` VARCHAR(32) NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `restriction_id` BIGINT UNSIGNED DEFAULT NULL,
  `loan_id` BIGINT UNSIGNED DEFAULT NULL,
  
  `case_type` ENUM('REPAYMENT_DISPUTE', 'PAYMENT_CONFIRMATION', 'ACCOUNT_RESTRICTION', 'FRAUD_REPORT', 'GENERAL_INQUIRY', 'TECHNICAL_ISSUE') NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `priority` ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT') DEFAULT 'MEDIUM',
  
  `status` ENUM('OPEN', 'IN_PROGRESS', 'WAITING_CUSTOMER', 'RESOLVED', 'CLOSED', 'ESCALATED') DEFAULT 'OPEN',
  `assigned_to_user_id` INT UNSIGNED DEFAULT NULL,
  
  `customer_language_code` CHAR(2) DEFAULT 'zh',
  `customer_provided_evidence` JSON DEFAULT NULL,
  
  `opened_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_activity_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` TIMESTAMP NULL DEFAULT NULL,
  `closed_at` TIMESTAMP NULL DEFAULT NULL,
  
  `resolution_summary` TEXT DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_case_ref` (`case_reference`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`restriction_id`) REFERENCES `account_restrictions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`loan_id`) REFERENCES `loans`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`customer_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_case_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` BIGINT UNSIGNED NOT NULL,
  `sender_type` ENUM('CUSTOMER', 'AGENT', 'SYSTEM', 'ADMIN') NOT NULL,
  `sender_id` INT UNSIGNED DEFAULT NULL,
  `message_content` TEXT NOT NULL,
  `message_language_code` CHAR(2) DEFAULT 'zh',
  `attachments` JSON DEFAULT NULL,
  `is_internal_note` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_case_id` (`case_id`),
  FOREIGN KEY (`case_id`) REFERENCES `support_cases`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`message_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 9: REFERRAL CODES & BONUSES (B1=6%, B2=3%, B3=1%)
-- ============================================================================

CREATE TABLE `referral_codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `referral_code` CHAR(6) NOT NULL,
  `times_used` INT UNSIGNED DEFAULT 0,
  `max_uses` INT UNSIGNED DEFAULT 100,
  `is_active` TINYINT(1) DEFAULT 1,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_referral_code` (`referral_code`),
  UNIQUE KEY `unique_user_code` (`user_id`),
  CONSTRAINT `chk_referral_code_format` CHECK (`referral_code` REGEXP '^[0-9]{6}$'),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referral_tiers` (
  `tier_code` VARCHAR(5) NOT NULL,
  `tier_name` VARCHAR(64) NOT NULL,
  `bonus_percentage` DECIMAL(5, 2) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`tier_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `referral_tiers` (`tier_code`, `tier_name`, `bonus_percentage`, `description`) VALUES
('B1', 'Tier 1 Referral Bonus', 6.00, 'First-level referral bonus - 6% of qualifying amount'),
('B2', 'Tier 2 Referral Bonus', 3.00, 'Second-level referral bonus - 3% of qualifying amount'),
('B3', 'Tier 3 Referral Bonus', 1.00, 'Third-level referral bonus - 1% of qualifying amount');

CREATE TABLE `referral_bonuses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `beneficiary_user_id` INT UNSIGNED NOT NULL,
  `referred_user_id` INT UNSIGNED NOT NULL,
  `qualifying_loan_id` BIGINT UNSIGNED NOT NULL,
  `referral_code_id` INT UNSIGNED DEFAULT NULL,
  `tier_code` VARCHAR(5) NOT NULL,
  `bonus_reference` VARCHAR(32) NOT NULL,
  
  -- Calculation basis (NEVER the grace-period charge unless explicitly approved)
  `calculation_basis_amount` DECIMAL(18, 4) NOT NULL,
  `bonus_percentage` DECIMAL(5, 2) NOT NULL,
  `bonus_amount` DECIMAL(18, 4) NOT NULL,
  `currency_code` CHAR(3) NOT NULL,
  
  -- Eligibility conditions
  `loan_fully_repaid` TINYINT(1) DEFAULT 0,
  `loan_repaid_at` TIMESTAMP NULL DEFAULT NULL,
  `eligibility_verified` TINYINT(1) DEFAULT 0,
  `fraud_check_passed` TINYINT(1) DEFAULT 0,
  
  -- Status
  `status` ENUM('PENDING_VERIFICATION', 'ELIGIBLE', 'CREDITED', 'REVERSED', 'INELIGIBLE') DEFAULT 'PENDING_VERIFICATION',
  `credited_at` TIMESTAMP NULL DEFAULT NULL,
  `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_bonus_ref` (`bonus_reference`),
  UNIQUE KEY `unique_user_loan_tier` (`beneficiary_user_id`, `qualifying_loan_id`, `tier_code`),
  KEY `idx_beneficiary` (`beneficiary_user_id`),
  KEY `idx_referred` (`referred_user_id`),
  KEY `idx_loan` (`qualifying_loan_id`),
  KEY `idx_status` (`status`),
  
  FOREIGN KEY (`beneficiary_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`qualifying_loan_id`) REFERENCES `loans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referral_code_id`) REFERENCES `referral_codes`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`tier_code`) REFERENCES `referral_tiers`(`tier_code`) ON UPDATE CASCADE,
  FOREIGN KEY (`currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referral_usage` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `referral_code_id` INT UNSIGNED NOT NULL,
  `referrer_user_id` INT UNSIGNED NOT NULL,
  `referred_user_id` INT UNSIGNED NOT NULL,
  `referral_code` CHAR(6) NOT NULL,
  `status` ENUM('PENDING', 'CREDITED', 'REVERSED', 'EXPIRED') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_referred_user` (`referred_user_id`),
  KEY `idx_referrer` (`referrer_user_id`),
  FOREIGN KEY (`referral_code_id`) REFERENCES `referral_codes`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referrer_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 10: AUTHENTICATION & SECURITY TABLES
-- ============================================================================

CREATE TABLE `login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `detected_country_code` CHAR(2) DEFAULT NULL,
  `detected_language_code` CHAR(2) DEFAULT 'zh',
  `status` ENUM('SUCCESS', 'FAILED', 'LOCKED', 'BLOCKED') NOT NULL DEFAULT 'FAILED',
  `failure_reason` VARCHAR(100) DEFAULT NULL,
  `device_fingerprint` VARCHAR(128) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_phone_number` (`phone_number`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`detected_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`detected_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `session_token` VARCHAR(128) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `country_code` CHAR(2) DEFAULT NULL,
  `language_code` CHAR(2) DEFAULT 'zh',
  `device_type` ENUM('WEB', 'MOBILE', 'TABLET', 'API') DEFAULT 'WEB',
  `device_fingerprint` VARCHAR(128) DEFAULT NULL,
  `last_activity_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_session_token` (`session_token`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_expires_at` (`expires_at`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `registration_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `detected_country_code` CHAR(2) DEFAULT NULL,
  `selected_country_code` CHAR(2) DEFAULT NULL,
  `detected_language_code` CHAR(2) DEFAULT 'zh',
  `referral_code_used` CHAR(6) DEFAULT NULL,
  `status` ENUM('SUCCESS', 'FAILED', 'PENDING_VERIFICATION', 'BLOCKED') NOT NULL DEFAULT 'FAILED',
  `failure_reason` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_status` (`status`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`detected_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`selected_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`detected_language_code`) REFERENCES `languages`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `security_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `detected_country_code` CHAR(2) DEFAULT NULL,
  `is_vpn_detected` TINYINT(1) DEFAULT 0,
  `is_proxy_detected` TINYINT(1) DEFAULT 0,
  `action` VARCHAR(50) NOT NULL,
  `status` ENUM('ALLOWED', 'BLOCKED', 'FLAGGED') DEFAULT 'ALLOWED',
  `details` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_action` (`action`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`detected_country_code`) REFERENCES `countries`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 11: BANKING LEDGER (Strict Actual vs Display Separation)
-- ============================================================================

CREATE TABLE `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `account_type` ENUM('MAIN_WALLET', 'SAVINGS', 'FIXED_DEPOSIT', 'INVESTMENT', 'LOAN', 'MM_LOCKED_PRINCIPAL', 'MM_EARNINGS') NOT NULL,
  `account_currency_code` CHAR(3) NOT NULL,
  `actual_balance` DECIMAL(18, 4) NOT NULL DEFAULT 0.0000,
  `is_locked` TINYINT(1) DEFAULT 0,
  `lock_reason` VARCHAR(100) DEFAULT NULL,
  `linked_subscription_id` BIGINT UNSIGNED DEFAULT NULL,
  `status` ENUM('ACTIVE', 'FROZEN', 'CLOSED', 'RESTRICTED') DEFAULT 'ACTIVE',
  `opened_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`account_type`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`account_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`linked_subscription_id`) REFERENCES `mm_subscriptions`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `transaction_reference` VARCHAR(64) NOT NULL,
  `transaction_type` ENUM('DEPOSIT', 'WITHDRAWAL', 'TRANSFER_OUT', 'TRANSFER_IN', 'PAYMENT', 'LOAN_DISBURSEMENT', 'LOAN_REPAYMENT', 'INTEREST_CREDIT', 'CURRENCY_EXCHANGE', 'REFERRAL_REWARD', 'DAILY_EARNING_CREDIT', 'GRACE_PERIOD_CHARGE', 'FEE', 'REVERSAL') NOT NULL,
  `transaction_currency_code` CHAR(3) NOT NULL,
  `amount` DECIMAL(18, 4) NOT NULL,
  `running_balance` DECIMAL(18, 4) DEFAULT NULL,
  `exchange_rate_applied` DECIMAL(18, 6) DEFAULT NULL,
  `fee_amount` DECIMAL(18, 4) DEFAULT 0.0000,
  `fee_currency_code` CHAR(3) DEFAULT NULL,
  `related_loan_id` BIGINT UNSIGNED DEFAULT NULL,
  `related_subscription_id` BIGINT UNSIGNED DEFAULT NULL,
  `related_bonus_id` BIGINT UNSIGNED DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `status` ENUM('PENDING', 'COMPLETED', 'FAILED', 'REVERSED') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_transaction_ref` (`transaction_reference`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_type` (`transaction_type`),
  
  FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`transaction_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`fee_currency_code`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`related_loan_id`) REFERENCES `loans`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`related_subscription_id`) REFERENCES `mm_subscriptions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`related_bonus_id`) REFERENCES `referral_bonuses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `sender_account_id` INT UNSIGNED NOT NULL,
  `recipient_account_id` INT UNSIGNED NOT NULL,
  `sender_currency` CHAR(3) NOT NULL,
  `sender_amount` DECIMAL(18, 4) NOT NULL,
  `recipient_currency` CHAR(3) NOT NULL,
  `recipient_amount` DECIMAL(18, 4) NOT NULL,
  `exchange_rate` DECIMAL(18, 6) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_transaction` (`transaction_id`),
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`recipient_account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_currency`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE,
  FOREIGN KEY (`recipient_currency`) REFERENCES `currencies`(`code`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 12: EXCHANGE RATES (Cached from ExchangeRate-API)
-- ============================================================================

CREATE TABLE `exchange_rates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `base_currency` CHAR(3) NOT NULL DEFAULT 'USD',
  `target_currency` CHAR(3) NOT NULL,
  `rate` DECIMAL(18, 6) NOT NULL,
  `source` VARCHAR(255) NOT NULL DEFAULT 'https://open.er-api.com/v6/latest/USD',
  `fetched_at` DATETIME NOT NULL,
  `valid_until` DATETIME NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_rate_pair` (`base_currency`, `target_currency`),
  KEY `idx_target_currency` (`target_currency`),
  KEY `idx_fetched_at` (`fetched_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `exchange_rates` (`base_currency`, `target_currency`, `rate`, `source`, `fetched_at`, `valid_until`, `is_active`) VALUES
('USD', 'USD', 1.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AED', 3.672500, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AFN', 64.990392, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ALL', 80.815106, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AMD', 361.277600, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ANG', 1.790000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AOA', 926.100188, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ARS', 1528.263000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AUD', 1.423077, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AWG', 1.790000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'AZN', 1.700372, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BAM', 1.745827, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BBD', 2.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BDT', 123.715528, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BGN', 1.745827, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BHD', 0.376000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BIF', 3005.348277, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BMD', 1.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BND', 1.278066, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BOB', 11.742022, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BRL', 5.044304, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BSD', 1.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BTN', 95.001009, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BWP', 14.146152, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BYN', 2.942320, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'BZD', 2.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CAD', 1.400390, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CDF', 2308.226163, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CHF', 0.824784, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CLF', 0.024913, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CLP', 984.715304, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CNH', 6.669855, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CNY', 6.689203, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'COP', 3180.084041, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CRC', 456.121487, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CUP', 24.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CVE', 98.425551, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'CZK', 21.710808, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'DJF', 177.721000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'DKK', 6.676237, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'DOP', 61.415139, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'DZD', 134.643627, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'EGP', 55.281789, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ERN', 15.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ETB', 161.874143, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'EUR', 0.892628, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'FJD', 2.251005, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'FKP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'FOK', 6.676236, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GBP', 0.747680, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GEL', 2.596699, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GGP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GHS', 11.671550, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GIP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GMD', 74.653333, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GNF', 8841.676166, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GTQ', 7.645339, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'GYD', 209.315128, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'HKD', 7.842415, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'HNL', 26.858058, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'HRK', 6.725501, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'HTG', 130.985495, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'HUF', 327.137353, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'IDR', 18086.998885, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ILS', 3.110754, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'IMP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'INR', 95.001468, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'IQD', 1517.423635, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'IRR', 1701454.689748, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ISK', 121.721785, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'JEP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'JMD', 158.883717, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'JOD', 0.709000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'JPY', 156.842558, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KES', 129.836385, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KGS', 87.492992, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KHR', 4059.847453, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KID', 1.423075, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KMF', 439.143852, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KPW', 1.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KRW', 1324.821047, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KWD', 0.310220, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KYD', 0.833333, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'KZT', 449.766656, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LAK', 22295.314693, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LBP', 89500.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LKR', 332.009545, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LRD', 170.612376, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LSL', 16.583277, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'LYD', 6.417416, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MAD', 9.812920, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MDL', 18.003495, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MGA', 4581.724789, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MKD', 54.980595, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MMK', 2099.103565, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MNT', 3583.186215, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MOP', 8.077687, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MRU', 40.126167, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MUR', 47.103706, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MVR', 15.451643, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MWK', 1743.830015, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MXN', 18.682584, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MYR', 4.084377, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'MZN', 63.721943, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NAD', 16.583277, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NGN', 1325.718117, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NIO', 36.819023, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NOK', 9.331084, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NPR', 152.001614, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'NZD', 1.755731, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'OMR', 0.384497, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PAB', 1.000000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PEN', 3.443192, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PGK', 4.474845, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PHP', 63.634662, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PKR', 276.820884, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PLN', 3.927790, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'PYG', 5735.165294, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'QAR', 3.640000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'RON', 4.774910, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'RSD', 104.901894, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'RUB', 81.140284, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'RWF', 1478.260636, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SAR', 3.750000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SBD', 7.951376, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SCR', 13.933208, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SDG', 509.255713, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SEK', 9.932479, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SGD', 1.278067, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SHP', 0.747679, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SLE', 22.971438, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SLL', 22971.438021, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SOS', 571.696572, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SRD', 37.679164, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SSP', 5750.641265, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'STN', 21.869369, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SYP', 121.903685, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'SZL', 16.583277, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'THB', 33.756940, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TJS', 9.215214, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TMT', 3.500379, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TND', 3.001259, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TOP', 2.384339, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TRY', 49.741756, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TTD', 6.776037, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TVD', 1.423075, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TWD', 32.584288, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'TZS', 2640.504808, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'UAH', 45.198690, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'UGX', 4113.321463, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'UYU', 40.154499, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'UZS', 11871.207242, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'VES', 887.302000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'VND', 25817.343746, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'VUV', 118.497563, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'WST', 2.730416, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XAF', 585.525137, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XCD', 2.700000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XCG', 1.790000, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XDR', 0.738129, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XOF', 585.525137, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'XPF', 106.519003, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'YER', 236.193948, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ZAR', 16.583315, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ZMW', 20.148531, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ZWG', 26.577500, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1),
('USD', 'ZWL', 26.577500, 'https://open.er-api.com/v6/latest/USD', '2026-10-25 00:02:31', '2026-10-26 00:18:21', 1);

-- ============================================================================
-- SECTION 13: SYSTEM SETTINGS & AUDIT LOGS
-- ============================================================================

CREATE TABLE `system_settings` (
  `setting_key` VARCHAR(50) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('api_provider', 'https://open.er-api.com/v6/latest/USD'),
('last_rate_update_unix', '1792886551'),
('next_rate_update_unix', '1792973901'),
('max_rate_age_hours', '24'),
('platform_name', 'TSB Bank'),
('default_language', 'zh'),
('supported_languages', '["zh", "en", "rw"]'),
('referral_code_length', '6'),
('max_referral_uses_per_code', '100'),
('loan_repayment_period_days', '13'),
('grace_period_days', '5'),
('grace_period_charge_rate_percent', '6.00'),
('mm_m1_amount', '10000'),
('mm_m1_daily_earning', '275'),
('mm_m2_amount', '22000'),
('mm_m2_daily_earning', '675'),
('mm_m3_amount', '90000'),
('mm_m3_daily_earning', '2612'),
('mm_m4_amount', '250000'),
('mm_m4_daily_earning', '7200'),
('referral_b1_rate_percent', '6.00'),
('referral_b2_rate_percent', '3.00'),
('referral_b3_rate_percent', '1.00');

CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `action_type` VARCHAR(100) NOT NULL,
  `entity_type` VARCHAR(50) DEFAULT NULL,
  `entity_id` BIGINT UNSIGNED DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `performed_by` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_action_type` (`action_type`),
  KEY `idx_entity` (`entity_type`, `entity_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECTION 14: VIEWS FOR SAFE DISPLAY
-- ============================================================================

CREATE OR REPLACE VIEW `v_account_balances_with_display` AS
SELECT 
    a.id AS account_id,
    a.user_id,
    a.account_type,
    a.account_currency_code AS actual_currency,
    a.actual_balance,
    a.is_locked,
    u.display_currency_code,
    u.preferred_language_code,
    ROUND(a.actual_balance * (rate_target.rate / rate_source.rate), 2) AS estimated_display_balance,
    rate_target.rate AS display_exchange_rate,
    rate_source.rate AS source_exchange_rate,
    rate_target.fetched_at AS rate_last_updated,
    CASE 
        WHEN rate_target.fetched_at < NOW() - INTERVAL 24 HOUR THEN 'STALE'
        ELSE 'VALID'
    END AS rate_freshness_status
FROM `accounts` a
JOIN `users` u ON a.user_id = u.id
JOIN `exchange_rates` rate_source 
    ON rate_source.target_currency = a.account_currency_code 
    AND rate_source.base_currency = 'USD' 
    AND rate_source.is_active = 1
JOIN `exchange_rates` rate_target 
    ON rate_target.target_currency = u.display_currency_code 
    AND rate_target.base_currency = 'USD' 
    AND rate_target.is_active = 1;

CREATE OR REPLACE VIEW `v_loan_status_summary` AS
SELECT 
    l.id AS loan_id,
    l.loan_reference,
    l.user_id,
    l.principal_amount,
    l.currency_code,
    l.outstanding_principal,
    l.repayment_due_date,
    l.grace_period_end_date,
    l.grace_period_charge_amount,
    l.grace_charge_paid_amount,
    l.total_repaid_amount,
    l.remaining_balance,
    l.status,
    u.preferred_language_code,
    CASE 
        WHEN l.status = 'ACTIVE' AND l.repayment_due_date < NOW() THEN 'IN_GRACE_PERIOD'
        WHEN l.status = 'ACTIVE' AND l.grace_period_end_date < NOW() THEN 'DEFAULTED'
        ELSE l.status
    END AS computed_status,
    TIMESTAMPDIFF(HOUR, NOW(), l.repayment_due_date) AS hours_until_due,
    TIMESTAMPDIFF(HOUR, NOW(), l.grace_period_end_date) AS hours_until_grace_end
FROM `loans` l
JOIN `users` u ON l.user_id = u.id;

CREATE OR REPLACE VIEW `v_mm_subscription_summary` AS
SELECT 
    s.id AS subscription_id,
    s.subscription_reference,
    s.user_id,
    l.level_code,
    l.level_name,
    s.principal_amount,
    s.principal_currency_code,
    s.daily_earning_amount,
    s.earning_currency_code,
    s.selected_earning_period_days,
    s.locked_principal_balance,
    s.accrued_earnings,
    s.credited_earnings,
    s.withdrawable_earnings,
    s.withdrawn_earnings,
    s.earning_start_date,
    s.earning_end_date,
    s.status,
    u.preferred_language_code,
    DATEDIFF(s.earning_end_date, CURDATE()) AS days_remaining
FROM `mm_subscriptions` s
JOIN `mm_levels` l ON s.mm_level_id = l.id
JOIN `users` u ON s.user_id = u.id;

-- ============================================================================
-- SECTION 15: TRIGGERS & STORED PROCEDURES
-- ============================================================================

DELIMITER $$

-- Auto-generate 6-digit referral code when a new user registers
CREATE TRIGGER `trg_after_user_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    DECLARE v_code CHAR(6);
    DECLARE v_exists INT DEFAULT 1;
    
    WHILE v_exists > 0 DO
        SET v_code = LPAD(FLOOR(RAND() * 1000000), 6, '0');
        SELECT COUNT(*) INTO v_exists FROM `referral_codes` WHERE `referral_code` = v_code;
    END WHILE;
    
    INSERT INTO `referral_codes` (`user_id`, `referral_code`, `max_uses`, `is_active`)
    VALUES (NEW.id, v_code, 100, 1);
END$$

-- Auto-calculate loan due dates on creation (13 days + 5 days grace)
CREATE TRIGGER `trg_before_loan_insert`
BEFORE INSERT ON `loans`
FOR EACH ROW
BEGIN
    IF NEW.repayment_due_date IS NULL THEN
        SET NEW.repayment_due_date = DATE_ADD(NEW.disbursement_date, INTERVAL 13 DAY);
    END IF;
    IF NEW.grace_period_start_date IS NULL THEN
        SET NEW.grace_period_start_date = NEW.repayment_due_date;
    END IF;
    IF NEW.grace_period_end_date IS NULL THEN
        SET NEW.grace_period_end_date = DATE_ADD(NEW.repayment_due_date, INTERVAL 5 DAY);
    END IF;
    IF NEW.grace_period_charge_amount = 0 AND NEW.principal_amount > 0 THEN
        SET NEW.grace_period_charge_amount = NEW.principal_amount * 0.06;
    END IF;
    IF NEW.outstanding_principal = 0 THEN
        SET NEW.outstanding_principal = NEW.principal_amount;
    END IF;
    IF NEW.remaining_balance = 0 THEN
        SET NEW.remaining_balance = NEW.principal_amount;
    END IF;
END$$

DELIMITER ;

-- Stored Procedure: Process daily earnings for all active MM subscriptions
DELIMITER $$

CREATE PROCEDURE `sp_process_daily_earnings`()
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_sub_id BIGINT UNSIGNED;
    DECLARE v_user_id INT UNSIGNED;
    DECLARE v_daily_amount DECIMAL(18,4);
    DECLARE v_currency CHAR(3);
    DECLARE v_end_date DATE;
    DECLARE v_last_posted DATE;
    
    DECLARE cur CURSOR FOR 
        SELECT id, user_id, daily_earning_amount, earning_currency_code, 
               DATE(earning_end_date), last_earning_posted_date
        FROM mm_subscriptions
        WHERE status = 'ACTIVE' 
          AND earning_start_date <= NOW()
          AND (earning_end_date IS NULL OR DATE(earning_end_date) >= CURDATE())
          AND (last_earning_posted_date IS NULL OR last_earning_posted_date < CURDATE());
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
    
    START TRANSACTION;
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_sub_id, v_user_id, v_daily_amount, v_currency, v_end_date, v_last_posted;
        IF v_done THEN LEAVE read_loop; END IF;
        
        -- Prevent duplicate posting
        IF NOT EXISTS (
            SELECT 1 FROM mm_earnings_ledger 
            WHERE subscription_id = v_sub_id AND earning_date = CURDATE()
        ) THEN
            INSERT INTO mm_earnings_ledger 
                (subscription_id, user_id, earning_date, earning_amount, currency_code, status)
            VALUES 
                (v_sub_id, v_user_id, CURDATE(), v_daily_amount, v_currency, 'ACCRUED');
            
            UPDATE mm_subscriptions 
            SET accrued_earnings = accrued_earnings + v_daily_amount,
                projected_earnings = projected_earnings + v_daily_amount,
                withdrawable_earnings = withdrawable_earnings + v_daily_amount,
                last_earning_posted_date = CURDATE(),
                total_earning_days_posted = total_earning_days_posted + 1
            WHERE id = v_sub_id;
        END IF;
    END LOOP;
    
    CLOSE cur;
    COMMIT;
END$$

DELIMITER ;

-- Stored Procedure: Check and apply loan repayment deadlines
DELIMITER $$

CREATE PROCEDURE `sp_process_loan_deadlines`()
BEGIN
    START TRANSACTION;
    
    -- Move active loans past due date into grace period
    UPDATE `loans`
    SET `status` = 'IN_GRACE_PERIOD',
        `grace_period_start_date` = COALESCE(`grace_period_start_date`, `repayment_due_date`)
    WHERE `status` = 'ACTIVE'
      AND `repayment_due_date` < NOW()
      AND (`grace_period_end_date` IS NULL OR `grace_period_end_date` >= NOW());
    
    -- Mark loans as defaulted after grace period
    UPDATE `loans`
    SET `status` = 'DEFAULTED',
        `defaulted_at` = NOW()
    WHERE `status` IN ('ACTIVE', 'IN_GRACE_PERIOD')
      AND `grace_period_end_date` < NOW()
      AND `remaining_balance` > 0;
    
    -- Restrict user accounts for defaulted loans
    INSERT INTO `account_restrictions` 
        (user_id, loan_id, restriction_type, restriction_code, reason_code, reason_details,
         triggered_by, outstanding_principal, outstanding_grace_charge, total_outstanding, currency_code,
         original_due_date, grace_period_end_date, status)
    SELECT 
        l.user_id, l.id, 'REPAYMENT_DEFAULT', 
        CONCAT('RES-', l.id, '-', UNIX_TIMESTAMP()),
        'LOAN_DEFAULT_AFTER_GRACE',
        CONCAT('Loan ', l.loan_reference, ' remains outstanding after 13-day repayment period and 5-day grace period.'),
        'AUTOMATED_SYSTEM',
        l.outstanding_principal, l.grace_period_charge_amount - l.grace_charge_paid_amount,
        l.remaining_balance + (l.grace_period_charge_amount - l.grace_charge_paid_amount),
        l.currency_code,
        l.repayment_due_date, l.grace_period_end_date, 'ACTIVE'
    FROM `loans` l
    WHERE l.status = 'DEFAULTED'
      AND NOT EXISTS (
          SELECT 1 FROM `account_restrictions` ar 
          WHERE ar.loan_id = l.id AND ar.status = 'ACTIVE'
      );
    
    -- Update user status to REPAYMENT_DEFAULT_SUPPORT_REVIEW
    UPDATE `users` u
    INNER JOIN `account_restrictions` ar ON ar.user_id = u.id AND ar.status = 'ACTIVE'
    SET u.status = 'REPAYMENT_DEFAULT_SUPPORT_REVIEW'
    WHERE u.status != 'REPAYMENT_DEFAULT_SUPPORT_REVIEW';
    
    COMMIT;
END$$

DELIMITER ;

-- Stored Procedure: Evaluate referral bonuses after loan repayment
DELIMITER $$

CREATE PROCEDURE `sp_evaluate_referral_bonuses`(IN p_loan_id BIGINT UNSIGNED)
BEGIN
    DECLARE v_user_id INT UNSIGNED;
    DECLARE v_referrer_id INT UNSIGNED;
    DECLARE v_principal DECIMAL(18,4);
    DECLARE v_currency CHAR(3);
    DECLARE v_bonus_ref VARCHAR(32);
    DECLARE v_b1_amount DECIMAL(18,4);
    DECLARE v_b2_amount DECIMAL(18,4);
    DECLARE v_b3_amount DECIMAL(18,4);
    
    -- Get loan details
    SELECT user_id, principal_amount, currency_code 
    INTO v_user_id, v_principal, v_currency
    FROM loans WHERE id = p_loan_id AND status = 'REPAID';
    
    IF v_user_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan not fully repaid or not found';
    END IF;
    
    -- Get referrer (B1 beneficiary)
    SELECT referred_by_user_id INTO v_referrer_id 
    FROM users WHERE id = v_user_id AND referred_by_user_id IS NOT NULL;
    
    IF v_referrer_id IS NOT NULL THEN
        -- B1: 6% to direct referrer
        SET v_b1_amount = v_principal * 0.06;
        SET v_bonus_ref = CONCAT('B1-', p_loan_id, '-', UNIX_TIMESTAMP());
        INSERT INTO referral_bonuses 
            (beneficiary_user_id, referred_user_id, qualifying_loan_id, tier_code, bonus_reference,
             calculation_basis_amount, bonus_percentage, bonus_amount, currency_code,
             loan_fully_repaid, loan_repaid_at, eligibility_verified, fraud_check_passed, status)
        VALUES 
            (v_referrer_id, v_user_id, p_loan_id, 'B1', v_bonus_ref,
             v_principal, 6.00, v_b1_amount, v_currency, 1, NOW(), 1, 1, 'ELIGIBLE');
    END IF;
    
    -- B2 and B3 would follow similar logic for higher-tier referrers
    -- (Implementation depends on multi-level referral chain structure)
END$$

DELIMITER ;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

COMMIT;