<?php ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

ini_set('upload_max_filesize', '50M');
ini_set('post_max_size', '50M');

include 'includes/db.php';
include 'includes/session.php';

function custom_log($message) {
    $logfile = $_SERVER['DOCUMENT_ROOT'] . '/lotus/custom_error.log'; // Log
    $timestamp = date('Y-m-d H:i:s');
    $logMessage = "[{$timestamp}] - {$message}\n";
    file_put_contents($logfile, $logMessage, FILE_APPEND);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['data_file'])) {
    header('Content-Type: application/json');

    $file = $_FILES['data_file'];
    if ($file['error'] !== UPLOAD_ERR_OK) {
        custom_log("File upload error: " . $file['error']);
        echo json_encode(['status' => 'error', 'message' => 'File upload error.']);
        exit;
    }
    
    // Log file details to confirm upload
custom_log("File uploaded: " . $_FILES['data_file']['name']);
custom_log("File size: " . $_FILES['data_file']['size']);
custom_log("File type: " . $_FILES['data_file']['type']);

    $filePath = $file['tmp_name'];
    $fileExtension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

    if (!in_array($fileExtension, ['csv', 'xlsx', 'xls'])) {
        custom_log("Invalid file format.");
        echo json_encode(['status' => 'error', 'message' => 'Invalid file format.']);
        exit;
    }

    $data = [];
    try {
        if ($fileExtension === 'csv') {
            if (($handle = fopen($filePath, 'r')) !== false) {
                $header = fgetcsv($handle);
                while (($row = fgetcsv($handle)) !== false) {
                    $data[] = array_combine($header, $row);
                }
                fclose($handle);
            } else {
                throw new Exception("Unable to process the CSV file.");
            }
        } else {
            require 'vendor/autoload.php';
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();
            $header = $rows[0];
            foreach (array_slice($rows, 1) as $row) {
                $data[] = array_combine($header, $row);
            }
        }
    } catch (Exception $e) {
        custom_log("File parsing failed: " . $e->getMessage());
        echo json_encode(['status' => 'error', 'message' => 'Failed to parse the file.']);
        exit;
    }

    $dbFields = [
        'store_code', 'bill_date', 'bill_no', 'invoice_type', 'po_ref_no', 'ac_name','addr1', 'addr2', 'city', 'bill_value', 'finance_company' 
    ];

    $inserted = 0;
    $failed = 0;

    foreach ($data as $row) {
        $mappedRow = [];
         foreach ($dbFields as $field) {
        if (array_key_exists($field, $row)) {
            $value = $row[$field];

            // Convert date format for 'bill_date'
            if ($field === 'bill_date' && !empty($value)) {
                // Check if the date is in the expected d-m-y format
                  $dateTime = DateTime::createFromFormat('d/m/Y', $value);
                if ($dateTime) {
                    $value = $dateTime->format('Y-m-d'); // Convert to Y-m-d format
                } else {
                    custom_log("Invalid date format in row for bill_date: $value");
                    $value = null; // Handle invalid date by setting it to NULL
                }
            }

            $mappedRow[$field] = $value;
        } else {
            $mappedRow[$field] = null; // Handle missing fields by setting them to null
        }
    }

        $placeholders = ":" . implode(", :", $dbFields);
        $sql = "INSERT INTO sales_records (" . implode(", ", $dbFields) . ") VALUES ($placeholders)";
        
        $stmt = $conn->prepare($sql);

        foreach ($dbFields as $field) {
            $stmt->bindValue(':' . $field, $mappedRow[$field]);
        }

        try {
            $stmt->execute();
            $inserted++;
        } catch (Exception $e) {
            custom_log("Database insert failed: " . $e->getMessage());
            $failed++;
        }
    }

    custom_log("Upload completed. Inserted: $inserted, Failed: $failed");
    echo json_encode([
        'status' => 'success',
        'message' => "Upload completed with $inserted inserted and $failed failed records.",
        'inserted' => $inserted,
        'failed' => $failed
    ]);
} else {
    custom_log("No file uploaded.");
    echo json_encode(['status' => 'error', 'message' => 'No file uploaded.']);
}
