<?php
/**
 * cron_backup.php — Backup automatique nocturne
 * v1.7 — JeTeGageQue
 *
 * À configurer dans cPanel cron jobs :
 *   0 3 * * * php /home/[user]/public_html/jetegageque/cron_backup.php >> /dev/null 2>&1
 *
 * Ce script :
 * 1. Exporte la BD principale en SQL compressé (gzip)
 * 2. Copie les données vers la BD de backup (même serveur)
 * 3. Garde 30 jours de fichiers SQL, purge les plus anciens
 * 4. Logue chaque opération dans admin_config
 */

// Sécurité : seulement depuis CLI ou token secret
$isCli   = (php_sapi_name() === 'cli');
$token   = $_GET['token'] ?? '';
$validToken = 'JETEGAGE_BACKUP_2024_SECRET'; // À changer !
if (!$isCli && $token !== $validToken) {
    http_response_code(403);
    die('Accès refusé');
}

require_once __DIR__ . '/config.php';

// ── Configuration ────────────────────────────────────────────────
$backupDir   = __DIR__ . '/backups/';
$backupDb    = 'jetegage_db_backup';   // Nom de la BD de backup
$retainDays  = 30;
$dateStr     = date('Y-m-d_H-i');
$sqlFile     = $backupDir . "backup_{$dateStr}.sql";
$gzFile      = $sqlFile . '.gz';

// Créer le dossier backups/ si absent
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0750, true);
    // Protéger avec .htaccess
    file_put_contents($backupDir . '.htaccess', "Deny from all\n");
}

$log = [];
$errors = [];

// ── 1. Export SQL via mysqldump ───────────────────────────────────
$dumpCmd = sprintf(
    'mysqldump --host=%s --user=%s --password=%s --single-transaction --routines --triggers %s 2>&1',
    escapeshellarg($host),
    escapeshellarg($user),
    escapeshellarg($pass),
    escapeshellarg($db)
);

$sqlContent = '';
exec($dumpCmd, $outputLines, $returnCode);

if ($returnCode === 0) {
    $sqlContent = implode("\n", $outputLines);
    // Sauvegarder compressé
    $gz = gzopen($gzFile, 'w9');
    gzwrite($gz, $sqlContent);
    gzclose($gz);
    $sizeMb = round(filesize($gzFile) / 1024 / 1024, 2);
    $log[] = "✅ Export SQL : {$gzFile} ({$sizeMb} MB)";
} else {
    // Fallback PHP si mysqldump non dispo
    $sqlContent = generateSqlDump($pdo, $db);
    $gz = gzopen($gzFile, 'w9');
    gzwrite($gz, $sqlContent);
    gzclose($gz);
    $sizeMb = round(filesize($gzFile) / 1024 / 1024, 2);
    $log[] = "✅ Export SQL (fallback PHP) : {$gzFile} ({$sizeMb} MB)";
}

// ── 2. Copie vers BD de backup ────────────────────────────────────
try {
    // Connexion à la BD de backup
    $backupDsn = "mysql:host={$host};charset=utf8mb4";
    $pdoBack = new PDO($backupDsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);

    // Créer la BD de backup si absente
    $pdoBack->exec("CREATE DATABASE IF NOT EXISTS `{$backupDb}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    $pdoBack->exec("USE `{$backupDb}`");

    // Récupérer toutes les tables de la BD principale
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    $copiedTables = 0;

    foreach ($tables as $table) {
        try {
            // Recréer la table dans la BD backup
            $create = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_NUM);
            $createSql = $create[1];

            $pdoBack->exec("DROP TABLE IF EXISTS `{$table}`");
            $pdoBack->exec($createSql);

            // Copier les données
            $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($rows)) {
                $cols = array_keys($rows[0]);
                $colList = '`' . implode('`,`', $cols) . '`';
                $placeholders = '(' . implode(',', array_fill(0, count($cols), '?')) . ')';
                $insertStmt = $pdoBack->prepare("INSERT INTO `{$table}` ({$colList}) VALUES {$placeholders}");

                $pdoBack->beginTransaction();
                foreach ($rows as $row) {
                    $insertStmt->execute(array_values($row));
                }
                $pdoBack->commit();
            }
            $copiedTables++;
        } catch (Exception $e) {
            $errors[] = "⚠️ Table {$table} : " . $e->getMessage();
        }
    }

    // Ajouter timestamp dans la BD backup
    try {
        $pdoBack->exec("CREATE TABLE IF NOT EXISTS `_backup_log` (
            id INT AUTO_INCREMENT PRIMARY KEY,
            backup_date DATETIME NOT NULL,
            source_db VARCHAR(100),
            tables_count INT DEFAULT 0,
            sql_file VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )");
        $pdoBack->prepare("INSERT INTO `_backup_log` (backup_date, source_db, tables_count, sql_file) VALUES (NOW(), ?, ?, ?)")
            ->execute([$db, $copiedTables, basename($gzFile)]);
    } catch (Exception $e) {}

    $log[] = "✅ Copie BD backup : " . $copiedTables . "/" . count($tables) . " tables copiées vers `{$backupDb}`";

} catch (Exception $e) {
    $errors[] = "❌ Copie BD backup échouée : " . $e->getMessage();
}

// ── 3. Purge des fichiers > 30 jours ─────────────────────────────
$files = glob($backupDir . 'backup_*.sql.gz');
$purged = 0;
if ($files) {
    usort($files, fn($a, $b) => filemtime($b) - filemtime($a));
    foreach (array_slice($files, $retainDays) as $old) {
        unlink($old);
        $purged++;
    }
}
if ($purged > 0) $log[] = "🗑️ {$purged} ancien(s) fichier(s) supprimé(s)";

// ── 4. Logger dans admin_config ───────────────────────────────────
try {
    $summary = implode(' | ', array_merge($log, $errors));
    $pdo->prepare("INSERT INTO admin_config (config_key, config_value) VALUES ('last_backup_log', ?) ON DUPLICATE KEY UPDATE config_value=?")
        ->execute([$summary, $summary]);
    $pdo->prepare("INSERT INTO admin_config (config_key, config_value) VALUES ('last_backup_date', ?) ON DUPLICATE KEY UPDATE config_value=?")
        ->execute([date('Y-m-d H:i:s'), date('Y-m-d H:i:s')]);
    $pdo->prepare("INSERT INTO admin_config (config_key, config_value) VALUES ('last_backup_file', ?) ON DUPLICATE KEY UPDATE config_value=?")
        ->execute([basename($gzFile), basename($gzFile)]);
} catch (Exception $e) {}

// ── Affichage du résultat ─────────────────────────────────────────
$output = [
    'date'   => date('Y-m-d H:i:s'),
    'log'    => $log,
    'errors' => $errors,
    'file'   => basename($gzFile),
];

if ($isCli) {
    echo "=== BACKUP {$output['date']} ===\n";
    foreach ($log as $l)    echo $l . "\n";
    foreach ($errors as $e) echo $e . "\n";
    echo "===========================\n";
} else {
    header('Content-Type: application/json');
    echo json_encode($output, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
}

// ── Fallback : génération SQL par PHP ────────────────────────────
function generateSqlDump(PDO $pdo, string $dbName): string {
    $sql  = "-- JeTeGageQue SQL Backup\n";
    $sql .= "-- Date: " . date('Y-m-d H:i:s') . "\n";
    $sql .= "-- Database: {$dbName}\n\n";
    $sql .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $create = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_NUM);
        $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
        $sql .= $create[1] . ";\n\n";

        $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
        if (!empty($rows)) {
            $cols = '`' . implode('`,`', array_keys($rows[0])) . '`';
            foreach (array_chunk($rows, 100) as $chunk) {
                $values = array_map(function($row) use ($pdo) {
                    return '(' . implode(',', array_map(function($v) use ($pdo) {
                        return $v === null ? 'NULL' : $pdo->quote($v);
                    }, array_values($row))) . ')';
                }, $chunk);
                $sql .= "INSERT INTO `{$table}` ({$cols}) VALUES\n" . implode(",\n", $values) . ";\n";
            }
            $sql .= "\n";
        }
    }
    $sql .= "SET FOREIGN_KEY_CHECKS=1;\n";
    return $sql;
}
?>
