from geopy.distance import geodesic
import mysql.connector
import pandas as pd
import json
import random
from math import isnan
from shapely.geometry import MultiPoint, Point, Polygon
from shapely.ops import transform
from pyproj import Transformer


def generate_random_color():
    """Generate a random color in HEX format."""
    return "#{:02x}{:02x}{:02x}".format(random.randint(0, 255), random.randint(0, 255), random.randint(0, 255))


def calculate_distance(coord1, coord2):
    """Calculate the distance in kilometers between two GPS coordinates."""
    return geodesic(coord1, coord2).km


def group_clients_by_sales(threshold):
    # Connexion à la base de données
    conn = mysql.connector.connect(
        host="51.77.140.160",
        user="bassem",
        password="Clediss1234++",
        database="dist_utic",
        port=3306
    )
    
    try:
        cursor = conn.cursor(dictionary=True)
        # Requête SQL
        query = """
            SELECT 
                c.id, c.code, c.nom, c.prenom, c.latitude, c.longitude, 
                (SELECT SUM(e.net_a_payer)  
                 FROM entetecommercials as e 
                 WHERE e.client_code=c.code AND e.type IN ('bl', 'avoir')) AS chiffre_vente
            FROM clients as c
            WHERE c.isactif=1
        """
        cursor.execute(query)
        data = cursor.fetchall()

        # Transformation des données
        for client in data:
            # Remplacer NaN par None
            if client['chiffre_vente'] is None or (isinstance(client['chiffre_vente'], float) and isnan(client['chiffre_vente'])):
                client['chiffre_vente'] = None
            
            # Convertir latitude et longitude en float ou None
            try:
                client['latitude'] = float(client['latitude']) if client['latitude'] else None
            except (ValueError, TypeError):
                client['latitude'] = None
            
            try:
                client['longitude'] = float(client['longitude']) if client['longitude'] else None
            except (ValueError, TypeError):
                client['longitude'] = None

        # Groupement des clients par proximité (distance) et chiffre de vente
        groups = []
        group_id = 1
        ungrouped_clients = data[:]  # Copy of clients list to process

        while ungrouped_clients:
            # Take the first client and initialize a new group
            current_group = [ungrouped_clients.pop(0)]
            current_sum = current_group[0]['chiffre_vente'] or 0  # Sales of the first client

            # Try to find the closest clients and add them to the group
            i = 0
            while i < len(ungrouped_clients):
                client = ungrouped_clients[i]
                # Calculate distance between the last added client and the current client
                distance = calculate_distance(
                    (current_group[-1]['latitude'], current_group[-1]['longitude']),
                    (client['latitude'], client['longitude'])
                )

                # If the distance is within acceptable range and the total sales do not exceed the threshold
                if distance <= 10 and current_sum + (client['chiffre_vente'] or 0) <= threshold:
                    current_group.append(client)
                    current_sum += client['chiffre_vente'] or 0  # Update the sales sum
                    ungrouped_clients.pop(i)  # Remove the client from the ungrouped list
                else:
                    i += 1  # Move to the next client

            # Add the group to the final list of groups
            groups.append({
                "group_id": group_id,
                "clients": current_group,
                "total_sales": current_sum,
                "color": generate_random_color()
            })
            group_id += 1

        # Ajouter les polygones, longueurs et surfaces
        transformer = Transformer.from_crs("EPSG:4326", "EPSG:3857", always_xy=True)  # WGS84 vers projection métrique
        transformer_back = Transformer.from_crs("EPSG:3857", "EPSG:4326", always_xy=True)  # Projection métrique vers WGS84

        for group in groups:
            # Générer la ligne basée sur les coordonnées GPS valides
            coordinates = [
                (client['longitude'], client['latitude'])
                for client in group['clients']
                if client['latitude'] is not None and client['longitude'] is not None and
                (client['latitude'], client['longitude']) not in [(-1, -1), (0, 0)]
            ]
            group['line'] = coordinates  # Liste ordonnée des points GPS

            # Calculer la longueur totale de la ligne
            if len(coordinates) >= 2:
                line = LineString(coordinates)
                line_metric = transform(transformer.transform, line)
                group['line_length'] = line_metric.length / 1_000  # m à km
            else:
                group['line_length'] = 0  # Pas de ligne possible avec moins de deux points

            # Générer un polygone englobant (layer_polygon)
            if len(coordinates) >= 3:
                points = MultiPoint([Point(lat, lon) for lat, lon in coordinates])
                polygon = points.convex_hull  # Polygone convexe englobant

                if isinstance(polygon, Polygon):
                    group['layer_polygon_wkt'] = polygon.wkt  # Représentation WKT du polygone

                    # Calculer la surface et la longueur en utilisant une projection métrique
                    polygon_metric = transform(transformer.transform, polygon)

                    if polygon.is_empty:
                        group['layer_polygon'] = []
                        group['polygon_area'] = 0  # m² à km²
                    else:
                        group['layer_polygon'] = list(map(list, polygon.exterior.coords))
                        group['polygon_area'] = polygon_metric.area / 1_000_000  # m² à km²
                else:
                    group['layer_polygon_wkt'] = None  # Aucun polygone possible
                    group['layer_polygon'] = []
            else:
                group['layer_polygon_wkt'] = None  # Aucun polygone possible
                group['layer_polygon'] = []

        # Conversion JSON
        response = json.dumps(groups, indent=4, ensure_ascii=False)
        print(response)

    finally:
        conn.close()


if __name__ == "__main__":
    # Lecture de l'argument seuil
    if len(sys.argv) != 2:
        print("Usage: python Tournees.py <threshold>")
        sys.exit(1)

    threshold = float(sys.argv[1])
    group_clients_by_sales(threshold)