-- =========================================================
-- MIGRATION: VTU Providers (multi-aggregator support)
-- Supports registering several providers (VTpass, Aspify,
-- Billstack, etc.) at once, and choosing which provider
-- handles which service (airtime, data, cable, electricity).
-- =========================================================

CREATE TABLE IF NOT EXISTS vtu_providers (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_name   VARCHAR(60)      NOT NULL,
    provider_slug   VARCHAR(40)      NOT NULL UNIQUE,
    base_url        VARCHAR(255)     DEFAULT NULL,
    api_key         VARCHAR(255)     DEFAULT NULL,
    secret_key      VARCHAR(255)     DEFAULT NULL,
    extra_config    TEXT             DEFAULT NULL,   -- JSON for provider-specific fields
    services        VARCHAR(255)     DEFAULT NULL,   -- comma list: airtime,data,cable,electricity
    is_active       TINYINT(1)       NOT NULL DEFAULT 0,
    created_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which provider is the DEFAULT handler for each service type.
-- One row per service — admin picks the active provider for that service.
CREATE TABLE IF NOT EXISTS service_defaults (
    service_type    ENUM('airtime','data','cable','electricity') NOT NULL PRIMARY KEY,
    provider_id     INT UNSIGNED     DEFAULT NULL,
    updated_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_servicedefaults_provider
        FOREIGN KEY (provider_id) REFERENCES vtu_providers(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed empty rows for each service so the admin UI always has something to configure
INSERT INTO service_defaults (service_type, provider_id) VALUES
    ('airtime', NULL), ('data', NULL), ('cable', NULL), ('electricity', NULL)
ON DUPLICATE KEY UPDATE service_type = service_type;