-- Create the audit_leads table
CREATE TABLE IF NOT EXISTS audit_leads (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    website VARCHAR(500) NOT NULL,
    company VARCHAR(255),
    industry VARCHAR(100),
    monthly_traffic VARCHAR(50),
    goals TEXT[], -- Array to store multiple goals
    status VARCHAR(20) DEFAULT 'new' CHECK (status IN (
        'new', 'contacted', 'qualified', 'proposal_sent', 
        'negotiation', 'won', 'lost', 'on_hold'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Add indexes for better performance
    CONSTRAINT unique_email_website UNIQUE (email, website)
);


-- Create table for custom plan inquiries
CREATE TABLE IF NOT EXISTS custom_plan_inquiries (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    company VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    website VARCHAR(500) NOT NULL,
    company_size VARCHAR(100),
    industry VARCHAR(100),
    monthly_budget VARCHAR(100),
    post_count INTEGER,
    da_range TEXT[], -- Array to store multiple DA ranges
    timeline VARCHAR(100),
    topics TEXT,
    additional_services TEXT[], -- Array for additional services
    status VARCHAR(20) DEFAULT 'new' CHECK (status IN (
        'new', 'contacted', 'qualified', 'proposal_sent', 
        'negotiation', 'won', 'lost', 'on_hold'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id TEXT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    status BOOLEAN DEFAULT true,
    parent_id TEXT REFERENCES categories(id) ON DELETE SET NULL,
    is_indexable BOOLEAN DEFAULT true,
    meta_title VARCHAR(255),
    meta_description TEXT,
    schemas JSONB DEFAULT '[]',
    category_type VARCHAR(50) DEFAULT 'GENERAL',
    featured_image VARCHAR(500),
    featured_image_alt_text VARCHAR(500),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Admin Users Table
CREATE TABLE IF NOT EXISTS admin_users (
    id TEXT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    avatar_url TEXT,
    is_2fa_enabled BOOLEAN DEFAULT FALSE,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    verify_code VARCHAR(6),
    verify_code_expiry TIMESTAMP,
    reset_token VARCHAR(100),
    reset_token_expiry TIMESTAMP,
    last_login_at TIMESTAMP WITH TIME ZONE,
    failed_code_attempts integer DEFAULT 0,
    code_lock_until timestamp with time zone,
    totp_verified BOOLEAN DEFAULT FALSE,
    two_factor_method VARCHAR(20) DEFAULT 'email' CHECK (two_factor_method IN ('email', 'totp', 'none')),
    backup_codes JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

-- Roles Table (Simplified)
CREATE TABLE IF NOT EXISTS roles (
    id TEXT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Permissions Table
CREATE TABLE IF NOT EXISTS permissions (
    id TEXT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    module VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Admin User Roles (Many-to-Many)
CREATE TABLE IF NOT EXISTS admin_user_roles (
    id TEXT PRIMARY KEY,
    admin_user_id TEXT NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    role_id TEXT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_by TEXT REFERENCES admin_users(id),
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(admin_user_id, role_id)
);

-- Role Permissions (Many-to-Many)
CREATE TABLE IF NOT EXISTS role_permissions (
    id TEXT PRIMARY KEY,
    role_id TEXT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id TEXT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    granted_by TEXT REFERENCES admin_users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(role_id, permission_id)
);

-- User Sessions Table
CREATE TABLE IF NOT EXISTS user_sessions (
    id TEXT PRIMARY KEY,
    admin_user_id TEXT NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    session_token TEXT UNIQUE NOT NULL,
    refresh_token TEXT UNIQUE NOT NULL,
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    refresh_token_expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_revoked BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Audit Log Table
CREATE TABLE IF NOT EXISTS audit_logs (
    id TEXT PRIMARY KEY,
    admin_user_id TEXT REFERENCES admin_users(id),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100),
    resource_id TEXT,
    previous_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Password Reset Tokens
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id TEXT PRIMARY KEY,
    admin_user_id TEXT NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_used BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS site_settings (
    -- Basic Information
    id TEXT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    meta_title VARCHAR(60) DEFAULT NULL,
    meta_description VARCHAR(155) DEFAULT NULL,
    
    -- Logos & Images
    logo TEXT DEFAULT NULL,
    favicon TEXT DEFAULT NULL,
    logo_alt TEXT DEFAULT NULL,
    footer_logo TEXT DEFAULT NULL,
    footer_logo_alt TEXT DEFAULT NULL,
    
    -- SEO & Schema
    home_schema JSONB DEFAULT NULL,
    robots TEXT DEFAULT NULL,
    body_scripts TEXT DEFAULT NULL,
    footer_scripts TEXT DEFAULT NULL,
    google_site_verification TEXT DEFAULT NULL,
    bing_site_verification TEXT DEFAULT NULL,
    baidu_site_verification TEXT DEFAULT NULL,
    yandex_site_verification TEXT DEFAULT NULL,
    pinterest_verification TEXT DEFAULT NULL,
    
    -- Social Media
    facebook_url TEXT DEFAULT NULL,
    twitter_url TEXT DEFAULT NULL,
    linkedin_url TEXT DEFAULT NULL,
    pinterest_url TEXT DEFAULT NULL,
    instagram_url TEXT DEFAULT NULL,
    youtube_url TEXT DEFAULT NULL,
    tiktok_url TEXT DEFAULT NULL,
    whatsapp_url TEXT DEFAULT NULL,
    reddit_url TEXT DEFAULT NULL,
    
    -- Footer & Copyright
    footer_text TEXT DEFAULT NULL,
    copyright_text TEXT DEFAULT NULL,
    
    -- Contact Information
    contact_email VARCHAR(255) DEFAULT NULL,
    contact_phone VARCHAR(50) DEFAULT NULL,
    support_email VARCHAR(255) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    google_maps_url TEXT DEFAULT NULL,
    
    -- Legal Pages
    privacy_policy_url TEXT DEFAULT NULL,
    terms_of_service_url TEXT DEFAULT NULL,
    
    -- Security & Authentication
    enable_captcha BOOLEAN DEFAULT FALSE,
    turnstile_site_key TEXT DEFAULT NULL,
    turnstile_secret_key TEXT DEFAULT NULL,
    max_login_attempts INTEGER DEFAULT 5,
    lock_duration_minutes INTEGER DEFAULT 15,
    user_registration_enabled BOOLEAN DEFAULT TRUE,
    
    -- Cloudinary Integration
    cloudinary_cloud_name TEXT DEFAULT NULL,
    cloudinary_api_key TEXT DEFAULT NULL,
    cloudinary_api_secret TEXT DEFAULT NULL,
    
    -- Email Configuration
    smtp_config JSONB DEFAULT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Users Table (Minimal & Efficient)
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
    
    -- Core Authentication
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT, -- NULL for social login users
    
    -- Profile Information
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    display_name VARCHAR(150),
    avatar_url TEXT,
    phone VARCHAR(20),
    whatsapp VARCHAR(20),
    company VARCHAR(255),

    -- address details

    country VARCHAR(100),
    city VARCHAR(100),
    zip_code VARCHAR(20),

    -- Account Status
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    
    -- Two-Factor Authentication (Email only)
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    
    -- Social Login IDs (Indexed for fast lookups)
    google_id VARCHAR(255),
    facebook_id VARCHAR(255),
    github_id VARCHAR(255),
    apple_id VARCHAR(255),

    -- 2FA verification
    email_verification_code VARCHAR(6),
    email_verification_expiry TIMESTAMP,
    
    -- Password Reset & Verification
    reset_token VARCHAR(100),
    reset_token_expiry TIMESTAMP WITH TIME ZONE,
    verification_token VARCHAR(100),
    verification_token_expiry TIMESTAMP WITH TIME ZONE,
    
    -- Login Tracking & Security
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_login_ip INET,
    failed_login_attempts INTEGER DEFAULT 0,
    login_lock_until TIMESTAMP WITH TIME ZONE,
    
    -- Signup Information
    signup_method VARCHAR(50) DEFAULT 'email', -- 'email', 'google', 'facebook', 'github', 'apple'
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    
    -- Email should be lowercase for consistency
    CONSTRAINT users_email_lowercase CHECK (email = LOWER(email))
);

-- Web User Sessions Table (Optimized for web apps)
CREATE TABLE IF NOT EXISTS web_user_sessions (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    -- Session Tokens (unique and indexed)
    session_token TEXT UNIQUE NOT NULL,
    refresh_token TEXT UNIQUE NOT NULL,
    
    -- Device/Browser Info
    user_agent TEXT,
    ip_address INET,
    
    -- Security
    is_revoked BOOLEAN DEFAULT false,
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoke_reason TEXT,
    
    -- Expiry
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    refresh_token_expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Activity Tracking
    last_accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS menu (
    id TEXT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(20) CHECK (position IN ('top_header','header', 'footer_menu_1', 'footer_menu_2', 'footer_menu_3')) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS menu_items (
    id TEXT PRIMARY KEY,
    menu_id TEXT REFERENCES menu(id) ON DELETE CASCADE,
    reference_id TEXT, -- ID of the page/category/tag if applicable
    title VARCHAR(255), -- optional for custom_link
    type VARCHAR(50),
    link VARCHAR(255), -- required only for custom_link
    parent_id TEXT REFERENCES menu_items(id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE IF NOT EXISTS pages (
    id TEXT PRIMARY KEY,
    page_title TEXT NOT NULL UNIQUE,
    page_slug TEXT NOT NULL UNIQUE,
    page_short_description TEXT,
    page_meta_title VARCHAR(255) NOT NULL,
    page_meta_description VARCHAR(255) NOT NULL,
    page_schemas JSONB DEFAULT NULL,
    builder_data JSONB DEFAULT NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    page_create_date TIMESTAMP DEFAULT NOW(),
    page_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sidebars (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  widgets JSONB NOT NULL DEFAULT '[]',
  status BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS authors (
  id TEXT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  slug VARCHAR(255)  NOT NULL UNIQUE,
  bio TEXT DEFAULT NULL,
  profile_image VARCHAR(500) DEFAULT NULL,
  website VARCHAR(500) DEFAULT NULL,
  social_facebook VARCHAR(500) DEFAULT NULL,
  social_twitter VARCHAR(500) DEFAULT NULL,
  social_instagram VARCHAR(500) DEFAULT NULL,
  social_linkedin VARCHAR(500) DEFAULT NULL,
  status BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS posts (
    id TEXT PRIMARY KEY,
    post_category TEXT REFERENCES categories(id) ON DELETE CASCADE,
    post_title TEXT NOT NULL UNIQUE,
    post_slug TEXT NOT NULL UNIQUE,
    post_content TEXT NOT NULL,
    post_excerpt TEXT DEFAULT NULL,
    post_featured_image TEXT DEFAULT NULL,
    post_featured_image_alt_text TEXT DEFAULT NULL,
    post_meta_title VARCHAR(255) NOT NULL,
    post_meta_description VARCHAR(255) NOT NULL,
    post_schemas JSONB DEFAULT NULL,
    author_id TEXT REFERENCES authors(id) ON DELETE SET NULL,
    is_indexable BOOLEAN DEFAULT TRUE,
    post_status VARCHAR(20) DEFAULT 'draft' CHECK (post_status IN ('draft', 'published', 'pending_review')),
    reading_time_minutes INTEGER DEFAULT NULL,
    is_featured BOOLEAN DEFAULT FALSE,
    post_sidebar_id VARCHAR(50) REFERENCES sidebars(id) ON DELETE SET NULL,
    post_sidebar_on_mobile  BOOLEAN DEFAULT TRUE,
    post_create_date TIMESTAMP DEFAULT NOW(),
    post_update_date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS redirects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_path VARCHAR(500) NOT NULL,
    destination_path VARCHAR(500) NOT NULL,
    redirect_type INT DEFAULT 301,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_path)
);

CREATE TABLE IF NOT EXISTS websites (
    id TEXT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    domain VARCHAR(255) UNIQUE NOT NULL,
    da INTEGER CHECK (da >= 0 AND da <= 100),
    dr INTEGER CHECK (dr >= 0 AND dr <= 100),
    traffic VARCHAR(50),
    spam_score INTEGER CHECK (spam_score >= 0 AND spam_score <= 100),
    backlinks INTEGER CHECK (backlinks >= 0),
    link_type VARCHAR(10) DEFAULT 'Dofollow' CHECK (link_type IN ('Dofollow', 'Nofollow')),
    base_price DECIMAL(10,2) NOT NULL CHECK (base_price > 0),
    sale_price DECIMAL(10,2) CHECK (sale_price >= 0),
    content_service_price DECIMAL(10,2) DEFAULT 10.00 CHECK (content_service_price >= 0),
    category_id TEXT REFERENCES categories(id) ON DELETE SET NULL,
    niches JSONB DEFAULT '[]', -- All accepted niches
    niche_pricing JSONB DEFAULT '{}', -- Special pricing for specific niches
    description TEXT,
    user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
    featured_image VARCHAR(500),
    featured_image_alt_text VARCHAR(500),
    meta_title VARCHAR(255),
    meta_description TEXT,
    is_indexable BOOLEAN DEFAULT true,
    schemas JSONB DEFAULT '[]',
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'sold_out')),
    featured BOOLEAN DEFAULT false,
    views INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Packages table
CREATE TABLE IF NOT EXISTS packages (
    id TEXT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    package_type VARCHAR(50) DEFAULT 'standard' CHECK (package_type IN ('standard', 'special_niche', 'custom')),
    base_price DECIMAL(10,2) NOT NULL CHECK (base_price > 0),
    sale_price DECIMAL(10,2) CHECK (sale_price > 0),
    features JSONB DEFAULT '[]',
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    is_popular BOOLEAN DEFAULT false,
    is_featured BOOLEAN DEFAULT false,
    schemas JSONB DEFAULT '[]',
    meta_title VARCHAR(255) DEFAULT NULL,
    meta_description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Package websites table
CREATE TABLE IF NOT EXISTS package_websites (
    id TEXT PRIMARY KEY,
    package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE,
    website_id TEXT NOT NULL REFERENCES websites(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(package_id, website_id)
);

CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
    package_id TEXT REFERENCES packages(id) ON DELETE SET NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    order_type VARCHAR(20) DEFAULT 'individual' CHECK (order_type IN ('individual', 'package', 'package_individual')),
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'pending_review', 'completed', 'cancelled')),
    payment_status VARCHAR(20) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'failed', 'refunded')),
    payment_method VARCHAR(50),
    transaction_id VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Order items table (for individual website orders)
CREATE TABLE IF NOT EXISTS order_items (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    website_id TEXT NOT NULL REFERENCES websites(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    content_service BOOLEAN DEFAULT false,
    content_service_price DECIMAL(10,2) DEFAULT 0 CHECK (content_service_price >= 0),
    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
    live_links JSONB DEFAULT '[]',
    rejection_reason TEXT DEFAULT NULL,
    rejected_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    rejected_by TEXT DEFAULT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Package order items table (for package orders)
CREATE TABLE IF NOT EXISTS package_order_items (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
    google_sheet_url TEXT DEFAULT NULL,
    rejection_reason TEXT DEFAULT NULL,
    rejected_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    rejected_by TEXT DEFAULT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Package order websites table (assigned websites for package orders)
CREATE TABLE IF NOT EXISTS package_order_websites (
    id TEXT PRIMARY KEY,
    package_order_item_id TEXT NOT NULL REFERENCES package_order_items(id) ON DELETE CASCADE,
    website_id TEXT NOT NULL REFERENCES websites(id) ON DELETE RESTRICT,
    assigned_da INTEGER,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(package_order_item_id, website_id)
);

-- Order item details table
CREATE TABLE IF NOT EXISTS order_item_details (
    id TEXT PRIMARY KEY,
    order_item_id TEXT REFERENCES order_items(id) ON DELETE CASCADE,
    package_order_item_id TEXT REFERENCES package_order_items(id) ON DELETE CASCADE,
    desired_topic TEXT NOT NULL,
    target_keywords TEXT,
    --content_type VARCHAR(20) DEFAULT 'customer_provided' CHECK (content_type IN ('customer_provided', 'we_write')),
    word_count INTEGER CHECK (word_count > 0),
    special_instructions TEXT,
    --deadline TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (order_item_id IS NOT NULL AND package_order_item_id IS NULL) OR
        (order_item_id IS NULL AND package_order_item_id IS NOT NULL)
    )
);

-- Order documents table
CREATE TABLE IF NOT EXISTS order_documents (
    id TEXT PRIMARY KEY,
    order_item_id TEXT REFERENCES order_items(id) ON DELETE CASCADE,
    package_order_item_id TEXT REFERENCES package_order_items(id) ON DELETE CASCADE,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(100),
    file_size BIGINT,
    uploaded_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (order_item_id IS NOT NULL AND package_order_item_id IS NULL) OR
        (order_item_id IS NULL AND package_order_item_id IS NOT NULL)
    )
);

-- 1. Support Tickets Table
CREATE TABLE IF NOT EXISTS support_tickets (
    id TEXT PRIMARY KEY,
    ticket_number VARCHAR(50) UNIQUE NOT NULL,
    user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
    subject VARCHAR(255) NOT NULL,
    category VARCHAR(50) NOT NULL CHECK (category IN ('order', 'payment', 'technical', 'website', 'other')),
    priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
    status VARCHAR(20) DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
    message TEXT NOT NULL,
    attachments JSONB DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 2. Support Replies Table
CREATE TABLE IF NOT EXISTS support_replies (
    id TEXT PRIMARY KEY,
    ticket_id TEXT NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE,
    user_id TEXT NULL REFERENCES users(id) ON DELETE SET NULL,
    admin_id TEXT NULL REFERENCES admin_users(id) ON DELETE SET NULL,
    is_admin BOOLEAN DEFAULT false,
    message TEXT NOT NULL,
    attachments JSONB DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT check_reply_source CHECK (
        (user_id IS NOT NULL AND admin_id IS NULL) OR 
        (user_id IS NULL AND admin_id IS NOT NULL)
    )
);

-- =============================================
-- COUNTRIES TABLE (Complete PostgreSQL Version)
-- =============================================

-- Create countries table
CREATE TABLE IF NOT EXISTS countries (
    id                      VARCHAR(2) PRIMARY KEY,
    name                    VARCHAR(100) NOT NULL,
    iso3                    VARCHAR(3),
    numeric_code            VARCHAR(3),
    phone_code              VARCHAR(10),
    capital                 VARCHAR(100),
    currency                VARCHAR(3),
    currency_name           VARCHAR(50),
    continent               VARCHAR(50),
    continent_code          VARCHAR(2),
    region                  VARCHAR(100),
    subregion               VARCHAR(100),
    emoji                   VARCHAR(10),
    emoji_unicode           VARCHAR(50),
    is_active               BOOLEAN DEFAULT TRUE,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- INSERT ALL COUNTRIES
-- =============================================

/*INSERT INTO countries (id, name, iso3, numeric_code, phone_code, capital, currency, currency_name, continent, continent_code, region, subregion, emoji, emoji_unicode) VALUES
('AF', 'Afghanistan', 'AFG', '004', '93', 'Kabul', 'AFN', 'Afghan afghani', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇦🇫', 'U+1F1E6 U+1F1EB'),
('AX', 'Åland Islands', 'ALA', '248', '358', 'Mariehamn', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇦🇽', 'U+1F1E6 U+1F1FD'),
('AL', 'Albania', 'ALB', '008', '355', 'Tirana', 'ALL', 'Albanian lek', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇦🇱', 'U+1F1E6 U+1F1F1'),
('DZ', 'Algeria', 'DZA', '012', '213', 'Algiers', 'DZD', 'Algerian dinar', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇩🇿', 'U+1F1E9 U+1F1FF'),
('AS', 'American Samoa', 'ASM', '016', '1684', 'Pago Pago', 'USD', 'US Dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇦🇸', 'U+1F1E6 U+1F1F8'),
('AD', 'Andorra', 'AND', '020', '376', 'Andorra la Vella', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇦🇩', 'U+1F1E6 U+1F1E9'),
('AO', 'Angola', 'AGO', '024', '244', 'Luanda', 'AOA', 'Angolan kwanza', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇦🇴', 'U+1F1E6 U+1F1F4'),
('AI', 'Anguilla', 'AIA', '660', '1264', 'The Valley', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇦🇮', 'U+1F1E6 U+1F1EE'),
('AQ', 'Antarctica', 'ATA', '010', '672', 'N/A', 'N/A', 'N/A', 'Antarctica', 'AN', 'Antarctica', 'Antarctica', '🇦🇶', 'U+1F1E6 U+1F1F6'),
('AG', 'Antigua and Barbuda', 'ATG', '028', '1268', 'St. Johns', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇦🇬', 'U+1F1E6 U+1F1EC'),
('AR', 'Argentina', 'ARG', '032', '54', 'Buenos Aires', 'ARS', 'Argentine peso', 'South America', 'SA', 'Americas', 'South America', '🇦🇷', 'U+1F1E6 U+1F1F7'),
('AM', 'Armenia', 'ARM', '051', '374', 'Yerevan', 'AMD', 'Armenian dram', 'Asia', 'AS', 'Asia', 'Western Asia', '🇦🇲', 'U+1F1E6 U+1F1F2'),
('AW', 'Aruba', 'ABW', '533', '297', 'Oranjestad', 'AWG', 'Aruban florin', 'North America', 'NA', 'Americas', 'Caribbean', '🇦🇼', 'U+1F1E6 U+1F1FC'),
('AU', 'Australia', 'AUS', '036', '61', 'Canberra', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Australia and New Zealand', '🇦🇺', 'U+1F1E6 U+1F1FA'),
('AT', 'Austria', 'AUT', '040', '43', 'Vienna', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇦🇹', 'U+1F1E6 U+1F1F9'),
('AZ', 'Azerbaijan', 'AZE', '031', '994', 'Baku', 'AZN', 'Azerbaijani manat', 'Asia', 'AS', 'Asia', 'Western Asia', '🇦🇿', 'U+1F1E6 U+1F1FF'),
('BS', 'Bahamas', 'BHS', '044', '1242', 'Nassau', 'BSD', 'Bahamian dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇧🇸', 'U+1F1E7 U+1F1F8'),
('BH', 'Bahrain', 'BHR', '048', '973', 'Manama', 'BHD', 'Bahraini dinar', 'Asia', 'AS', 'Asia', 'Western Asia', '🇧🇭', 'U+1F1E7 U+1F1ED'),
('BD', 'Bangladesh', 'BGD', '050', '880', 'Dhaka', 'BDT', 'Bangladeshi taka', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇧🇩', 'U+1F1E7 U+1F1E9'),
('BB', 'Barbados', 'BRB', '052', '1246', 'Bridgetown', 'BBD', 'Barbadian dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇧🇧', 'U+1F1E7 U+1F1E7'),
('BY', 'Belarus', 'BLR', '112', '375', 'Minsk', 'BYN', 'Belarusian ruble', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇧🇾', 'U+1F1E7 U+1F1FE'),
('BE', 'Belgium', 'BEL', '056', '32', 'Brussels', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇧🇪', 'U+1F1E7 U+1F1EA'),
('BZ', 'Belize', 'BLZ', '084', '501', 'Belmopan', 'BZD', 'Belize dollar', 'North America', 'NA', 'Americas', 'Central America', '🇧🇿', 'U+1F1E7 U+1F1FF'),
('BJ', 'Benin', 'BEN', '204', '229', 'Porto-Novo', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇧🇯', 'U+1F1E7 U+1F1EF'),
('BM', 'Bermuda', 'BMU', '060', '1441', 'Hamilton', 'BMD', 'Bermudian dollar', 'North America', 'NA', 'Americas', 'Northern America', '🇧🇲', 'U+1F1E7 U+1F1F2'),
('BT', 'Bhutan', 'BTN', '064', '975', 'Thimphu', 'BTN', 'Bhutanese ngultrum', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇧🇹', 'U+1F1E7 U+1F1F9'),
('BO', 'Bolivia', 'BOL', '068', '591', 'Sucre', 'BOB', 'Bolivian boliviano', 'South America', 'SA', 'Americas', 'South America', '🇧🇴', 'U+1F1E7 U+1F1F4'),
('BA', 'Bosnia and Herzegovina', 'BIH', '070', '387', 'Sarajevo', 'BAM', 'Convertible mark', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇧🇦', 'U+1F1E7 U+1F1E6'),
('BW', 'Botswana', 'BWA', '072', '267', 'Gaborone', 'BWP', 'Botswana pula', 'Africa', 'AF', 'Africa', 'Southern Africa', '🇧🇼', 'U+1F1E7 U+1F1FC'),
('BV', 'Bouvet Island', 'BVT', '074', '47', 'N/A', 'NOK', 'Norwegian krone', 'Antarctica', 'AN', 'Antarctica', 'Antarctica', '🇧🇻', 'U+1F1E7 U+1F1FB'),
('BR', 'Brazil', 'BRA', '076', '55', 'Brasília', 'BRL', 'Brazilian real', 'South America', 'SA', 'Americas', 'South America', '🇧🇷', 'U+1F1E7 U+1F1F7'),
('IO', 'British Indian Ocean Territory', 'IOT', '086', '246', 'Diego Garcia', 'USD', 'United States dollar', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇮🇴', 'U+1F1EE U+1F1F4'),
('BN', 'Brunei', 'BRN', '096', '673', 'Bandar Seri Begawan', 'BND', 'Brunei dollar', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇧🇳', 'U+1F1E7 U+1F1F3'),
('BG', 'Bulgaria', 'BGR', '100', '359', 'Sofia', 'BGN', 'Bulgarian lev', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇧🇬', 'U+1F1E7 U+1F1EC'),
('BF', 'Burkina Faso', 'BFA', '854', '226', 'Ouagadougou', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇧🇫', 'U+1F1E7 U+1F1EB'),
('BI', 'Burundi', 'BDI', '108', '257', 'Gitega', 'BIF', 'Burundian franc', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇧🇮', 'U+1F1E7 U+1F1EE'),
('KH', 'Cambodia', 'KHM', '116', '855', 'Phnom Penh', 'KHR', 'Cambodian riel', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇰🇭', 'U+1F1F0 U+1F1ED'),
('CM', 'Cameroon', 'CMR', '120', '237', 'Yaoundé', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇨🇲', 'U+1F1E8 U+1F1F2'),
('CA', 'Canada', 'CAN', '124', '1', 'Ottawa', 'CAD', 'Canadian dollar', 'North America', 'NA', 'Americas', 'Northern America', '🇨🇦', 'U+1F1E8 U+1F1E6'),
('CV', 'Cape Verde', 'CPV', '132', '238', 'Praia', 'CVE', 'Cape Verdean escudo', 'Africa', 'AF', 'Africa', 'Western Africa', '🇨🇻', 'U+1F1E8 U+1F1FB'),
('KY', 'Cayman Islands', 'CYM', '136', '1345', 'George Town', 'KYD', 'Cayman Islands dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇰🇾', 'U+1F1F0 U+1F1FE'),
('CF', 'Central African Republic', 'CAF', '140', '236', 'Bangui', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇨🇫', 'U+1F1E8 U+1F1EB'),
('TD', 'Chad', 'TCD', '148', '235', 'Ndjamena', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇹🇩', 'U+1F1F9 U+1F1E9'),
('CL', 'Chile', 'CHL', '152', '56', 'Santiago', 'CLP', 'Chilean peso', 'South America', 'SA', 'Americas', 'South America', '🇨🇱', 'U+1F1E8 U+1F1F1'),
('CN', 'China', 'CHN', '156', '86', 'Beijing', 'CNY', 'Chinese yuan', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇨🇳', 'U+1F1E8 U+1F1F3'),
('CX', 'Christmas Island', 'CXR', '162', '61', 'Flying Fish Cove', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Australia and New Zealand', '🇨🇽', 'U+1F1E8 U+1F1FD'),
('CC', 'Cocos (Keeling) Islands', 'CCK', '166', '61', 'West Island', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Australia and New Zealand', '🇨🇨', 'U+1F1E8 U+1F1E8'),
('CO', 'Colombia', 'COL', '170', '57', 'Bogotá', 'COP', 'Colombian peso', 'South America', 'SA', 'Americas', 'South America', '🇨🇴', 'U+1F1E8 U+1F1F4'),
('KM', 'Comoros', 'COM', '174', '269', 'Moroni', 'KMF', 'Comorian franc', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇰🇲', 'U+1F1F0 U+1F1F2'),
('CG', 'Congo', 'COG', '178', '242', 'Brazzaville', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇨🇬', 'U+1F1E8 U+1F1EC'),
('CD', 'Congo (Democratic Republic)', 'COD', '180', '243', 'Kinshasa', 'CDF', 'Congolese franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇨🇩', 'U+1F1E8 U+1F1E9'),
('CK', 'Cook Islands', 'COK', '184', '682', 'Avarua', 'NZD', 'New Zealand dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇨🇰', 'U+1F1E8 U+1F1F0'),
('CR', 'Costa Rica', 'CRI', '188', '506', 'San José', 'CRC', 'Costa Rican colón', 'North America', 'NA', 'Americas', 'Central America', '🇨🇷', 'U+1F1E8 U+1F1F7'),
('CI', 'Côte dIvoire', 'CIV', '384', '225', 'Yamoussoukro', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇨🇮', 'U+1F1E8 U+1F1EE'),
('HR', 'Croatia', 'HRV', '191', '385', 'Zagreb', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇭🇷', 'U+1F1ED U+1F1F7'),
('CU', 'Cuba', 'CUB', '192', '53', 'Havana', 'CUP', 'Cuban peso', 'North America', 'NA', 'Americas', 'Caribbean', '🇨🇺', 'U+1F1E8 U+1F1FA'),
('CW', 'Curaçao', 'CUW', '531', '599', 'Willemstad', 'ANG', 'Netherlands Antillean guilder', 'North America', 'NA', 'Americas', 'Caribbean', '🇨🇼', 'U+1F1E8 U+1F1FC'),
('CY', 'Cyprus', 'CYP', '196', '357', 'Nicosia', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇨🇾', 'U+1F1E8 U+1F1FE'),
('CZ', 'Czechia', 'CZE', '203', '420', 'Prague', 'CZK', 'Czech koruna', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇨🇿', 'U+1F1E8 U+1F1FF'),
('DK', 'Denmark', 'DNK', '208', '45', 'Copenhagen', 'DKK', 'Danish krone', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇩🇰', 'U+1F1E9 U+1F1F0'),
('DJ', 'Djibouti', 'DJI', '262', '253', 'Djibouti', 'DJF', 'Djiboutian franc', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇩🇯', 'U+1F1E9 U+1F1EF'),
('DM', 'Dominica', 'DMA', '212', '1767', 'Roseau', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇩🇲', 'U+1F1E9 U+1F1F2'),
('DO', 'Dominican Republic', 'DOM', '214', '1809', 'Santo Domingo', 'DOP', 'Dominican peso', 'North America', 'NA', 'Americas', 'Caribbean', '🇩🇴', 'U+1F1E9 U+1F1F4'),
('EC', 'Ecuador', 'ECU', '218', '593', 'Quito', 'USD', 'United States dollar', 'South America', 'SA', 'Americas', 'South America', '🇪🇨', 'U+1F1EA U+1F1E8'),
('EG', 'Egypt', 'EGY', '818', '20', 'Cairo', 'EGP', 'Egyptian pound', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇪🇬', 'U+1F1EA U+1F1EC'),
('SV', 'El Salvador', 'SLV', '222', '503', 'San Salvador', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Central America', '🇸🇻', 'U+1F1F8 U+1F1FB'),
('GQ', 'Equatorial Guinea', 'GNQ', '226', '240', 'Malabo', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇬🇶', 'U+1F1EC U+1F1F6'),
('ER', 'Eritrea', 'ERI', '232', '291', 'Asmara', 'ERN', 'Eritrean nakfa', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇪🇷', 'U+1F1EA U+1F1F7'),
('EE', 'Estonia', 'EST', '233', '372', 'Tallinn', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇪🇪', 'U+1F1EA U+1F1EA'),
('ET', 'Ethiopia', 'ETH', '231', '251', 'Addis Ababa', 'ETB', 'Ethiopian birr', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇪🇹', 'U+1F1EA U+1F1F9'),
('FK', 'Falkland Islands', 'FLK', '238', '500', 'Stanley', 'FKP', 'Falkland Islands pound', 'South America', 'SA', 'Americas', 'South America', '🇫🇰', 'U+1F1EB U+1F1F0'),
('FO', 'Faroe Islands', 'FRO', '234', '298', 'Tórshavn', 'DKK', 'Danish krone', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇫🇴', 'U+1F1EB U+1F1F4'),
('FJ', 'Fiji', 'FJI', '242', '679', 'Suva', 'FJD', 'Fijian dollar', 'Oceania', 'OC', 'Oceania', 'Melanesia', '🇫🇯', 'U+1F1EB U+1F1EF'),
('FI', 'Finland', 'FIN', '246', '358', 'Helsinki', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇫🇮', 'U+1F1EB U+1F1EE'),
('FR', 'France', 'FRA', '250', '33', 'Paris', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇫🇷', 'U+1F1EB U+1F1F7'),
('GF', 'French Guiana', 'GUF', '254', '594', 'Cayenne', 'EUR', 'Euro', 'South America', 'SA', 'Americas', 'South America', '🇬🇫', 'U+1F1EC U+1F1EB'),
('PF', 'French Polynesia', 'PYF', '258', '689', 'Papeete', 'XPF', 'CFP franc', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇵🇫', 'U+1F1F5 U+1F1EB'),
('TF', 'French Southern Territories', 'ATF', '260', '262', 'Port-aux-Français', 'EUR', 'Euro', 'Antarctica', 'AN', 'Antarctica', 'Antarctica', '🇹🇫', 'U+1F1F9 U+1F1EB'),
('GA', 'Gabon', 'GAB', '266', '241', 'Libreville', 'XAF', 'Central African CFA franc', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇬🇦', 'U+1F1EC U+1F1E6'),
('GM', 'Gambia', 'GMB', '270', '220', 'Banjul', 'GMD', 'Gambian dalasi', 'Africa', 'AF', 'Africa', 'Western Africa', '🇬🇲', 'U+1F1EC U+1F1F2'),
('GE', 'Georgia', 'GEO', '268', '995', 'Tbilisi', 'GEL', 'Georgian lari', 'Asia', 'AS', 'Asia', 'Western Asia', '🇬🇪', 'U+1F1EC U+1F1EA'),
('DE', 'Germany', 'DEU', '276', '49', 'Berlin', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇩🇪', 'U+1F1E9 U+1F1EA'),
('GH', 'Ghana', 'GHA', '288', '233', 'Accra', 'GHS', 'Ghanaian cedi', 'Africa', 'AF', 'Africa', 'Western Africa', '🇬🇭', 'U+1F1EC U+1F1ED'),
('GI', 'Gibraltar', 'GIB', '292', '350', 'Gibraltar', 'GIP', 'Gibraltar pound', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇬🇮', 'U+1F1EC U+1F1EE'),
('GR', 'Greece', 'GRC', '300', '30', 'Athens', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇬🇷', 'U+1F1EC U+1F1F7'),
('GL', 'Greenland', 'GRL', '304', '299', 'Nuuk', 'DKK', 'Danish krone', 'North America', 'NA', 'Americas', 'Northern America', '🇬🇱', 'U+1F1EC U+1F1F1'),
('GD', 'Grenada', 'GRD', '308', '1473', 'St. George''s', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇬🇩', 'U+1F1EC U+1F1E9'),
('GP', 'Guadeloupe', 'GLP', '312', '590', 'Basse-Terre', 'EUR', 'Euro', 'North America', 'NA', 'Americas', 'Caribbean', '🇬🇵', 'U+1F1EC U+1F1F5'),
('GU', 'Guam', 'GUM', '316', '1671', 'Hagåtña', 'USD', 'US Dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇬🇺', 'U+1F1EC U+1F1FA'),
('GT', 'Guatemala', 'GTM', '320', '502', 'Guatemala City', 'GTQ', 'Guatemalan quetzal', 'North America', 'NA', 'Americas', 'Central America', '🇬🇹', 'U+1F1EC U+1F1F9'),
('GG', 'Guernsey', 'GGY', '831', '44', 'St. Peter Port', 'GBP', 'British pound', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇬🇬', 'U+1F1EC U+1F1EC'),
('GN', 'Guinea', 'GIN', '324', '224', 'Conakry', 'GNF', 'Guinean franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇬🇳', 'U+1F1EC U+1F1F3'),
('GW', 'Guinea-Bissau', 'GNB', '624', '245', 'Bissau', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇬🇼', 'U+1F1EC U+1F1FC'),
('GY', 'Guyana', 'GUY', '328', '592', 'Georgetown', 'GYD', 'Guyanese dollar', 'South America', 'SA', 'Americas', 'South America', '🇬🇾', 'U+1F1EC U+1F1FE'),
('HT', 'Haiti', 'HTI', '332', '509', 'Port-au-Prince', 'HTG', 'Haitian gourde', 'North America', 'NA', 'Americas', 'Caribbean', '🇭🇹', 'U+1F1ED U+1F1F9'),
('HM', 'Heard Island and McDonald Islands', 'HMD', '334', '61', 'N/A', 'AUD', 'Australian dollar', 'Antarctica', 'AN', 'Antarctica', 'Antarctica', '🇭🇲', 'U+1F1ED U+1F1F2'),
('HN', 'Honduras', 'HND', '340', '504', 'Tegucigalpa', 'HNL', 'Honduran lempira', 'North America', 'NA', 'Americas', 'Central America', '🇭🇳', 'U+1F1ED U+1F1F3'),
('HK', 'Hong Kong', 'HKG', '344', '852', 'Hong Kong', 'HKD', 'Hong Kong dollar', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇭🇰', 'U+1F1ED U+1F1F0'),
('HU', 'Hungary', 'HUN', '348', '36', 'Budapest', 'HUF', 'Hungarian forint', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇭🇺', 'U+1F1ED U+1F1FA'),
('IS', 'Iceland', 'ISL', '352', '354', 'Reykjavik', 'ISK', 'Icelandic króna', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇮🇸', 'U+1F1EE U+1F1F8'),
('IN', 'India', 'IND', '356', '91', 'New Delhi', 'INR', 'Indian rupee', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇮🇳', 'U+1F1EE U+1F1F3'),
('ID', 'Indonesia', 'IDN', '360', '62', 'Jakarta', 'IDR', 'Indonesian rupiah', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇮🇩', 'U+1F1EE U+1F1E9'),
('IR', 'Iran', 'IRN', '364', '98', 'Tehran', 'IRR', 'Iranian rial', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇮🇷', 'U+1F1EE U+1F1F7'),
('IQ', 'Iraq', 'IRQ', '368', '964', 'Baghdad', 'IQD', 'Iraqi dinar', 'Asia', 'AS', 'Asia', 'Western Asia', '🇮🇶', 'U+1F1EE U+1F1F6'),
('IE', 'Ireland', 'IRL', '372', '353', 'Dublin', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇮🇪', 'U+1F1EE U+1F1EA'),
('IM', 'Isle of Man', 'IMN', '833', '44', 'Douglas', 'GBP', 'British pound', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇮🇲', 'U+1F1EE U+1F1F2'),
('IL', 'Israel', 'ISR', '376', '972', 'Jerusalem', 'ILS', 'Israeli new shekel', 'Asia', 'AS', 'Asia', 'Western Asia', '🇮🇱', 'U+1F1EE U+1F1F1'),
('IT', 'Italy', 'ITA', '380', '39', 'Rome', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇮🇹', 'U+1F1EE U+1F1F9'),
('JM', 'Jamaica', 'JAM', '388', '1876', 'Kingston', 'JMD', 'Jamaican dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇯🇲', 'U+1F1EF U+1F1F2'),
('JP', 'Japan', 'JPN', '392', '81', 'Tokyo', 'JPY', 'Japanese yen', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇯🇵', 'U+1F1EF U+1F1F5'),
('JE', 'Jersey', 'JEY', '832', '44', 'Saint Helier', 'GBP', 'British pound', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇯🇪', 'U+1F1EF U+1F1EA'),
('JO', 'Jordan', 'JOR', '400', '962', 'Amman', 'JOD', 'Jordanian dinar', 'Asia', 'AS', 'Asia', 'Western Asia', '🇯🇴', 'U+1F1EF U+1F1F4'),
('KZ', 'Kazakhstan', 'KAZ', '398', '7', 'Astana', 'KZT', 'Kazakhstani tenge', 'Asia', 'AS', 'Asia', 'Central Asia', '🇰🇿', 'U+1F1F0 U+1F1FF'),
('KE', 'Kenya', 'KEN', '404', '254', 'Nairobi', 'KES', 'Kenyan shilling', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇰🇪', 'U+1F1F0 U+1F1EA'),
('KI', 'Kiribati', 'KIR', '296', '686', 'Tarawa', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇰🇮', 'U+1F1F0 U+1F1EE'),
('KP', 'North Korea', 'PRK', '408', '850', 'Pyongyang', 'KPW', 'North Korean won', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇰🇵', 'U+1F1F0 U+1F1F5'),
('KR', 'South Korea', 'KOR', '410', '82', 'Seoul', 'KRW', 'South Korean won', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇰🇷', 'U+1F1F0 U+1F1F7'),
('KW', 'Kuwait', 'KWT', '414', '965', 'Kuwait City', 'KWD', 'Kuwaiti dinar', 'Asia', 'AS', 'Asia', 'Western Asia', '🇰🇼', 'U+1F1F0 U+1F1FC'),
('KG', 'Kyrgyzstan', 'KGZ', '417', '996', 'Bishkek', 'KGS', 'Kyrgyzstani som', 'Asia', 'AS', 'Asia', 'Central Asia', '🇰🇬', 'U+1F1F0 U+1F1EC'),
('LA', 'Laos', 'LAO', '418', '856', 'Vientiane', 'LAK', 'Lao kip', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇱🇦', 'U+1F1F1 U+1F1E6'),
('LV', 'Latvia', 'LVA', '428', '371', 'Riga', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇱🇻', 'U+1F1F1 U+1F1FB'),
('LB', 'Lebanon', 'LBN', '422', '961', 'Beirut', 'LBP', 'Lebanese pound', 'Asia', 'AS', 'Asia', 'Western Asia', '🇱🇧', 'U+1F1F1 U+1F1E7'),
('LS', 'Lesotho', 'LSO', '426', '266', 'Maseru', 'LSL', 'Lesotho loti', 'Africa', 'AF', 'Africa', 'Southern Africa', '🇱🇸', 'U+1F1F1 U+1F1F8'),
('LR', 'Liberia', 'LBR', '430', '231', 'Monrovia', 'LRD', 'Liberian dollar', 'Africa', 'AF', 'Africa', 'Western Africa', '🇱🇷', 'U+1F1F1 U+1F1F7'),
('LY', 'Libya', 'LBY', '434', '218', 'Tripoli', 'LYD', 'Libyan dinar', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇱🇾', 'U+1F1F1 U+1F1FE'),
('LI', 'Liechtenstein', 'LIE', '438', '423', 'Vaduz', 'CHF', 'Swiss franc', 'Europe', 'EU', 'Europe', 'Western Europe', '🇱🇮', 'U+1F1F1 U+1F1EE'),
('LT', 'Lithuania', 'LTU', '440', '370', 'Vilnius', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇱🇹', 'U+1F1F1 U+1F1F9'),
('LU', 'Luxembourg', 'LUX', '442', '352', 'Luxembourg', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇱🇺', 'U+1F1F1 U+1F1FA'),
('MO', 'Macao', 'MAC', '446', '853', 'Macao', 'MOP', 'Macanese pataca', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇲🇴', 'U+1F1F2 U+1F1F4'),
('MK', 'North Macedonia', 'MKD', '807', '389', 'Skopje', 'MKD', 'Macedonian denar', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇲🇰', 'U+1F1F2 U+1F1F0'),
('MG', 'Madagascar', 'MDG', '450', '261', 'Antananarivo', 'MGA', 'Malagasy ariary', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇲🇬', 'U+1F1F2 U+1F1EC'),
('MW', 'Malawi', 'MWI', '454', '265', 'Lilongwe', 'MWK', 'Malawian kwacha', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇲🇼', 'U+1F1F2 U+1F1FC'),
('MY', 'Malaysia', 'MYS', '458', '60', 'Kuala Lumpur', 'MYR', 'Malaysian ringgit', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇲🇾', 'U+1F1F2 U+1F1FE'),
('MV', 'Maldives', 'MDV', '462', '960', 'Malé', 'MVR', 'Maldivian rufiyaa', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇲🇻', 'U+1F1F2 U+1F1FB'),
('ML', 'Mali', 'MLI', '466', '223', 'Bamako', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇲🇱', 'U+1F1F2 U+1F1F1'),
('MT', 'Malta', 'MLT', '470', '356', 'Valletta', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇲🇹', 'U+1F1F2 U+1F1F9'),
('MH', 'Marshall Islands', 'MHL', '584', '692', 'Majuro', 'USD', 'United States dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇲🇭', 'U+1F1F2 U+1F1ED'),
('MQ', 'Martinique', 'MTQ', '474', '596', 'Fort-de-France', 'EUR', 'Euro', 'North America', 'NA', 'Americas', 'Caribbean', '🇲🇶', 'U+1F1F2 U+1F1F6'),
('MR', 'Mauritania', 'MRT', '478', '222', 'Nouakchott', 'MRU', 'Mauritanian ouguiya', 'Africa', 'AF', 'Africa', 'Western Africa', '🇲🇷', 'U+1F1F2 U+1F1F7'),
('MU', 'Mauritius', 'MUS', '480', '230', 'Port Louis', 'MUR', 'Mauritian rupee', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇲🇺', 'U+1F1F2 U+1F1FA'),
('YT', 'Mayotte', 'MYT', '175', '262', 'Mamoudzou', 'EUR', 'Euro', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇾🇹', 'U+1F1FE U+1F1F9'),
('MX', 'Mexico', 'MEX', '484', '52', 'Mexico City', 'MXN', 'Mexican peso', 'North America', 'NA', 'Americas', 'Central America', '🇲🇽', 'U+1F1F2 U+1F1FD'),
('FM', 'Micronesia', 'FSM', '583', '691', 'Palikir', 'USD', 'United States dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇫🇲', 'U+1F1EB U+1F1F2'),
('MD', 'Moldova', 'MDA', '498', '373', 'Chișinău', 'MDL', 'Moldovan leu', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇲🇩', 'U+1F1F2 U+1F1E9'),
('MC', 'Monaco', 'MCO', '492', '377', 'Monaco', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇲🇨', 'U+1F1F2 U+1F1E8'),
('MN', 'Mongolia', 'MNG', '496', '976', 'Ulaanbaatar', 'MNT', 'Mongolian tögrög', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇲🇳', 'U+1F1F2 U+1F1F3'),
('ME', 'Montenegro', 'MNE', '499', '382', 'Podgorica', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇲🇪', 'U+1F1F2 U+1F1EA'),
('MS', 'Montserrat', 'MSR', '500', '1664', 'Plymouth', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇲🇸', 'U+1F1F2 U+1F1F8'),
('MA', 'Morocco', 'MAR', '504', '212', 'Rabat', 'MAD', 'Moroccan dirham', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇲🇦', 'U+1F1F2 U+1F1E6'),
('MZ', 'Mozambique', 'MOZ', '508', '258', 'Maputo', 'MZN', 'Mozambican metical', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇲🇿', 'U+1F1F2 U+1F1FF'),
('MM', 'Myanmar', 'MMR', '104', '95', 'Naypyidaw', 'MMK', 'Burmese kyat', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇲🇲', 'U+1F1F2 U+1F1F2'),
('NA', 'Namibia', 'NAM', '516', '264', 'Windhoek', 'NAD', 'Namibian dollar', 'Africa', 'AF', 'Africa', 'Southern Africa', '🇳🇦', 'U+1F1F3 U+1F1E6'),
('NR', 'Nauru', 'NRU', '520', '674', 'Yaren', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇳🇷', 'U+1F1F3 U+1F1F7'),
('NP', 'Nepal', 'NPL', '524', '977', 'Kathmandu', 'NPR', 'Nepalese rupee', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇳🇵', 'U+1F1F3 U+1F1F5'),
('NL', 'Netherlands', 'NLD', '528', '31', 'Amsterdam', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Western Europe', '🇳🇱', 'U+1F1F3 U+1F1F1'),
('NC', 'New Caledonia', 'NCL', '540', '687', 'Nouméa', 'XPF', 'CFP franc', 'Oceania', 'OC', 'Oceania', 'Melanesia', '🇳🇨', 'U+1F1F3 U+1F1E8'),
('NZ', 'New Zealand', 'NZL', '554', '64', 'Wellington', 'NZD', 'New Zealand dollar', 'Oceania', 'OC', 'Oceania', 'Australia and New Zealand', '🇳🇿', 'U+1F1F3 U+1F1FF'),
('NI', 'Nicaragua', 'NIC', '558', '505', 'Managua', 'NIO', 'Nicaraguan córdoba', 'North America', 'NA', 'Americas', 'Central America', '🇳🇮', 'U+1F1F3 U+1F1EE'),
('NE', 'Niger', 'NER', '562', '227', 'Niamey', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇳🇪', 'U+1F1F3 U+1F1EA'),
('NG', 'Nigeria', 'NGA', '566', '234', 'Abuja', 'NGN', 'Nigerian naira', 'Africa', 'AF', 'Africa', 'Western Africa', '🇳🇬', 'U+1F1F3 U+1F1EC'),
('NU', 'Niue', 'NIU', '570', '683', 'Alofi', 'NZD', 'New Zealand dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇳🇺', 'U+1F1F3 U+1F1FA'),
('NF', 'Norfolk Island', 'NFK', '574', '672', 'Kingston', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Australia and New Zealand', '🇳🇫', 'U+1F1F3 U+1F1EB'),
('MP', 'Northern Mariana Islands', 'MNP', '580', '1670', 'Saipan', 'USD', 'United States dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇲🇵', 'U+1F1F2 U+1F1F5'),
('NO', 'Norway', 'NOR', '578', '47', 'Oslo', 'NOK', 'Norwegian krone', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇳🇴', 'U+1F1F3 U+1F1F4'),
('OM', 'Oman', 'OMN', '512', '968', 'Muscat', 'OMR', 'Omani rial', 'Asia', 'AS', 'Asia', 'Western Asia', '🇴🇲', 'U+1F1F4 U+1F1F2'),
('PK', 'Pakistan', 'PAK', '586', '92', 'Islamabad', 'PKR', 'Pakistani rupee', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇵🇰', 'U+1F1F5 U+1F1F0'),
('PW', 'Palau', 'PLW', '585', '680', 'Ngerulmud', 'USD', 'United States dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇵🇼', 'U+1F1F5 U+1F1FC'),
('PS', 'Palestine', 'PSE', '275', '970', 'Ramallah', 'ILS', 'Israeli new shekel', 'Asia', 'AS', 'Asia', 'Western Asia', '🇵🇸', 'U+1F1F5 U+1F1F8'),
('PA', 'Panama', 'PAN', '591', '507', 'Panama City', 'PAB', 'Panamanian balboa', 'North America', 'NA', 'Americas', 'Central America', '🇵🇦', 'U+1F1F5 U+1F1E6'),
('PG', 'Papua New Guinea', 'PNG', '598', '675', 'Port Moresby', 'PGK', 'Papua New Guinean kina', 'Oceania', 'OC', 'Oceania', 'Melanesia', '🇵🇬', 'U+1F1F5 U+1F1EC'),
('PY', 'Paraguay', 'PRY', '600', '595', 'Asunción', 'PYG', 'Paraguayan guaraní', 'South America', 'SA', 'Americas', 'South America', '🇵🇾', 'U+1F1F5 U+1F1FE'),
('PE', 'Peru', 'PER', '604', '51', 'Lima', 'PEN', 'Peruvian sol', 'South America', 'SA', 'Americas', 'South America', '🇵🇪', 'U+1F1F5 U+1F1EA'),
('PH', 'Philippines', 'PHL', '608', '63', 'Manila', 'PHP', 'Philippine peso', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇵🇭', 'U+1F1F5 U+1F1ED'),
('PN', 'Pitcairn Islands', 'PCN', '612', '64', 'Adamstown', 'NZD', 'New Zealand dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇵🇳', 'U+1F1F5 U+1F1F3'),
('PL', 'Poland', 'POL', '616', '48', 'Warsaw', 'PLN', 'Polish złoty', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇵🇱', 'U+1F1F5 U+1F1F1'),
('PT', 'Portugal', 'PRT', '620', '351', 'Lisbon', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇵🇹', 'U+1F1F5 U+1F1F9'),
('PR', 'Puerto Rico', 'PRI', '630', '1787', 'San Juan', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇵🇷', 'U+1F1F5 U+1F1F7'),
('QA', 'Qatar', 'QAT', '634', '974', 'Doha', 'QAR', 'Qatari riyal', 'Asia', 'AS', 'Asia', 'Western Asia', '🇶🇦', 'U+1F1F6 U+1F1E6'),
('RE', 'Réunion', 'REU', '638', '262', 'Saint-Denis', 'EUR', 'Euro', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇷🇪', 'U+1F1F7 U+1F1EA'),
('RO', 'Romania', 'ROU', '642', '40', 'Bucharest', 'RON', 'Romanian leu', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇷🇴', 'U+1F1F7 U+1F1F4'),
('RU', 'Russia', 'RUS', '643', '7', 'Moscow', 'RUB', 'Russian ruble', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇷🇺', 'U+1F1F7 U+1F1FA'),
('RW', 'Rwanda', 'RWA', '646', '250', 'Kigali', 'RWF', 'Rwandan franc', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇷🇼', 'U+1F1F7 U+1F1FC'),
('BL', 'Saint Barthélemy', 'BLM', '652', '590', 'Gustavia', 'EUR', 'Euro', 'North America', 'NA', 'Americas', 'Caribbean', '🇧🇱', 'U+1F1E7 U+1F1F1'),
('SH', 'Saint Helena', 'SHN', '654', '290', 'Jamestown', 'SHP', 'Saint Helena pound', 'Africa', 'AF', 'Africa', 'Western Africa', '🇸🇭', 'U+1F1F8 U+1F1ED'),
('KN', 'Saint Kitts and Nevis', 'KNA', '659', '1869', 'Basseterre', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇰🇳', 'U+1F1F0 U+1F1F3'),
('LC', 'Saint Lucia', 'LCA', '662', '1758', 'Castries', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇱🇨', 'U+1F1F1 U+1F1E8'),
('MF', 'Saint Martin', 'MAF', '663', '590', 'Marigot', 'EUR', 'Euro', 'North America', 'NA', 'Americas', 'Caribbean', '🇲🇫', 'U+1F1F2 U+1F1EB'),
('PM', 'Saint Pierre and Miquelon', 'SPM', '666', '508', 'Saint-Pierre', 'EUR', 'Euro', 'North America', 'NA', 'Americas', 'Northern America', '🇵🇲', 'U+1F1F5 U+1F1F2'),
('VC', 'Saint Vincent and the Grenadines', 'VCT', '670', '1784', 'Kingstown', 'XCD', 'East Caribbean dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇻🇨', 'U+1F1FB U+1F1E8'),
('WS', 'Samoa', 'WSM', '882', '685', 'Apia', 'WST', 'Samoan tālā', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇼🇸', 'U+1F1FC U+1F1F8'),
('SM', 'San Marino', 'SMR', '674', '378', 'San Marino', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇸🇲', 'U+1F1F8 U+1F1F2'),
('ST', 'São Tomé and Príncipe', 'STP', '678', '239', 'São Tomé', 'STN', 'São Tomé and Príncipe dobra', 'Africa', 'AF', 'Africa', 'Middle Africa', '🇸🇹', 'U+1F1F8 U+1F1F9'),
('SA', 'Saudi Arabia', 'SAU', '682', '966', 'Riyadh', 'SAR', 'Saudi riyal', 'Asia', 'AS', 'Asia', 'Western Asia', '🇸🇦', 'U+1F1F8 U+1F1E6'),
('SN', 'Senegal', 'SEN', '686', '221', 'Dakar', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇸🇳', 'U+1F1F8 U+1F1F3'),
('RS', 'Serbia', 'SRB', '688', '381', 'Belgrade', 'RSD', 'Serbian dinar', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇷🇸', 'U+1F1F7 U+1F1F8'),
('SC', 'Seychelles', 'SYC', '690', '248', 'Victoria', 'SCR', 'Seychellois rupee', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇸🇨', 'U+1F1F8 U+1F1E8'),
('SL', 'Sierra Leone', 'SLE', '694', '232', 'Freetown', 'SLL', 'Sierra Leonean leone', 'Africa', 'AF', 'Africa', 'Western Africa', '🇸🇱', 'U+1F1F8 U+1F1F1'),
('SG', 'Singapore', 'SGP', '702', '65', 'Singapore', 'SGD', 'Singapore dollar', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇸🇬', 'U+1F1F8 U+1F1EC'),
('SX', 'Sint Maarten', 'SXM', '534', '1721', 'Philipsburg', 'ANG', 'Netherlands Antillean guilder', 'North America', 'NA', 'Americas', 'Caribbean', '🇸🇽', 'U+1F1F8 U+1F1FD'),
('SK', 'Slovakia', 'SVK', '703', '421', 'Bratislava', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇸🇰', 'U+1F1F8 U+1F1F0'),
('SI', 'Slovenia', 'SVN', '705', '386', 'Ljubljana', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇸🇮', 'U+1F1F8 U+1F1EE'),
('SB', 'Solomon Islands', 'SLB', '090', '677', 'Honiara', 'SBD', 'Solomon Islands dollar', 'Oceania', 'OC', 'Oceania', 'Melanesia', '🇸🇧', 'U+1F1F8 U+1F1E7'),
('SO', 'Somalia', 'SOM', '706', '252', 'Mogadishu', 'SOS', 'Somali shilling', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇸🇴', 'U+1F1F8 U+1F1F4'),
('ZA', 'South Africa', 'ZAF', '710', '27', 'Pretoria', 'ZAR', 'South African rand', 'Africa', 'AF', 'Africa', 'Southern Africa', '🇿🇦', 'U+1F1FF U+1F1E6'),
('GS', 'South Georgia and the South Sandwich Islands', 'SGS', '239', '500', 'King Edward Point', 'GBP', 'British pound', 'Antarctica', 'AN', 'Antarctica', 'Antarctica', '🇬🇸', 'U+1F1EC U+1F1F8'),
('SS', 'South Sudan', 'SSD', '728', '211', 'Juba', 'SSP', 'South Sudanese pound', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇸🇸', 'U+1F1F8 U+1F1F8'),
('ES', 'Spain', 'ESP', '724', '34', 'Madrid', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇪🇸', 'U+1F1EA U+1F1F8'),
('LK', 'Sri Lanka', 'LKA', '144', '94', 'Sri Jayawardenepura Kotte', 'LKR', 'Sri Lankan rupee', 'Asia', 'AS', 'Asia', 'Southern Asia', '🇱🇰', 'U+1F1F1 U+1F1F0'),
('SD', 'Sudan', 'SDN', '729', '249', 'Khartoum', 'SDG', 'Sudanese pound', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇸🇩', 'U+1F1F8 U+1F1E9'),
('SR', 'Suriname', 'SUR', '740', '597', 'Paramaribo', 'SRD', 'Surinamese dollar', 'South America', 'SA', 'Americas', 'South America', '🇸🇷', 'U+1F1F8 U+1F1F7'),
('SJ', 'Svalbard and Jan Mayen', 'SJM', '744', '47', 'Longyearbyen', 'NOK', 'Norwegian krone', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇸🇯', 'U+1F1F8 U+1F1EF'),
('SE', 'Sweden', 'SWE', '752', '46', 'Stockholm', 'SEK', 'Swedish krona', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇸🇪', 'U+1F1F8 U+1F1EA'),
('CH', 'Switzerland', 'CHE', '756', '41', 'Bern', 'CHF', 'Swiss franc', 'Europe', 'EU', 'Europe', 'Western Europe', '🇨🇭', 'U+1F1E8 U+1F1ED'),
('SY', 'Syria', 'SYR', '760', '963', 'Damascus', 'SYP', 'Syrian pound', 'Asia', 'AS', 'Asia', 'Western Asia', '🇸🇾', 'U+1F1F8 U+1F1FE'),
('TW', 'Taiwan', 'TWN', '158', '886', 'Taipei', 'TWD', 'New Taiwan dollar', 'Asia', 'AS', 'Asia', 'Eastern Asia', '🇹🇼', 'U+1F1F9 U+1F1FC'),
('TJ', 'Tajikistan', 'TJK', '762', '992', 'Dushanbe', 'TJS', 'Tajikistani somoni', 'Asia', 'AS', 'Asia', 'Central Asia', '🇹🇯', 'U+1F1F9 U+1F1EF'),
('TZ', 'Tanzania', 'TZA', '834', '255', 'Dodoma', 'TZS', 'Tanzanian shilling', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇹🇿', 'U+1F1F9 U+1F1FF'),
('TH', 'Thailand', 'THA', '764', '66', 'Bangkok', 'THB', 'Thai baht', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇹🇭', 'U+1F1F9 U+1F1ED'),
('TL', 'Timor-Leste', 'TLS', '626', '670', 'Dili', 'USD', 'United States dollar', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇹🇱', 'U+1F1F9 U+1F1F1'),
('TG', 'Togo', 'TGO', '768', '228', 'Lomé', 'XOF', 'West African CFA franc', 'Africa', 'AF', 'Africa', 'Western Africa', '🇹🇬', 'U+1F1F9 U+1F1EC'),
('TK', 'Tokelau', 'TKL', '772', '690', 'N/A', 'NZD', 'New Zealand dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇹🇰', 'U+1F1F9 U+1F1F0'),
('TO', 'Tonga', 'TON', '776', '676', 'Nukualofa', 'TOP', 'Tongan paʻanga', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇹🇴', 'U+1F1F9 U+1F1F4'),
('TT', 'Trinidad and Tobago', 'TTO', '780', '1868', 'Port of Spain', 'TTD', 'Trinidad and Tobago dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇹🇹', 'U+1F1F9 U+1F1F9'),
('TN', 'Tunisia', 'TUN', '788', '216', 'Tunis', 'TND', 'Tunisian dinar', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇹🇳', 'U+1F1F9 U+1F1F3'),
('TR', 'Turkey', 'TUR', '792', '90', 'Ankara', 'TRY', 'Turkish lira', 'Asia', 'AS', 'Asia', 'Western Asia', '🇹🇷', 'U+1F1F9 U+1F1F7'),
('TM', 'Turkmenistan', 'TKM', '795', '993', 'Ashgabat', 'TMT', 'Turkmenistani manat', 'Asia', 'AS', 'Asia', 'Central Asia', '🇹🇲', 'U+1F1F9 U+1F1F2'),
('TC', 'Turks and Caicos Islands', 'TCA', '796', '1649', 'Cockburn Town', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇹🇨', 'U+1F1F9 U+1F1E8'),
('TV', 'Tuvalu', 'TUV', '798', '688', 'Funafuti', 'AUD', 'Australian dollar', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇹🇻', 'U+1F1F9 U+1F1FB'),
('UG', 'Uganda', 'UGA', '800', '256', 'Kampala', 'UGX', 'Ugandan shilling', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇺🇬', 'U+1F1FA U+1F1EC'),
('UA', 'Ukraine', 'UKR', '804', '380', 'Kyiv', 'UAH', 'Ukrainian hryvnia', 'Europe', 'EU', 'Europe', 'Eastern Europe', '🇺🇦', 'U+1F1FA U+1F1E6'),
('AE', 'United Arab Emirates', 'ARE', '784', '971', 'Abu Dhabi', 'AED', 'United Arab Emirates dirham', 'Asia', 'AS', 'Asia', 'Western Asia', '🇦🇪', 'U+1F1E6 U+1F1EA'),
('GB', 'United Kingdom', 'GBR', '826', '44', 'London', 'GBP', 'British pound', 'Europe', 'EU', 'Europe', 'Northern Europe', '🇬🇧', 'U+1F1EC U+1F1E7'),
('US', 'United States', 'USA', '840', '1', 'Washington, D.C.', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Northern America', '🇺🇸', 'U+1F1FA U+1F1F8'),
('UM', 'United States Minor Outlying Islands', 'UMI', '581', '1', 'N/A', 'USD', 'United States dollar', 'Oceania', 'OC', 'Oceania', 'Micronesia', '🇺🇲', 'U+1F1FA U+1F1F2'),
('UY', 'Uruguay', 'URY', '858', '598', 'Montevideo', 'UYU', 'Uruguayan peso', 'South America', 'SA', 'Americas', 'South America', '🇺🇾', 'U+1F1FA U+1F1FE'),
('UZ', 'Uzbekistan', 'UZB', '860', '998', 'Tashkent', 'UZS', 'Uzbekistani soʻm', 'Asia', 'AS', 'Asia', 'Central Asia', '🇺🇿', 'U+1F1FA U+1F1FF'),
('VU', 'Vanuatu', 'VUT', '548', '678', 'Port Vila', 'VUV', 'Vanuatu vatu', 'Oceania', 'OC', 'Oceania', 'Melanesia', '🇻🇺', 'U+1F1FB U+1F1FA'),
('VA', 'Vatican City', 'VAT', '336', '379', 'Vatican City', 'EUR', 'Euro', 'Europe', 'EU', 'Europe', 'Southern Europe', '🇻🇦', 'U+1F1FB U+1F1E6'),
('VE', 'Venezuela', 'VEN', '862', '58', 'Caracas', 'VES', 'Venezuelan bolívar', 'South America', 'SA', 'Americas', 'South America', '🇻🇪', 'U+1F1FB U+1F1EA'),
('VN', 'Vietnam', 'VNM', '704', '84', 'Hanoi', 'VND', 'Vietnamese đồng', 'Asia', 'AS', 'Asia', 'South-eastern Asia', '🇻🇳', 'U+1F1FB U+1F1F3'),
('VG', 'Virgin Islands (British)', 'VGB', '092', '1284', 'Road Town', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇻🇬', 'U+1F1FB U+1F1EC'),
('VI', 'Virgin Islands (U.S.)', 'VIR', '850', '1340', 'Charlotte Amalie', 'USD', 'United States dollar', 'North America', 'NA', 'Americas', 'Caribbean', '🇻🇮', 'U+1F1FB U+1F1EE'),
('WF', 'Wallis and Futuna', 'WLF', '876', '681', 'Mata-Utu', 'XPF', 'CFP franc', 'Oceania', 'OC', 'Oceania', 'Polynesia', '🇼🇫', 'U+1F1FC U+1F1EB'),
('EH', 'Western Sahara', 'ESH', '732', '212', 'El Aaiún', 'MAD', 'Moroccan dirham', 'Africa', 'AF', 'Africa', 'Northern Africa', '🇪🇭', 'U+1F1EA U+1F1ED'),
('YE', 'Yemen', 'YEM', '887', '967', 'Sanaa', 'YER', 'Yemeni rial', 'Asia', 'AS', 'Asia', 'Western Asia', '🇾🇪', 'U+1F1FE U+1F1EA'),
('ZM', 'Zambia', 'ZMB', '894', '260', 'Lusaka', 'ZMW', 'Zambian kwacha', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇿🇲', 'U+1F1FF U+1F1F2'),
('ZW', 'Zimbabwe', 'ZWE', '716', '263', 'Harare', 'ZWL', 'Zimbabwean dollar', 'Africa', 'AF', 'Africa', 'Eastern Africa', '🇿🇼', 'U+1F1FF U+1F1FC');
*/