-- ============================================================
-- 5SIM Integration Tables
-- Run this SQL once to add 5SIM support to the database.
-- ============================================================

-- 1. Add 5SIM columns to existing settings table
ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS fivesim_api_token VARCHAR(255) NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS fivesim_api_url   VARCHAR(255) NOT NULL DEFAULT 'https://5sim.net/v1',
    ADD COLUMN IF NOT EXISTS fivesim_markup    FLOAT        NOT NULL DEFAULT 35;

-- 2. Countries synced from 5SIM
CREATE TABLE IF NOT EXISTS fivesim_countries (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    country_key VARCHAR(100) NOT NULL UNIQUE,   -- e.g. "england", "russia"
    title       VARCHAR(150) NOT NULL,           -- e.g. "England"
    iso         VARCHAR(10)  NOT NULL DEFAULT '', -- e.g. "gb"
    prefix      VARCHAR(20)  NOT NULL DEFAULT '', -- e.g. "+44"
    updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Products (services) synced per country
CREATE TABLE IF NOT EXISTS fivesim_products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    country_key VARCHAR(100) NOT NULL,           -- FK to fivesim_countries.country_key
    product     VARCHAR(150) NOT NULL,           -- e.g. "facebook", "google"
    category    VARCHAR(50)  NOT NULL DEFAULT 'activation',
    qty         INT          NOT NULL DEFAULT 0, -- available quantity
    cost_usd    FLOAT        NOT NULL DEFAULT 0, -- 5SIM price in USD
    our_price   FLOAT        NOT NULL DEFAULT 0, -- converted NGN price with markup
    synced_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_country_product (country_key, product),
    KEY idx_country (country_key),
    KEY idx_product (product)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. User orders from 5SIM
CREATE TABLE IF NOT EXISTS fivesim_orders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT           NOT NULL,
    order_id5    INT           NOT NULL UNIQUE,  -- 5SIM order ID
    phone        VARCHAR(30)   NOT NULL,
    country_key  VARCHAR(100)  NOT NULL,
    country_name VARCHAR(150)  NOT NULL DEFAULT '',
    product      VARCHAR(150)  NOT NULL,
    operator     VARCHAR(100)  NOT NULL DEFAULT 'any',
    cost_usd     FLOAT         NOT NULL DEFAULT 0,
    our_price    DECIMAL(12,2) NOT NULL DEFAULT 0,
    status       VARCHAR(30)   NOT NULL DEFAULT 'waiting',
    -- status values: waiting | received | cancelled | timeout | banned | finished
    sms_code     VARCHAR(50)   NOT NULL DEFAULT '',
    sms_text     TEXT,
    expires_at   DATETIME,
    received_at  DATETIME,
    created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user_id   (user_id),
    KEY idx_status    (status),
    KEY idx_order_id5 (order_id5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
