
#Imports
import sys
import os
import re

from qgis.core import (
    QgsVectorLayer,
    QgsFeature,
    QgsSpatialIndex,
    QgsFeatureRequest,
)

from PyQt5.QtWidgets import QFileDialog, QMessageBox
from datetime import datetime
from qgis.PyQt.QtCore import QVariant
from PyQt5.QtCore import QDate


from xlsxwriter.utility import xl_rowcol_to_cell

from .constantes import TYPE_PARC_LIBELLES

from .utils import tr

from datetime import datetime

# Statistiques et analyses des éléments forestiers

def calcul_surface_forestiere(layer):
    total_surface_m2 = 0

    for feature in layer.getFeatures():
        possession = feature["Possession"]
        type_parc = feature["typeParc"]
        surface = feature["SURFACE"]
        indice_parc = feature["indice_parc"]

        # Ne pas inclure si indice_parc est défini (non nul, non vide)
        if indice_parc not in (None, "", 0):
            continue

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        if possession and type_parc != 1:
            total_surface_m2 += surface_val * 100

    surface_str = convertir_surface_ha(total_surface_m2)
    return surface_str


def calcul_surface_friche(layer):
    total_surface_m2 = 0

    for feature in layer.getFeatures():
        possession = feature["Possession"]
        type_parc = feature["typeParc"]
        surface = feature["SURFACE"]
        indice_parc = feature["indice_parc"]

        # Ne pas inclure si indice_parc est défini (non nul, non vide)
        if indice_parc not in (None, "", 0):
            continue

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        if possession and type_parc in (8, 9, 14):
            total_surface_m2 += surface_val * 100

    surface_str = convertir_surface_ha(total_surface_m2)
    return surface_str

def compter_parcelles_possedees(layer):
    """
    Compte les entités dont le champ 'Possession' est True.
    """
    total = 0

    for feature in layer.getFeatures():
        possession = feature["Possession"]

        if possession:
            total += 1

    return total


def analyse_types_parcelles(layer, type_parc_min=2, type_parc_max=14, top_n=5):
    """
    Analyse la répartition des surfaces par type de parcelle (2 à 14),
    affiche les `top_n` types les plus représentés + une catégorie "Autres".
    """

    surfaces_par_type = {}
    total_surface_m2 = 0.0  # float

    for feature in layer.getFeatures():
        type_parc = feature["typeParc"]

        # Conversion explicite de surface en float
        try:
            surface = float(feature["SURFACE"])
        except (TypeError, ValueError):
            surface = 0.0

        if type_parc_min <= type_parc <= type_parc_max:
            total_surface_m2 += surface
            if type_parc in surfaces_par_type:
                surfaces_par_type[type_parc] += surface
            else:
                surfaces_par_type[type_parc] = surface

    if total_surface_m2 == 0:
        return "Aucune donnée disponible"

    # Calcul des pourcentages et tri
    pourcentages = {
        type_parc: (surface / total_surface_m2) * 100
        for type_parc, surface in surfaces_par_type.items()
    }
    sorted_types = sorted(pourcentages.items(), key=lambda x: x[1], reverse=True)

    top_types = sorted_types[:top_n]
    autres = sorted_types[top_n:]

    result_lines = []
    for type_parc, pourcentage in top_types:
        nom_type = TYPE_PARC_LIBELLES.get(type_parc, f"Type {type_parc}")
        result_lines.append(f"{nom_type} : {pourcentage:.1f} %")

    autres_pourcentage = sum(p[1] for p in autres)
    if autres_pourcentage > 0:
        result_lines.append(f"Autres : {autres_pourcentage:.1f} %")

    return "\n".join(result_lines)

def analyse_types_essences(layer, top_n=5):
    """
    Analyse la répartition des surfaces pondérées par type d'essence,
    en utilisant les champs plant1 à plant4 et leurs pourcentages Tx1 à Tx4.
    Affiche les `top_n` types les plus représentés + une catégorie "Autres".
    """

    surfaces_par_essence = {}
    total_surface_m2 = 0.0

    for feature in layer.getFeatures():
        try:
            surface = float(feature["SURFACE"])
        except (TypeError, ValueError):
            surface = 0.0

        for i in range(1, 5):
            nom_essence = feature[f"plant{i}"]
            pourcent = feature[f"Tx{i}"]

            if nom_essence is None or nom_essence == "":
                continue

            try:
                poids = (float(pourcent) / 100.0) * surface
            except (TypeError, ValueError, ZeroDivisionError):
                poids = 0.0

            total_surface_m2 += poids
            surfaces_par_essence[nom_essence] = surfaces_par_essence.get(nom_essence, 0.0) + poids

    if total_surface_m2 == 0:
        return "Aucune donnée disponible"

    # Calcul des pourcentages
    pourcentages = {
        essence: (surface / total_surface_m2) * 100
        for essence, surface in surfaces_par_essence.items()
    }
    sorted_essences = sorted(pourcentages.items(), key=lambda x: x[1], reverse=True)

    top_essences = sorted_essences[:top_n]
    autres = sorted_essences[top_n:]

    result_lines = []
    for essence, pourcentage in top_essences:
        result_lines.append(f"{essence} : {pourcentage:.1f} %")

    autres_pourcentage = sum(p[1] for p in autres)
    if autres_pourcentage > 0:
        result_lines.append(f"Autres : {autres_pourcentage:.1f} %")

    return "\n".join(result_lines)

def convertir_surface_ha(surface_m2):
    """
    Convertit une surface en m² en hectares, ares et centiares.
    Ex: 21789 m² -> '2 ha 17 a 89 ca'
    """
    ha = int(surface_m2) // 10000
    reste = int(surface_m2) % 10000
    ares = reste // 100
    centiares = reste % 100
    return f"{ha} ha {ares} a {centiares} ca"

def format_surface_ha_are_ca(value_in_ares):
    try:
        hectares = value_in_ares / 100.0  # conversion ares → hectares décimaux
        ha = int(hectares)  # partie entière hectares
        reste = hectares - ha  # décimales hectares
        are = int(reste * 100)  # conversion décimale hectares → ares
        ca = int(round((reste * 100 - are) * 100))  # conversion décimale ares → centiares

        parts = []
        if ha > 0:
            parts.append(f"{ha} ha")
        if are > 0:
            parts.append(f"{are} a")
        if ca > 0:
            parts.append(f"{ca} ca")

        return " ".join(parts) if parts else "0 ha"
    except Exception:
        return str(value_in_ares)




def total_plantation (layer):

    total_plants = 0

    for feature in layer. getFeatures():
        plants = feature["totalplants"]
        if plants is not None and plants != QVariant():
            total_plants += int(plants)

    return total_plants

def calcul_regroupement(layer):
    """
    Regroupe les entités contiguës dont 'possession' est True,
    retourne les 3 plus grands regroupements en surface totale (champ 'contenance'),
    et affiche pour chaque groupe le numéro de la plus grande parcelle (avec section)
    et la surface totale.
    """

    # Créer un index spatial
    index = QgsSpatialIndex(layer.getFeatures())

    # Mettre en cache toutes les entités ET leur géométrie
    features_dict = {f.id(): (f, f.geometry()) for f in layer.getFeatures()}

    visited = set()
    regroupements = []

    # Boucle principale sur chaque entité non encore visitée
    for fid, (feat, geom) in features_dict.items():
        if fid in visited or not feat['possession']:
            continue

        groupe = []
        to_visit = [fid]

        while to_visit:
            current_fid = to_visit.pop()
            if current_fid in visited:
                continue
            visited.add(current_fid)

            current_feat, current_geom = features_dict[current_fid]

            if not current_feat['possession']:
                continue

            groupe.append(current_feat)

            # Chercher les voisins candidats (bbox intersecte)
            for neighbor_id in index.intersects(current_geom.boundingBox()):
                if neighbor_id not in visited:
                    neighbor_feat, neighbor_geom = features_dict.get(neighbor_id, (None, None))
                    if neighbor_feat and neighbor_geom and neighbor_feat['possession']:
                        # Test topologique touches()
                        if current_geom.touches(neighbor_geom):
                            to_visit.append(neighbor_id)

        # Ajouter le groupe trouvé s'il est non vide
        if groupe:
            regroupements.append(groupe)

    # Trier les groupes par contenance totale décroissante
    regroupements.sort(
        key=lambda grp: sum(f['contenance'] for f in grp if f['contenance'] is not None),
        reverse=True
    )

    # Prendre les 3 plus gros groupes
    top3 = regroupements[:3]

    # Construire le texte de retour
    result_lines = []
    for i, groupe in enumerate(top3, start=1):
        surface_totale = sum(f['contenance'] for f in groupe if f['contenance'] is not None)
        surface_str = convertir_surface_ha(surface_totale)

        # Trouver la parcelle avec la plus grande contenance
        plus_grande_parcelle = max(
            groupe,
            key=lambda f: f['contenance'] if f['contenance'] is not None else 0
        )
        section = plus_grande_parcelle['section']
        numero = plus_grande_parcelle['numero']

        result_lines.append(f"{i} - {section}{numero} = {surface_str}")

    return '\n'.join(result_lines)


def export_standard_excel(self):
    # Charger xlsxwriter
    xlsxwriter_path = os.path.join(os.path.dirname(__file__), "xlsxwriter_lib")
    if xlsxwriter_path not in sys.path:
        sys.path.insert(0, xlsxwriter_path)
    import xlsxwriter
    from PyQt5.QtCore import QDate

    layer = self.iface.activeLayer()
    if not layer or not isinstance(layer, QgsVectorLayer):
        QMessageBox.warning(None, tr("Export"), tr("Veuillez sélectionner une couche valide."))
        return

    champs_generaux = [
        "section", "numero", "indice_parc", "contenance", "SURFACE", "Nous",
        "plant1", "plant2", "plant3", "plant4", "Tx1", "Tx2", "Tx3", "Tx4",
        "annee", "totalplants", "typeParc"
    ]

    champs_exclus = {
        "fid", "commune", "prefixe", "arpente", "possession", "created", "updated",
        "nom_Voisin", "adresse_Voisin", "mail_Voisin", "tel_Voisin"
    }

    available_fields = [field.name() for field in layer.fields()]

    if self.dlg.checkToutExport.isChecked():
        export_fields = [f for f in available_fields if f.lower() not in champs_exclus]
    else:
        export_fields = champs_generaux.copy()

        if self.dlg.checkTravExport.isChecked():
            export_fields += [f"Tvx{i}" for i in range(1, 7)]
            export_fields += [f"dateTvx{i}" for i in range(1, 7)]
            export_fields += [f"remTvx{i}" for i in range(1, 7)]

        if self.dlg.checkTraitExport.isChecked():
            export_fields += [f"Trait{i}" for i in range(1, 7)]
            export_fields += [f"dateTrait{i}" for i in range(1, 7)]
            export_fields += [f"remTrait{i}" for i in range(1, 7)]

        if self.dlg.checkPrevExport.isChecked():
            export_fields += [f"Prev{i}" for i in range(1, 5)]
            export_fields += [f"datePrev{i}" for i in range(1, 5)]
            export_fields += [f"remPrev{i}" for i in range(1, 5)]

    export_fields = list(dict.fromkeys(export_fields))
    export_fields = [f for f in export_fields if f in available_fields and f != "Possession"]

    if not export_fields:
        QMessageBox.warning(None, tr("Export"), tr("Aucun champ valide sélectionné."))
        return

    folder = QFileDialog.getExistingDirectory(None, tr("Choisir un dossier d'export"))
    if not folder:
        return

    output_file = os.path.join(folder, f"Export_{datetime.now().strftime('%Y%m%d')}.xlsx")
    workbook = xlsxwriter.Workbook(output_file)
    worksheet = workbook.add_worksheet()

    # Écrire les entêtes
    for col, field in enumerate(export_fields):
        worksheet.write(0, col, field)

    row = 1
    for feature in layer.getFeatures():
        if not feature["Possession"]:
            continue

        # Filtrage selon cases à cocher Travaux / Traitements / Prévisions
        if self.dlg.checkTravExport.isChecked():
            if not any(str(feature[f"Tvx{i}"]) not in (None, "", "NULL", "null") for i in range(1, 7)):
                continue

        if self.dlg.checkTraitExport.isChecked():
            if not any(str(feature[f"Trait{i}"]) not in (None, "", "NULL", "null") for i in range(1, 7)):
                continue

        if self.dlg.checkPrevExport.isChecked():
            if not any(str(feature[f"Prev{i}"]) not in (None, "", "NULL", "null") for i in range(1, 5)):
                continue

        # Écriture des valeurs avec gestion spécifique
        for col, field in enumerate(export_fields):
            value = feature[field]

            if value in (None, "NULL", "null"):
                value_str = ""
            elif isinstance(value, QDate):
                value_str = value.toString("dd/MM/yyyy")
            elif field == "Nous":
                try:
                    value_str = TYPE_PARC_LIBELLES.get(int(value), str(value))
                except (ValueError, TypeError):
                    value_str = str(value)
            else:
                value_str = str(value)

            worksheet.write(row, col, value_str)

        row += 1

    workbook.close()

    QMessageBox.information(self.dlg, tr("Export"), tr(f"Export terminé :\n{output_file}"))
    if self.dlg.checkOpenExport.isChecked():
        try:
            os.startfile(output_file)
        except Exception as e:
            QMessageBox.warning(self.dlg, tr("Erreur"), tr(f"Impossible d’ouvrir le fichier :\n{str(e)}"))

# Export de la valeur, date d'achat et type de parcelles
def export_typeval_excel(self):
    try:
        import xlsxwriter
        from xlsxwriter.utility import xl_rowcol_to_cell
    except ImportError:
        QMessageBox.critical(self.dlg, tr("Erreur"), tr("Module xlsxwriter non trouvé."))
        return

    layer = self.iface.activeLayer()
    if not layer or not hasattr(layer, "getFeatures"):
        QMessageBox.warning(self.dlg, tr("Export"), tr("Veuillez sélectionner une couche valide."))
        return

    required_fields = ["section", "numero", "SURFACE", "prix", "typeParc", "Possession", "dateAchat"]
    fields = [f.name() for f in layer.fields()]
    for req in required_fields:
        if req not in fields:
            QMessageBox.critical(self.dlg, tr("Erreur"), tr(f"Champ manquant : {req}"))
            return

    # Calcul surface totale des "Prés" (typeParc == 1)
    surface_pre = 0.0
    for f in layer.getFeatures():
        try:
            key = int(f["typeParc"])
        except (ValueError, TypeError):
            key = None
        if key == 1 and f["Possession"]:
            surface_pre += float(f["SURFACE"]) if f["SURFACE"] else 0.0

    # Filtrer les features à exporter : possession=True et typeParc n'existe pas
    features = []
    exclues = 0

    for f in layer.getFeatures():
        if not f["Possession"]:
            continue

        try:
            type_parc = int(f["typeParc"])
        except (ValueError, TypeError):
            type_parc = None

        # Exclure les prés (typeParc == 1)
        if type_parc == 1:
            continue

        # Ne garder que si indice_parc est vide ou absent
        if "indice_parc" not in f.fields().names():
            features.append(f)
            continue

        val = f["indice_parc"]
        val_str = str(val).strip().upper()

        if val_str == "" or val_str == "NULL":
            features.append(f)
        else:
            exclues += 1  # sous-parcelle, on exclut

    def get_libelle_nous(feature):
        try:
            key = int(feature["typeParc"])
            return TYPE_PARC_LIBELLES.get(key, "")
        except (ValueError, TypeError):
            return ""

    features.sort(key=get_libelle_nous)

    folder = QFileDialog.getExistingDirectory(None, "Choisir un dossier d'export")
    if not folder:
        return

    date_str = datetime.now().strftime("%Y%m%d")
    filename = f"Export_TypeVal_{date_str}.xlsx"
    output_file = os.path.join(folder, filename)

    if os.path.exists(output_file):
        try:
            os.remove(output_file)
        except Exception as e:
            QMessageBox.critical(self.dlg, tr("Erreur"), tr(f"Impossible d’écraser le fichier :\n{e}"))
            return

    sheet_name = re.sub(r'[:\\/*?\[\]]', '_', layer.name())[:31]

    workbook = xlsxwriter.Workbook(output_file)
    worksheet = workbook.add_worksheet(sheet_name)

    bold_format = workbook.add_format({'bold': True})
    euro_format = workbook.add_format({'num_format': '#,##0 €'})
    date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})

    export_fields = ["parcelle", "surface_lisible", "surface_ares", "typeParc", "prix", "dateAchat"]

    renamed_headers = {
        "typeParc": tr("Type de boisement"),
        "surface_lisible": tr("Surface (ha)"),
        "surface_ares": tr("Surface (a)"),
        "dateAchat": tr("Date acquisition")
    }

    # Écriture des en-têtes
    for col, field_name in enumerate(export_fields):
        worksheet.write(0, col, renamed_headers.get(field_name, field_name), bold_format)

    total_surface = 0.0
    total_prix = 0.0
    surface_par_type = {}
    row = 1

    def format_surface_ha_are_ca(surface):
        try:
            surface = float(surface)
        except (ValueError, TypeError):
            return ""

        total_cares = round(surface * 100)  # ares → centiares
        ha = total_cares // 10000
        remainder = total_cares % 10000
        a = remainder // 100
        ca = remainder % 100

        parts = []
        if ha > 0:
            parts.append(f"{ha} ha")
        if a > 0:
            parts.append(f"{a} a")
        if ca > 0:
            parts.append(f"{ca} ca")
        return " ".join(parts) if parts else "0 a"

    for feature in features:
        try:
            key = int(feature["typeParc"])
        except (ValueError, TypeError):
            key = None

        section = str(feature["section"]).strip()
        numero = str(feature["numero"]).strip()
        parcelle = f"{section}{numero}"
        surface = float(feature["SURFACE"]) if feature["SURFACE"] else 0.0
        surface_lisible = format_surface_ha_are_ca(surface)
        prix = int(feature["prix"]) if feature["prix"] else 0


        libelle = TYPE_PARC_LIBELLES.get(key, str(key) if key is not None else "")

        # Récupération dateAchat et formatage date
        date_achat_val = feature["dateAchat"]
        if date_achat_val and hasattr(date_achat_val, 'toPyDate'):
            date_achat_py = date_achat_val.toPyDate()
            date_achat_str = date_achat_py.strftime("%d/%m/%Y") if date_achat_py else ""
        else:
            date_achat_str = ""

        surface_par_type[libelle] = surface_par_type.get(libelle, 0.0) + surface
        total_surface += surface
        total_prix += prix

        values = [parcelle, surface_lisible, surface, libelle, prix, date_achat_str]
        for col, val in enumerate(values):
            field = export_fields[col]

            if field == "prix":
                try:
                    worksheet.write_number(row, col, float(val), euro_format)
                except (ValueError, TypeError):
                    worksheet.write(row, col, "")
            elif field == "surface_ares":
                try:
                    worksheet.write_number(row, col, float(val))
                except (ValueError, TypeError):
                    worksheet.write(row, col, "")
            elif field == "dateAchat":
                if val:
                    try:
                        dt = datetime.strptime(val, "%d/%m/%Y")
                        worksheet.write_datetime(row, col, dt, date_format)
                    except Exception:
                        worksheet.write(row, col, val)
                else:
                    worksheet.write(row, col, "")
            else:
                worksheet.write(row, col, val)

        row += 1

    # Totaux en bas de la liste
    if "surface_lisible" in export_fields:
        col_surf = export_fields.index("surface_lisible")
        worksheet.write(row, col_surf - 1, tr("Total surface"), bold_format)
        worksheet.write(row, col_surf, format_surface_ha_are_ca(total_surface), bold_format)

    if "prix" in export_fields:
        col_prix = export_fields.index("prix")
        worksheet.write(row, col_prix - 1, tr("Total valeur"), bold_format)
        cell1 = xl_rowcol_to_cell(1, col_prix)
        cell2 = xl_rowcol_to_cell(row - 1, col_prix)
        worksheet.write_formula(row, col_prix, f"=SUM({cell1}:{cell2})", euro_format)

    # Statistiques par type (hors "Pré")
    start_col = len(export_fields) + 1
    worksheet.write(0, start_col, tr("Type de boisement"), bold_format)
    worksheet.write(0, start_col + 1, tr("Surface (ha)"), bold_format)

    row_type = 1
    for typ, surf in sorted(surface_par_type.items()):
        worksheet.write(row_type, start_col, typ)
        worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(surf))
        row_type += 1

    # Total général (hors prés)
    worksheet.write(row_type, start_col, tr("Total général"), bold_format)
    worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(sum(surface_par_type.values())), bold_format)
    row_type += 2  # on saute une ligne

    # Total prés (typeParc=1)
    worksheet.write(row_type, start_col, tr("Total prés"), bold_format)
    worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(surface_pre))

    # Ajustement colonnes
    for col in range(len(export_fields)):
        worksheet.set_column(col, col, 20)
    worksheet.set_column(start_col, start_col, 25)
    worksheet.set_column(start_col + 1, start_col + 1, 22)

    workbook.close()

    QMessageBox.information(self.dlg, tr("Export"), tr(f"Export terminé :\n{output_file}"))

    if self.dlg.checkOpenExport.isChecked():
        try:
            os.startfile(output_file)
        except Exception as e:
            QMessageBox.warning(self.dlg, tr("Erreur"), tr(f"Impossible d’ouvrir le fichier :\n{e}"))
