<?php
/**
 * API: Descargar respaldo de base de datos MySQL
 * Intenta usar mysqldump primero; fallback a exportacion nativa en PHP
 */
header('Content-Type: application/json; charset=utf-8');
require_once __DIR__ . '/../config/database.php';

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

try {
    $pdo = getConnection();

    if ($action === 'descargar') {
        $format = $_GET['formato'] ?? 'sql';

        // Try mysqldump first
        $dumpPath = null;
        $candidates = [
            'C:\wamp64\bin\mysql\mysql8.4.7\bin\mysqldump.exe',
            'C:\wamp64\bin\mariadb\mariadb11.4.9\bin\mysqldump.exe',
            'mysqldump'
        ];
        foreach ($candidates as $c) {
            $out = shell_exec("where \"$c\" 2>NUL");
            $test = $out ? trim($out) : '';
            if ($test || is_file($c)) { $dumpPath = $c; break; }
        }

        if ($dumpPath) {
            $filename = 'backup_' . DB_NAME . '_' . date('Ymd_His') . '.sql';
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="' . $filename . '"');
            header('Cache-Control: no-cache');
            header('Pragma: no-cache');

            $cmd = sprintf(
                '"%s" --host=%s --port=%s --user=%s --password=%s --routines --triggers --add-drop-database --databases %s 2>NUL',
                $dumpPath,
                DB_HOST,
                DB_PORT,
                DB_USER,
                DB_PASS,
                DB_NAME
            );
            passthru($cmd);
            exit;
        }

        // Fallback: pure PHP export
        $filename = 'backup_' . DB_NAME . '_' . date('Ymd_His') . '.sql';
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: no-cache');

        $sql = "-- Backup generated: " . date('Y-m-d H:i:s') . "\n";
        $sql .= "-- Database: " . DB_NAME . "\n";
        $sql .= "-- Host: " . DB_HOST . "\n\n";
        $sql .= "CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n";
        $sql .= "USE `" . DB_NAME . "`;\n\n";

        $tables = $pdo->query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = '" . DB_NAME . "' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME")->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            // Drop table
            $sql .= "--\n-- Table structure for `$table`\n--\n";
            $sql .= "DROP TABLE IF EXISTS `$table`;\n";

            // Create table
            $create = $pdo->query("SHOW CREATE TABLE `$table`")->fetch();
            $sql .= $create['Create Table'] . ";\n\n";

            // Data
            $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
            if (count($rows) > 0) {
                $columns = array_keys($rows[0]);
                $colList = '`' . implode('`,`', $columns) . '`';
                $sql .= "--\n-- Dumping data for `$table`\n--\n";
                foreach ($rows as $row) {
                    $vals = [];
                    foreach ($row as $v) {
                        if ($v === null) $vals[] = 'NULL';
                        else $vals[] = "'" . str_replace("'", "''", $v) . "'";
                    }
                    $sql .= "INSERT INTO `$table` ($colList) VALUES (" . implode(',', $vals) . ");\n";
                }
                $sql .= "\n";
            }
        }
        $sql .= "-- Backup completed: " . date('Y-m-d H:i:s') . "\n";
        echo $sql;
        exit;
    }

    if ($action === 'info') {
        // Get database size info
        $s = $pdo->query("SELECT
            COUNT(*) as total_tables,
            ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
            FROM information_schema.tables WHERE table_schema = '" . DB_NAME . "'");
        $info = $s->fetch();
        $s2 = $pdo->query("SELECT COUNT(*) as total_rows FROM information_schema.tables WHERE table_schema = '" . DB_NAME . "'");
        $tables = $s2->fetchColumn();
        $info['total_tables'] = intval($tables);

        // Count rows per table (skip views)
        $rowsPerTable = [];
        $tabs = $pdo->query("SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.tables WHERE table_schema = '" . DB_NAME . "' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME")->fetchAll();
        foreach ($tabs as $t) {
            try {
                $cnt = $pdo->query("SELECT COUNT(*) FROM `{$t['TABLE_NAME']}`")->fetchColumn();
                if ($cnt > 0) $rowsPerTable[] = ['table'=>$t['TABLE_NAME'], 'rows'=>intval($cnt)];
            } catch (Exception $e) {
                // skip problematic tables/views
            }
        }

        $info['tables_with_data'] = $rowsPerTable;
        // Check if mysqldump is available
        $dumpPath = null;
        foreach (['C:\wamp64\bin\mysql\mysql8.4.7\bin\mysqldump.exe','C:\wamp64\bin\mariadb\mariadb11.4.9\bin\mysqldump.exe','mysqldump'] as $c) {
            $out = shell_exec("where \"$c\" 2>NUL");
            $test = $out ? trim($out) : '';
            if ($test || is_file($c)) { $dumpPath = $c; break; }
        }
        $info['mysqldump_available'] = $dumpPath !== null;

        header('Content-Type: application/json; charset=utf-8');
        echo json_encode(['success'=>true,'data'=>$info], JSON_UNESCAPED_UNICODE);
        exit;
    }

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['success'=>false,'error'=>'Accion no valida']);
} catch (Exception $e) {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['success'=>false,'error'=>$e->getMessage()]);
}
