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

$month = date('n');
$year = date('Y');
if ($month < 4) {
    $financial_year = ($year - 1) . '-' . substr($year, 2); // Example: 2024-25
} else {
    $financial_year = $year . '-' . substr($year + 1, 2); // Example: 2025-26
}

include __DIR__ . '/../inc/db.php'; // Adjust path if necessary

header('Content-Type: application/json');

$response = ['status' => 'error', 'message' => 'Something went wrong'];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    
    $action = $_POST['action'] ?? 'Create';
    $id = $_POST['id'] ?? null;

    $name = trim($_POST['name'] ?? '');
    $phone = trim($_POST['phone'] ?? '');
    $email = trim($_POST['email'] ?? '');
    $address = trim($_POST['address'] ?? '');
    $source = trim($_POST['source'] ?? '');
    $inquiry_type = $_POST['inquiry_type'] ?? null;
    $destination = trim($_POST['destination'] ?? '');
    $travel_date = $_POST['travel_date'] ?? null;
    $nights = $_POST['nights']  ?? 1;
    $adults = $_POST['adults']  ?? 1;
    $child = $_POST['child']  ?? 0;
    $extrabed = $_POST['extrabed']  ?? 0;
    $rooms = $_POST['rooms']  ?? 0;
    $hotel_type = $_POST['hotel_type'] ?? null;
    $transport_type = $_POST['transport_type'] ?? null;
    $with_flight = $_POST['with_flight']  ?? 'no';
    $assigned_to = $_POST['assigned_to'] ?? null;
    $remarks = $_POST['remarks'] ?? null;

    // Create or update customer and inquiry
    if ($action === 'Create') {
        
        if (empty($name) || empty($phone) || empty($assigned_to)) {
            $response['message'] = 'Customer Name, Mobile, and Assigned To are required.';
            echo json_encode($response);
            exit;
        }

        // Check if customer exists
        $stmt = $conn->prepare("SELECT id FROM customers WHERE phone = ?");
        $stmt->bind_param("s", $phone);
        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows > 0) {
            $stmt->bind_result($customer_id);
            $stmt->fetch();
        } else {
            $stmt = $conn->prepare("INSERT INTO customers (name, phone, email, address) VALUES (?, ?, ?, ?)");
            $stmt->bind_param("ssss", $name, $phone, $email, $address);
            $stmt->execute();
            $customer_id = $stmt->insert_id;
        }

        // Insert into inquiries
			// Ensure the data types match
			$sql = "INSERT INTO inquiries 
			(customer_id, inquiry_type, source, destination, travel_date, nights, adults, child, extrabed, rooms, hotel_type, transport_type, with_flight, assigned_to, remarks, inquiry_date, financialyear) 
			VALUES ('$customer_id', '$inquiry_type', '$source', '$destination', '$travel_date', '$nights', '$adults', '$child', '$extrabed', '$rooms', '$hotel_type', '$transport_type', '$with_flight', '$assigned_to', '$remarks', CURDATE(), '$financial_year')";


			if ($conn->query($sql) === TRUE) {
			   $response = ['status' => 'success', 'message' => 'Inquiry created successfully'];
			} else {
			   $response = ['status' => 'Error', 'message' => $conn->error]; 
			}

		
     }

    if ($action === 'Update' && $id) {
        
        if (empty($name) || empty($phone) || empty($assigned_to)) {
            $response['message'] = 'Customer Name, Mobile, and Assigned To are required.';
            echo json_encode($response);
            exit;
        }

        // Update customer
        $stmt = $conn->prepare("UPDATE customers SET name = ?, phone = ?, email = ?, address = ? WHERE id = (SELECT customer_id FROM inquiries WHERE id = ?)");
        $stmt->bind_param("ssssi", $name, $phone, $email, $address, $id);
        $stmt->execute();

        // Update inquiry
        $stmt = $conn->prepare("UPDATE inquiries SET inquiry_type = ?, source = ?, destination = ?, travel_date = ?, nights = ?, adults = ?, child = ?, extrabed = ?, rooms = ?, hotel_type = ?, transport_type = ?, with_flight = ?, assigned_to = ?, remarks = ? WHERE id = ?");
		$stmt->bind_param("ssssiiiiisssssi", $inquiry_type, $source, $destination, $travel_date, $nights, $adults, $child, $extrabed, $rooms, $hotel_type, $transport_type, $with_flight, $assigned_to, $remarks, $id);
		$stmt->execute();


        $response = ['status' => 'success', 'message' => 'Inquiry updated successfully'];
    }

    if ($action === 'Select' && $id) {
        $query = "SELECT c.name, c.phone, c.email, c.address, i.source, i.destination, i.travel_date, i.nights, i.adults, i.child, i.extrabed, i.rooms, i.hotel_type, i.transport_type, i.with_flight, i.assigned_to,i.inquiry_type, i.remarks
                  FROM inquiries i
                  JOIN customers c ON i.customer_id = c.id
                  WHERE i.id = ?";
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $inquiry = $result->fetch_assoc();

        echo json_encode($inquiry);
        exit;
    }

    if ($action === 'Delete' && $id) {
    // Start by deleting related records in the inquiry_followups table
    $stmt1 = $conn->prepare("DELETE FROM inquiry_followups WHERE inquiry_id = ?");
    if ($stmt1 === false) {
        // Handle SQL errors
        $response = ['status' => 'error', 'message' => 'Error preparing delete from inquiry_followups'];
        echo json_encode($response);
        exit;
    }
    $stmt1->bind_param("i", $id);

    if (!$stmt1->execute()) {
        $response = ['status' => 'error', 'message' => 'Error deleting from inquiry_followups'];
        echo json_encode($response);
        exit;
    }

    // Now delete the inquiry record from the inquiries table
    $stmt2 = $conn->prepare("DELETE FROM inquiries WHERE id = ?");
    if ($stmt2 === false) {
        // Handle SQL errors
        $response = ['status' => 'error', 'message' => 'Error preparing delete from inquiries'];
        echo json_encode($response);
        exit;
    }
    $stmt2->bind_param("i", $id);

    if ($stmt2->execute()) {
        // Successfully deleted the inquiry record
        $response = ['status' => 'Delete', 'message' => 'Inquiry deleted successfully'];
    } else {
        $response = ['status' => 'error', 'message' => 'Failed to delete inquiry'];
    }
    
    echo json_encode($response);
    exit;
}

}

echo json_encode($response);
?>
