-- =========================================================
-- MIGRATION: Pricing & Plans
-- Airtime/Electricity: commission-based (percentage discount)
-- Data/Cable: fixed plans with cost price vs selling price
-- =========================================================

-- Airtime discount % per network, and electricity commission %
CREATE TABLE IF NOT EXISTS service_pricing (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_type        ENUM('airtime','electricity') NOT NULL,
    network             VARCHAR(30)   DEFAULT NULL,   -- e.g. 'MTN','Glo','Airtel','9mobile' — NULL for electricity (flat rate)
    commission_percent  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    is_active           TINYINT(1)   NOT NULL DEFAULT 1,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_service_network (service_type, network)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the 4 major Nigerian networks for airtime + one electricity row
INSERT INTO service_pricing (service_type, network, commission_percent) VALUES
    ('airtime', 'MTN', 2.00),
    ('airtime', 'Glo', 3.00),
    ('airtime', 'Airtel', 2.00),
    ('airtime', '9mobile', 3.00),
    ('electricity', NULL, 1.50)
ON DUPLICATE KEY UPDATE service_type = service_type;

-- Data / Cable TV plans — fixed plans, cost price vs selling price
CREATE TABLE IF NOT EXISTS service_plans (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_type        ENUM('data','cable') NOT NULL,
    provider_id         INT UNSIGNED  DEFAULT NULL,
    network             VARCHAR(40)   NOT NULL,        -- e.g. 'MTN','DStv','GOtv'
    plan_name           VARCHAR(120)  NOT NULL,        -- e.g. '1GB - 30 Days', 'DStv Compact'
    provider_plan_code  VARCHAR(60)   DEFAULT NULL,    -- the ID the VTU API expects when you buy this plan
    validity             VARCHAR(30)  DEFAULT NULL,    -- e.g. '30 Days' (data only)
    cost_price           DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    selling_price         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_active            TINYINT(1)   NOT NULL DEFAULT 1,
    created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_serviceplans_provider
        FOREIGN KEY (provider_id) REFERENCES vtu_providers(id)
        ON DELETE SET NULL,

    INDEX idx_serviceplans_type_network (service_type, network)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;