-- TRAIL DPS 4.2 - LOT 11.13
-- Verification non destructive de la table de supervision commerciale licence.
-- A executer uniquement si la table license_usage_stats est absente ou incomplete.

CREATE TABLE IF NOT EXISTS license_usage_stats (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    license_id INT UNSIGNED 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,
    PRIMARY KEY (id),
    KEY idx_license_usage_license_time (license_id, computed_at),
    KEY idx_license_usage_source_time (source, 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;
