<?php
// Connect to database
$mysqli = new mysqli("localhost", "root", "", "busvall_dev-data");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Find tickets that share trip_id, journeydata, and seats, and are not refunded or cancelled
$sql = "SELECT t1.id as t1_id, t1.booking_id as t1_booking, t1.trip_id, t1.journeydata, t1.seatnumber as t1_seats, t1.cancel_status as t1_cancel, t1.refund as t1_refund,
               t2.id as t2_id, t2.booking_id as t2_booking, t2.seatnumber as t2_seats, t2.cancel_status as t2_cancel, t2.refund as t2_refund
        FROM tickets t1
        JOIN tickets t2 ON t1.trip_id = t2.trip_id AND t1.journeydata = t2.journeydata AND t1.id < t2.id
        WHERE (t1.cancel_status != '1' AND (t1.refund != '1' OR t1.refund IS NULL))
          AND (t2.cancel_status != '1' AND (t2.refund != '1' OR t2.refund IS NULL))
        ";
$result = $mysqli->query($sql);

$overlaps = [];
while ($row = $result->fetch_assoc()) {
    $t1_seats = explode(',', $row['t1_seats']);
    $t2_seats = explode(',', $row['t2_seats']);
    $intersect = array_intersect($t1_seats, $t2_seats);
    if (count($intersect) > 0) {
        // Now need to check if subtrips actually overlap
        $overlaps[] = [
            't1' => $row['t1_id'], 't1_booking' => $row['t1_booking'], 't1_seats' => $row['t1_seats'],
            't2' => $row['t2_id'], 't2_booking' => $row['t2_booking'], 't2_seats' => $row['t2_seats'],
            'trip_id' => $row['trip_id'], 'journeydata' => $row['journeydata'],
            'overlap_seats' => implode(',', $intersect)
        ];
    }
}

if (count($overlaps) > 0) {
    print_r($overlaps);
} else {
    echo "No seat overlaps found in the same journey dates.\n";
}
