<?php
/**
 * Script d'import des contours des départements français
 * 
 * Les données peuvent provenir de :
 * - IGN Admin Express : https://geoservices.ign.fr/adminexpress
 * - data.gouv.fr : https://www.data.gouv.fr/fr/datasets/contours-des-departements-francais-issus-d-openstreetmap/
 * - OpenStreetMap via Overpass API
 * 
 * Format attendu : GeoJSON ou Shapefile converti en SQL
 */

require_once __DIR__ . '/../src/Config/AppConfig.php';
require_once __DIR__ . '/../src/Core/Database.php';

echo "Import des contours des départements\n";
echo "===================================\n\n";

// Initialiser la base de données
$appConfig = AppConfig::getInstance();
Database::init($appConfig->getDatabaseConfig());
$db = Database::getInstance();

// Exemple de données pour quelques départements bretons
// En production, ces données viendraient d'un fichier GeoJSON ou d'une API
$departements = [
    [
        'code' => '22',
        'nom' => 'Côtes-d\'Armor',
        // Contour simplifié - en réalité il faudrait des centaines de points
        'points' => [
            [-3.6546, 48.9012], [-3.3856, 48.8756], [-3.1234, 48.8234],
            [-2.7856, 48.7845], [-2.4567, 48.7234], [-2.1234, 48.6456],
            [-2.0123, 48.5234], [-2.0456, 48.3456], [-2.1567, 48.1234],
            [-2.3456, 48.0567], [-2.6789, 48.0789], [-3.0123, 48.1234],
            [-3.3456, 48.2345], [-3.5678, 48.4567], [-3.6234, 48.6789],
            [-3.6546, 48.9012]  // Fermer le polygone
        ]
    ],
    [
        'code' => '29',
        'nom' => 'Finistère',
        'points' => [
            [-5.1423, 48.7523], [-4.8234, 48.6845], [-4.5123, 48.6234],
            [-4.2345, 48.5678], [-3.9876, 48.4567], [-3.7234, 48.3456],
            [-3.4567, 48.2345], [-3.3876, 48.0123], [-3.4234, 47.8234],
            [-3.5678, 47.6456], [-3.8765, 47.6789], [-4.2345, 47.7234],
            [-4.5678, 47.8234], [-4.8765, 47.9345], [-5.0876, 48.1234],
            [-5.1234, 48.3456], [-5.1345, 48.5678], [-5.1423, 48.7523]
        ]
    ],
    [
        'code' => '35',
        'nom' => 'Ille-et-Vilaine',
        'points' => [
            [-2.0123, 48.6456], [-1.7234, 48.5678], [-1.4567, 48.4567],
            [-1.2345, 48.3456], [-1.0234, 48.2345], [-1.0567, 48.0123],
            [-1.1234, 47.8234], [-1.2567, 47.6456], [-1.4678, 47.6789],
            [-1.7234, 47.7234], [-1.9876, 47.8234], [-2.1234, 47.9345],
            [-2.2345, 48.1234], [-2.1567, 48.3456], [-2.0678, 48.5234],
            [-2.0123, 48.6456]
        ]
    ],
    [
        'code' => '56',
        'nom' => 'Morbihan',
        'points' => [
            [-3.4567, 48.2345], [-3.2345, 48.1234], [-2.9876, 48.0123],
            [-2.7234, 47.9234], [-2.4567, 47.8345], [-2.2345, 47.7456],
            [-2.1234, 47.6234], [-2.2567, 47.4567], [-2.4678, 47.3456],
            [-2.7234, 47.3789], [-3.0123, 47.4234], [-3.2876, 47.5234],
            [-3.5234, 47.6345], [-3.6789, 47.7456], [-3.7234, 47.9234],
            [-3.6567, 48.0789], [-3.4567, 48.2345]
        ]
    ]
];

try {
    $db->beginTransaction();
    
    // Préparer la requête d'insertion
    $sql = "INSERT INTO departements_contours 
            (code_dept, nom_dept, contour, bbox_min_lat, bbox_max_lat, bbox_min_lng, bbox_max_lng) 
            VALUES 
            (:code, :nom, ST_GeomFromText(:polygon, 4326), :min_lat, :max_lat, :min_lng, :max_lng)
            ON DUPLICATE KEY UPDATE
            nom_dept = VALUES(nom_dept),
            contour = VALUES(contour),
            bbox_min_lat = VALUES(bbox_min_lat),
            bbox_max_lat = VALUES(bbox_max_lat),
            bbox_min_lng = VALUES(bbox_min_lng),
            bbox_max_lng = VALUES(bbox_max_lng),
            updated_at = CURRENT_TIMESTAMP";
    
    $stmt = $db->prepare($sql);
    
    foreach ($departements as $dept) {
        echo "Import du département {$dept['code']} - {$dept['nom']}...\n";
        
        // Créer le polygone WKT
        $polygonPoints = [];
        $lats = [];
        $lngs = [];
        
        foreach ($dept['points'] as $point) {
            $lng = $point[0];
            $lat = $point[1];
            $polygonPoints[] = "$lng $lat";
            $lats[] = $lat;
            $lngs[] = $lng;
        }
        
        $polygon = 'POLYGON((' . implode(',', $polygonPoints) . '))';
        
        // Calculer la bounding box
        $minLat = min($lats);
        $maxLat = max($lats);
        $minLng = min($lngs);
        $maxLng = max($lngs);
        
        // Exécuter l'insertion
        $stmt->execute([
            'code' => $dept['code'],
            'nom' => $dept['nom'],
            'polygon' => $polygon,
            'min_lat' => $minLat,
            'max_lat' => $maxLat,
            'min_lng' => $minLng,
            'max_lng' => $maxLng
        ]);
        
        echo "✓ Département {$dept['code']} importé\n";
    }
    
    $db->commit();
    
    echo "\n✓ Import terminé avec succès!\n\n";
    
    // Vérifier les données importées
    $checkSql = "SELECT code_dept, nom_dept, 
                 ST_Area(contour) as area,
                 ST_NumPoints(ST_ExteriorRing(contour)) as num_points
                 FROM x_departements_contours
                 ORDER BY code_dept";
    
    $result = $db->query($checkSql);
    echo "Départements importés:\n";
    echo "---------------------\n";
    
    foreach ($result as $row) {
        echo sprintf("- %s (%s) : %d points, aire: %.4f\n", 
            $row['nom_dept'], 
            $row['code_dept'], 
            $row['num_points'],
            $row['area']
        );
    }
    
} catch (Exception $e) {
    $db->rollBack();
    echo "✗ Erreur lors de l'import : " . $e->getMessage() . "\n";
}

echo "\n";
echo "Note importante:\n";
echo "---------------\n";
echo "Ce script utilise des données simplifiées pour l'exemple.\n";
echo "Pour un usage en production, vous devez :\n";
echo "1. Télécharger les vrais contours depuis l'IGN ou data.gouv.fr\n";
echo "2. Les convertir en format GeoJSON ou SQL\n";
echo "3. Adapter ce script pour lire ces fichiers\n";
echo "\n";
echo "Sources recommandées:\n";
echo "- IGN Admin Express: https://geoservices.ign.fr/adminexpress\n";
echo "- data.gouv.fr: https://www.data.gouv.fr/fr/datasets/contours-des-departements-francais-issus-d-openstreetmap/\n";