<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Database configuration
$host = 'localhost'; // Database host
$dbname = 'codesite_codesitex'; // Database name
$username = 'codesite_codesitex'; // Database username
$password = '~O25D_)lgLc4VL7H'; // Database password

// Create a new spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Create a connection to the database
$conn = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query to get data from the database
$sql = "SELECT leads FROM leads"; // Replace 'your_table' with your table name
$result = $conn->query($sql);

// Initialize the row index for the spreadsheet
$rowIndex = 1;

// Set column headers
$sheet->setCellValue('A' . $rowIndex, 'Name');
$sheet->setCellValue('B' . $rowIndex, 'Email');
$sheet->setCellValue('C' . $rowIndex, 'Phone');
$sheet->setCellValue('D' . $rowIndex, 'Service');
$sheet->setCellValue('E' . $rowIndex, 'Message');

$rowIndex++; // Move to the next row

if ($result->num_rows > 0) {
    // Fetch rows and decode JSON data
    while ($row = $result->fetch_assoc()) {
        $jsonData = json_decode($row['leads'], true); // Decode JSON data
        
        if (json_last_error() === JSON_ERROR_NONE && is_array($jsonData)) {
            // Use htmlspecialchars to escape special characters
            $sheet->setCellValue('A' . $rowIndex, htmlspecialchars($jsonData['name'], ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('B' . $rowIndex, htmlspecialchars($jsonData['email'], ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('C' . $rowIndex, htmlspecialchars($jsonData['phone'], ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('D' . $rowIndex, htmlspecialchars($jsonData['service'], ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('E' . $rowIndex, htmlspecialchars($jsonData['message'], ENT_QUOTES, 'UTF-8'));
            $rowIndex++;
        } else {
            // Log error for debugging
            error_log('JSON Decode Error: ' . json_last_error_msg());
        }
    }
} else {
    echo "0 results";
}

// Close the database connection
$conn->close();

// Write the file to disk
$writer = new Xlsx($spreadsheet);
$fileName = 'exported_data.xlsx';

try {
    $writer->save($fileName);
} catch (Exception $e) {
    error_log('Error saving Excel file: ' . $e->getMessage());
    die('Error saving Excel file.');
}

// Send the file to the browser for download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
readfile($fileName);

// Optionally delete the file from server after download
unlink($fileName);

?>



