<?php

//include('database/connection.php');
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$response = array();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
    $fileTmpPath = $_FILES['file']['tmp_name'];
    $fileName = $_FILES['file']['name'];
    $fileSize = $_FILES['file']['size'];
    $fileType = $_FILES['file']['type'];
    $fileNameCmps = explode(".", $fileName);
    $fileExtension = strtolower(end($fileNameCmps));

    $allowedExtensions = ['xls', 'xlsx'];

    if (in_array($fileExtension, $allowedExtensions)) {
        try {
            $spreadsheet = IOFactory::load($fileTmpPath);
            $worksheet = $spreadsheet->getActiveSheet();

            $conn = new mysqli('localhost', 'codesite_codesitex', '~O25D_)lgLc4VL7H', 'codesite_codesitex');
            if ($conn->connect_error) {
                $response['error'] = true;
                $response['msg'] = 'Database connection failed: ' . $conn->connect_error;
            } else {
                $conn->autocommit(FALSE); // Start transaction
                foreach ($worksheet->getRowIterator() as $row) {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false);

                    $rowData = [];
                    foreach ($cellIterator as $cell) {
                        $rowData[] = $cell->getValue();
                    }

                    if (count($rowData) >= 5) {
                        $data = [
                            'name' => $rowData[0],
                            'email' => $rowData[1],
                            'phone' => $rowData[2],
                            'service' => $rowData[3],
                            'message' => $rowData[4]
                        ];

                        $json_data = json_encode($data);

                        $sql = "INSERT INTO leads (leads) VALUES (?)";
                        $stmt = $conn->prepare($sql);
                        $stmt->bind_param("s", $json_data);

                        if ($stmt->execute()) {
                            $response['success'] = true;
                            $response['msg'] = 'Record inserted successfully';
                        } else {
                            $response['error'] = true;
                            $response['msg'] = 'Error: ' . $stmt->error;
                            $conn->rollback(); // Rollback transaction on error
                            break;
                        }
                        $stmt->close();
                    }
                }

                if (!isset($response['error'])) {
                    $conn->commit(); // Commit transaction
                    $response['success'] = true;
                    $response['msg'] = 'Leads imported successfully!';
                }
                $conn->close();
            }
        } catch (Exception $e) {
            $response['error'] = true;
            $response['msg'] = 'Error loading file: ' . $e->getMessage();
        }
    } else {
        $response['error'] = true;
        $response['msg'] = 'Invalid file type. Only Excel files are allowed.';
    }
} else {
    $response['error'] = true;
    $response['msg'] = 'No file uploaded or there was an upload error.';
}

header('Content-Type: application/json');
echo json_encode($response);
?>
