-- TRAIL DPS 4.2 - LOT 11.19
-- Parametrage commercial des editions et quotas depuis l'administration OVH.
-- Migration non destructive. A executer uniquement si les colonnes LOT 11.1 ne sont pas encore presentes.

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;

-- Valeurs de repli pour les licences historiques sans parametrage commercial explicite.
UPDATE licenses
SET offline_grace_days = 15
WHERE offline_grace_days IS NULL OR offline_grace_days < 0;

-- Controle des colonnes commerciales.
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'licenses'
  AND COLUMN_NAME IN (
      'edition_code',
      'max_courses_rolling_year',
      'sms_quota_month',
      'sms_overage_price',
      'offline_grace_days',
      'features_json'
  )
ORDER BY ORDINAL_POSITION;

-- Controle des dernieres licences et de leurs parametres commerciaux.
SELECT
    id,
    license_key,
    product_code,
    edition_code,
    max_courses_rolling_year,
    sms_quota_month,
    sms_overage_price,
    offline_grace_days,
    features_json,
    updated_at
FROM licenses
ORDER BY id DESC
LIMIT 20;
