<?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;
	$quotation_id = $_POST['quotation_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;
	$exclusions = $_POST['exclusions'] ?? '';

    // Pricing
    $adult_price = $_POST['adult_price'] ?? 0;
    $child_price = $_POST['child_price'] ?? 0;
    $extra_bed_price = $_POST['extra_bed_price'] ?? 0;
	
	$payment_mode = $_POST['payment_mode'] ?? '';
	$advance_payment = $_POST['advance_payment'] ?? 0;
	$upi_details = $_POST['upi_details'] ?? '';
	$cheque_no = $_POST['cheque_no'] ?? '';
	$bank_name = $_POST['bank_name'] ?? '';
	$booking_date = $_POST['booking_date'] ?? date('Y-m-d');
	$remarks = $_POST['remarks'] ?? null;

    // 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') {

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

		if ($check_stmt->num_rows > 0) {
			$response = ['status' => 'error', 'message' => 'This already exists.'];
			echo json_encode($response);
			exit;
		}
		
		$prefix = 'MTT' . $year . $month;
		 $sql = "
			SELECT MAX(CAST(SUBSTRING_INDEX(booking_no, '-', -1) AS UNSIGNED)) AS last_no
			FROM package_bookings
			WHERE gst_included = '$gst_included'
			  AND financial_year = '$financial_year'
			  AND booking_no LIKE '$prefix-%'
		";

		$result = mysqli_query($conn, $sql);
		$row = mysqli_fetch_assoc($result);
		$last_booking_no = $row['last_no'] ?? 0;

		$next_number = $last_booking_no + 1;
		$next_booking_no = $prefix . '-' . str_pad($next_number, 4, '0', STR_PAD_LEFT);


        // Insert Quotation
        $status = 'confirmed'; 
        // Prepare the SQL statement
        $stmt = $conn->prepare("INSERT INTO package_bookings (customer_id,quotation_id, package_name,exclusions, assigned_to, booking_no, booking_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_amount, gst_included, total_amount,discount_amount,
        grand_total, remarks, financial_year, status) VALUES (?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("iississsiiiiisddddddddsdddsss",$customer_id,$quotation_id, $package_name,$exclusions, $assigned_to, $next_booking_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_amount, $gst_included,
        $base_total,$discount_amount, $grand_total, $remarks, $financial_year, $status);
		if ($stmt->execute()) {
           $inserted_id = $conn->insert_id;
		// Generate Receipt No (you can improve this with year/formatting)
//		$receipt_no_base = 'RCPT-' . date('Ymd');
		$rand_suffix = mt_rand(1000, 9999);
		$receipt_no_debit = $rand_suffix;
//		$receipt_no_credit = $receipt_no_base . '-' . ($rand_suffix + 1);
			
		 $stmt = $conn->prepare("SELECT MAX(CAST(receipt_no AS UNSIGNED)) FROM package_payments WHERE gst_included = ? AND financial_year = ?");
		$stmt->bind_param("ss", $gst_included, $financial_year);
		$stmt->execute();
		$stmt->bind_result($last_receipt_no);
		$stmt->fetch();
		$stmt->close();

	if ($last_receipt_no === NULL) {
		$receipt_no_credit = str_pad(1, 4, '0', STR_PAD_LEFT); // 0001
	} else {
		$receipt_no_credit = str_pad($last_receipt_no + 1, 4, '0', STR_PAD_LEFT);
	}

		// Insert DEBIT Entry (Total Booking Amount)
		$payment_type = 'debit';
		$payment_date = $booking_date;
		 $insert_debit_sql = "INSERT INTO package_payments (receipt_no, booking_id, customer_id, type, amount, payment_mode, payment_date, financial_year, remarks)
							 VALUES ('$receipt_no_debit', '$inserted_id', '$customer_id', '$payment_type', '$grand_total', '$payment_mode', '$payment_date', '$financial_year', 'Booking total')";
		$conn->query($insert_debit_sql);

		// Insert CREDIT Entry (Advance Payment), only if > 0
		if (!empty($advance_payment) && $advance_payment > 0) {
			$payment_type = 'credit';
			$advance_amount = floatval($advance_payment);
			$upi_details_safe = $conn->real_escape_string($upi_details);
			$cheque_no_safe = $conn->real_escape_string($cheque_no);
			$bank_name_safe = $conn->real_escape_string($bank_name);

			$insert_credit_sql = "INSERT INTO package_payments (receipt_no, booking_id, customer_id, type, amount, payment_mode, payment_date, financial_year, upi_details, cheque_no, bank_name, remarks)
								  VALUES ('$receipt_no_credit', '$inserted_id', '$customer_id', '$payment_type', '$advance_amount', '$payment_mode', '$payment_date', '$financial_year', 
								  '$upi_details_safe', '$cheque_no_safe', '$bank_name_safe', 'Advance Payment')";
			$conn->query($insert_credit_sql);
		}


        
            if (isset($_POST['hotel_name']) && is_array($_POST['hotel_name'])) {
    $hotel_names = $_POST['hotel_name'];
    $hotel_locations = $_POST['hotel_location'];
    $hotel_rooms = $_POST['hotel_rooms'];
    $hotel_types = $_POST['hotel_type'];
    $meal_plans = $_POST['meal_plan'];

    for ($i = 0; $i < count($hotel_names); $i++) {
        $name = trim($hotel_names[$i]);
        $location = trim($hotel_locations[$i]);
        $rooms = (int)$hotel_rooms[$i];
        $type = trim($hotel_types[$i]);
        $meal = trim($meal_plans[$i]);

        if ($name && $location && $rooms > 0) {
            $stmt = $conn->prepare("INSERT INTO package_booking_hotels (booking_id, hotel_name, hotel_location, hotel_rooms, hotel_type, meal_plan) VALUES (?, ?, ?, ?, ?, ?)");
            $stmt->bind_param("ississ", $inserted_id, $name, $location, $rooms, $type, $meal);
            $stmt->execute();
            $stmt->close();
        }
    }
}


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

    }

    if ($action === 'Update' && $id) {
    // Update package_bookings table
    $stmt = $conn->prepare("UPDATE package_bookings SET customer_id = ?, quotation_id = ?, package_name = ?, assigned_to = ?, destination = ?, travel_date = ?, nights = ?, adults = ?, child = ?, extrabed = ?, transport_type = ?, transport_charges = ?, extra_charges = ?, adult_price = ?, child_price = ?, extra_bed_price = ?, discount = ?, gst_rate = ?, gst_included = ?, total_amount = ?, grand_total = ?, remarks = ?, financial_year = ?, updated_at = NOW() WHERE id = ?");
$stmt->bind_param("iisisssiiiiisssdsssi", $customer_id, $quotation_id, $package_name, $assigned_to, $destination, $travel_date, $nights, $adults, $child, $extrabed, $transport_type, $transport_charges, $extra_charges, $adult_price, $child_price, $extra_bed_price, $discount, $gst, $gst_included, $base_total, $grand_total, $remarks, $financial_year, $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'];
    } 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 package_bookings 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;
        }

        $quotation['hotels'] = $hotels;

        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 package_bookings 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) {
    // Start transaction
    $conn->begin_transaction();

    // Delete from package_booking_hotels
    $stmt1 = $conn->prepare("DELETE FROM package_booking_hotels WHERE booking_id = ?");
    $stmt1->bind_param("i", $id);
    $success1 = $stmt1->execute();

    // Delete from package_payments
    $stmt2 = $conn->prepare("DELETE FROM package_payments WHERE booking_id = ?");
    $stmt2->bind_param("i", $id);
    $success2 = $stmt2->execute();

    // Delete from package_bookings
    $stmt3 = $conn->prepare("DELETE FROM package_bookings WHERE id = ?");
    $stmt3->bind_param("i", $id);
    $success3 = $stmt3->execute();

    // Check all
    if ($success1 && $success2 && $success3) {
        $conn->commit();
        $response = ['status' => 'Delete', 'message' => 'Quotation and related records deleted successfully'];
    } else {
        $conn->rollback();
        $response = ['status' => 'error', 'message' => 'Failed to delete quotation or related data'];
    }
}

}

echo json_encode($response);
?>
