<?php
// Script de vérification et création des tables manquantes
require_once __DIR__ . '/../app/Core/Config.php';

echo "=== Vérification des tables ===\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 les tables existantes
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "📋 Tables existantes :\n";
    foreach ($tables as $table) {
        echo "  - $table\n";
    }
    echo "\n";
    
    // Créer la table locations si elle n'existe pas
    if (!in_array('locations', $tables)) {
        echo "⚠ Table 'locations' manquante - 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 {
        // Vérifier si la colonne client_id existe
        $stmt = $pdo->query("SHOW COLUMNS FROM locations LIKE 'client_id'");
        if ($stmt->rowCount() == 0) {
            echo "⚠ Colonne 'client_id' manquante dans locations - Ajout...\n";
            $pdo->exec("ALTER TABLE locations ADD COLUMN client_id INT(11) DEFAULT 1 AFTER id");
            $pdo->exec("ALTER TABLE locations ADD KEY idx_client (client_id)");
            echo "✓ Colonne 'client_id' ajoutée\n";
        } else {
            echo "✓ Table 'locations' OK\n";
        }
    }
    
    // Créer la table incidents si elle n'existe pas
    if (!in_array('incidents', $tables)) {
        echo "⚠ Table 'incidents' manquante - 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 {
        // Vérifier si la colonne 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) DEFAULT 1 AFTER reference");
            $pdo->exec("ALTER TABLE incidents ADD KEY idx_client (client_id)");
            echo "✓ Colonne 'client_id' ajoutée\n";
        } else {
            echo "✓ Table 'incidents' OK\n";
        }
    }
    
    // Créer la table incident_statuses si elle n'existe pas
    if (!in_array('incident_statuses', $tables)) {
        echo "⚠ Table 'incident_statuses' manquante - Création...\n";
        $pdo->exec("
            CREATE TABLE `incident_statuses` (
                `id` INT(11) NOT NULL AUTO_INCREMENT,
                `key_name` VARCHAR(50) NOT NULL,
                `label` VARCHAR(100) NOT NULL,
                `color` VARCHAR(7) DEFAULT '#6c757d',
                `active` TINYINT(1) DEFAULT 1,
                `sort_order` INT(11) DEFAULT 0,
                PRIMARY KEY (`id`),
                UNIQUE KEY `idx_key` (`key_name`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        // Insérer des statuts de base
        $pdo->exec("
            INSERT INTO incident_statuses (key_name, label, color, sort_order) VALUES
            ('nouveau', 'Nouveau', '#dc3545', 1),
            ('ouvert', 'Ouvert', '#fd7e14', 2),
            ('en_cours', 'En cours', '#ffc107', 3),
            ('resolu', 'Résolu', '#20c997', 4),
            ('clos', 'Clos', '#198754', 5)
        ");
        echo "✓ Table 'incident_statuses' créée avec données de base\n";
    }
    
    // Insérer des données de test
    echo "\n🎯 Ajout de données de test...\n";
    
    // Locations de test pour les clients existants
    $pdo->exec("
        INSERT IGNORE INTO locations (id, client_id, name, address, city) VALUES
        (1, 1, 'Orange Siège Social', 'Boulevard Lagunaire', 'Abidjan'),
        (2, 1, 'Orange Plateau', 'Avenue Chardy', 'Abidjan'),
        (3, 2, 'MTN Siège', 'Avenue Terrasson de Fougères', 'Abidjan'),
        (4, 3, 'Banque Atlantique Plateau', 'Boulevard Carde', 'Abidjan')
    ");
    echo "✓ Locations de test ajoutées\n";
    
    // Incidents de test
    $pdo->exec("
        INSERT IGNORE INTO incidents (id, reference, client_id, location_id, title, description, status_id) VALUES
        (1, 'INC-2025-001', 1, 1, 'Panne réseau fibre', 'Interruption du service internet sur le site principal', 1),
        (2, 'INC-2025-002', 2, 3, 'Problème téléphonie', 'Pas de tonalité sur les postes fixes', 2),
        (3, '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=== Vérification finale ===\n";
    
    // Statistiques
    $stmt = $pdo->query("SELECT COUNT(*) FROM clients");
    echo "📊 Clients : " . $stmt->fetchColumn() . "\n";
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM locations");
    echo "📊 Locations : " . $stmt->fetchColumn() . "\n";
    
    $stmt = $pdo->query("SELECT COUNT(*) FROM incidents");
    echo "📊 Incidents : " . $stmt->fetchColumn() . "\n";
    
    echo "\n✅ Installation complète terminée !\n";
    echo "🌐 Accès : http://localhost/Insuite_backbones/public/clients\n";
    
} catch (Exception $e) {
    echo "❌ Erreur : " . $e->getMessage() . "\n";
    exit(1);
}