<?php
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
require("includes/function.php");

// in_set('display_errors',1);
// error_reporting(E_ERROR);

$zdir = $dbname;
$comp = $_SESSION["comp_" . $dbname];
$yearname = $_SESSION["zdbyear_" . $yearname];

$zcomp = 1;
$zdbroot = $zdir;
$focusfield = "submit";
$zroot = $_SESSION["zdbroot"];
$zroot1 = substr($zroot, 0, -4);
date_default_timezone_set("Asia/Kolkata");
$zdb = $_SESSION["zdbyear_" . $dbname];

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
ini_set('memory_limit', '512M');
ini_set('max_execution_time', 300); // 5 minutes
set_time_limit(300);

mysqli_select_db($conn, $zdb);

$spltype = $_SESSION["spltype_" . $dbname];

if ($spltype == '1') {
    $database_name = "PAKKA";
} else {
    $database_name = "KACHHA";
}

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$tables = [];
$result = mysqli_query($conn, "SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

$sqlContent = "-- SQL Export for Database SISGST\n";
$sqlContent .= "-- Generated on " . date('Y-m-d H:i:s') . "\n\n";

foreach ($tables as $table) {
    $result = mysqli_query($conn, "SHOW CREATE TABLE `$table`");
    $row = mysqli_fetch_assoc($result);
    $sqlContent .= "--\n-- Table structure for table `$table`\n--\n\n";
    $sqlContent .= $row['Create Table'] . ";\n\n";

    // Table data
    $data = mysqli_query($conn, "SELECT * FROM `$table`");
    if (mysqli_num_rows($data) > 0) {
        
        if ($table == 'transacn') {
            $limit = 1000;
            $offset = 0;
            
            $sqlContent .= "--\n-- Dumping data for table `$table`\n--\n\n";

            do {
                $data = mysqli_query($conn, "SELECT * FROM `$table` LIMIT $offset, $limit");
                if (!$data) {
                    die("SELECT chunk failed for `$table`: " . mysqli_error($conn));
                    }
                    
                $rowCount = mysqli_num_rows($data);
                
                while ($row = mysqli_fetch_assoc($data)) {
                    $values = array_map(function ($value) use ($conn) {
                        return is_null($value) ? 'NULL' : "'" . mysqli_real_escape_string($conn, $value) . "'";
                    }, array_values($row));

                    $sqlContent .= "INSERT INTO `$table` VALUES (" . implode(", ", $values) . ");\n";
                }

                $offset += $limit;
            } while ($rowCount > 0);

            $sqlContent .= "\n";
        } else {
            $sqlContent .= "--\n-- Dumping data for table `$table`\n--\n\n";
            while ($row = mysqli_fetch_assoc($data)) {
                $values = array_map(function ($value) use ($conn) {
                    return isset($value) ? "'" . mysqli_real_escape_string($conn, $value) . "'" : 'NULL';
                }, $row);
                $sqlContent .= "INSERT INTO `$table` VALUES (" . implode(", ", $values) . ");\n";
            }
            $sqlContent .= "\n";
            }
            }
}

// Output headers to force download
header('Content-Type: application/sql');
header('Content-Disposition: attachment; filename="' . $database_name . '_' . $yearname . '_backup_' . date('Y-m-d_H-i-s') . '.sql"');
header('Content-Length: ' . strlen($sqlContent));

// Output the content
echo $sqlContent;
