-- TRAIL DPS 4.2 - LOT 11.22.1
-- Validation de mise en facturation SMS.
-- Migration non destructive : cree les tables de figement de facturation si absentes.

CREATE TABLE IF NOT EXISTS sms_billing_batches (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_reference VARCHAR(64) NOT NULL UNIQUE,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    license_id INT NULL,
    scope VARCHAR(128) NULL,
    sms_count INT NOT NULL DEFAULT 0,
    license_count INT NOT NULL DEFAULT 0,
    overage_count INT NOT NULL DEFAULT 0,
    estimated_amount DECIMAL(12,4) NOT NULL DEFAULT 0,
    status VARCHAR(32) NOT NULL DEFAULT 'billed',
    created_by VARCHAR(128) NULL,
    created_at DATETIME NOT NULL,
    cancelled_by VARCHAR(128) NULL,
    cancelled_at DATETIME NULL,
    summary_json JSON NULL,
    filter_hash CHAR(64) NULL,
    INDEX idx_sms_billing_batches_period (period_start, period_end),
    INDEX idx_sms_billing_batches_license (license_id),
    INDEX idx_sms_billing_batches_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sms_billing_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT NOT NULL,
    sms_log_id BIGINT NOT NULL,
    license_id INT NULL,
    sms_created_at DATETIME NULL,
    scope VARCHAR(128) NULL,
    provider VARCHAR(64) NULL,
    sms_status VARCHAR(64) NULL,
    phone_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_sms_billing_items_sms_log (sms_log_id),
    INDEX idx_sms_billing_items_batch (batch_id),
    INDEX idx_sms_billing_items_license (license_id),
    INDEX idx_sms_billing_items_sms_created (sms_created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Controle rapide apres deploiement :
SHOW TABLES LIKE 'sms_billing_%';
