<?php
require_once('../../../' . $_REQUEST['tenant'] . '/config.php');

$client_id = $_POST['client_id'];
$surveyor_id = $_POST['surveyor_id'];
$appointment_date = $_POST['appointment_date']; // Already a Unix timestamp from the JavaScript

$con->begin_transaction();

try {
    // Step 1: Check if the surveyor already has an assignment in admin_assignments
    $sql_check_admin = "SELECT 1 FROM admin_assignments WHERE client_id = ? AND surveyor_id = ? AND appointment_date = ?";
    $stmt_check_admin = $con->prepare($sql_check_admin);
    if (!$stmt_check_admin) {
        throw new Exception('Error preparing statement for admin_assignments check: ' . $con->error);
    }
    $stmt_check_admin->bind_param('iii', $client_id, $surveyor_id, $appointment_date);
    $stmt_check_admin->execute();
    $stmt_check_admin->store_result();

    if ($stmt_check_admin->num_rows > 0) {
        // Admin assignment already exists, return message
        echo 'error: Assignment already exists for this surveyor at this client on this date.';
        $con->rollback(); // Rollback transaction
        return;
    } else {
        // Step 2: Insert assignment into admin_assignments table
        $sql_admin = "INSERT INTO admin_assignments (client_id, surveyor_id, appointment_date, date_assigned) 
                      VALUES (?, ?, ?, UNIX_TIMESTAMP())";
        $stmt_admin = $con->prepare($sql_admin);
        if (!$stmt_admin) {
            throw new Exception('Error preparing statement for admin_assignments: ' . $con->error);
        }
        $stmt_admin->bind_param('iii', $client_id, $surveyor_id, $appointment_date);

        if (!$stmt_admin->execute()) {
            throw new Exception('Error inserting into admin_assignments: ' . $stmt_admin->error);
        }
    }

    // Step 3: Check if surveyor is already assigned in surveyor_assignments table
    $sql_check_surveyor = "SELECT status FROM surveyor_assignments WHERE surveyor = ? AND client_id = ?";
    $stmt_check_surveyor = $con->prepare($sql_check_surveyor);
    if (!$stmt_check_surveyor) {
        throw new Exception('Error preparing statement for surveyor_assignments check: ' . $con->error);
    }
    $stmt_check_surveyor->bind_param('ii', $surveyor_id, $client_id);
    $stmt_check_surveyor->execute();
    $stmt_check_surveyor->store_result();

    if ($stmt_check_surveyor->num_rows > 0) {
        // Surveyor is already assigned
        $stmt_check_surveyor->bind_result($status);
        $stmt_check_surveyor->fetch();

        if ($status == 1) {
            echo 'success: Assignment successful, surveyor already has access permissions.';
        } else {
            // Update the status to 1
            $sql_update_status = "UPDATE surveyor_assignments SET status = 1 WHERE surveyor = ? AND client_id = ?";
            $stmt_update_status = $con->prepare($sql_update_status);
            if (!$stmt_update_status) {
                throw new Exception('Error preparing statement for status update: ' . $con->error);
            }
            $stmt_update_status->bind_param('ii', $surveyor_id, $client_id);
            if (!$stmt_update_status->execute()) {
                throw new Exception('Error updating status in surveyor_assignments: ' . $stmt_update_status->error);
            }

            echo 'success: Assignment successful, permissions have been reinstated.';
        }
    } else {
        // Assign the surveyor to all active sites of the client
        $sql_locations = "SELECT locnumber FROM locations WHERE clientnumber = ? AND statuslive = 1";
        $stmt_locations = $con->prepare($sql_locations);
        if (!$stmt_locations) {
            throw new Exception('Error preparing statement for locations: ' . $con->error);
        }
        $stmt_locations->bind_param('i', $client_id);
        $stmt_locations->execute();
        $result_locations = $stmt_locations->get_result();

        $sql_surveyor_assign = "INSERT INTO surveyor_assignments (surveyor, client_id, site_id, asset_id, date_assigned, date_expired, status)
                                VALUES (?, ?, ?, 0, UNIX_TIMESTAMP(), 0, 1)";
        $stmt_surveyor_assign = $con->prepare($sql_surveyor_assign);
        if (!$stmt_surveyor_assign) {
            throw new Exception('Error preparing statement for surveyor_assignments: ' . $con->error);
        }

        while ($row = $result_locations->fetch_assoc()) {
            $site_id = $row['locnumber'];
            $stmt_surveyor_assign->bind_param('iii', $surveyor_id, $client_id, $site_id);
            if (!$stmt_surveyor_assign->execute()) {
                throw new Exception('Error inserting into surveyor_assignments: ' . $stmt_surveyor_assign->error);
            }
        }

        echo 'success: Assignment successful, permissions given for the surveyor to access the full schedule.';
    }

    // Commit the transaction if everything is successful
    $con->commit();
} catch (Exception $e) {
    // Rollback the transaction if any error occurs
    $con->rollback();
    echo 'error: ' . $e->getMessage();
}

// Close the statements and connection
$stmt_admin->close();
$stmt_check_admin->close();
$stmt_check_surveyor->close();
if (isset($stmt_update_status)) $stmt_update_status->close();
if (isset($stmt_surveyor_assign)) $stmt_surveyor_assign->close();
$con->close();
?>