-- ============================================================
-- XtraLogTools Logs Marketplace Integration
-- Run this SQL once to add XtraLogTools support.
-- ============================================================

-- 1. Add XtraLogTools columns to settings table
ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS xtralogtools_api_key  VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'XtraLogTools API key',
    ADD COLUMN IF NOT EXISTS xtralogtools_api_url  VARCHAR(255) NOT NULL DEFAULT 'https://xtralogtools.com/api/v1/logs.php' COMMENT 'XtraLogTools API endpoint',
    ADD COLUMN IF NOT EXISTS xtralogtools_enabled  TINYINT(1)   NOT NULL DEFAULT 0  COMMENT '1=enabled, 0=disabled',
    ADD COLUMN IF NOT EXISTS xtralogtools_markup   DECIMAL(6,2) NOT NULL DEFAULT 10.00 COMMENT '% markup added on top of API price';

-- 2. XtraLogTools product catalog (synced from API services list)
CREATE TABLE IF NOT EXISTS xtralog_products (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    product_id   VARCHAR(100) NOT NULL UNIQUE COMMENT 'product_id from API e.g. WA_LOG_01',
    product_name VARCHAR(255) NOT NULL,
    description  TEXT,
    server       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Server number (1, 2, or 3)',
    api_price    DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Price from API (NGN)',
    our_price    DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Price charged to user after markup',
    category     VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Category/platform e.g. WhatsApp',
    status       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '1=active, 0=disabled',
    synced_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_status   (status),
    KEY idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='XtraLogTools product catalog synced from API';

-- 3. XtraLogTools purchase orders
CREATE TABLE IF NOT EXISTS xtralog_orders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT           NOT NULL,
    product_id   VARCHAR(100)  NOT NULL COMMENT 'FK → xtralog_products.product_id',
    product_name VARCHAR(255)  NOT NULL,
    server       TINYINT(1)    NOT NULL DEFAULT 1,
    quantity     INT           NOT NULL DEFAULT 1,
    unit_price   DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Price per unit charged to user',
    total_charge DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Total amount deducted',
    order_ref    VARCHAR(100)  NOT NULL UNIQUE COMMENT 'Internal order reference',
    api_response TEXT          COMMENT 'Full JSON response from XtraLogTools API',
    logs_data    LONGTEXT      COMMENT 'Delivered log credentials/data',
    status       ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
    created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_user_id  (user_id),
    KEY idx_status   (status),
    KEY idx_product  (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='XtraLogTools purchase orders';
