-- Business Countries Table Schema
-- Comprehensive country information for dynamic country selection

SET FOREIGN_KEY_CHECKS = 0;

-- BUSINESS COUNTRIES TABLE
CREATE TABLE IF NOT EXISTS `business_country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  `country_code` varchar(3) NOT NULL COMMENT 'ISO 3166-1 alpha-3 country code',
  `country_code_2` varchar(2) NOT NULL COMMENT 'ISO 3166-1 alpha-2 country code',
  `isd_code` varchar(10) NOT NULL COMMENT 'International Subscriber Dialing code',
  `timezone` varchar(50) NOT NULL COMMENT 'Primary timezone for the country',
  `currency_code` varchar(3) NOT NULL COMMENT 'ISO 4217 currency code',
  `currency_name` varchar(50) NOT NULL COMMENT 'Full currency name',
  `currency_symbol` varchar(10) NOT NULL COMMENT 'Currency symbol',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `display_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_id`),
  UNIQUE KEY `unique_country_code` (`country_code`),
  UNIQUE KEY `unique_country_code_2` (`country_code_2`),
  INDEX `idx_country_name` (`country_name`),
  INDEX `idx_is_active` (`is_active`),
  INDEX `idx_display_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert comprehensive country data
INSERT INTO `business_country` 
(`country_name`, `country_code`, `country_code_2`, `isd_code`, `timezone`, `currency_code`, `currency_name`, `currency_symbol`, `is_active`, `display_order`) VALUES
-- Asia Pacific
('Philippines', 'PHL', 'PH', '+63', 'Asia/Manila', 'PHP', 'Philippine Peso', '₱', 1, 1),
('Singapore', 'SGP', 'SG', '+65', 'Asia/Singapore', 'SGD', 'Singapore Dollar', 'S$', 1, 2),
('Malaysia', 'MYS', 'MY', '+60', 'Asia/Kuala_Lumpur', 'MYR', 'Malaysian Ringgit', 'RM', 1, 3),
('Indonesia', 'IDN', 'ID', '+62', 'Asia/Jakarta', 'IDR', 'Indonesian Rupiah', 'Rp', 1, 4),
('Thailand', 'THA', 'TH', '+66', 'Asia/Bangkok', 'THB', 'Thai Baht', '฿', 1, 5),
('Vietnam', 'VNM', 'VN', '+84', 'Asia/Ho_Chi_Minh', 'VND', 'Vietnamese Dong', '₫', 1, 6),
('Japan', 'JPN', 'JP', '+81', 'Asia/Tokyo', 'JPY', 'Japanese Yen', '¥', 1, 7),
('South Korea', 'KOR', 'KR', '+82', 'Asia/Seoul', 'KRW', 'South Korean Won', '₩', 1, 8),
('China', 'CHN', 'CN', '+86', 'Asia/Shanghai', 'CNY', 'Chinese Yuan', '¥', 1, 9),
('India', 'IND', 'IN', '+91', 'Asia/Kolkata', 'INR', 'Indian Rupee', '₹', 1, 10),
('Australia', 'AUS', 'AU', '+61', 'Australia/Sydney', 'AUD', 'Australian Dollar', 'A$', 1, 11),
('New Zealand', 'NZL', 'NZ', '+64', 'Pacific/Auckland', 'NZD', 'New Zealand Dollar', 'NZ$', 1, 12),

-- North America
('United States', 'USA', 'US', '+1', 'America/New_York', 'USD', 'US Dollar', '$', 1, 13),
('Canada', 'CAN', 'CA', '+1', 'America/Toronto', 'CAD', 'Canadian Dollar', 'C$', 1, 14),
('Mexico', 'MEX', 'MX', '+52', 'America/Mexico_City', 'MXN', 'Mexican Peso', '$', 1, 15),

-- Europe
('United Kingdom', 'GBR', 'GB', '+44', 'Europe/London', 'GBP', 'British Pound', '£', 1, 16),
('Germany', 'DEU', 'DE', '+49', 'Europe/Berlin', 'EUR', 'Euro', '€', 1, 17),
('France', 'FRA', 'FR', '+33', 'Europe/Paris', 'EUR', 'Euro', '€', 1, 18),
('Italy', 'ITA', 'IT', '+39', 'Europe/Rome', 'EUR', 'Euro', '€', 1, 19),
('Spain', 'ESP', 'ES', '+34', 'Europe/Madrid', 'EUR', 'Euro', '€', 1, 20),
('Netherlands', 'NLD', 'NL', '+31', 'Europe/Amsterdam', 'EUR', 'Euro', '€', 1, 21),
('Belgium', 'BEL', 'BE', '+32', 'Europe/Brussels', 'EUR', 'Euro', '€', 1, 22),
('Switzerland', 'CHE', 'CH', '+41', 'Europe/Zurich', 'CHF', 'Swiss Franc', 'CHF', 1, 23),
('Sweden', 'SWE', 'SE', '+46', 'Europe/Stockholm', 'SEK', 'Swedish Krona', 'kr', 1, 24),
('Norway', 'NOR', 'NO', '+47', 'Europe/Oslo', 'NOK', 'Norwegian Krone', 'kr', 1, 25),
('Denmark', 'DNK', 'DK', '+45', 'Europe/Copenhagen', 'DKK', 'Danish Krone', 'kr', 1, 26),
('Finland', 'FIN', 'FI', '+358', 'Europe/Helsinki', 'EUR', 'Euro', '€', 1, 27),
('Poland', 'POL', 'PL', '+48', 'Europe/Warsaw', 'PLN', 'Polish Złoty', 'zł', 1, 28),
('Czech Republic', 'CZE', 'CZ', '+420', 'Europe/Prague', 'CZK', 'Czech Koruna', 'Kč', 1, 29),
('Hungary', 'HUN', 'HU', '+36', 'Europe/Budapest', 'HUF', 'Hungarian Forint', 'Ft', 1, 30),
('Austria', 'AUT', 'AT', '+43', 'Europe/Vienna', 'EUR', 'Euro', '€', 1, 31),
('Portugal', 'PRT', 'PT', '+351', 'Europe/Lisbon', 'EUR', 'Euro', '€', 1, 32),
('Ireland', 'IRL', 'IE', '+353', 'Europe/Dublin', 'EUR', 'Euro', '€', 1, 33),
('Greece', 'GRC', 'GR', '+30', 'Europe/Athens', 'EUR', 'Euro', '€', 1, 34),

-- Middle East
('United Arab Emirates', 'ARE', 'AE', '+971', 'Asia/Dubai', 'AED', 'UAE Dirham', 'د.إ', 1, 35),
('Saudi Arabia', 'SAU', 'SA', '+966', 'Asia/Riyadh', 'SAR', 'Saudi Riyal', 'ر.س', 1, 36),
('Qatar', 'QAT', 'QA', '+974', 'Asia/Qatar', 'QAR', 'Qatari Riyal', 'ر.ق', 1, 37),
('Kuwait', 'KWT', 'KW', '+965', 'Asia/Kuwait', 'KWD', 'Kuwaiti Dinar', 'د.ك', 1, 38),
('Bahrain', 'BHR', 'BH', '+973', 'Asia/Bahrain', 'BHD', 'Bahraini Dinar', '.د.ب', 1, 39),
('Oman', 'OMN', 'OM', '+968', 'Asia/Muscat', 'OMR', 'Omani Rial', 'ر.ع.', 1, 40),
('Israel', 'ISR', 'IL', '+972', 'Asia/Jerusalem', 'ILS', 'Israeli Shekel', '₪', 1, 41),
('Turkey', 'TUR', 'TR', '+90', 'Europe/Istanbul', 'TRY', 'Turkish Lira', '₺', 1, 42),

-- Africa
('South Africa', 'ZAF', 'ZA', '+27', 'Africa/Johannesburg', 'ZAR', 'South African Rand', 'R', 1, 43),
('Egypt', 'EGY', 'EG', '+20', 'Africa/Cairo', 'EGP', 'Egyptian Pound', '£', 1, 44),
('Nigeria', 'NGA', 'NG', '+234', 'Africa/Lagos', 'NGN', 'Nigerian Naira', '₦', 1, 45),
('Kenya', 'KEN', 'KE', '+254', 'Africa/Nairobi', 'KES', 'Kenyan Shilling', 'KSh', 1, 46),
('Morocco', 'MAR', 'MA', '+212', 'Africa/Casablanca', 'MAD', 'Moroccan Dirham', 'د.م.', 1, 47),
('Ghana', 'GHA', 'GH', '+233', 'Africa/Accra', 'GHS', 'Ghanaian Cedi', '₵', 1, 48),

-- South America
('Brazil', 'BRA', 'BR', '+55', 'America/Sao_Paulo', 'BRL', 'Brazilian Real', 'R$', 1, 49),
('Argentina', 'ARG', 'AR', '+54', 'America/Argentina/Buenos_Aires', 'ARS', 'Argentine Peso', '$', 1, 50),
('Chile', 'CHL', 'CL', '+56', 'America/Santiago', 'CLP', 'Chilean Peso', '$', 1, 51),
('Colombia', 'COL', 'CO', '+57', 'America/Bogota', 'COP', 'Colombian Peso', '$', 1, 52),
('Peru', 'PER', 'PE', '+51', 'America/Lima', 'PEN', 'Peruvian Sol', 'S/', 1, 53),
('Venezuela', 'VEN', 'VE', '+58', 'America/Caracas', 'VES', 'Venezuelan Bolívar', 'Bs.', 1, 54),

-- Central America & Caribbean
('Costa Rica', 'CRI', 'CR', '+506', 'America/Costa_Rica', 'CRC', 'Costa Rican Colón', '₡', 1, 55),
('Panama', 'PAN', 'PA', '+507', 'America/Panama', 'PAB', 'Panamanian Balboa', 'B/.', 1, 56),
('Jamaica', 'JAM', 'JM', '+1876', 'America/Jamaica', 'JMD', 'Jamaican Dollar', '$', 1, 57),
('Trinidad and Tobago', 'TTO', 'TT', '+1868', 'America/Port_of_Spain', 'TTD', 'Trinidad and Tobago Dollar', '$', 1, 58),

-- Other Major Countries
('Russia', 'RUS', 'RU', '+7', 'Europe/Moscow', 'RUB', 'Russian Ruble', '₽', 1, 59),
('Ukraine', 'UKR', 'UA', '+380', 'Europe/Kiev', 'UAH', 'Ukrainian Hryvnia', '₴', 1, 60),
('Belarus', 'BLR', 'BY', '+375', 'Europe/Minsk', 'BYN', 'Belarusian Ruble', 'Br', 1, 61),
('Kazakhstan', 'KAZ', 'KZ', '+7', 'Asia/Almaty', 'KZT', 'Kazakhstani Tenge', '₸', 1, 62),
('Uzbekistan', 'UZB', 'UZ', '+998', 'Asia/Tashkent', 'UZS', 'Uzbekistani Som', 'so''m', 1, 63),
('Pakistan', 'PAK', 'PK', '+92', 'Asia/Karachi', 'PKR', 'Pakistani Rupee', '₨', 1, 64),
('Bangladesh', 'BGD', 'BD', '+880', 'Asia/Dhaka', 'BDT', 'Bangladeshi Taka', '৳', 1, 65),
('Sri Lanka', 'LKA', 'LK', '+94', 'Asia/Colombo', 'LKR', 'Sri Lankan Rupee', 'Rs', 1, 66),
('Nepal', 'NPL', 'NP', '+977', 'Asia/Kathmandu', 'NPR', 'Nepalese Rupee', '₨', 1, 67),
('Myanmar', 'MMR', 'MM', '+95', 'Asia/Yangon', 'MMK', 'Myanmar Kyat', 'K', 1, 68),
('Cambodia', 'KHM', 'KH', '+855', 'Asia/Phnom_Penh', 'KHR', 'Cambodian Riel', '៛', 1, 69),
('Laos', 'LAO', 'LA', '+856', 'Asia/Vientiane', 'LAK', 'Lao Kip', '₭', 1, 70),
('Mongolia', 'MNG', 'MN', '+976', 'Asia/Ulaanbaatar', 'MNT', 'Mongolian Tögrög', '₮', 1, 71),
('Brunei', 'BRN', 'BN', '+673', 'Asia/Brunei', 'BND', 'Brunei Dollar', '$', 1, 72),
('Papua New Guinea', 'PNG', 'PG', '+675', 'Pacific/Port_Moresby', 'PGK', 'Papua New Guinean Kina', 'K', 1, 73),
('Fiji', 'FJI', 'FJ', '+679', 'Pacific/Fiji', 'FJD', 'Fijian Dollar', '$', 1, 74),
('Vanuatu', 'VUT', 'VU', '+678', 'Pacific/Efate', 'VUV', 'Vanuatu Vatu', 'Vt', 1, 75),
('Solomon Islands', 'SLB', 'SB', '+677', 'Pacific/Guadalcanal', 'SBD', 'Solomon Islands Dollar', '$', 1, 76);

SET FOREIGN_KEY_CHECKS = 1; 