#!/usr/bin/env python3
"""
Script d'audit des données orphelines dans les relations de clés étrangères.
Génère un rapport détaillé en Markdown pour chaque relation problématique.
"""
import os
import re
from pathlib import Path

import mysql.connector


def connecter_mysql():
    return mysql.connector.connect(
        host=os.getenv("MYSQL_HOST", "127.0.0.1"),
        port=int(os.getenv("MYSQL_PORT", "3306")),
        user=os.getenv("MYSQL_USER", "app_user"),
        password=os.getenv("MYSQL_PASSWORD", "apppass"),
        database=os.getenv("MYSQL_DATABASE", "app_db"),
    )


def auditer_relation_orpheline(cursor, table_source: str, col_source: str, table_cible: str, col_cible: str) -> dict:
    """Audite une relation FK problématique et retourne les statistiques et exemples d'orphelins."""
    
    # Compter les valeurs totales
    cursor.execute(f"SELECT COUNT(*) FROM `{table_source}` WHERE `{col_source}` IS NOT NULL")
    total_non_null = cursor.fetchone()[0]
    
    # Compter les orphelins
    cursor.execute(
        f"SELECT COUNT(DISTINCT t1.`{col_source}`) FROM `{table_source}` t1 "
        f"LEFT JOIN `{table_cible}` t2 ON t1.`{col_source}` = t2.`{col_cible}` "
        f"WHERE t1.`{col_source}` IS NOT NULL AND t2.`{col_cible}` IS NULL"
    )
    nb_orphelins = cursor.fetchone()[0]
    
    # Récupérer les exemples d'orphelins
    cursor.execute(
        f"SELECT DISTINCT t1.`{col_source}` FROM `{table_source}` t1 "
        f"LEFT JOIN `{table_cible}` t2 ON t1.`{col_source}` = t2.`{col_cible}` "
        f"WHERE t1.`{col_source}` IS NOT NULL AND t2.`{col_cible}` IS NULL "
        f"LIMIT 20"
    )
    exemples = [row[0] for row in cursor.fetchall()]
    
    # Récupérer les IDs des lignes problématiques (premiers exemples)
    cursor.execute(
        f"SELECT id, `{col_source}` FROM `{table_source}` WHERE `{col_source}` IS NOT NULL "
        f"AND `{col_source}` NOT IN (SELECT `{col_cible}` FROM `{table_cible}`) "
        f"LIMIT 5"
    )
    lignes_exemple = cursor.fetchall()
    
    taux_orphelins = (nb_orphelins / total_non_null * 100) if total_non_null > 0 else 0
    
    return {
        "total_non_null": total_non_null,
        "nb_orphelins": nb_orphelins,
        "taux_orphelins": taux_orphelins,
        "exemples_valeurs": exemples,
        "lignes_exemple": lignes_exemple,
    }


def generer_rapport_markdown():
    """Génère un rapport d'audit complet en Markdown."""
    
    connection = connecter_mysql()
    cursor = connection.cursor()
    
    relations_problematiques = [
        ("avenants_contrat", "id_avenant", "avenants_contrat", "id", 
         "Avenants contrats auto-référencés"),
        ("avenants_contrat", "id_contrat", "contrats", "id_contrat", 
         "Avenants → Contrats"),
        ("contrats", "ID_Contrat", "contrats", "id", 
         "Contrats auto-référencés (source vs auto-généré)"),
        ("contrats", "Id_Salarie", "employes", "id", 
         "Contrats → Employés"),
        ("visites_medicales", "id_salarie", "employes", "id", 
         "Visites médicales → Employés"),
    ]
    
    rapport = []
    rapport.append("# Audit des Données Orphelines - Relations FK\n")
    rapport.append("_Généré automatiquement pour identifier les incohérences de données_\n\n")
    
    rapport.append("## Résumé Exécutif\n\n")
    rapport.append("Cette analyse identifie les lignes dans la base de données qui référencent des enregistrements inexistants.\n")
    rapport.append("Ces orphelins empêchent l'ajout de contraintes de clé étrangère strictes.\n\n")
    
    for table_source, col_source, table_cible, col_cible, description in relations_problematiques:
        audit = auditer_relation_orpheline(cursor, table_source, col_source, table_cible, col_cible)
        
        rapport.append(f"## {description}\n\n")
        rapport.append(f"**Relation:** `{table_source}`.`{col_source}` → `{table_cible}`.`{col_cible}`\n\n")
        
        rapport.append("### Statistiques\n\n")
        rapport.append(f"- **Total de lignes non-NULL:** {audit['total_non_null']:,}\n")
        rapport.append(f"- **Nombre de valeurs orphelines:** {audit['nb_orphelins']:,}\n")
        rapport.append(f"- **Taux d'orphelins:** {audit['taux_orphelins']:.1f}%\n")
        rapport.append(f"- **Lignes valides:** {audit['total_non_null'] - audit['nb_orphelins']:,}\n\n")
        
        if audit['taux_orphelins'] > 50:
            rapport.append("⚠️ **CRITIQUE:** Plus de 50% des références sont orphelines\n\n")
        elif audit['taux_orphelins'] > 10:
            rapport.append("⚠️ **ATTENTION:** Plus de 10% des références sont orphelines\n\n")
        else:
            rapport.append("✓ **Acceptable:** Moins de 10% d'orphelins\n\n")
        
        rapport.append("### Exemples de valeurs orphelines\n\n")
        rapport.append("```\n")
        for exemple in audit['exemples_valeurs'][:10]:
            rapport.append(f"{exemple}\n")
        rapport.append("```\n\n")
        
        rapport.append("### Exemples de lignes problématiques\n\n")
        rapport.append("| ID | Valeur référencée |\n")
        rapport.append("|---|---|\n")
        for id_ligne, valeur in audit['lignes_exemple']:
            rapport.append(f"| {id_ligne} | {valeur} |\n")
        rapport.append("\n")
        
        rapport.append("### Recommandations\n\n")
        if audit['taux_orphelins'] == 0:
            rapport.append("✓ **Aucune action requise.** Cette relation peut être ajoutée comme FK stricte.\n\n")
        elif audit['taux_orphelins'] < 5:
            rapport.append("- Nettoyer manuellement les " + str(audit['nb_orphelins']) + " orphelins\n")
            rapport.append("- Ou utiliser `SET NULL` dans la FK pour gérer les cas manquants\n")
            rapport.append("- Ou ignorer les lignes orphelines (approche moins recommandée)\n\n")
        elif audit['taux_orphelins'] < 50:
            rapport.append("- Audit recommandé pour comprendre d'où viennent ces références\n")
            rapport.append("- Vérifier si les données source sont corrompues en Access\n")
            rapport.append("- Envisager une migration partiellement manuelle\n\n")
        else:
            rapport.append("- **Problème structural détecté.** Nécessite une investigation approfondie\n")
            rapport.append("- Vérifier la mapping des IDs entre Access et MySQL\n")
            rapport.append("- Envisager un rechargement avec clés source converties\n\n")
        
        rapport.append("---\n\n")
    
    cursor.close()
    connection.close()
    
    return "".join(rapport)


if __name__ == "__main__":
    rapport = generer_rapport_markdown()
    
    chemin_sortie = Path(__file__).resolve().parents[1] / 'docs' / 'audit_orphelins.md'
    chemin_sortie.write_text(rapport, encoding="utf-8")
    
    print(f"✓ Rapport d'audit généré: {chemin_sortie}")
    print("\nAperçu du rapport:\n")
    print(rapport[:1000] + "...\n(voir le fichier complet pour tous les détails)")