#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import os
import sys

# Ajouter le chemin courant
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

try:
    import db_adapter
except ImportError:
    print("Content-type: text/html; charset=UTF-8\n")
    print("<html><body><h1>Erreur de configuration</h1>")
    print("<p>Impossible de trouver le module <code>db_adapter.py</code>.</p>")
    print("</body></html>")
    sys.exit(1)

# Dictionnaire de construction des requêtes SQL (identique à l'original)
# Format: [colonnes SELECT, LEFT JOIN clause, champ WHERE, type, étiquettes]
base_construct_requetes = {
    "nomGite": ("nomGite", "LEFT JOIN nomgite on gite.nGiteId = nomgite.nGiteId", "nomgite.nGiteId", 0, "Nom du gîte:"),
    "typeGite": ("typeGite", "LEFT JOIN typegite ON gite.tGiteId = typegite.tGiteId", "typegite.tGiteId", 0, "Type de gîte:"),
    "alteration": ("alteration", "LEFT JOIN alteration ON gite.alterationId = alteration.alterationId", "alteration.alterationId", 0, "Altération:"),
    "mineralisation": ("mineralisation", "LEFT JOIN mineralisation ON gite.mineralisationId = mineralisation.mineralisationId", "mineralisation.mineralisationId", 0, "Type de minéralisation:"),
    "rocheEncaissante": ("rocheEncaissante", "LEFT JOIN rocheencaissante ON gite.rocheEncId = rocheencaissante.rocheEncId", "rocheencaissante.rocheEncId", 0, "Roche encaissante:"),
    "identificateur": ("identificateur", "LEFT JOIN origine on gite.origId = origine.origId", "", 0, "Origine:"),
    "tiroir": ("tiroir", "", "tiroir", 0, "Tiroir:"),
    "numeroGm": ("numeroGm", "", "numeroGm", 0, "Numéro:"),
    "numero": ("numeroGm,numeroGmRef,ancienNumero,autreNumero", "", "", 0, "NuméroGm:NuméroGmRef:Ancien numéro:Autre numéro:"),
    "structure": ("structure,categorieStructure", 
                  "LEFT JOIN relationstructure on gite.giteId = relationstructure.giteId "
                  "LEFT JOIN structure on structure.structureId = relationstructure.structureId "
                  "LEFT JOIN categoriestructure on categoriestructure.catStructureId = relationstructure.catStructureId",
                  "relationstructure.structureId", 1, "Structure:Catégorie structure:"),
    "texture": ("texture,categorieTexture", 
                "LEFT JOIN relationtexture on gite.giteId = relationtexture.giteId "
                "LEFT JOIN texture on texture.textureId = relationtexture.textureId "
                "LEFT JOIN categorietexture on categorietexture.catTextureId = relationtexture.catTextureId",
                "relationtexture.textureId", 1, "Texture:Catégorie texture:"),
    "mineralisation_principale": ("mineral,categorieMineral", 
                                  "LEFT JOIN relationmineral on gite.giteId = relationmineral.giteId "
                                  "LEFT JOIN mineral on mineral.mineralId = relationmineral.mineralId "
                                  "LEFT JOIN categoriemineral on categoriemineral.catMineralId = relationmineral.catMineralId",
                                  "categorieMineral = 'Minéralisation Principale' AND relationmineral.mineralId", 1, "Minéralisation:Catégorie minéralisation:"),
    "localisation_geo": ("region,province,pays", 
                         "LEFT JOIN relationregion on gite.giteId = relationregion.giteId "
                         "LEFT JOIN region on region.regionId = relationregion.regionId "
                         "LEFT JOIN province on province.provinceId = relationregion.provinceId "
                         "LEFT JOIN pays on pays.paysId = relationregion.paysId",
                         "region.regionId", 1, "Région:Province:Pays:"),
    "pays": ("pays", 
             "LEFT JOIN relationregion on gite.giteId = relationregion.giteId "
             "LEFT JOIN pays on pays.paysId = relationregion.paysId",
             "pays.paysId", 0, "Pays:"),
    "hyperlien": ("hyperlien", "LEFT JOIN hyperlien ON gite.giteId = hyperlien.giteId", "", 1, "Hyperlien:"),
    "photo": ("photoid", "LEFT JOIN photo ON gite.giteId = photo.giteId", "", 2, "Photo:")
}

def print_header(title):
    """Génère l'en-tête HTML5 moderne avec styles intégrés."""
    print("Content-type: text/html; charset=UTF-8\n")
    print(f"""<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{title}</title>
    {db_adapter.get_css_style()}
</head>
<body>
<div class="container">
    <header>
        <h1>{title}</h1>
        <p class="subtitle">Résultat de la recherche dans la base de données sur les gîtes minéraux.</p>
    </header>
""")

def print_end():
    """Ferme la page HTML."""
    print("""
</div>
</body>
</html>""")

def genere_requete_tmp(form, dict_selection):
    """Génère la requête SQL pour remplir la table temporaire 'tmp' (critères WHERE)."""
    req_tmp = "INSERT into tmp SELECT DISTINCT gite.giteId FROM gite"
    
    search_query = form.getvalue("search_query", "")
    if isinstance(search_query, list):
        search_query = search_query[0] if search_query else ""
    search_query = search_query.strip()
    
    # 1. Joindre les tables nécessaires pour la recherche textuelle globale
    if search_query:
        search_joins = [
            "LEFT JOIN nomgite on gite.nGiteId = nomgite.nGiteId",
            "LEFT JOIN typegite ON gite.tGiteId = typegite.tGiteId",
            "LEFT JOIN relationregion on gite.giteId = relationregion.giteId",
            "LEFT JOIN pays on pays.paysId = relationregion.paysId"
        ]
        for join_clause in search_joins:
            for join in join_clause.strip().split("LEFT JOIN"):
                join = join.strip()
                if not join:
                    continue
                full_join = f"LEFT JOIN {join}"
                table_name = join.split()[0]
                if f"JOIN {table_name} " not in req_tmp and f"JOIN {table_name}\n" not in req_tmp and not req_tmp.endswith(f"JOIN {table_name}"):
                    req_tmp = req_tmp + ' ' + full_join
                    
    # 2. Génération des LEFT JOIN pour les clauses WHERE (sans doublons de table)
    for key in dict_selection:
        if key in base_construct_requetes:
            join_clause = base_construct_requetes[key][1]
            if join_clause:
                for join in join_clause.strip().split("LEFT JOIN"):
                    join = join.strip()
                    if not join:
                        continue
                    full_join = f"LEFT JOIN {join}"
                    table_name = join.split()[0]
                    if f"JOIN {table_name} " not in req_tmp and f"JOIN {table_name}\n" not in req_tmp and not req_tmp.endswith(f"JOIN {table_name}"):
                        req_tmp = req_tmp + ' ' + full_join

    # 3. Génération des clauses WHERE
    where_clauses = []
    
    if search_query:
        escaped_search = search_query.replace("'", "''")
        or_clauses = [
            f"nomgite.nomGite LIKE '%{escaped_search}%'",
            f"typegite.typeGite LIKE '%{escaped_search}%'",
            f"pays.pays LIKE '%{escaped_search}%'"
        ]
        if search_query.isdigit():
            or_clauses.append(f"gite.numeroGm = {int(search_query)}")
        where_clauses.append("(" + " OR ".join(or_clauses) + ")")

    for key in dict_selection:
        if key in base_construct_requetes:
            field_name = base_construct_requetes[key][2]
            value = form.getvalue(key)
            escaped_val = value.replace("'", "''") # Sécurisation et gestion des apostrophes
            where_clauses.append(f"{field_name} = '{escaped_val}'")

    if where_clauses:
        req_tmp += " WHERE " + " AND ".join(where_clauses)
    
    if where_clauses:
        req_tmp += " AND gite.giteId > 0"
    else:
        req_tmp += " WHERE gite.giteId > 0"

    return req_tmp

def genere_requete_0(dict_cb):
    """Génère la requête SELECT pour les données uniques par échantillon (Type 0)."""
    # On sélectionne toujours gite.numeroGm en deuxième position pour l'affichage en haut à droite des cartes
    etiq = "No échantillon:Numéro GM:"
    req_0 = "SELECT gite.giteId, gite.numeroGm"
    
    for key in dict_cb:
        if key in base_construct_requetes:
            if base_construct_requetes[key][3] == 0:
                req_0 += ',' + base_construct_requetes[key][0]
                etiq += base_construct_requetes[key][4]

    req_0 += ' ' + "FROM tmp LEFT JOIN gite on tmp.giteId = gite.giteId"
    
    for key in dict_cb:
        if key in base_construct_requetes:
            if base_construct_requetes[key][3] == 0:
                join_clause = base_construct_requetes[key][1]
                if join_clause:
                    for join in join_clause.strip().split("LEFT JOIN"):
                        join = join.strip()
                        if not join:
                            continue
                        full_join = f"LEFT JOIN {join}"
                        table_name = join.split()[0]
                        if f"JOIN {table_name} " not in req_0 and f"JOIN {table_name}\n" not in req_0 and not req_0.endswith(f"JOIN {table_name}"):
                            req_0 += ' ' + full_join

    return etiq, req_0


def genere_requete_m(key):
    """Génère la requête SELECT pour les relations multiples (Type 1 et 2)."""
    etiq = "No échantillon:"
    req_m = (f"SELECT gite.giteId, {base_construct_requetes[key][0]} "
             f"FROM tmp LEFT JOIN gite on tmp.giteId = gite.giteId ")
    
    if base_construct_requetes[key][1] != "":
        req_m += base_construct_requetes[key][1]
        
    etiq += base_construct_requetes[key][4]
    return etiq, req_m

def run_report(form):
    selection_done = False
    cb_done = False
    dict_selection = {}  # Critères de sélection (listes déroulantes)
    dict_cb = {}         # Champs à afficher (checkboxes)
    
    # 1. Traitement des valeurs du formulaire
    search_query_present = False
    for key in form.keys():
        if key in ("export_type", "search_query"):
            continue
            
        # Case à cocher
        if key.endswith("_cb"):
            cb_done = True
            col_name = key[:-3] # enlève le suffixe "_cb"
            dict_cb[col_name] = 1
        # Liste déroulante
        else:
            val = form.getvalue(key)
            if val != '*':
                selection_done = True
                dict_selection[key] = val
                
    # Vérification de la présence d'une recherche textuelle libre
    search_val = form.getvalue("search_query", "")
    if isinstance(search_val, list):
        search_val = search_val[0] if search_val else ""
    if search_val.strip():
        search_query_present = True
            
    # 2. Validation
    if not cb_done or (not selection_done and not search_query_present):
        print_header("Erreur de requête")
        print("""<div class="alert alert-error">
            <strong>Données insuffisantes :</strong> Vous devez saisir un texte de recherche libre ou sélectionner au moins un critère de recherche par catégorie, 
            et cocher au moins une case à afficher.
        </div>""")
        print('<div class="back-link"><a href="giteform.cgi" class="btn btn-secondary">Retour au formulaire</a></div>')
        print_end()
        return

    # 3. Connexion à la base
    conn, cursor, is_sqlite = db_adapter.connect_database()

    # 4. Requête et insertion dans la table temporaire
    try:
        req_tmp = genere_requete_tmp(form, dict_selection)
        db_adapter.setup_temporary_table(cursor, is_sqlite, req_tmp)
    except Exception as err:
        print_header("Erreur SQL")
        print(f'<div class="alert alert-error"><strong>Erreur d\'initialisation :</strong> Impossible de filtrer les données. <code>{err}</code></div>')
        print('<div class="back-link"><a href="giteform.cgi" class="btn btn-secondary">Retour au formulaire</a></div>')
        print_end()
        cursor.close()
        conn.close()
        return

    # 5. Extraction des données de Type 0 (uniques par échantillon)
    allRecords_0 = []
    etiq_0_list = []
    try:
        etiq_0, req_0 = genere_requete_0(dict_cb)
        etiq_0_list = etiq_0.split(":")
        cursor.execute(req_0)
        allRecords_0 = cursor.fetchall()
    except Exception as err:
        print_header("Erreur SQL")
        print(f'<div class="alert alert-error"><strong>Erreur de requête (données primaires) :</strong> <code>{err}</code></div>')
        print('<div class="back-link"><a href="giteform.cgi" class="btn btn-secondary">Retour au formulaire</a></div>')
        print_end()
        cursor.close()
        conn.close()
        return

    # Si aucun résultat
    if not allRecords_0:
        print_header("Résultats de la recherche")
        print("""
        <div class="results-header">
            <span class="results-info">Recherche terminée</span>
            <span class="results-count">Aucun échantillon trouvé</span>
        </div>
        <div class="panel" style="text-align: center; padding: 3rem 1.5rem;">
            <p style="color: var(--text-secondary); margin-bottom: 1.5rem; font-size: 1.1rem;">Aucun échantillon ne correspond à vos critères de recherche.</p>
            <a href="giteform.cgi" class="btn btn-primary">Modifier la recherche</a>
        </div>
        """)
        print_end()
        cursor.close()
        conn.close()
        return

    # 6. Extraction des données de Type M (relations multiples : structure, texture, photos...)
    allRecords_multi = {}
    etiq_multi = {}
    
    try:
        for key in dict_cb:
            if key in base_construct_requetes:
                type_requete = base_construct_requetes[key][3]
                if type_requete in (1, 2):
                    etiq_multi[key], req_m = genere_requete_m(key)
                    cursor.execute(req_m)
                    allRecords_multi[key] = cursor.fetchall()
    except Exception as err:
        print_header("Erreur SQL (Champs multiples)")
        print(f'<div class="alert alert-error"><strong>Erreur lors de la récupération des données détaillées ({key}) :</strong> <code>{err}</code></div>')
        print_end()
        cursor.close()
        conn.close()
        return

    # Fermeture de la connexion à la base
    cursor.close()
    conn.close()

    # 7. Indexation / Groupement des résultats multiples par giteId pour une recherche en O(1)
    grouped_multi = {}
    for key in allRecords_multi:
        grouped_multi[key] = {}
        for record in allRecords_multi[key]:
            giteId = record[0]
            if giteId not in grouped_multi[key]:
                grouped_multi[key][giteId] = []
            grouped_multi[key][giteId].append(record)

    # 8. Affichage de la page de résultats
    print_header("Résultats de la recherche")
    
    print(f"""
    <div class="results-header">
        <span class="results-info">Recherche effectuée avec succès</span>
        <span class="results-count"><strong style="color: var(--primary);">{len(allRecords_0)}</strong> échantillon(s) trouvé(s)</span>
    </div>
    """)

    # Boucle sur chaque échantillon unique (Lignes principales de type 0)
    for record_0 in allRecords_0:
        giteId = record_0[0]
        gmNum = record_0[1]
        
        # Détermination du titre de la carte (Nom du gîte si disponible, sinon ID)
        gite_name = ""
        if "nomGite" in dict_cb:
            # Recherche de l'index de nomGite dans les résultats
            # Le select commence par giteId (index 0) et numeroGm (index 1)
            select_cols = []
            for k in dict_cb:
                if k in base_construct_requetes and base_construct_requetes[k][3] == 0:
                    select_cols.append(k)
            
            if "nomGite" in select_cols:
                col_idx = select_cols.index("nomGite") + 2 # +2 car giteId est en 0 et numeroGm est en 1
                if col_idx < len(record_0) and record_0[col_idx]:
                    gite_name = str(record_0[col_idx])

        card_title = gite_name if gite_name else f"Échantillon #{giteId}"
        gm_display = f"GM: {gmNum}" if gmNum else f"ID: {giteId}"

        print(f"""
        <div class="sample-card">
            <div class="sample-card-header">
                <h3 class="sample-title">{card_title}</h3>
                <span class="sample-id">{gm_display}</span>
            </div>
            <div class="property-grid">
        """)

        # Affichage des propriétés simples (Type 0)
        # On saute les index 0 et 1 (qui sont le giteId et le numeroGm)
        # On affiche uniquement les valeurs existantes (non vides / non nulles)
        select_cols = []
        for k in dict_cb:
            if k in base_construct_requetes and base_construct_requetes[k][3] == 0:
                select_cols.append(k)

        for i in range(2, len(record_0)):
            val = record_0[i]
            if val is not None and str(val).strip() != "" and str(val).lower() != "none":
                # Associe l'étiquette correspondante
                label = etiq_0_list[i] if i < len(etiq_0_list) else f"Propriété {i}"
                print(f"""
                <div class="property-item">
                    <span class="property-label">{label.rstrip(':')}</span>
                    <span class="property-value">{val}</span>
                </div>
                """)

        # Fin de la grille des propriétés simples
        print("</div>")


        # Affichage des propriétés multiples (Type 1)
        for key in dict_cb:
            if key in base_construct_requetes and base_construct_requetes[key][3] == 1:
                if key in grouped_multi and giteId in grouped_multi[key]:
                    records = grouped_multi[key][giteId]
                    etiq_list = etiq_multi[key].split(':')
                    
                    label_section = base_construct_requetes[key][4].split(':')[0]
                    print(f"""
                    <div class="multi-section">
                        <h4 class="multi-title">{label_section}</h4>
                        <div class="multi-grid">
                    """)
                    
                    for record_multi in records:
                        # Concaténation des valeurs secondaires
                        parts = []
                        for i in range(1, len(record_multi)):
                            val = record_multi[i]
                            if val is not None and str(val).strip() != "" and str(val).lower() != "none":
                                # Traitement spécial pour les hyperliens
                                if key == "hyperlien":
                                    print(f'<div class="multi-item"><a href="{val}" target="_blank" style="color: var(--primary); text-decoration: underline;">{val}</a></div>')
                                    continue
                                parts.append(str(val))
                        
                        if key != "hyperlien" and parts:
                            text = " - ".join(parts)
                            print(f'<div class="multi-item">{text}</div>')
                            
                    print("</div></div>") # Fin multi-grid & multi-section

        # Affichage des photos (Type 2)
        for key in dict_cb:
            if key in base_construct_requetes and base_construct_requetes[key][3] == 2:
                if key in grouped_multi and giteId in grouped_multi[key]:
                    records = grouped_multi[key][giteId]
                    
                    photos_to_render = []
                    for record_multi in records:
                        photo_id = record_multi[1]
                        if photo_id and int(photo_id) > 0:
                            photos_to_render.append(photo_id)
                            
                    if photos_to_render:
                        print("""
                        <div class="photo-gallery">
                            <h4 class="multi-title">Galerie Photos</h4>
                            <div class="photo-grid">
                        """)
                        
                        for p_id in photos_to_render:
                            print(f"""
                            <div class="photo-wrapper">
                                <a href="giterep.cgi?action=photo&id={p_id}" target="_blank" title="Cliquez pour agrandir">
                                    <img src="giterep.cgi?action=photo&id={p_id}" alt="Photo de l'échantillon #{giteId}">
                                </a>
                            </div>
                            """)
                            
                        print("</div></div>") # Fin photo-grid & photo-gallery

        # Fin de la carte de l'échantillon
        print("</div>")

    # Bouton retour en bas de page
    print("""
    <div class="back-link">
        <a href="giteform.cgi" class="btn btn-secondary">Retourner au formulaire de recherche</a>
    </div>
    """)

    print_end()