-- TRAIL DPS 4.2 - LOT 11.22
-- Verification non destructive - exports / refacturation SMS.
-- A executer en lecture seule si besoin.

-- 1) Base licence : verifier les parametres commerciaux necessaires au calcul du hors forfait.
SELECT
    id,
    license_key,
    customer_name,
    edition_code,
    sms_quota_month,
    sms_overage_price,
    last_check_at
FROM licenses
ORDER BY updated_at DESC, id DESC
LIMIT 20;

-- 2) Base licence : verifier les derniers snapshots de supervision commerciale.
SELECT
    id,
    license_id,
    computed_at,
    rolling_365_course_count AS rolling_300_course_count_legacy_column,
    sms_sent_count,
    active_events_count,
    active_runs_count,
    source,
    created_at
FROM license_usage_stats
ORDER BY id DESC
LIMIT 20;

-- 3) Base tracking : verifier la structure sms_logs pour les exports LOT 11.22.
-- A executer dans la base tracking si elle est separee de la base licence.
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'sms_logs'
  AND COLUMN_NAME IN ('id','license_id','license_key','place_instance_id','scope','billable','provider','phone','status','provider_message_id','error_message','created_at')
ORDER BY ORDINAL_POSITION;

-- 4) Base tracking : synthese mensuelle par licence.
-- Adapter les dates si necessaire.
SELECT
    license_id,
    COALESCE(scope, 'UNKNOWN') AS scope,
    COUNT(*) AS sms_facturables,
    MIN(created_at) AS premier_sms,
    MAX(created_at) AS dernier_sms
FROM sms_logs
WHERE created_at >= DATE_FORMAT(UTC_TIMESTAMP(), '%Y-%m-01 00:00:00')
  AND created_at < DATE_ADD(UTC_DATE(), INTERVAL 1 DAY)
  AND (billable = 1 OR billable IS NULL)
GROUP BY license_id, COALESCE(scope, 'UNKNOWN')
ORDER BY sms_facturables DESC, license_id ASC;
