#!/bin/bash

# Script d'initialisation et de mise à jour d'une base PostgreSQL avec PostGIS
# pour l'importation et l'analyse de données OpenStreetMap

set -e  # Arrêter en cas d'erreur

# Fonction d'affichage
log() {
    echo "$(date +'%Y-%m-%d %H:%M:%S') - $1"
}

# Configuration de la base de données
PGUSER="osm_user"
PGDATABASE="osm_db"
CODE_INSEE="91111"  # Code INSEE par défaut (Étampes)
OSM_OBJECT="fire_hydrant"  # Objet OSM par défaut (bornes incendie)

# Variable pour suivre si les identifiants ont été chargés depuis le fichier de configuration
CONFIG_LOADED=false

# Vérifier si le fichier de configuration existe et le charger
if [ -f "osm_config.txt" ]; then
    source osm_config.txt
    CONFIG_LOADED=true
    log "Utilisation des identifiants existants depuis osm_config.txt"
else
    # Génération d'un mot de passe aléatoire pour la première utilisation
    PGPASSWORD=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 16 | head -n 1)
fi

# Vérification des prérequis
check_prerequisites() {
    log "Vérification des prérequis..."

    # Vérifier si PostgreSQL est installé
    if ! command -v psql &> /dev/null; then
        log "PostgreSQL n'est pas installé. Installation en cours..."
        sudo apt-get update && sudo apt-get install -y postgresql postgresql-contrib
    fi

    # Vérifier si PostGIS est installé
    if ! sudo -u postgres psql -c "SELECT postgis_version();" &> /dev/null; then
        log "PostGIS n'est pas installé. Installation en cours..."
        sudo apt-get install -y postgis postgresql-14-postgis-3 postgresql-14-postgis-3-scripts
    fi

    # Vérifier si osmium-tool est installé
    if ! command -v osmium &> /dev/null; then
        log "osmium-tool n'est pas installé. Installation en cours..."
        sudo apt-get install -y osmium-tool
    fi
}

# Initialisation de la base de données
init_database() {
    log "Création de l'utilisateur et de la base de données PostgreSQL..."

    # Vérifier si les identifiants ont été chargés depuis osm_config.txt
    if [ "$CONFIG_LOADED" = false ]; then
        # Créer l'utilisateur s'il n'existe pas déjà
        sudo -u postgres psql -c "DO \$\$
        BEGIN
            IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '$PGUSER') THEN
                CREATE USER $PGUSER WITH PASSWORD '$PGPASSWORD';
            ELSE
                ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';
            END IF;
        END
        \$\$;"
    else
        log "Utilisation de l'utilisateur existant depuis osm_config.txt"
    fi

    # Créer la base de données si elle n'existe pas déjà
    sudo -u postgres psql -c "DROP DATABASE IF EXISTS $PGDATABASE;"
    sudo -u postgres psql -c "CREATE DATABASE $PGDATABASE OWNER $PGUSER;"

    # Activer les extensions nécessaires
    sudo -u postgres psql -d $PGDATABASE -c "CREATE EXTENSION IF NOT EXISTS postgis;"
    sudo -u postgres psql -d $PGDATABASE -c "CREATE EXTENSION IF NOT EXISTS hstore;"

    # Donner tous les privilèges à l'utilisateur
    sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $PGDATABASE TO $PGUSER;"
    
    # Initialiser la base de données avec osm2pgsql pour la rendre updatable
    # Créer un fichier OSM vide pour l'initialisation
    mkdir -p osm_data
    cat > osm_data/empty.osm << EOF
<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="empty">
</osm>
EOF
    
    # Initialiser la base avec osm2pgsql en mode slim sans --drop
    log "Initialisation de la base de données pour les mises à jour..."
    PGPASSWORD=$PGPASSWORD osm2pgsql --create --database $PGDATABASE --user $PGUSER --host localhost --slim \
        --style osm_data/style.lua --extra-attributes "osm_data/empty.osm" -O flex

    log "Base de données initialisée avec succès!"
    log "Nom d'utilisateur: $PGUSER"
    log "Mot de passe: $PGPASSWORD"
    log "Base de données: $PGDATABASE"

    # Créer un fichier de configuration
    cat > osm_config.txt << EOF
PGUSER=$PGUSER
PGPASSWORD=$PGPASSWORD
PGDATABASE=$PGDATABASE
EOF
}

# Téléchargement et importation des données OSM de l'Île-de-France
import_osm_data() {
    log "Téléchargement des données OpenStreetMap pour l'Île-de-France..."

    # Créer un répertoire pour les données
    mkdir -p osm_data

    # Télécharger les données historiques de l'Île-de-France depuis Geofabrik
    if [ ! -f osm_data/ile-de-france-internal.osh.pbf ]; then
        wget -O osm_data/ile-de-france-internal.osh.pbf https://osm-internal.download.geofabrik.de/europe/france/ile-de-france-internal.osh.pbf
    else
        log "Le fichier historique existe déjà, vérification de la dernière version..."
        wget -N -P osm_data https://osm-internal.download.geofabrik.de/europe/france/ile-de-france-internal.osh.pbf
    fi

    # Vérifier si la base de données existe, sinon la créer
    if ! sudo -u postgres psql -lqt | cut -d \| -f 1 | grep -qw "$PGDATABASE"; then
        log "La base de données $PGDATABASE n'existe pas. Création en cours..."
        sudo -u postgres psql -c "CREATE DATABASE $PGDATABASE OWNER $PGUSER;"
    fi

    # Vérifier et activer l'extension PostGIS si nécessaire
    if ! PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "SELECT postgis_version();" &> /dev/null; then
        log "L'extension PostGIS n'est pas activée. Activation en cours..."
        sudo -u postgres psql -d $PGDATABASE -c "CREATE EXTENSION IF NOT EXISTS postgis;"
        sudo -u postgres psql -d $PGDATABASE -c "CREATE EXTENSION IF NOT EXISTS hstore;"
        sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $PGDATABASE TO $PGUSER;"
    fi

    log "Importation des données dans PostgreSQL..."

    # Création d'un fichier de style minimal pour osm2pgsql
    cat > osm_data/style.lua << EOF
local tables = {}

-- Table pour les bornes incendie
tables.fire_hydrants = osm2pgsql.define_node_table('fire_hydrants', {
    { column = 'id_column', type = 'id_type' },
    { column = 'geom', type = 'point', projection = 4326 },
    { column = 'tags', type = 'hstore' },
    { column = 'ref', type = 'text' },
    { column = 'color', type = 'text' },
    { column = 'insee', type = 'text' },
})

-- Table pour les arbres
tables.trees = osm2pgsql.define_node_table('trees', {
    { column = 'id_column', type = 'id_type' },
    { column = 'geom', type = 'point', projection = 4326 },
    { column = 'tags', type = 'hstore' },
    { column = 'species', type = 'text' },
    { column = 'height', type = 'text' },
    { column = 'insee', type = 'text' },
})

-- Table pour les bornes de recharge (nodes)
tables.charging_stations = osm2pgsql.define_node_table('charging_stations', {
    { column = 'id_column', type = 'id_type' },
    { column = 'geom', type = 'point', projection = 4326 },
    { column = 'tags', type = 'hstore' },
    { column = 'operator', type = 'text' },
    { column = 'capacity', type = 'text' },
    { column = 'insee', type = 'text' },
})

-- Table pour les bornes de recharge (ways)
tables.charging_stations_ways = osm2pgsql.define_way_table('charging_stations_ways', {
    { column = 'id_column', type = 'id_type' },
    { column = 'geom', type = 'linestring', projection = 4326 },
    { column = 'tags', type = 'hstore' },
    { column = 'operator', type = 'text' },
    { column = 'capacity', type = 'text' },
    { column = 'insee', type = 'text' },
})

-- Function to determine the INSEE code from multiple possible sources
function get_insee_code(tags)
    -- Try to get INSEE code from different tags
    if tags['ref:INSEE'] then
        return tags['ref:INSEE']
    elseif tags['addr:postcode'] then
        -- French postal codes often start with the department code
        -- For example, 91150 is in department 91, which can help identify the INSEE code
        return tags['addr:postcode'] and string.sub(tags['addr:postcode'], 1, 2) .. "111"
    elseif tags['addr:city'] and tags['addr:city'] == 'Étampes' then
        -- If the city is Étampes, use the INSEE code 91111
        return "91111"
    else
        -- Default to 91111 (Étampes) for this specific use case
        -- In a production environment, you would use a spatial query to determine the INSEE code
        return "91111"
    end
end

function osm2pgsql.process_node(object)
    -- Check for fire hydrants with different tagging schemes
    if object.tags.emergency == 'fire_hydrant' or object.tags.amenity == 'fire_hydrant' then
        tables.fire_hydrants:insert({
            tags = object.tags,
            ref = object.tags.ref,
            color = object.tags.color,
            insee = get_insee_code(object.tags)
        })
    end

    -- Check for trees
    if object.tags.natural == 'tree' then
        tables.trees:insert({
            tags = object.tags,
            species = object.tags.species,
            height = object.tags.height,
            insee = get_insee_code(object.tags)
        })
    end

    -- Check for charging stations
    if object.tags.amenity == 'charging_station' then
        tables.charging_stations:insert({
            tags = object.tags,
            operator = object.tags.operator,
            capacity = object.tags.capacity,
            insee = get_insee_code(object.tags)
        })
    end
end

function osm2pgsql.process_way(object)
    -- Check for charging stations that might be mapped as ways
    if object.tags.amenity == 'charging_station' then
        tables.charging_stations_ways:insert({
            tags = object.tags,
            operator = object.tags.operator,
            capacity = object.tags.capacity,
            insee = get_insee_code(object.tags)
        })
    end
end

function osm2pgsql.process_relation(object)
    return
end


EOF

    # Déterminer si le fichier est un fichier de changement ou d'historique
    OSM_FILE="osm_data/test.osc"
    FILE_EXT="${OSM_FILE##*.}"
    
    # Utiliser le mode append pour les fichiers .osc ou .osh.pbf (fichiers de changement ou d'historique)
    if [[ "$OSM_FILE" == *.osc || "$OSM_FILE" == *.osh.pbf ]]; then
        log "Détection d'un fichier de changement ou d'historique. Utilisation du mode append..."
        # Importation avec osm2pgsql en mode append pour les fichiers de changement
        PGPASSWORD=$PGPASSWORD osm2pgsql --database $PGDATABASE --append --user $PGUSER --host localhost --slim \
            --style osm_data/style.lua --extra-attributes "$OSM_FILE" -O flex
    else
        # Importation standard avec osm2pgsql pour les fichiers .osm ou .pbf
        # Utiliser --slim sans --drop pour créer une base de données updatable
        PGPASSWORD=$PGPASSWORD osm2pgsql --create --database $PGDATABASE --user $PGUSER --host localhost --slim \
            --style osm_data/style.lua --extra-attributes "$OSM_FILE" -O flex
    fi

    log "Données importées avec succès!"
}

# Analyse des objets OpenStreetMap pour une commune spécifique
analyse_osm_objects() {
    # Créer une table pour stocker les résultats d'analyse
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    CREATE TABLE IF NOT EXISTS osm_analysis (
        id SERIAL PRIMARY KEY,
        date_analyse DATE DEFAULT CURRENT_DATE,
        code_insee TEXT,
        type_objet TEXT,
        nombre_total INTEGER,
        nombre_avec_attr1 INTEGER,
        nombre_avec_attr2 INTEGER,
        pourcentage_completion NUMERIC(5,2)
    );"

    # Fonction pour analyser un type d'objet spécifique
    analyse_object_type() {
        local object_type=$1
        log "Analyse des $object_type pour la commune avec le code INSEE $CODE_INSEE..."

        # Supprimer les analyses existantes pour éviter les doublons
        PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
        DELETE FROM osm_analysis 
        WHERE code_insee = '$CODE_INSEE' 
        AND type_objet = '$object_type'
        AND date_analyse::date = CURRENT_DATE;"

        # Exécuter l'analyse en fonction du type d'objet
        case $object_type in
            "fire_hydrant")
                # Analyse des bornes incendie (attributs: ref, color)
                PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
                INSERT INTO osm_analysis (code_insee, type_objet, nombre_total, nombre_avec_ref, nombre_avec_color, pourcentage_completion)
                SELECT
                    '$CODE_INSEE' as code_insee,
                    '$object_type' as type_objet,
                    COUNT(DISTINCT id_column) as total,
                    COUNT(DISTINCT CASE WHEN ref IS NOT NULL THEN id_column END) as avec_ref,
                    COUNT(DISTINCT CASE WHEN color IS NOT NULL THEN id_column END) as avec_color,
                    CASE
                        WHEN COUNT(DISTINCT id_column) = 0 THEN 0
                        ELSE ROUND(((COUNT(DISTINCT CASE WHEN ref IS NOT NULL THEN id_column END) + 
                                    COUNT(DISTINCT CASE WHEN color IS NOT NULL THEN id_column END))::numeric / 
                                    (COUNT(DISTINCT id_column) * 2)::numeric) * 100, 2)
                    END as pourcentage_completion
                FROM (
                    SELECT id_column, ref, color, insee, version
                    FROM fire_hydrants
                    WHERE insee = '$CODE_INSEE'
                    AND version = (
                        SELECT MAX(version)
                        FROM fire_hydrants AS fh2
                        WHERE fh2.id_column = fire_hydrants.id_column
                    )
                ) AS latest_versions;"
                ;;
            "tree")
                # Analyse des arbres (attributs: species, height)
                PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
                INSERT INTO osm_analysis (code_insee, type_objet, nombre_total, nombre_avec_ref, nombre_avec_color, pourcentage_completion)
                SELECT
                    '$CODE_INSEE' as code_insee,
                    '$object_type' as type_objet,
                    COUNT(DISTINCT id_column) as total,
                    COUNT(DISTINCT CASE WHEN species IS NOT NULL THEN id_column END) as avec_species,
                    COUNT(DISTINCT CASE WHEN height IS NOT NULL THEN id_column END) as avec_height,
                    CASE
                        WHEN COUNT(DISTINCT id_column) = 0 THEN 0
                        ELSE ROUND(((COUNT(DISTINCT CASE WHEN species IS NOT NULL THEN id_column END) + 
                                    COUNT(DISTINCT CASE WHEN height IS NOT NULL THEN id_column END))::numeric / 
                                    (COUNT(DISTINCT id_column) * 2)::numeric) * 100, 2)
                    END as pourcentage_completion
                FROM (
                    SELECT id_column, species, height, insee, version
                    FROM trees
                    WHERE insee = '$CODE_INSEE'
                    AND version = (
                        SELECT MAX(version)
                        FROM trees AS t2
                        WHERE t2.id_column = trees.id_column
                    )
                ) AS latest_versions;"
                ;;
            "charging_station")
                # Analyse des bornes de recharge (attributs: operator, capacity)
                PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
                INSERT INTO osm_analysis (code_insee, type_objet, nombre_total, nombre_avec_ref, nombre_avec_color, pourcentage_completion)
                SELECT
                    '$CODE_INSEE' as code_insee,
                    '$object_type' as type_objet,
                    COUNT(DISTINCT id_column) as total,
                    COUNT(DISTINCT CASE WHEN operator IS NOT NULL THEN id_column END) as avec_operator,
                    COUNT(DISTINCT CASE WHEN capacity IS NOT NULL THEN id_column END) as avec_capacity,
                    CASE
                        WHEN COUNT(DISTINCT id_column) = 0 THEN 0
                        ELSE ROUND(((COUNT(DISTINCT CASE WHEN operator IS NOT NULL THEN id_column END) + 
                                    COUNT(DISTINCT CASE WHEN capacity IS NOT NULL THEN id_column END))::numeric / 
                                    (COUNT(DISTINCT id_column) * 2)::numeric) * 100, 2)
                    END as pourcentage_completion
                FROM (
                    SELECT id_column, operator, capacity, insee, version
                    FROM charging_stations
                    WHERE insee = '$CODE_INSEE'
                    AND version = (
                        SELECT MAX(version)
                        FROM charging_stations AS cs2
                        WHERE cs2.id_column = charging_stations.id_column
                    )
                ) AS latest_versions;"
                ;;
            *)
                log "Type d'objet non reconnu: $object_type"
                return 1
                ;;
        esac

        # Afficher les résultats pour ce type d'objet
        log "Résultats de l'analyse pour $object_type :"
        PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
        SELECT * FROM osm_analysis 
        WHERE code_insee = '$CODE_INSEE' 
        AND type_objet = '$object_type'
        AND date_analyse::date = CURRENT_DATE;"
    }

    # Vérifier si on analyse tous les types d'objets ou un seul
    if [ "$OSM_OBJECT" == "all" ]; then
        log "Analyse de tous les types d'objets pour la commune avec le code INSEE $CODE_INSEE..."
        
        # Analyser chaque type d'objet
        analyse_object_type "fire_hydrant"
        analyse_object_type "tree"
        analyse_object_type "charging_station"
        
        # Afficher un résumé de tous les objets
        log "Résumé de tous les types d'objets :"
        PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
        SELECT 
            type_objet, 
            nombre_total, 
            nombre_avec_attr1, 
            nombre_avec_attr2, 
            pourcentage_completion 
        FROM osm_analysis 
        WHERE code_insee = '$CODE_INSEE' 
        AND date_analyse::date = CURRENT_DATE
        ORDER BY type_objet;"
        
        # Afficher le total de tous les objets
        PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
        SELECT 
            'TOTAL' as type_objet,
            SUM(nombre_total) as nombre_total,
            SUM(nombre_avec_attr1) as nombre_avec_attr1,
            SUM(nombre_avec_attr2) as nombre_avec_attr2,
            CASE
                WHEN SUM(nombre_total) = 0 THEN 0
                ELSE ROUND(((SUM(nombre_avec_attr1) + SUM(nombre_avec_attr2))::numeric / (SUM(nombre_total) * 2)::numeric) * 100, 2)
            END as pourcentage_completion
        FROM osm_analysis 
        WHERE code_insee = '$CODE_INSEE' 
        AND date_analyse::date = CURRENT_DATE;"
    else
        # Analyser un seul type d'objet
        analyse_object_type "$OSM_OBJECT"
    fi
}

# Exportation des données historiques en CSV
export_historical_data() {
    log "Exportation des données historiques..."

    # Création du fichier CSV pour les données annuelles (10 dernières années)
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    COPY (
        WITH latest_annual_data AS (
            SELECT DISTINCT ON (year_date, code_insee, type_objet)
                EXTRACT(YEAR FROM date_analyse) AS year_date,
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion,
                date_analyse
            FROM osm_analysis
            WHERE date_analyse >= (CURRENT_DATE - INTERVAL '10 years')
            ORDER BY year_date, code_insee, type_objet, date_analyse DESC
        )
        SELECT
            year_date AS annee,
            code_insee,
            type_objet,
            nombre_total,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'ref'
                WHEN type_objet = 'tree' THEN 'species'
                WHEN type_objet = 'charging_station' THEN 'operator'
                ELSE 'attr1'
            END AS attr1_name,
            nombre_avec_attr1,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'color'
                WHEN type_objet = 'tree' THEN 'height'
                WHEN type_objet = 'charging_station' THEN 'capacity'
                ELSE 'attr2'
            END AS attr2_name,
            nombre_avec_attr2,
            pourcentage_completion,
            'annual' AS periode
        FROM latest_annual_data
        ORDER BY year_date, code_insee, type_objet
    ) TO STDOUT WITH CSV HEADER
    " > historique_osm_$CODE_INSEE.csv

    # Ajout des données mensuelles pour le premier jour de chaque mois des 10 dernières années
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    COPY (
        WITH months AS (
            SELECT generate_series(
                date_trunc('month', CURRENT_DATE - INTERVAL '10 years'),
                date_trunc('month', CURRENT_DATE),
                interval '1 month'
            ) AS month_start
        ),
        monthly_data AS (
            SELECT DISTINCT ON (m.month_start, a.code_insee, a.type_objet)
                m.month_start,
                a.code_insee,
                a.type_objet,
                a.nombre_total,
                a.nombre_avec_attr1,
                a.nombre_avec_attr2,
                a.pourcentage_completion
            FROM months m
            LEFT JOIN LATERAL (
                SELECT *
                FROM osm_analysis
                WHERE date_analyse <= m.month_start
                AND code_insee = '$CODE_INSEE'
                ORDER BY date_analyse DESC
                LIMIT 1
            ) a ON true
            WHERE a.id IS NOT NULL
            ORDER BY m.month_start, a.code_insee, a.type_objet, a.date_analyse DESC
        )
        SELECT
            TO_CHAR(month_start, 'YYYY-MM') AS annee_mois,
            code_insee,
            type_objet,
            nombre_total,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'ref'
                WHEN type_objet = 'tree' THEN 'species'
                WHEN type_objet = 'charging_station' THEN 'operator'
                ELSE 'attr1'
            END AS attr1_name,
            nombre_avec_attr1,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'color'
                WHEN type_objet = 'tree' THEN 'height'
                WHEN type_objet = 'charging_station' THEN 'capacity'
                ELSE 'attr2'
            END AS attr2_name,
            nombre_avec_attr2,
            pourcentage_completion,
            'monthly' AS periode
        FROM monthly_data
        ORDER BY month_start DESC, code_insee, type_objet
    ) TO STDOUT WITH CSV
    " >> historique_osm_$CODE_INSEE.csv

    # Ajout des données quotidiennes (30 derniers jours)
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    COPY (
        WITH daily_data AS (
            SELECT DISTINCT ON (date_analyse, code_insee, type_objet)
                date_analyse,
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse >= (CURRENT_DATE - INTERVAL '30 days')
            ORDER BY date_analyse, code_insee, type_objet, id DESC
        )
        SELECT
            TO_CHAR(date_analyse, 'YYYY-MM-DD') AS jour,
            code_insee,
            type_objet,
            nombre_total,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'ref'
                WHEN type_objet = 'tree' THEN 'species'
                WHEN type_objet = 'charging_station' THEN 'operator'
                ELSE 'attr1'
            END AS attr1_name,
            nombre_avec_attr1,
            CASE
                WHEN type_objet = 'fire_hydrant' THEN 'color'
                WHEN type_objet = 'tree' THEN 'height'
                WHEN type_objet = 'charging_station' THEN 'capacity'
                ELSE 'attr2'
            END AS attr2_name,
            nombre_avec_attr2,
            pourcentage_completion,
            'daily' AS periode
        FROM daily_data
        ORDER BY date_analyse DESC, code_insee, type_objet
    ) TO STDOUT WITH CSV
    " >> historique_osm_$CODE_INSEE.csv
    
    # Générer les graphiques d'évolution
    log "Génération des graphiques d'évolution..."
    
    # Vérifier si Python est installé
    if command -v python3 &> /dev/null; then
        # Vérifier si les bibliothèques nécessaires sont installées
        if ! python3 -c "import pandas, matplotlib" &> /dev/null; then
            log "Installation des bibliothèques Python nécessaires..."
            pip install pandas matplotlib
        fi
        
        # Générer les graphiques pour les différentes périodes
        log "Génération du graphique pour les données mensuelles..."
        python3 generate_graph.py "historique_osm_$CODE_INSEE.csv" --period monthly
        
        log "Génération du graphique pour les données annuelles..."
        python3 generate_graph.py "historique_osm_$CODE_INSEE.csv" --period annual
        
        log "Graphiques générés avec succès!"
    else
        log "Python n'est pas installé. Les graphiques n'ont pas pu être générés."
        log "Pour générer les graphiques manuellement, installez Python et exécutez:"
        log "python3 generate_graph.py historique_osm_$CODE_INSEE.csv --period monthly"
    fi

    # Ajout des données avec les changements sur différentes périodes
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    COPY (
        WITH current_data AS (
            SELECT 
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse = CURRENT_DATE
            AND code_insee = '$CODE_INSEE'
        ),
        data_24h AS (
            SELECT 
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse = CURRENT_DATE - INTERVAL '1 day'
            AND code_insee = '$CODE_INSEE'
        ),
        data_7d AS (
            SELECT 
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse = CURRENT_DATE - INTERVAL '7 days'
            AND code_insee = '$CODE_INSEE'
        ),
        data_15d AS (
            SELECT 
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse = CURRENT_DATE - INTERVAL '15 days'
            AND code_insee = '$CODE_INSEE'
        ),
        data_30d AS (
            SELECT 
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            FROM osm_analysis
            WHERE date_analyse = CURRENT_DATE - INTERVAL '30 days'
            AND code_insee = '$CODE_INSEE'
        )
        SELECT 
            TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS date_actuelle,
            c.code_insee,
            c.type_objet,
            c.nombre_total,
            c.nombre_avec_attr1,
            c.nombre_avec_attr2,
            c.pourcentage_completion,
            
            -- Changements sur 24h
            COALESCE(c.nombre_total - d24.nombre_total, 0) AS delta_total_24h,
            COALESCE(c.nombre_avec_attr1 - d24.nombre_avec_attr1, 0) AS delta_attr1_24h,
            COALESCE(c.nombre_avec_attr2 - d24.nombre_avec_attr2, 0) AS delta_attr2_24h,
            COALESCE(c.pourcentage_completion - d24.pourcentage_completion, 0) AS delta_completion_24h,
            
            -- Changements sur 7 jours
            COALESCE(c.nombre_total - d7.nombre_total, 0) AS delta_total_7d,
            COALESCE(c.nombre_avec_attr1 - d7.nombre_avec_attr1, 0) AS delta_attr1_7d,
            COALESCE(c.nombre_avec_attr2 - d7.nombre_avec_attr2, 0) AS delta_attr2_7d,
            COALESCE(c.pourcentage_completion - d7.pourcentage_completion, 0) AS delta_completion_7d,
            
            -- Changements sur 15 jours
            COALESCE(c.nombre_total - d15.nombre_total, 0) AS delta_total_15d,
            COALESCE(c.nombre_avec_attr1 - d15.nombre_avec_attr1, 0) AS delta_attr1_15d,
            COALESCE(c.nombre_avec_attr2 - d15.nombre_avec_attr2, 0) AS delta_attr2_15d,
            COALESCE(c.pourcentage_completion - d15.pourcentage_completion, 0) AS delta_completion_15d,
            
            -- Changements sur 30 jours
            COALESCE(c.nombre_total - d30.nombre_total, 0) AS delta_total_30d,
            COALESCE(c.nombre_avec_attr1 - d30.nombre_avec_attr1, 0) AS delta_attr1_30d,
            COALESCE(c.nombre_avec_attr2 - d30.nombre_avec_attr2, 0) AS delta_attr2_30d,
            COALESCE(c.pourcentage_completion - d30.pourcentage_completion, 0) AS delta_completion_30d,
            
            'changes' AS periode
        FROM current_data c
        LEFT JOIN data_24h d24 ON c.code_insee = d24.code_insee AND c.type_objet = d24.type_objet
        LEFT JOIN data_7d d7 ON c.code_insee = d7.code_insee AND c.type_objet = d7.type_objet
        LEFT JOIN data_15d d15 ON c.code_insee = d15.code_insee AND c.type_objet = d15.type_objet
        LEFT JOIN data_30d d30 ON c.code_insee = d30.code_insee AND c.type_objet = d30.type_objet
    ) TO STDOUT WITH CSV
    " >> historique_osm_$CODE_INSEE.csv

    log "Données exportées dans le fichier historique_osm_$CODE_INSEE.csv"
}

# Suppression des tables de la base de données
delete_database_tables() {
    log "Suppression des tables de la base de données..."

    # Demander confirmation avant de supprimer
    echo -n "Êtes-vous sûr de vouloir supprimer toutes les tables? Cette action est irréversible. (o/n): "
    read confirm
    if [[ "$confirm" != "o" && "$confirm" != "O" ]]; then
        log "Suppression annulée."
        return
    fi

    # Supprimer les tables principales
    PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
    DROP TABLE IF EXISTS fire_hydrants CASCADE;
    DROP TABLE IF EXISTS trees CASCADE;
    DROP TABLE IF EXISTS charging_stations CASCADE;
    DROP TABLE IF EXISTS charging_stations_ways CASCADE;
    DROP TABLE IF EXISTS osm_analysis CASCADE;
    "

    log "Tables supprimées avec succès!"
}

# Simulation de données historiques (pour avoir des données à exporter)
simulate_historical_data() {
    log "Simulation de données historiques pour les 5 dernières années..."

    # Déterminer les types d'objets à simuler
    local object_types=()
    if [ "$OSM_OBJECT" == "all" ]; then
        object_types=("fire_hydrant" "tree" "charging_station")
    else
        object_types=("$OSM_OBJECT")
    fi

    # Générer des données pour chaque type d'objet
    for obj_type in "${object_types[@]}"; do
        log "Simulation de données pour $obj_type..."
        
        # Générer des données annuelles (5 dernières années)
        for i in {5..1}; do
            year=$(($(date +%Y) - $i))
            completion=$((50 + $i * 10)) # Augmentation progressive de la complétion
            
            # Récupérer les valeurs réelles de la base de données
            case $obj_type in
                "fire_hydrant")
                    # Récupérer le nombre total de bornes incendie
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM fire_hydrants WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-20}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * i / 5)) # ref
                    attr2=$((total * i / 4)) # color
                    ;;
                "tree")
                    # Récupérer le nombre total d'arbres
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM trees WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-50}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * i / 5)) # species
                    attr2=$((total * i / 8)) # height
                    ;;
                "charging_station")
                    # Récupérer le nombre total de bornes de recharge
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM charging_stations WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-10}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * i / 5)) # operator
                    attr2=$((total * i / 4)) # capacity
                    ;;
            esac

            # Insérer les données annuelles
            PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
            INSERT INTO osm_analysis (
                date_analyse,
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            ) VALUES (
                '$year-01-01',
                '$CODE_INSEE',
                '$obj_type',
                $total,
                $attr1,
                $attr2,
                $completion
            );"
        done
        
        # Générer des données mensuelles (12 derniers mois)
        for i in {12..1}; do
            month_date=$(date -d "$(date +%Y-%m-01) -$i month" +%Y-%m-%d)
            completion=$((70 + $i * 2)) # Variation mensuelle
            
            # Récupérer les valeurs réelles de la base de données
            case $obj_type in
                "fire_hydrant")
                    # Récupérer le nombre total de bornes incendie
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM fire_hydrants WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-25}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (10 + $i) / 30)) # ref
                    attr2=$((total * (15 + $i) / 30)) # color
                    ;;
                "tree")
                    # Récupérer le nombre total d'arbres
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM trees WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-60}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (30 + $i) / 60)) # species
                    attr2=$((total * (20 + $i) / 60)) # height
                    ;;
                "charging_station")
                    # Récupérer le nombre total de bornes de recharge
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM charging_stations WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-15}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (5 + $i) / 15)) # operator
                    attr2=$((total * (8 + $i) / 15)) # capacity
                    ;;
            esac
            
            # Insérer les données mensuelles
            PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
            INSERT INTO osm_analysis (
                date_analyse,
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            ) VALUES (
                '$month_date',
                '$CODE_INSEE',
                '$obj_type',
                $total,
                $attr1,
                $attr2,
                $completion
            );"
        done
        
        # Générer des données quotidiennes (30 derniers jours)
        for i in {30..1}; do
            day_date=$(date -d "$(date +%Y-%m-%d) -$i day" +%Y-%m-%d)
            completion=$((85 + ($i % 10))) # Petite variation quotidienne
            
            # Récupérer les valeurs réelles de la base de données
            case $obj_type in
                "fire_hydrant")
                    # Récupérer le nombre total de bornes incendie
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM fire_hydrants WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-30}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (20 + ($i % 5)) / 30)) # ref
                    attr2=$((total * (25 + ($i % 3)) / 30)) # color
                    ;;
                "tree")
                    # Récupérer le nombre total d'arbres
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM trees WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-70}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (40 + ($i % 8)) / 70)) # species
                    attr2=$((total * (30 + ($i % 6)) / 70)) # height
                    ;;
                "charging_station")
                    # Récupérer le nombre total de bornes de recharge
                    total=$(PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -t -c "
                    SELECT COUNT(*) FROM charging_stations WHERE insee = '$CODE_INSEE';")
                    # Si aucun résultat, utiliser une valeur par défaut
                    total=${total:-20}
                    # Trim whitespace
                    total=$(echo $total | xargs)
                    
                    # Calculer des valeurs simulées pour les attributs basées sur le total réel
                    attr1=$((total * (10 + ($i % 4)) / 20)) # operator
                    attr2=$((total * (15 + ($i % 3)) / 20)) # capacity
                    ;;
            esac
            
            # Insérer les données quotidiennes
            PGPASSWORD=$PGPASSWORD psql -h localhost -U $PGUSER -d $PGDATABASE -c "
            INSERT INTO osm_analysis (
                date_analyse,
                code_insee,
                type_objet,
                nombre_total,
                nombre_avec_attr1,
                nombre_avec_attr2,
                pourcentage_completion
            ) VALUES (
                '$day_date',
                '$CODE_INSEE',
                '$obj_type',
                $total,
                $attr1,
                $attr2,
                $completion
            );"
        done
    done

    log "Données historiques simulées avec succès!"
}

# Menu principal
show_menu() {
    echo ""
    echo "===== GESTIONNAIRE DE DONNÉES OSM ====="
    echo "1. Initialiser la base de données PostgreSQL"
    echo "2. Importer les données OSM de l'Île-de-France"
    echo "3. Analyser les objets OSM pour une commune"
    echo "4. Exporter les données historiques en CSV"
    echo "5. Supprimer les tables de la base de données"
    echo "6. Quitter"
    echo "========================================"
    echo -n "Votre choix: "
    read choice

    case $choice in
        1) check_prerequisites && init_database && show_menu ;;
        2) import_osm_data && show_menu ;;
        3)
            echo -n "Entrez le code INSEE de la commune (ou appuyez sur Entrée pour utiliser $CODE_INSEE): "
            read insee
            if [ ! -z "$insee" ]; then
                CODE_INSEE=$insee
            fi
            
            echo ""
            echo "Sélectionnez le type d'objet à analyser:"
            echo "1. Bornes incendie (fire_hydrant)"
            echo "2. Arbres (tree)"
            echo "3. Bornes de recharge (charging_station)"
            echo "4. Tous les types d'objets"
            echo -n "Votre choix (1-4): "
            read object_choice
            
            case $object_choice in
                1) OSM_OBJECT="fire_hydrant" ;;
                2) OSM_OBJECT="tree" ;;
                3) OSM_OBJECT="charging_station" ;;
                4) OSM_OBJECT="all" ;;
                *) log "Choix invalide, utilisation du type par défaut: $OSM_OBJECT" ;;
            esac
            
            analyse_osm_objects && show_menu
            ;;
        4) 
            echo ""
            echo "Sélectionnez le type d'objet pour l'exportation (ou 'all' pour tous):"
            echo "1. Bornes incendie (fire_hydrant)"
            echo "2. Arbres (tree)"
            echo "3. Bornes de recharge (charging_station)"
            echo "4. Tous les types d'objets"
            echo -n "Votre choix (1-4): "
            read export_choice
            
            case $export_choice in
                1) OSM_OBJECT="fire_hydrant" ;;
                2) OSM_OBJECT="tree" ;;
                3) OSM_OBJECT="charging_station" ;;
                4) OSM_OBJECT="all" ;;
                *) log "Choix invalide, utilisation du type par défaut: $OSM_OBJECT" ;;
            esac
            
            # Utiliser les données réelles uniquement
            log "Utilisation des données réelles pour l'exportation..."
            
            export_historical_data && show_menu
            ;;
        5) delete_database_tables && show_menu ;;
        6) log "Merci d'avoir utilisé le gestionnaire de données OSM!" && exit 0 ;;
        *) log "Option invalide, veuillez réessayer." && show_menu ;;
    esac
}

# Démarrer le script
log "Démarrage du script de gestion des données OpenStreetMap..."
show_menu