<?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;

    // Incoming Data
    $customer_id = $_POST['customer_id'] ?? null;
	$inquiry_id = $_POST['inquiry_id'] ?? null;
    $package_name = $_POST['package_name'] ?? null;
	$destination = $_POST['destination'] ?? null;
    $assigned_to = $_POST['useridnew'] ?? null;
    $destination = $_POST['destination'] ?? '';
    $travel_date = $_POST['travel_date'] ?? null;
	$rooms = $_POST['rooms'] ?? 1;
    $nights = $_POST['nights'] ?? 1;
    $adults = $_POST['adults'] ?? 1;
    $child = $_POST['child'] ?? 0;
    $extrabed = $_POST['extrabed'] ?? 0;
	$description = $_POST['description'] ?? '';
	$inclusions = $_POST['inclusions'] ?? '';
	$exclusions = $_POST['exclusions'] ?? '';

    // Pricing
    $adult_price = $_POST['adult_price'] ?? 0;
    $child_price = $_POST['child_price'] ?? 0;
    $extra_bed_price = $_POST['extra_bed_price'] ?? 0;

    // Other Charges
    $transport_type = $_POST['transport_type'] ?? null;
    $transport_charges = $_POST['transport_charges'] ?? 0;
    $extra_charges = $_POST['extra_charges'] ?? 0;
    $gst_included = $_POST['gst_included'] ?? 'no';
    $gst = $_POST['gst'] ?? 5;
    $discount = $_POST['discount'] ?? 0;


$base_total = ($adult_price * $adults)+ ($child_price * $child) + ($extra_bed_price * $extrabed) + $extra_charges;

$discount_amount = 0.00;
if ($discount > 0) {
    $discount_amount = ($discount / 100) * $base_total;
}

$subtotal = $base_total - $discount_amount;
$gst_amount = 0.00;
if ($gst_included === 'yes' && $gst > 0) {
    $gst_amount = ($gst / 100) * $subtotal;
}

$grand_total = ceil($subtotal + $gst_amount + $transport_charges);

    if ($action === 'Create') {

        // Generate Quotation Number based on gst_included
        $stmt = $conn->prepare("SELECT MAX(CAST(SUBSTRING(quotation_no, 5) AS UNSIGNED)) FROM quotations WHERE gst_included = ? AND financial_year = ?");
		$stmt->bind_param("ss", $gst_included, $financial_year);
		$stmt->execute();
		$stmt->bind_result($last_quotation_no);
		$stmt->fetch();
		$stmt->close();

		$next_number = ($last_quotation_no !== null) ? $last_quotation_no + 1 : 1;
		$next_quotation_no = 'QTN-' . str_pad($next_number, 4, '0', STR_PAD_LEFT);

        // Validate essential fields
        if (empty($customer_id) || empty($package_name) || empty($assigned_to)) {
            $response = ['status' => 'error', 'message' => 'Customer, Package, and Assigned To are required.'];
            echo json_encode($response);
            exit;
        }
		$check_stmt = $conn->prepare("SELECT id FROM quotations WHERE inquiry_id = ?");
		$check_stmt->bind_param("i", $inquiry_id);
		$check_stmt->execute();
		$check_stmt->store_result();

		if ($check_stmt->num_rows > 0) {
			$response = ['status' => 'error', 'message' => 'A quotation already exists.'];
			echo json_encode($response);
			exit;
		}

        // Insert Quotation
        $status = 'pending'; 
        // Prepare the SQL statement
        $stmt = $conn->prepare("INSERT INTO quotations (customer_id,inquiry_id, package_name,exclusions,inclusions,assigned_to, quotation_no, quotation_date,
        destination, travel_date,rooms, nights, adults, child, extrabed,transport_type, transport_charges, extra_charges, adult_price, child_price,extra_bed_price, discount, gst_rate, gst_included, total_amount,discount_amount,gst_amount,grand_total, remarks, financial_year, status) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("iisssisssiiiiisdddddddsddddsss", 
  $customer_id, $inquiry_id, $package_name, $exclusions,$inclusions, $assigned_to, $next_quotation_no,$destination, $travel_date, $rooms, $nights, $adults, $child, $extrabed,$transport_type, $transport_charges, $extra_charges, $adult_price,$child_price, $extra_bed_price, $discount, $gst, $gst_included,$base_total,$discount_amount,$gst_amount, $grand_total, $description, $financial_year, $status);

        if ($stmt->execute()) {
            $inserted_id = $conn->insert_id;
            if (isset($_POST['hotels']) && is_array($_POST['hotels'])) {
                foreach ($_POST['hotels'] as $hotel) {
                    $hotel_name = $hotel['hotel_name'] ?? '';
                    $hotel_location = $hotel['hotel_location'] ?? '';
                    $hotel_rooms = $hotel['hotel_rooms'] ?? 0;
                    $hotel_type = $hotel['hotel_type'] ?? '';
                    $meal_plan = $hotel['meal_plan'] ?? '';

                    $stmt_hotels = $conn->prepare("INSERT INTO quotation_hotels (quotation_id, hotel_name, hotel_location, hotel_rooms, hotel_type, meal_plan) VALUES (?, ?, ?, ?, ?, ?)");
                    $stmt_hotels->bind_param("ississ", $inserted_id, $hotel_name, $hotel_location, $hotel_rooms, $hotel_type, $meal_plan);
                    $stmt_hotels->execute();
                    $stmt_hotels->close();
                }
            }

            $response = ['status' => 'success', 'message' => 'Quotation created successfully.', 'id' => $inserted_id];
        } else {
            $response = ['status' => 'error', 'message' => $stmt->error];
        }

    }

    if ($action === 'Update' && $id) {
		
	$check_stmt = $conn->prepare("SELECT id FROM quotations WHERE inquiry_id = ? AND id != ?");
$check_stmt->bind_param("ii", $inquiry_id, $id);
$check_stmt->execute();
$check_stmt->store_result();

if ($check_stmt->num_rows > 0) {
    $response = ['status' => 'error', 'message' => 'Another quotation with this Inquiry already exists.'];
    echo json_encode($response);
    exit;
}
    // Update quotations table
		$stmt = $conn->prepare("UPDATE quotations SET customer_id = ?, inquiry_id = ?,package_name = ?, destination = ?, travel_date = ?, nights = ?,rooms = ?,adults = ?, child = ?, extrabed = ?, transport_type = ?, transport_charges = ?, adult_price = ?,  child_price = ?, extra_bed_price = ?, discount = ?, gst_rate = ?, gst_included = ?, 
    total_amount = ?,discount_amount = ?,gst_amount = ?, grand_total = ?,exclusions = ?,inclusions = ?, remarks = ?, updated_at = NOW() 
    WHERE id = ?");

		$stmt->bind_param("iisssiiiiisddddddsddddsssi",$customer_id, $inquiry_id, $package_name, $destination, $travel_date,$nights,$rooms,$adults, $child, $extrabed,
    $transport_type, $transport_charges, $adult_price,$child_price, $extra_bed_price, $discount, $gst, $gst_included,$base_total,$discount_amount,$gst_amount, $grand_total,$exclusions,$inclusions, $description, $id
						 );
			
		
	 if ($stmt->execute()) {
        // Step 1: Fetch existing hotel IDs
        $existingHotelIds = [];
        $result = $conn->query("SELECT id FROM quotation_hotels WHERE quotation_id = $id");
        while ($row = $result->fetch_assoc()) {
            $existingHotelIds[] = $row['id'];
        }

        // Step 2: Track IDs received in the POST
        $submittedHotelIds = [];

        if (isset($_POST['hotels']) && is_array($_POST['hotels'])) {
            foreach ($_POST['hotels'] as $hotel) {
                $hotel_id = $hotel['id'] ?? null;
                $hotel_name = $hotel['hotel_name'] ?? '';
                $hotel_location = $hotel['hotel_location'] ?? '';
                $hotel_rooms = $hotel['hotel_rooms'] ?? 0;
                $hotel_type = $hotel['hotel_type'] ?? '';
                $meal_plan = $hotel['meal_plan'] ?? '';

                if ($hotel_id) {
                    // Update existing
                    $submittedHotelIds[] = $hotel_id;
                    $stmt_hotel_update = $conn->prepare("UPDATE quotation_hotels SET hotel_name = ?, hotel_location = ?, hotel_rooms = ?, hotel_type = ?, meal_plan = ? WHERE id = ? AND quotation_id = ?");
                    $stmt_hotel_update->bind_param("ssissii", $hotel_name, $hotel_location, $hotel_rooms, $hotel_type, $meal_plan, $hotel_id, $id);
                    $stmt_hotel_update->execute();
                    $stmt_hotel_update->close();
                } else {
                    // Insert new
                    $stmt_hotels = $conn->prepare("INSERT INTO quotation_hotels (quotation_id, hotel_name, hotel_location, hotel_rooms, hotel_type, meal_plan) VALUES (?, ?, ?, ?, ?, ?)");
                    $stmt_hotels->bind_param("ississ", $id, $hotel_name, $hotel_location, $hotel_rooms, $hotel_type, $meal_plan);
                    $stmt_hotels->execute();
                    $stmt_hotels->close();
                }
            }
        }

        // Step 3: Delete hotels that were removed in frontend
        $idsToDelete = array_diff($existingHotelIds, $submittedHotelIds);
        if (!empty($idsToDelete)) {
            $idsStr = implode(',', array_map('intval', $idsToDelete));
            $conn->query("DELETE FROM quotation_hotels WHERE id IN ($idsStr)");
        }
		  $response = ['status' => 'success', 'message' => 'Quotation updated successfully.', 'id' => $id];
        
    } else {
        $response = ['status' => 'error', 'message' => $stmt->error];
    }
}


    if ($action === 'Select' && $id) {
    // Select quotation and join with customer
    $stmt = $conn->prepare("
        SELECT q.*, c.name AS customer_name
        FROM quotations q
        LEFT JOIN customers c ON q.customer_id = c.id
        WHERE q.id = ?
    ");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $quotation = $stmt->get_result()->fetch_assoc();

    if ($quotation) {
        // Fetch hotel blocks
        $hotels = [];
        $stmt_hotel = $conn->prepare("SELECT * FROM quotation_hotels WHERE quotation_id = ?");
        $stmt_hotel->bind_param("i", $id);
        $stmt_hotel->execute();
        $result_hotel = $stmt_hotel->get_result();
        while ($row = $result_hotel->fetch_assoc()) {
            $hotels[] = $row;
        }

        // Add hotels data to the quotation
        $quotation['hotels'] = $hotels;

        // Return the JSON-encoded quotation data
        echo json_encode($quotation);
        exit;
    }
}

    if ($action === 'update_status' && $id && isset($_POST['status'])) {
        // Sanitize the input status value
        $newStatus = mysqli_real_escape_string($conn, $_POST['status']);
        $last_followup_date = date('Y-m-d H:i:s');  // Get current timestamp

        // Update quotation status and set the last follow-up date
        $query = "UPDATE quotations SET status = ?, last_followup_date = ? WHERE id = ?";
        $stmt = $conn->prepare($query);
        if ($stmt === false) {
            echo json_encode(['status' => 'error', 'message' => 'Failed to prepare the query.']);
            exit;
        }
        
        // Bind parameters: status (string), last_followup_date (string), id (integer)
        $stmt->bind_param("ssi", $newStatus, $last_followup_date, $id);

        if ($stmt->execute()) {
            // Successfully updated status
            echo json_encode(['status' => 'success', 'message' => 'Status updated successfully.']);
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Failed to update status.']);
        }

        // Close the statement
        $stmt->close();
        exit;
    }

    if ($action === 'Delete' && $id) {
        // Delete quotation
        $stmt = $conn->prepare("DELETE FROM quotations WHERE id = ?");
        $stmt->bind_param("i", $id);
        
        if ($stmt->execute()) {
            $response = ['status' => 'Delete', 'message' => 'Quotation deleted successfully'];
        } else {
            $response = ['status' => 'error', 'message' => 'Failed to delete quotation'];
        }
    }
}

echo json_encode($response);
?>
