CREATE TABLE IF NOT EXISTS currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    rate DECIMAL(15,6) NOT NULL DEFAULT 1.000000,
    is_active TINYINT(1) DEFAULT 1,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO currencies (code, name, symbol, rate, is_active, is_default) VALUES
('BDT', 'Bangladeshi Taka',     '৳',  1.000000,  1, 1),
('USD', 'US Dollar',            '$',  0.009091,  1, 0),
('EUR', 'Euro',                 '€',  0.008403,  1, 0),
('GBP', 'British Pound',        '£',  0.007194,  1, 0),
('SAR', 'Saudi Riyal',          'SR', 0.034091,  1, 0),
('AED', 'UAE Dirham',           'د.إ',0.033397,  1, 0),
('MYR', 'Malaysian Ringgit',    'RM', 0.042254,  1, 0),
('KWD', 'Kuwaiti Dinar',        'KD', 0.002792,  1, 0),
('QAR', 'Qatari Riyal',         'QR', 0.033112,  1, 0),
('OMR', 'Omani Rial',           'OMR',0.003497,  1, 0),
('BHD', 'Bahraini Dinar',       'BD', 0.003427,  1, 0),
('INR', 'Indian Rupee',         '₹',  0.757576,  1, 0),
('PKR', 'Pakistani Rupee',      '₨',  2.531646,  1, 0),
('SGD', 'Singapore Dollar',     'S$', 0.012195,  1, 0),
('AUD', 'Australian Dollar',    'A$', 0.013966,  1, 0),
('CAD', 'Canadian Dollar',      'C$', 0.012579,  1, 0),
('JPY', 'Japanese Yen',         '¥',  1.388889,  1, 0),
('CNY', 'Chinese Yuan',         '¥',  0.065789,  1, 0),
('TRY', 'Turkish Lira',         '₺',  0.327869,  1, 0),
('NGN', 'Nigerian Naira',       '₦',  14.492754, 1, 0),
('KES', 'Kenyan Shilling',      'Ksh',1.176471,  1, 0),
('GHS', 'Ghanaian Cedi',        'GH₵',0.108696,  1, 0),
('ZAR', 'South African Rand',   'R',  0.166667,  1, 0),
('NPR', 'Nepalese Rupee',       '₨',  1.212121,  1, 0),
('LKR', 'Sri Lankan Rupee',     'Rs', 2.941176,  1, 0),
('PHP', 'Philippine Peso',      '₱',  0.534759,  1, 0),
('IDR', 'Indonesian Rupiah',    'Rp', 148.148148,1, 0),
('EGP', 'Egyptian Pound',       'E£', 0.454545,  1, 0),
('BRL', 'Brazilian Real',       'R$', 0.050505,  1, 0),
('MXN', 'Mexican Peso',         'MX$',0.180180,  1, 0)
ON DUPLICATE KEY UPDATE name=VALUES(name), symbol=VALUES(symbol);

-- Add preferred_currency to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS preferred_currency VARCHAR(10) DEFAULT 'BDT';
