<?php
/**
 * Migration: Ajouter la section Services (Billets, Visas, etc.)
 */
require_once __DIR__ . '/config/app.php';

$db = getDB();
echo "<h2>Migration: Section Services</h2>";
$db->beginTransaction();

try {
    // 1. Create `services` table
    $db->exec("
        CREATE TABLE IF NOT EXISTS services (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            voyageur_id INTEGER NOT NULL,
            type_service TEXT NOT NULL, -- 'billet', 'visa', 'autre'
            titre TEXT NOT NULL,
            description TEXT,
            prix_total REAL DEFAULT 0,
            montant_paye REAL DEFAULT 0,
            statut_paiement TEXT DEFAULT 'impaye',
            statut_service TEXT DEFAULT 'en_cours',
            date_service TEXT,
            fournisseur TEXT,
            reference_fournisseur TEXT,
            notes TEXT,
            created_by INTEGER,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now')),
            FOREIGN KEY (voyageur_id) REFERENCES voyageurs(id),
            FOREIGN KEY (created_by) REFERENCES users(id)
        )
    ");
    echo "<p>✅ Table `services` créée avec succès.</p>";

    // 2. Update `paiements` table to support service payments
    $columns = $db->query("PRAGMA table_info(paiements)")->fetchAll();
    $colNames = array_column($columns, 'name');
    
    if (!in_array('service_id', $colNames)) {
        // SQLite: ALTER TABLE ADD COLUMN is supported for adding columns
        $db->exec("ALTER TABLE paiements ADD COLUMN service_id INTEGER REFERENCES services(id)");
        
        // Make inscription_id nullable since a payment could be for a service instead
        // SQLite doesn't easily let us change a column to NULL if it was NOT NULL without recreating the table.
        // However, in our original schema, inscription_id was just an INTEGER without NOT NULL constraint. Let's verify.
        
        echo "<p>✅ Colonne `service_id` ajoutée à la table `paiements`.</p>";
    } else {
        echo "<p style='color:orange;'>⚠️ La colonne `service_id` existe déjà.</p>";
    }

    $db->commit();
    echo "<h3 style='color: green;'>✅ Migration terminée avec succès!</h3>";
    
} catch (Exception $e) {
    $db->rollBack();
    echo "<h3 style='color: red;'>❌ Erreur lors de la migration: " . $e->getMessage() . "</h3>";
}
