package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	"github.com/joho/godotenv"
)

// Définitions des structures
type Station struct {
	ID              int
	IDStation       string
	IDApp           sql.NullString
	Name            string
	Address         sql.NullString
	PostalCode      sql.NullInt64
	City            sql.NullString
	Latitude        sql.NullFloat64
	Longitude       sql.NullFloat64
	Comments        sql.NullString
	IsElectric      bool
	CreatedAt       time.Time
	ModifiedAt      time.Time
	BrandName       sql.NullString
	BrandLogo       sql.NullString
	IsActive        bool
	IsValid         bool
	ElectricDetails []byte
}

type CarburantList struct {
	ID            int
	StationID     int
	CarburantID   int
	Price         sql.NullFloat64
	LastUpdate    mysql.NullTime
	PowerKW       float64
	NumConnectors int
	Availability  string
	PricingModel  string
}

type JSONData struct {
	Features []struct {
		Type       string                 `json:"type"`
		Properties map[string]interface{} `json:"properties"`
		Geometry   struct {
			Type        string    `json:"type"`
			Coordinates []float64 `json:"coordinates"`
		} `json:"geometry"`
	} `json:"features"`
}

var connectorTypeToCarburantID = map[string]int{
	"prise_type_2":       8,
	"prise_type_combo_ccs": 9,
	"prise_type_chademo": 10,
	"prise_type_ef":      11,
}

func main() {
	// Charger les variables d'environnement
	err := godotenv.Load()
	if err != nil {
		log.Fatal("Error loading .env file")
	}

	dbHost := os.Getenv("DB_HOST")
	dbName := os.Getenv("DB_NAME")
	dbUser := os.Getenv("DB_USER")
	dbPass := os.Getenv("DB_PASS")
	dbPort := os.Getenv("DB_PORT")
	jsonFilePath := os.Getenv("JSON_FILE_PATH")

	// Construire la DSN
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		dbUser, dbPass, dbHost, dbPort, dbName)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Error connecting to database: %v", err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %v", err)
	}
	fmt.Println("Successfully connected to MySQL database!")

	// Lire le fichier JSON
	fileContent, err := ioutil.ReadFile(jsonFilePath)
	if err != nil {
		log.Fatalf("Error reading JSON file: %v", err)
	}

	var jsonData JSONData
	err = json.Unmarshal(fileContent, &jsonData)
	if err != nil {
		log.Fatalf("Error unmarshaling JSON: %v", err)
	}

	fmt.Printf("Starting import of %d charging points...\n", len(jsonData.Features))

	tx, err := db.Begin()
	if err != nil {
		log.Fatalf("Error starting transaction: %v", err)
	}
	defer tx.Rollback() // Rollback en cas d'erreur ou d'oubli de commit

	// Map pour regrouper les bornes par coordonnées (latitude,longitude) -> ID de la station
	stationMap := make(map[string]int64)

	for _, feature := range jsonData.Features {
		props := feature.Properties

		// --- Extraction et normalisation des coordonnées pour le regroupement ---
		latitude := sql.NullFloat64{}
		longitude := sql.NullFloat64{}

		if lat, ok := getPropFloat(props, "consolidated_latitude"); ok {
			latitude = sql.NullFloat64{Float64: lat, Valid: true}
		}
		if lon, ok := getPropFloat(props, "consolidated_longitude"); ok {
			longitude = sql.NullFloat64{Float64: lon, Valid: true}
		}

		if !latitude.Valid || !longitude.Valid {
			// Fallback sur geometry.coordinates si consolidated_ n'est pas valide
			if len(feature.Geometry.Coordinates) == 2 {
				longitude = sql.NullFloat64{Float64: feature.Geometry.Coordinates[0], Valid: true}
				latitude = sql.NullFloat64{Float64: feature.Geometry.Coordinates[1], Valid: true}
			}
		}

		if !latitude.Valid || !longitude.Valid {
			log.Printf("Warning: Skipping charging point as coordinates are invalid.")
			continue
		}

		// Créer une clé unique basée sur les coordonnées pour le regroupement
		// Formatage à 6 décimales pour la précision (environ 11cm)
		uniqueKey := fmt.Sprintf("%.6f,%.6f", latitude.Float64, longitude.Float64)
		var stationDBID int64
		var isNewStation bool

		// --- Logique de regroupement: vérifier si une station existe déjà ---
		if id, ok := stationMap[uniqueKey]; ok {
			stationDBID = id
			isNewStation = false
		} else {
			// La station n'est pas dans notre map, vérifier si elle existe en base de données
			err = tx.QueryRow("SELECT id FROM station WHERE ROUND(latitude, 6) = ? AND ROUND(longitude, 6) = ?",
				latitude.Float64, longitude.Float64).Scan(&stationDBID)

			if err == sql.ErrNoRows {
				// C'est une nouvelle station, on l'insère
				isNewStation = true

				// --- Préparation des données pour la nouvelle Station ---
				fullAddress := getPropString(props, "adresse_station")
				extractedAddress, extractedPostalCode, extractedCity := extractAddressComponents(fullAddress)

				stationAddress := sql.NullString{String: extractedAddress, Valid: extractedAddress != ""}
				stationPostalCode := sql.NullInt64{Int64: int64(extractedPostalCode), Valid: extractedPostalCode != 0}
				stationCity := sql.NullString{String: extractedCity, Valid: extractedCity != ""}

				if !stationAddress.Valid {
					stationAddress.String = getPropString(props, "adresse_station")
					stationAddress.Valid = stationAddress.String != ""
				}
				if !stationPostalCode.Valid {
					if pcStr := getPropString(props, "consolidated_code_postal"); pcStr != "" {
						if pc, err := strconv.ParseInt(pcStr, 10, 64); err == nil {
							stationPostalCode = sql.NullInt64{Int64: pc, Valid: true}
						}
					}
				}
				if !stationCity.Valid {
					stationCity.String = getPropString(props, "consolidated_commune")
					stationCity.Valid = stationCity.String != ""
				}

				stationIDLocal := getPropString(props, "id_station_local")
				if stationIDLocal == "" {
					stationIDLocal = getPropString(props, "id_station_itinerance")
				}
				stationName := getPropString(props, "nom_station")
				if stationName == "" {
					stationName = getPropString(props, "nom_enseigne")
				}
				brandName := getPropString(props, "nom_enseigne")
				if brandName == "" {
					brandName = getPropString(props, "nom_operateur")
				}

				electricDetailsMap := map[string]interface{}{
					"nom_amenageur":       getPropString(props, "nom_amenageur"),
					"contact_amenageur":   getPropString(props, "contact_amenageur"),
					"nom_operateur":       getPropString(props, "nom_operateur"),
					"contact_operateur":   getPropString(props, "contact_operateur"),
					"telephone_operateur": getPropString(props, "telephone_operateur"),
					"horaires":            getPropString(props, "horaires"),
					"accessibilite_pmr":   getPropString(props, "accessibilite_pmr"),
					"observations":        getPropString(props, "observations"),
				}
				electricDetailsJSON, _ := json.Marshal(electricDetailsMap)

				comments := getPropString(props, "observations")
				if len(comments) > 100 {
					comments = comments[:100]
				}

				// Insertion de la nouvelle station
				upsertStationSQL := `
					INSERT INTO station (
						id_station, id_app, name, address, postalcode, city, latitude, longitude, comments,
						created_at, modified_at, brand_name, is_active, is_valid, is_electric, electric_details
					) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
					ON DUPLICATE KEY UPDATE
						id_app = VALUES(id_app), name = VALUES(name), address = VALUES(address),
						postalcode = VALUES(postalcode), city = VALUES(city), latitude = VALUES(latitude),
						longitude = VALUES(longitude), comments = VALUES(comments), modified_at = VALUES(modified_at),
						brand_name = VALUES(brand_name), is_active = VALUES(is_active), is_valid = VALUES(is_valid),
						is_electric = VALUES(is_electric), electric_details = VALUES(electric_details);
				`
				res, err := tx.Exec(upsertStationSQL,
					stationIDLocal,
					sql.NullString{String: getPropString(props, "id_station_itinerance"), Valid: getPropString(props, "id_station_itinerance") != ""},
					stationName,
					stationAddress,
					stationPostalCode,
					stationCity,
					latitude,
					longitude,
					sql.NullString{String: comments, Valid: comments != ""},
					time.Now(),
					time.Now(),
					sql.NullString{String: brandName, Valid: brandName != ""},
					true,
					true,
					true,
					electricDetailsJSON,
				)
				if err != nil {
					log.Fatalf("Error upserting station %s: %v", stationIDLocal, err)
				}
				
				stationDBID, err = res.LastInsertId()
				if err != nil {
					log.Fatalf("Error getting station ID: %v", err)
				}
				// Stocker l'ID de la nouvelle station dans la map
				stationMap[uniqueKey] = stationDBID
				fmt.Printf("Created new station: %s (%s)\n", stationName, stationIDLocal)

			} else if err != nil {
				log.Fatalf("Error querying existing station: %v", err)
			} else {
				// Station trouvée en base de données, la stocker dans la map
				stationMap[uniqueKey] = stationDBID
				isNewStation = false
			}
		}

		// --- Insérer dans carburantlist pour la borne de recharge actuelle ---
		// Les données de chaque borne doivent être insérées individuellement
		// La logique de suppression des anciens Carburantlist est déplacée
		// pour être plus précise.
		
		id_pdc_local := getPropString(props, "id_pdc_local")
		id_pdc_itinerance := getPropString(props, "id_pdc_itinerance")

		// Supprimer l'ancienne entrée pour ce point de charge spécifique, si elle existe.
		_, err = tx.Exec("DELETE FROM carburantlist WHERE station_id = ? AND id_pdc_local = ? AND id_pdc_itinerance = ?",
		stationDBID, id_pdc_local, id_pdc_itinerance)
		if err != nil {
			log.Fatalf("Error deleting old carburantlist entry: %v", err)
		}

		powerKW, _ := strconv.ParseFloat(getPropString(props, "puissance_nominale"), 64)
		numConnectors, _ := strconv.Atoi(getPropString(props, "nbre_pdc"))

		pricingModel := "unknown"
		if getPropBool(props, "gratuit") {
			pricingModel = "free"
		} else if getPropBool(props, "paiement_acte") || getPropBool(props, "paiement_cb") {
			pricingModel = "per_kwh"
		}

		lastUpdateStr := getPropString(props, "date_maj")
		var lastUpdate mysql.NullTime
		if lastUpdateStr != "" {
			t, err := time.Parse("2006-01-02", lastUpdateStr)
			if err == nil {
				lastUpdate = mysql.NullTime{Time: t, Valid: true}
			}
		}
		
		// Insérer une entrée pour chaque type de prise
		connectorTypes := map[string]bool{
			"prise_type_2":       getPropBool(props, "prise_type_2"),
			"prise_type_combo_ccs": getPropBool(props, "prise_type_combo_ccs"),
			"prise_type_chademo": getPropBool(props, "prise_type_chademo"),
			"prise_type_ef":      getPropBool(props, "prise_type_ef"),
		}
		
		for alias, isAvailable := range connectorTypes {
			if isAvailable {
				carburantID, ok := connectorTypeToCarburantID[alias]
				if !ok {
					log.Printf("Warning: Carburant ID not found for alias '%s'. Skipping for station %s.\n", alias, getPropString(props, "id_station_local"))
					continue
				}

				insertCarburantListSQL := `
					INSERT INTO carburantlist (
						station_id, carburant_id, price, modified_at, power_kw,
						num_connectors, availability, pricing_model
					) VALUES (?, ?, ?, ?, ?, ?, ?, ?);
				`
				_, err := tx.Exec(insertCarburantListSQL,
					stationDBID,
					carburantID,
					sql.NullFloat64{},
					lastUpdate,
					powerKW,
					numConnectors,
					"unknown",
					pricingModel,
				)
				if err != nil {
					log.Fatalf("Error inserting carburantlist for station %d: %v", stationDBID, err)
				}
			}
		}
		
		fmt.Printf("Processed charging point %s for station ID %d\n", getPropString(props, "id_pdc_local"), stationDBID)
	}

	err = tx.Commit()
	if err != nil {
		log.Fatalf("Error committing transaction: %v", err)
	}

	fmt.Println("Importation terminée avec succès !")
}

// Helper functions (inchangées)
func getPropString(props map[string]interface{}, key string) string {
	if val, ok := props[key]; ok {
		if s, isString := val.(string); isString {
			return s
		}
		return fmt.Sprintf("%v", val)
	}
	return ""
}

func getPropBool(props map[string]interface{}, key string) bool {
	if val, ok := props[key]; ok {
		if b, isBool := val.(bool); isBool {
			return b
		}
		if s, isString := val.(string); isString {
			return strings.ToLower(s) == "true"
		}
	}
	return false
}

func getPropFloat(props map[string]interface{}, key string) (float64, bool) {
	if val, ok := props[key]; ok {
		if f, isFloat := val.(float64); isFloat {
			return f, true
		}
		if s, isString := val.(string); isString {
			if f, err := strconv.ParseFloat(s, 64); err == nil {
				return f, true
			}
		}
	}
	return 0, false
}

func extractAddressComponents(fullAddress string) (address string, postalCode int, city string) {
	if fullAddress == "" {
		return "", 0, ""
	}
	parts := strings.Fields(fullAddress)
	for i := len(parts) - 1; i >= 0; i-- {
		word := parts[i]
		if len(word) == 5 {
			pc, err := strconv.Atoi(word)
			if err == nil {
				postalCode = pc
				if i+1 < len(parts) {
					city = strings.Join(parts[i+1:], " ")
				}
				address = strings.Join(parts[:i], " ")
				address = strings.TrimSuffix(address, ",")
				return strings.TrimSpace(address), postalCode, strings.TrimSpace(city)
			}
		}
	}
	return fullAddress, 0, ""
}