-- TRAIL DPS 4.2 - LOT 11.1
-- Licence commerciale glissante 365 jours et supervision SMS facturable.
-- Migration non destructive MySQL/MariaDB.

ALTER TABLE licenses
    ADD COLUMN IF NOT EXISTS max_courses_rolling_year INT NULL AFTER edition_code,
    ADD COLUMN IF NOT EXISTS sms_quota_month INT NULL AFTER max_courses_rolling_year,
    ADD COLUMN IF NOT EXISTS sms_overage_price DECIMAL(10,4) NULL AFTER sms_quota_month,
    ADD COLUMN IF NOT EXISTS offline_grace_days INT NOT NULL DEFAULT 15 AFTER sms_overage_price,
    ADD COLUMN IF NOT EXISTS features_json JSON NULL AFTER offline_grace_days;

CREATE TABLE IF NOT EXISTS license_usage_stats (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rolling_365_course_count INT NOT NULL DEFAULT 0,
    sms_sent_count INT NOT NULL DEFAULT 0,
    active_events_count INT NOT NULL DEFAULT 0,
    active_runs_count INT NOT NULL DEFAULT 0,
    source VARCHAR(32) NOT NULL DEFAULT 'OVH',
    details_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_license_usage_license_time (license_id, computed_at),
    CONSTRAINT fk_license_usage_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE sms_logs
    ADD COLUMN IF NOT EXISTS license_id INT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS place_instance_id VARCHAR(128) NULL AFTER license_id,
    ADD COLUMN IF NOT EXISTS scope VARCHAR(64) NULL AFTER status,
    ADD COLUMN IF NOT EXISTS related_share_id BIGINT UNSIGNED NULL AFTER scope,
    ADD COLUMN IF NOT EXISTS billable TINYINT(1) NOT NULL DEFAULT 1 AFTER related_share_id;

CREATE INDEX IF NOT EXISTS idx_sms_logs_license_time ON sms_logs (license_id, created_at);
CREATE INDEX IF NOT EXISTS idx_sms_logs_place_time ON sms_logs (place_instance_id, created_at);
CREATE INDEX IF NOT EXISTS idx_sms_logs_scope_time ON sms_logs (scope, created_at);
