# Requêtes SQL utiles - Virements Salaires

## Consultation des données

### Lister tous les virements avec détails de l'organisation
```sql
SELECT 
  st.id,
  st.period_month,
  st.period_label,
  st.deadline,
  st.mode,
  st.total_net,
  st.callsheet_url,
  st.notification_sent,
  st.notification_ok,
  st.client_wire_received_at,
  st.notes,
  st.created_at,
  o.name as organization_name
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
ORDER BY st.period_month DESC, st.created_at DESC;
```

### Virements par organisation
```sql
SELECT 
  o.name as organization_name,
  COUNT(st.id) as total_virements,
  COUNT(st.callsheet_url) as virements_avec_pdf,
  SUM(CASE WHEN st.callsheet_url IS NULL THEN 1 ELSE 0 END) as virements_sans_pdf,
  SUM(CAST(st.total_net AS NUMERIC)) as total_montant
FROM organizations o
LEFT JOIN salary_transfers st ON st.org_id = o.id
GROUP BY o.id, o.name
ORDER BY total_virements DESC;
```

### Virements pour un mois spécifique
```sql
SELECT 
  st.*,
  o.name as organization_name
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE DATE_TRUNC('month', st.period_month) = '2025-01-01'::date
ORDER BY st.deadline ASC;
```

### Virements en attente de PDF
```sql
SELECT 
  st.id,
  st.period_label,
  o.name as organization_name,
  st.deadline,
  st.created_at
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE st.callsheet_url IS NULL
ORDER BY st.deadline ASC;
```

### Virements avec notifications en erreur
```sql
SELECT 
  st.id,
  st.period_label,
  o.name as organization_name,
  st.notification_sent,
  st.notification_ok,
  st.notes
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE st.notification_sent = true 
  AND st.notification_ok = false
ORDER BY st.created_at DESC;
```

## Contrats associés à un virement

### Contrats pour une période donnée (logique de filtrage)
```sql
-- Récupérer tous les contrats qui seront inclus dans un PDF
-- pour une organisation et un mois donnés
SELECT 
  c.id,
  c.employee_first_name,
  c.employee_last_name,
  c.employee_iban,
  c.payment_date,
  c.net_amount,
  c.gross_amount
FROM cddu_contracts c
WHERE c.org_id = 'ORG_ID_ICI'
  AND c.payment_date IS NOT NULL
  AND DATE_TRUNC('month', c.payment_date) = '2025-01-01'::date
ORDER BY c.employee_last_name, c.employee_first_name;
```

### Calculer le total net pour une période
```sql
SELECT 
  DATE_TRUNC('month', c.payment_date) as mois,
  COUNT(c.id) as nombre_contrats,
  SUM(CAST(c.net_amount AS NUMERIC)) as total_net,
  SUM(CAST(c.gross_amount AS NUMERIC)) as total_brut
FROM cddu_contracts c
WHERE c.org_id = 'ORG_ID_ICI'
  AND c.payment_date IS NOT NULL
  AND DATE_TRUNC('month', c.payment_date) = '2025-01-01'::date
GROUP BY DATE_TRUNC('month', c.payment_date);
```

### Contrats par virement (avec jointure)
```sql
SELECT 
  st.id as virement_id,
  st.period_label,
  c.id as contrat_id,
  c.employee_first_name,
  c.employee_last_name,
  c.payment_date,
  c.net_amount
FROM salary_transfers st
LEFT JOIN cddu_contracts c ON (
  c.org_id = st.org_id
  AND DATE_TRUNC('month', c.payment_date) = DATE_TRUNC('month', st.period_month)
  AND c.payment_date IS NOT NULL
)
WHERE st.id = 'VIREMENT_ID_ICI'
ORDER BY c.employee_last_name, c.employee_first_name;
```

### Contrats sans IBAN pour une période
```sql
-- Utile pour détecter les problèmes avant génération
SELECT 
  c.id,
  c.employee_first_name,
  c.employee_last_name,
  c.employee_iban,
  c.payment_date,
  c.net_amount
FROM cddu_contracts c
WHERE c.org_id = 'ORG_ID_ICI'
  AND c.payment_date IS NOT NULL
  AND DATE_TRUNC('month', c.payment_date) = '2025-01-01'::date
  AND (c.employee_iban IS NULL OR c.employee_iban = '')
ORDER BY c.employee_last_name;
```

## Statistiques et rapports

### Résumé mensuel par organisation
```sql
SELECT 
  o.name as organisation,
  TO_CHAR(st.period_month, 'YYYY-MM') as mois,
  COUNT(st.id) as nb_virements,
  SUM(CAST(st.total_net AS NUMERIC)) as total_montant,
  COUNT(st.callsheet_url) as nb_pdf_generes,
  COUNT(CASE WHEN st.notification_sent = true THEN 1 END) as nb_notifications_envoyees,
  COUNT(CASE WHEN st.client_wire_received_at IS NOT NULL THEN 1 END) as nb_virements_recus
FROM organizations o
LEFT JOIN salary_transfers st ON st.org_id = o.id
WHERE st.period_month >= '2025-01-01'
GROUP BY o.id, o.name, TO_CHAR(st.period_month, 'YYYY-MM')
ORDER BY mois DESC, organisation ASC;
```

### Virements par mode de paiement
```sql
SELECT 
  st.mode,
  COUNT(st.id) as nombre,
  SUM(CAST(st.total_net AS NUMERIC)) as total_montant,
  AVG(CAST(st.total_net AS NUMERIC)) as montant_moyen
FROM salary_transfers st
GROUP BY st.mode
ORDER BY nombre DESC;
```

### Délais entre création et génération PDF
```sql
SELECT 
  st.id,
  st.period_label,
  o.name as organisation,
  st.created_at,
  st.updated_at,
  st.callsheet_url,
  EXTRACT(EPOCH FROM (st.updated_at - st.created_at)) / 3600 as heures_ecart
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE st.callsheet_url IS NOT NULL
ORDER BY heures_ecart DESC;
```

### Taux de succès des notifications
```sql
SELECT 
  COUNT(*) as total_virements,
  COUNT(CASE WHEN notification_sent = true THEN 1 END) as notifications_envoyees,
  COUNT(CASE WHEN notification_ok = true THEN 1 END) as notifications_ok,
  ROUND(
    100.0 * COUNT(CASE WHEN notification_ok = true THEN 1 END) / 
    NULLIF(COUNT(CASE WHEN notification_sent = true THEN 1 END), 0), 
    2
  ) as taux_succes_pct
FROM salary_transfers
WHERE notification_sent = true;
```

## Opérations de maintenance

### Mettre à jour le total_net d'un virement
```sql
-- Recalculer et mettre à jour le total net basé sur les contrats
UPDATE salary_transfers st
SET 
  total_net = (
    SELECT SUM(CAST(c.net_amount AS NUMERIC))
    FROM cddu_contracts c
    WHERE c.org_id = st.org_id
      AND c.payment_date IS NOT NULL
      AND DATE_TRUNC('month', c.payment_date) = DATE_TRUNC('month', st.period_month)
  ),
  updated_at = NOW()
WHERE st.id = 'VIREMENT_ID_ICI';
```

### Supprimer un virement (avec précaution)
```sql
-- Vérifier d'abord qu'il n'y a pas de dépendances
SELECT * FROM salary_transfers WHERE id = 'VIREMENT_ID_ICI';

-- Supprimer (ne pas oublier de supprimer le fichier S3 manuellement)
DELETE FROM salary_transfers WHERE id = 'VIREMENT_ID_ICI';
```

### Regénérer tous les PDFs manquants (via script)
```sql
-- Lister les virements sans PDF
SELECT 
  st.id,
  st.org_id,
  st.period_month,
  o.name
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE st.callsheet_url IS NULL
ORDER BY st.deadline ASC;
```

### Nettoyer les URLs S3 obsolètes
```sql
-- Lister les virements avec URLs S3 (pour vérification manuelle)
SELECT 
  id,
  period_label,
  callsheet_url,
  updated_at
FROM salary_transfers
WHERE callsheet_url IS NOT NULL
ORDER BY updated_at DESC;
```

## Vérifications de cohérence

### Virements avec total_net incohérent
```sql
-- Compare le total_net stocké avec le total calculé des contrats
SELECT 
  st.id,
  st.period_label,
  st.total_net as total_enregistre,
  (
    SELECT SUM(CAST(c.net_amount AS NUMERIC))
    FROM cddu_contracts c
    WHERE c.org_id = st.org_id
      AND c.payment_date IS NOT NULL
      AND DATE_TRUNC('month', c.payment_date) = DATE_TRUNC('month', st.period_month)
  ) as total_calcule,
  ABS(
    CAST(st.total_net AS NUMERIC) - 
    COALESCE((
      SELECT SUM(CAST(c.net_amount AS NUMERIC))
      FROM cddu_contracts c
      WHERE c.org_id = st.org_id
        AND c.payment_date IS NOT NULL
        AND DATE_TRUNC('month', c.payment_date) = DATE_TRUNC('month', st.period_month)
    ), 0)
  ) as difference
FROM salary_transfers st
WHERE st.total_net IS NOT NULL
HAVING ABS(
  CAST(st.total_net AS NUMERIC) - 
  COALESCE((
    SELECT SUM(CAST(c.net_amount AS NUMERIC))
    FROM cddu_contracts c
    WHERE c.org_id = st.org_id
      AND c.payment_date IS NOT NULL
      AND DATE_TRUNC('month', c.payment_date) = DATE_TRUNC('month', st.period_month)
  ), 0)
) > 0.01
ORDER BY difference DESC;
```

### Virements pour des organisations inexistantes
```sql
SELECT st.*
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
WHERE o.id IS NULL;
```

### Doublons de virements (même org, même période, même mode)
```sql
SELECT 
  org_id,
  period_month,
  mode,
  COUNT(*) as nombre_doublons
FROM salary_transfers
GROUP BY org_id, period_month, mode
HAVING COUNT(*) > 1
ORDER BY nombre_doublons DESC;
```

## Requêtes d'analyse

### Évolution mensuelle du nombre de virements
```sql
SELECT 
  TO_CHAR(period_month, 'YYYY-MM') as mois,
  COUNT(*) as nombre_virements,
  COUNT(CASE WHEN callsheet_url IS NOT NULL THEN 1 END) as avec_pdf,
  SUM(CAST(total_net AS NUMERIC)) as total_montant
FROM salary_transfers
WHERE period_month >= '2024-01-01'
GROUP BY TO_CHAR(period_month, 'YYYY-MM')
ORDER BY mois DESC;
```

### Top 10 des organisations par volume
```sql
SELECT 
  o.name as organisation,
  COUNT(st.id) as nb_virements,
  SUM(CAST(st.total_net AS NUMERIC)) as total_montant,
  AVG(CAST(st.total_net AS NUMERIC)) as montant_moyen
FROM organizations o
LEFT JOIN salary_transfers st ON st.org_id = o.id
GROUP BY o.id, o.name
ORDER BY total_montant DESC
LIMIT 10;
```

### Analyse des deadlines
```sql
SELECT 
  EXTRACT(DOW FROM deadline) as jour_semaine, -- 0=Dimanche, 1=Lundi, ...
  COUNT(*) as nombre_virements
FROM salary_transfers
GROUP BY EXTRACT(DOW FROM deadline)
ORDER BY jour_semaine;
```

## Scripts d'export

### Export CSV des virements
```sql
COPY (
  SELECT 
    st.id,
    o.name as organisation,
    st.period_label,
    st.period_month,
    st.deadline,
    st.mode,
    st.total_net,
    CASE WHEN st.callsheet_url IS NOT NULL THEN 'Oui' ELSE 'Non' END as pdf_genere,
    CASE WHEN st.notification_sent THEN 'Oui' ELSE 'Non' END as notification_envoyee,
    CASE WHEN st.notification_ok THEN 'Oui' ELSE 'Non' END as notification_ok,
    st.client_wire_received_at,
    st.notes,
    st.created_at
  FROM salary_transfers st
  LEFT JOIN organizations o ON o.id = st.org_id
  WHERE st.period_month >= '2025-01-01'
  ORDER BY st.period_month DESC
) TO '/tmp/virements_export.csv' WITH CSV HEADER;
```

### Export des contrats pour un virement
```sql
COPY (
  SELECT 
    c.employee_last_name as nom,
    c.employee_first_name as prenom,
    c.employee_iban as iban,
    c.payment_date as date_paiement,
    c.net_amount as montant_net,
    c.gross_amount as montant_brut
  FROM cddu_contracts c
  WHERE c.org_id = 'ORG_ID_ICI'
    AND c.payment_date IS NOT NULL
    AND DATE_TRUNC('month', c.payment_date) = '2025-01-01'::date
  ORDER BY c.employee_last_name, c.employee_first_name
) TO '/tmp/contrats_export.csv' WITH CSV HEADER;
```

## Notes importantes

1. **Remplacer les placeholders** :
   - `'ORG_ID_ICI'` → ID réel de l'organisation
   - `'VIREMENT_ID_ICI'` → ID réel du virement
   - `'2025-01-01'` → Date de période réelle

2. **Performance** :
   - Pour les grandes tables, ajouter des index :
     ```sql
     CREATE INDEX idx_salary_transfers_org_id ON salary_transfers(org_id);
     CREATE INDEX idx_salary_transfers_period_month ON salary_transfers(period_month);
     CREATE INDEX idx_cddu_contracts_payment_date ON cddu_contracts(payment_date);
     ```

3. **Types de données** :
   - `total_net`, `net_amount`, `gross_amount` peuvent être `TEXT` ou `NUMERIC`
   - Utiliser `CAST(...AS NUMERIC)` pour les calculs

4. **Dates** :
   - `period_month` est stocké comme `DATE` (1er du mois)
   - `deadline` est une `DATE` complète
   - `payment_date` est une `DATE` complète

5. **RLS (Row Level Security)** :
   - Ces requêtes doivent être exécutées avec un compte ayant les permissions appropriées
   - En production, utiliser le Service Role Key pour les opérations sensibles