#!/usr/bin/env php
<?php
/**
 * Database Backup Script
 * Creates a full database backup
 * Cron job: Daily at 2 AM
 */

require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../config/database.php';

$backupDir = __DIR__ . '/../backups/';
$date = date('Y-m-d_H-i-s');
$backupFile = $backupDir . "backup_{$date}.sql";
$logFile = __DIR__ . '/../logs/backup.log';

function logMessage($message) {
    global $logFile;
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents($logFile, "[$timestamp] $message\n", FILE_APPEND);
}

logMessage("Starting database backup...");

// Create backup directory if not exists
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0755, true);
}

$pdo = getDB();

// Get all tables
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

$backupContent = "-- Homoeopathy System Database Backup\n";
$backupContent .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
$backupContent .= "-- Database: " . DB_NAME . "\n";
$backupContent .= "-- Tables: " . implode(', ', $tables) . "\n\n";
$backupContent .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

foreach ($tables as $table) {
    logMessage("Backing up table: $table");
    
    // Get create table syntax
    $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
    $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
    $backupContent .= "DROP TABLE IF EXISTS `$table`;\n";
    $backupContent .= $createTable['Create Table'] . ";\n\n";
    
    // Get data
    $stmt = $pdo->query("SELECT * FROM `$table`");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($rows)) {
        $columns = array_keys($rows[0]);
        $backupContent .= "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES\n";
        
        $values = [];
        foreach ($rows as $row) {
            $rowValues = [];
            foreach ($row as $value) {
                if ($value === null) {
                    $rowValues[] = "NULL";
                } else {
                    $rowValues[] = "'" . addslashes($value) . "'";
                }
            }
            $values[] = "(" . implode(', ', $rowValues) . ")";
        }
        $backupContent .= implode(",\n", $values) . ";\n\n";
    }
}

$backupContent .= "SET FOREIGN_KEY_CHECKS=1;\n";

// Write backup file
if (file_put_contents($backupFile, $backupContent)) {
    $size = filesize($backupFile);
    logMessage("Backup completed: $backupFile (" . formatFileSize($size) . ")");
    
    // Delete old backups (keep last 30 days)
    $files = glob($backupDir . "*.sql");
    usort($files, function($a, $b) {
        return filemtime($a) - filemtime($b);
    });
    
    $keep = 30;
    $toDelete = count($files) - $keep;
    for ($i = 0; $i < $toDelete; $i++) {
        unlink($files[$i]);
        logMessage("Deleted old backup: " . basename($files[$i]));
    }
} else {
    logMessage("ERROR: Failed to create backup");
}

logMessage("Backup process completed");