-- ============================================================================
-- Script SQL pour vérifier les quittances concernées par les pénalités
-- Date: 2026-02-12
-- Description: Ce script permet de vérifier l'état des pénalités sur les quittances
-- ============================================================================

-- 1. Lister toutes les quittances avec pénalité déjà appliquée
-- ----------------------------------------------------------------------------
SELECT 
    'Quittances avec pénalité appliquée' as categorie,
    COUNT(*) as nombre
FROM quittances
WHERE is_penalite = 1 AND deleted_at IS NULL;

-- 2. Montant total des pénalités appliquées
-- ----------------------------------------------------------------------------
SELECT 
    'Total des pénalités appliquées' as description,
    SUM(total * penalite / 100) as montant_total_penalites,
    COUNT(*) as nombre_quittances
FROM quittances
WHERE is_penalite = 1 AND deleted_at IS NULL;

-- 3. Quittances non payées éligibles à une pénalité (après le 10 du mois)
-- ----------------------------------------------------------------------------
SELECT 
    q.id,
    q.code,
    q.total as montant_original,
    ROUND(q.total * 0.10, 2) as montant_penalite,
    ROUND(q.total * 1.10, 2) as montant_total_avec_penalite,
    q.end_at as date_fin,
    DATE_FORMAT(q.end_at, '%Y-%m-10') as date_limite,
    DATEDIFF(CURDATE(), DATE_FORMAT(q.end_at, '%Y-%m-10')) as jours_retard,
    l.name as locataire,
    p.name as propriete
FROM quittances q
INNER JOIN locataires l ON q.locataires_id = l.id
INNER JOIN properties p ON q.properties_id = p.id
WHERE q.is_payer = 0 
  AND q.is_penalite = 0
  AND q.deleted_at IS NULL
  AND CURDATE() > DATE_FORMAT(q.end_at, '%Y-%m-10')
ORDER BY q.end_at DESC;

-- 4. Statistiques par compagnie
-- ----------------------------------------------------------------------------
SELECT 
    q.company_id,
    COUNT(*) as total_quittances,
    SUM(CASE WHEN q.is_payer = 1 THEN 1 ELSE 0 END) as quittances_payees,
    SUM(CASE WHEN q.is_payer = 0 THEN 1 ELSE 0 END) as quittances_impayees,
    SUM(CASE WHEN q.is_penalite = 1 THEN 1 ELSE 0 END) as avec_penalite,
    SUM(CASE WHEN q.is_payer = 0 
             AND q.is_penalite = 0 
             AND CURDATE() > DATE_FORMAT(q.end_at, '%Y-%m-10') 
        THEN 1 ELSE 0 END) as penalites_a_appliquer
FROM quittances q
WHERE q.deleted_at IS NULL
GROUP BY q.company_id;

-- 5. Quittances en retard sans pénalité (détail par locataire)
-- ----------------------------------------------------------------------------
SELECT 
    l.name as locataire,
    l.telephone,
    COUNT(q.id) as nombre_quittances_retard,
    SUM(q.total) as montant_total_du,
    SUM(q.total * 0.10) as montant_total_penalites,
    MIN(q.end_at) as plus_ancienne_quittance,
    MAX(q.end_at) as plus_recente_quittance
FROM quittances q
INNER JOIN locataires l ON q.locataires_id = l.id
WHERE q.is_payer = 0 
  AND q.deleted_at IS NULL
  AND CURDATE() > DATE_FORMAT(q.end_at, '%Y-%m-10')
GROUP BY l.id, l.name, l.telephone
ORDER BY montant_total_du DESC;

-- 6. Évolution des pénalités par mois
-- ----------------------------------------------------------------------------
SELECT 
    DATE_FORMAT(q.end_at, '%Y-%m') as mois,
    COUNT(*) as nombre_quittances,
    SUM(CASE WHEN q.is_penalite = 1 THEN 1 ELSE 0 END) as avec_penalite,
    SUM(CASE WHEN q.is_penalite = 1 THEN q.total * q.penalite / 100 ELSE 0 END) as montant_penalites
FROM quittances q
WHERE q.deleted_at IS NULL
  AND q.end_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(q.end_at, '%Y-%m')
ORDER BY mois DESC;

-- 7. Top 10 des locataires avec le plus de pénalités
-- ----------------------------------------------------------------------------
SELECT 
    l.name as locataire,
    l.telephone,
    COUNT(q.id) as nombre_quittances_penalisees,
    SUM(q.total * q.penalite / 100) as montant_total_penalites,
    AVG(DATEDIFF(CURDATE(), DATE_FORMAT(q.end_at, '%Y-%m-10'))) as jours_retard_moyen
FROM quittances q
INNER JOIN locataires l ON q.locataires_id = l.id
WHERE q.is_penalite = 1 
  AND q.deleted_at IS NULL
GROUP BY l.id, l.name, l.telephone
ORDER BY montant_total_penalites DESC
LIMIT 10;

-- 8. Quittances à traiter en priorité (plus de 30 jours de retard)
-- ----------------------------------------------------------------------------
SELECT 
    q.id,
    q.code,
    l.name as locataire,
    l.telephone,
    p.name as propriete,
    q.total as montant,
    ROUND(q.total * 0.10, 2) as penalite,
    q.end_at as date_fin,
    DATEDIFF(CURDATE(), DATE_FORMAT(q.end_at, '%Y-%m-10')) as jours_retard
FROM quittances q
INNER JOIN locataires l ON q.locataires_id = l.id
INNER JOIN properties p ON q.properties_id = p.id
WHERE q.is_payer = 0 
  AND q.deleted_at IS NULL
  AND DATEDIFF(CURDATE(), DATE_FORMAT(q.end_at, '%Y-%m-10')) > 30
ORDER BY jours_retard DESC;

-- 9. Résumé global
-- ----------------------------------------------------------------------------
SELECT 
    'Total des quittances' as description,
    COUNT(*) as valeur
FROM quittances WHERE deleted_at IS NULL
UNION ALL
SELECT 
    'Quittances payées',
    COUNT(*)
FROM quittances WHERE is_payer = 1 AND deleted_at IS NULL
UNION ALL
SELECT 
    'Quittances non payées',
    COUNT(*)
FROM quittances WHERE is_payer = 0 AND deleted_at IS NULL
UNION ALL
SELECT 
    'Quittances avec pénalité',
    COUNT(*)
FROM quittances WHERE is_penalite = 1 AND deleted_at IS NULL
UNION ALL
SELECT 
    'Quittances éligibles à pénalité',
    COUNT(*)
FROM quittances 
WHERE is_payer = 0 
  AND is_penalite = 0
  AND deleted_at IS NULL
  AND CURDATE() > DATE_FORMAT(end_at, '%Y-%m-10');

-- ============================================================================
-- Notes d'utilisation:
-- - Exécutez ces requêtes dans MySQL Workbench ou phpMyAdmin
-- - Adaptez les noms de colonnes si votre schéma est différent
-- - Ces requêtes sont en lecture seule et ne modifient pas les données
-- ============================================================================
