<?php
// Script de diagnostic et correction de la table locations
require_once __DIR__ . '/../app/Core/Config.php';

echo "=== Diagnostic et correction des colonnes manquantes ===\n";

$host = 'localhost';
$username = 'root';
$password = '';
$database = 'bd_insuite_backbone';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$database", $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
    ]);
    
    echo "✓ Connexion réussie\n\n";
    
    // Vérifier si la table locations existe
    $stmt = $pdo->query("SHOW TABLES LIKE 'locations'");
    if ($stmt->rowCount() == 0) {
        echo "❌ Table 'locations' n'existe pas - Création...\n";
        $pdo->exec("
            CREATE TABLE `locations` (
                `id` INT(11) NOT NULL AUTO_INCREMENT,
                `client_id` INT(11) NOT NULL,
                `name` VARCHAR(255) NOT NULL,
                `address` TEXT,
                `city` VARCHAR(100),
                `postal_code` VARCHAR(20),
                `latitude` DECIMAL(10,8),
                `longitude` DECIMAL(11,8),
                `active` TINYINT(1) DEFAULT 1,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_client` (`client_id`),
                KEY `idx_name` (`name`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        echo "✅ Table 'locations' créée\n";
    } else {
        echo "ℹ️ Table 'locations' existe déjà\n";
        
        // Vérifier la structure existante
        echo "\n🔍 Structure actuelle de la table 'locations' :\n";
        $stmt = $pdo->query("DESCRIBE locations");
        $columns = $stmt->fetchAll();
        foreach ($columns as $col) {
            echo "  - {$col['Field']} ({$col['Type']})\n";
        }
        
        // Vérifier si client_id existe
        $stmt = $pdo->query("SHOW COLUMNS FROM locations LIKE 'client_id'");
        if ($stmt->rowCount() == 0) {
            echo "\n❌ Colonne 'client_id' manquante - Ajout...\n";
            $pdo->exec("ALTER TABLE locations ADD COLUMN client_id INT(11) NOT NULL DEFAULT 1 AFTER id");
            $pdo->exec("ALTER TABLE locations ADD INDEX idx_client (client_id)");
            echo "✅ Colonne 'client_id' ajoutée\n";
        } else {
            echo "\n✅ Colonne 'client_id' existe déjà\n";
        }
    }
    
    // Vérifier si la table incidents existe et a la bonne structure
    $stmt = $pdo->query("SHOW TABLES LIKE 'incidents'");
    if ($stmt->rowCount() == 0) {
        echo "\n❌ Table 'incidents' n'existe pas - Création...\n";
        $pdo->exec("
            CREATE TABLE `incidents` (
                `id` INT(11) NOT NULL AUTO_INCREMENT,
                `reference` VARCHAR(50) UNIQUE,
                `client_id` INT(11) NOT NULL,
                `location_id` INT(11),
                `title` VARCHAR(255) NOT NULL,
                `description` TEXT,
                `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
                `status_id` INT(11) DEFAULT 1,
                `assigned_to` INT(11),
                `declared_by` INT(11),
                `declared_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `resolved_at` TIMESTAMP NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_client` (`client_id`),
                KEY `idx_location` (`location_id`),
                KEY `idx_status` (`status_id`),
                KEY `idx_assigned` (`assigned_to`),
                KEY `idx_reference` (`reference`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        echo "✅ Table 'incidents' créée\n";
    } else {
        echo "\nℹ️ Table 'incidents' existe\n";
        
        // Vérifier si client_id existe
        $stmt = $pdo->query("SHOW COLUMNS FROM incidents LIKE 'client_id'");
        if ($stmt->rowCount() == 0) {
            echo "❌ Colonne 'client_id' manquante dans incidents - Ajout...\n";
            $pdo->exec("ALTER TABLE incidents ADD COLUMN client_id INT(11) NOT NULL DEFAULT 1 AFTER reference");
            $pdo->exec("ALTER TABLE incidents ADD INDEX idx_client (client_id)");
            echo "✅ Colonne 'client_id' ajoutée à incidents\n";
        }
    }
    
    // Insérer des données de test si les tables sont vides
    $stmt = $pdo->query("SELECT COUNT(*) FROM locations");
    $locationCount = $stmt->fetchColumn();
    
    if ($locationCount == 0) {
        echo "\n🎯 Ajout de locations de test...\n";
        $pdo->exec("
            INSERT INTO locations (client_id, name, address, city) VALUES
            (1, 'Orange Siège Social', 'Boulevard Lagunaire, Cocody', 'Abidjan'),
            (1, 'Orange Plateau', 'Avenue Chardy, Plateau', 'Abidjan'),
            (2, 'MTN Siège', 'Avenue Terrasson de Fougères, Plateau', 'Abidjan'),
            (3, 'Banque Atlantique Plateau', 'Boulevard Carde, Plateau', 'Abidjan')
        ");
        echo "✅ Locations de test ajoutées\n";
    }
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM incidents");
    $incidentCount = $stmt->fetchColumn();
    
    if ($incidentCount == 0) {
        echo "\n🚨 Ajout d'incidents de test...\n";
        $pdo->exec("
            INSERT INTO incidents (reference, client_id, location_id, title, description, status_id) VALUES
            ('INC-2025-001', 1, 1, 'Panne réseau fibre', 'Interruption du service internet sur le site principal', 1),
            ('INC-2025-002', 2, 3, 'Problème téléphonie', 'Pas de tonalité sur les postes fixes', 1),
            ('INC-2025-003', 3, 4, 'Accès système bancaire', 'Impossible d\'accéder au système de gestion', 1)
        ");
        echo "✅ Incidents de test ajoutés\n";
    }
    
    echo "\n=== Test de la requête problématique ===\n";
    
    // Tester la requête du contrôleur
    try {
        $stmt = $pdo->prepare("
            SELECT c.*, 
                   COALESCE(COUNT(DISTINCT l.id), 0) as locations_count,
                   COALESCE(COUNT(DISTINCT i.id), 0) as incidents_count
            FROM clients c
            LEFT JOIN locations l ON l.client_id = c.id
            LEFT JOIN incidents i ON i.client_id = c.id
            WHERE 1=1
            GROUP BY c.id 
            ORDER BY c.name ASC
        ");
        $stmt->execute();
        $results = $stmt->fetchAll();
        
        echo "✅ Requête réussie ! Clients trouvés : " . count($results) . "\n";
        
        foreach ($results as $client) {
            echo "  • {$client['name']} - {$client['locations_count']} sites, {$client['incidents_count']} incidents\n";
        }
        
    } catch (Exception $e) {
        echo "❌ Erreur dans la requête : " . $e->getMessage() . "\n";
        
        // Afficher plus de détails sur les tables
        echo "\n🔍 Diagnostic approfondi :\n";
        $stmt = $pdo->query("SHOW CREATE TABLE locations");
        $create = $stmt->fetch();
        echo "Structure locations :\n" . $create['Create Table'] . "\n\n";
    }
    
    echo "\n✅ Diagnostic et correction terminés !\n";
    
} catch (Exception $e) {
    echo "❌ Erreur : " . $e->getMessage() . "\n";
    exit(1);
}