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

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] === UPLOAD_ERR_OK) {
        $fileTmpPath = $_FILES['excel_file']['tmp_name'];
        $spreadsheet = IOFactory::load($fileTmpPath);
        $sheetData = $spreadsheet->getActiveSheet()->toArray();

        $conn = new mysqli('localhost', 'tbnexmember_nizar', 'E1dJab_^P*%-', 'tbnexmember_clublink');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $conn->query("TRUNCATE TABLE members");

        foreach ($sheetData as $index => $row) {
            if ($index == 0) continue;

            $row = array_pad($row, 15, '');

            if (empty($row[1])) {
                break;
            }

            // Set parent_id and member_id to 0 if empty or invalid (since NULL is not allowed)
            $parent_id = is_numeric($row[0]) && $row[0] !== '' ? (int)$row[0] : 0;
            $member_id = is_numeric($row[1]) && $row[1] !== '' ? (int)$row[1] : 0;

            $first_name = !empty($row[2]) ? "'" . $conn->real_escape_string($row[2]) . "'" : "NULL";
            $last_name = !empty($row[3]) ? "'" . $conn->real_escape_string($row[3]) . "'" : "NULL";
            $email = !empty($row[4]) ? "'" . $conn->real_escape_string($row[4]) . "'" : "NULL";
            $gender = !empty($row[5]) ? "'" . $conn->real_escape_string($row[5]) . "'" : "NULL";
            $phone = !empty($row[6]) ? "'" . $conn->real_escape_string($row[6]) . "'" : "NULL";
            $dob = !empty($row[7]) && strtotime($row[7]) ? "'" . date('Y-m-d', strtotime($row[7])) . "'" : "NULL";
            $address = !empty($row[8]) ? "'" . $conn->real_escape_string($row[8]) . "'" : "NULL";
            $image = !empty($row[9]) ? "'" . $conn->real_escape_string($row[9]) . "'" : "NULL";
            $membership_part = !empty($row[10]) ? "'" . $conn->real_escape_string($row[10]) . "'" : "NULL";
            $emergency_contact_information = !empty($row[11]) ? "'" . $conn->real_escape_string($row[11]) . "'" : "NULL";
            $notes = !empty($row[12]) ? "'" . $conn->real_escape_string($row[12]) . "'" : "NULL";

            $created_at = !empty($row[13]) && strtotime($row[13]) ? "'" . date('Y-m-d H:i:s', strtotime($row[13])) . "'" : "NULL";
            $updated_at = !empty($row[14]) && strtotime($row[14]) ? "'" . date('Y-m-d H:i:s', strtotime($row[14])) . "'" : "NULL";

            $sql = "INSERT INTO members (
                parent_id, member_id, first_name, last_name, email, gender, phone, dob, address, image,
                membership_part, emergency_contact_information, notes, created_at, updated_at
            ) VALUES (
                $parent_id, $member_id, $first_name, $last_name, $email, $gender, $phone, $dob, $address, $image,
                $membership_part, $emergency_contact_information, $notes, $created_at, $updated_at
            )";

            if (!$conn->query($sql)) {
                echo "Error on row $index: " . $conn->error . "<br>";
            }
        }
         // Reset auto-increment to the next available value
        $conn->query("ALTER TABLE members AUTO_INCREMENT = 1");

        echo "Data uploaded successfully.";
        $conn->close();
    } else {
        echo "Error uploading file.";
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Upload Excel to MySQL</title>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <input type="file" name="excel_file" accept=".xlsx,.xls">
        <button type="submit">Upload to MySQL</button>
    </form>
</body>
</html>
