-- ====================================================================
-- Migration PostgreSQL : Ajout des colonnes SAST dans api_key
-- Date: 7 novembre 2025
-- Description: Ajoute les colonnes pour gérer les quotas SAST par licence
-- ====================================================================

-- 1. Ajouter les colonnes SAST si elles n'existent pas déjà
ALTER TABLE api_key 
    ADD COLUMN IF NOT EXISTS allow_source_scan BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS max_source_files INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS max_source_size_mb INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS advanced_rules BOOLEAN DEFAULT FALSE;

-- 2. Mettre à jour les licences existantes avec les valeurs par défaut selon le tier
-- Tier FREE: Pas de SAST
UPDATE api_key 
SET 
    allow_source_scan = FALSE,
    max_source_files = 0,
    max_source_size_mb = 0,
    advanced_rules = FALSE
WHERE tier = 'FREE' 
    AND (allow_source_scan IS NULL OR max_source_files IS NULL);

-- Tier PRO: 100 fichiers, 20 MB, pas de règles avancées
UPDATE api_key 
SET 
    allow_source_scan = TRUE,
    max_source_files = 100,
    max_source_size_mb = 20,
    advanced_rules = FALSE
WHERE tier = 'PRO' 
    AND (allow_source_scan IS NULL OR max_source_files IS NULL);

-- Tier ENTERPRISE: 500 fichiers, 100 MB, règles avancées
UPDATE api_key 
SET 
    allow_source_scan = TRUE,
    max_source_files = 500,
    max_source_size_mb = 100,
    advanced_rules = TRUE
WHERE tier = 'ENTERPRISE' 
    AND (allow_source_scan IS NULL OR max_source_files IS NULL);

-- Tier SYSOP: Illimité (9999 fichiers), 500 MB, règles avancées
UPDATE api_key 
SET 
    allow_source_scan = TRUE,
    max_source_files = 9999,
    max_source_size_mb = 500,
    advanced_rules = TRUE
WHERE tier = 'SYSOP' 
    AND (allow_source_scan IS NULL OR max_source_files IS NULL);

-- 3. Vérification: Afficher le résultat de la migration
SELECT 
    tier,
    COUNT(*) as total_licenses,
    COUNT(CASE WHEN allow_source_scan = TRUE THEN 1 END) as with_sast,
    AVG(max_source_files) as avg_file_quota,
    AVG(max_source_size_mb) as avg_size_quota
FROM api_key
GROUP BY tier
ORDER BY 
    CASE tier
        WHEN 'SYSOP' THEN 1
        WHEN 'ENTERPRISE' THEN 2
        WHEN 'PRO' THEN 3
        WHEN 'STARTER' THEN 4
        WHEN 'FREE' THEN 5
        ELSE 6
    END;

-- 4. Log de la migration dans la table events (si applicable)
-- Note: Ceci est optionnel et dépend si vous utilisez la colonne events JSONB
UPDATE api_key
SET events = events || jsonb_build_array(
    jsonb_build_object(
        'type', 'migration',
        'action', 'sast_columns_added',
        'timestamp', NOW(),
        'values', jsonb_build_object(
            'allow_source_scan', allow_source_scan,
            'max_source_files', max_source_files,
            'max_source_size_mb', max_source_size_mb,
            'advanced_rules', advanced_rules
        )
    )
)
WHERE allow_source_scan IS NOT NULL;

-- 5. Afficher un résumé final
SELECT 
    '✅ Migration terminée!' as status,
    COUNT(*) as total_licenses_migrated,
    COUNT(CASE WHEN allow_source_scan = TRUE THEN 1 END) as licenses_with_sast
FROM api_key;
