<?php
header('Content-Type: application/json');
define('SECURE_ACCESS', true);
require_once __DIR__ . '/../../includes/config.php';
require_once __DIR__ . '/../../includes/db.php';
require_once __DIR__ . '/../../includes/auth.php';

if (!isLoggedIn() || getCurrentUser()['role'] !== 'admin') {
    http_response_code(403);
    echo json_encode(['success' => false, 'message' => 'Unauthorized.']);
    exit();
}

$action = $_GET['action'] ?? $_POST['action'] ?? '';

if ($action === 'download') {
    try {
        // Build SQL backup
        $sql = "-- EventPortal PostgreSQL Backup\n";
        $sql .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
        $sql .= "SET statement_timeout = 0;\n";
        $sql .= "SET lock_timeout = 0;\n";
        $sql .= "SET client_encoding = 'UTF8';\n";
        $sql .= "SET standard_conforming_strings = on;\n";
        $sql .= "SET check_function_bodies = false;\n";
        $sql .= "SET xmloption = content;\n";
        $sql .= "SET client_min_messages = warning;\n";
        $sql .= "SET row_security = off;\n\n";

        // Get all base tables in public schema
        $stmt = $pdo->query("
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' AND table_type = 'BASE TABLE' 
            ORDER BY table_name
        ");
        $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            $sql .= "-- -----------------------------------------------------\n";
            $sql .= "-- Table Structure for {$table}\n";
            $sql .= "-- -----------------------------------------------------\n";
            $sql .= "DROP TABLE IF EXISTS \"{$table}\" CASCADE;\n\n";

            // Get columns definition
            $col_stmt = $pdo->prepare("
                SELECT column_name, data_type, character_maximum_length, is_nullable, column_default 
                FROM information_schema.columns 
                WHERE table_schema = 'public' AND table_name = ? 
                ORDER BY ordinal_position
            ");
            $col_stmt->execute([$table]);
            $cols = $col_stmt->fetchAll(PDO::FETCH_ASSOC);

            $col_defs = [];
            $col_names = [];
            foreach ($cols as $col) {
                $name = $col['column_name'];
                $col_names[] = "\"{$name}\"";

                $type = $col['data_type'];
                if ($type === 'character varying' && !empty($col['character_maximum_length'])) {
                    $type = "varchar({$col['character_maximum_length']})";
                }
                
                $null = ($col['is_nullable'] === 'NO') ? 'NOT NULL' : 'NULL';
                
                $default = '';
                if (!empty($col['column_default'])) {
                    // Check if default is nextval for sequences (serial)
                    if (strpos($col['column_default'], 'nextval') !== false) {
                        $type = ($type === 'bigint') ? 'BIGSERIAL' : 'SERIAL';
                    } else {
                        $default = "DEFAULT " . $col['column_default'];
                    }
                }

                // If column is primary key
                $pk_suffix = ($name === 'id') ? ' PRIMARY KEY' : '';

                $col_defs[] = "  \"{$name}\" {$type} {$null} {$default}{$pk_suffix}";
            }

            $sql .= "CREATE TABLE \"{$table}\" (\n" . implode(",\n", $col_defs) . "\n);\n\n";

            // Export Table Data
            $sql .= "-- Data for {$table}\n";
            $data_stmt = $pdo->query("SELECT * FROM \"{$table}\"");
            $rows = $data_stmt->fetchAll(PDO::FETCH_ASSOC);

            if (!empty($rows)) {
                $cols_list = implode(", ", $col_names);
                foreach ($rows as $row) {
                    $vals = [];
                    foreach ($cols as $col) {
                        $val = $row[$col['column_name']];
                        if ($val === null) {
                            $vals[] = 'NULL';
                        } elseif (is_bool($val)) {
                            $vals[] = $val ? 'TRUE' : 'FALSE';
                        } elseif (is_int($val) || is_float($val)) {
                            $vals[] = $val;
                        } else {
                            $vals[] = $pdo->quote($val);
                        }
                    }
                    $vals_list = implode(", ", $vals);
                    $sql .= "INSERT INTO \"{$table}\" ({$cols_list}) VALUES ({$vals_list});\n";
                }
            }
            $sql .= "\n";
        }

        // Output File headers
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="eventportal_backup_' . date('Ymd_His') . '.sql"');
        header('Content-Length: ' . strlen($sql));
        echo $sql;
        exit();
    } catch (Exception $e) {
        http_response_code(500);
        echo json_encode(['success' => false, 'message' => 'Failed to generate backup: ' . $e->getMessage()]);
        exit();
    }
} elseif ($action === 'save_settings') {
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        echo json_encode(['success' => false, 'message' => 'Invalid request method.']);
        exit();
    }
    if (!verifyCSRFToken($_POST['csrf_token'] ?? '')) {
        echo json_encode(['success' => false, 'message' => 'Security check failed (CSRF).']);
        exit();
    }

    $frequency = trim($_POST['backup_frequency'] ?? 'disabled');
    $custom_interval = (int)($_POST['backup_custom_interval'] ?? 24);
    $custom_unit = trim($_POST['backup_custom_unit'] ?? 'hours');
    $dest_local = isset($_POST['backup_dest_local']) ? '1' : '0';
    $dest_gdrive = isset($_POST['backup_dest_gdrive']) ? '1' : '0';
    $dest_onedrive = isset($_POST['backup_dest_onedrive']) ? '1' : '0';
    $prune = isset($_POST['backup_prune']) ? '1' : '0';
    $prune_max = (int)($_POST['backup_prune_max'] ?? 5);

    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO system_settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value");
        
        $stmt->execute(['backup_frequency', $frequency]);
        $stmt->execute(['backup_custom_interval', $custom_interval]);
        $stmt->execute(['backup_custom_unit', $custom_unit]);
        $stmt->execute(['backup_dest_local', $dest_local]);
        $stmt->execute(['backup_dest_gdrive', $dest_gdrive]);
        $stmt->execute(['backup_dest_onedrive', $dest_onedrive]);
        $stmt->execute(['backup_prune', $prune]);
        $stmt->execute(['backup_prune_max', $prune_max]);

        $pdo->commit();
        echo json_encode(['success' => true, 'message' => 'Backup settings updated successfully.']);
    } catch (PDOException $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
    }
    exit();
}

echo json_encode(['success' => false, 'message' => 'Invalid action.']);
