-- International Products Table
CREATE TABLE IF NOT EXISTS intl_products (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    sku_code        VARCHAR(100) NOT NULL UNIQUE,
    provider_code   VARCHAR(100),
    provider_name   VARCHAR(200),
    provider_logo   VARCHAR(500),
    country_iso     VARCHAR(10),
    country_name    VARCHAR(200),
    product_type    ENUM('recharge','pin','voucher') DEFAULT 'recharge',
    display_text    VARCHAR(200),
    send_value      DECIMAL(10,4),
    send_currency   VARCHAR(10) DEFAULT 'USD',
    recv_value      DECIMAL(10,4),
    recv_currency   VARCHAR(10),
    bdt_price       DECIMAL(10,2),
    usd_rate        DECIMAL(10,4) DEFAULT 110,
    validation_regex VARCHAR(500),
    pay_type        VARCHAR(50),
    is_active       TINYINT(1) DEFAULT 1,
    synced_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_country (country_iso),
    INDEX idx_type (product_type),
    INDEX idx_provider (provider_code),
    INDEX idx_active (is_active)
);

-- International Countries Cache
CREATE TABLE IF NOT EXISTS intl_countries (
    iso         VARCHAR(10) NOT NULL PRIMARY KEY,
    name        VARCHAR(200),
    dial_code   VARCHAR(20),
    synced_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Settings for sync info
INSERT IGNORE INTO settings (`key`, `value`) VALUES ('intl_last_sync', '');
INSERT IGNORE INTO settings (`key`, `value`) VALUES ('intl_usd_rate', '110');
