<?php

namespace App\Libraries;

use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;
use Modules\Ticket\Models\TicketModel;
use Modules\Trip\Models\TripModel;
use Modules\Trip\Models\SubtripModel;
use App\Libraries\HoldSeatSupport;

/**
 * Trip-level seat inventory: main route stoppage + bus departure dates (incl. midnight trips).
 */
class BookingInventorySupport
{
    public static function resolveTripStoppageForOverlap(int $tripId, int $subtripId = 0): string
    {
        if ($tripId > 0) {
            $mainTrip = (new TripModel())->find($tripId);
            if ($mainTrip && trim((string) ($mainTrip->stoppage ?? '')) !== '') {
                return trim((string) $mainTrip->stoppage);
            }
        }

        if ($subtripId > 0) {
            $sub = (new SubtripModel())->find($subtripId);

            return $sub ? trim((string) ($sub->stoppage ?? '')) : '';
        }

        return '';
    }

    /**
     * Bus start date (inventory key) and passenger boarding calendar date (midnight trips).
     *
     * @return array{bus_start: string, boarding: string, all: list<string>}
     */
    public static function resolveDepartureDates(
        int $tripId,
        int $subtripId,
        string $busStartDate,
        ?string $boardingDate = null,
        int $pickStandId = 0
    ): array {
        $boarding = $boardingDate !== null && $boardingDate !== ''
            ? date('Y-m-d', strtotime($boardingDate))
            : date('Y-m-d', strtotime($busStartDate));

        $busStart = date('Y-m-d', strtotime($busStartDate));

        // IMPORTANT: $busStartDate is the service run date key used for inventory.
        // Do not recompute it from boarding date; callers already provide the run date.

        return [
            'bus_start' => $busStart,
            'boarding' => $boarding,
            'all' => self::buildInventoryMatchDates($busStart, $boarding),
        ];
    }

    /**
     * Calendar dates for seat inventory on one physical bus departure.
     *
     * Same-day trips use only bus_start + boarding. Midnight / multi-day trips also
     * include the calendar span between them (plus lookback) so Cairo and Ras Sudr
     * bookings on the same run always see each other even when bus-start offsets differ.
     *
     * @return list<string>
     */
    public static function buildInventoryMatchDates(string $busStart, string $boarding): array
    {
        $busStart = date('Y-m-d', strtotime($busStart));
        $boarding = date('Y-m-d', strtotime($boarding));

        if ($busStart === $boarding) {
            return [$busStart];
        }

        // ±1 day around run/boarding covers legacy journeydata stored on the wrong calendar day
        // without matching unrelated departures several days away on the same route.
        $dates      = [$busStart, $boarding];
        $lookback   = 1;
        $rangeStart = date('Y-m-d', strtotime(min($busStart, $boarding) . " -{$lookback} day"));
        $rangeEnd   = date('Y-m-d', strtotime(max($busStart, $boarding) . " +{$lookback} day"));

        for ($timestamp = strtotime($rangeStart); $timestamp <= strtotime($rangeEnd); $timestamp += 86400) {
            $dates[] = date('Y-m-d', $timestamp);
        }

        return array_values(array_unique($dates));
    }

    /**
     * @param array{bus_start?: string, boarding?: string, all?: list<string>}|list<string> $departureDates
     * @return array{bus_start: string, boarding: string, all: list<string>}
     */
    public static function normalizeDepartureDates($departureDates): array
    {
        if (isset($departureDates['all']) || isset($departureDates['bus_start'])) {
            $busStart = isset($departureDates['bus_start'])
                ? date('Y-m-d', strtotime((string) $departureDates['bus_start']))
                : date('Y-m-d', strtotime((string) ($departureDates['all'][0] ?? 'today')));
            $boarding = isset($departureDates['boarding'])
                ? date('Y-m-d', strtotime((string) $departureDates['boarding']))
                : $busStart;
            $all = $departureDates['all'] ?? self::buildInventoryMatchDates($busStart, $boarding);

            return [
                'bus_start' => $busStart,
                'boarding' => $boarding,
                'all' => array_values(array_unique(array_filter(array_map(static function ($d) {
                    return date('Y-m-d', strtotime((string) $d));
                }, $all)))),
            ];
        }

        $list = array_values(array_unique(array_filter(array_map(static function ($d) {
            return date('Y-m-d', strtotime((string) $d));
        }, (array) $departureDates))));

        $busStart = $list[0] ?? date('Y-m-d');
        $boarding = $list[1] ?? $busStart;

        return [
            'bus_start' => $busStart,
            'boarding' => $boarding,
            'all' => $list !== [] ? $list : self::buildInventoryMatchDates($busStart, $boarding),
        ];
    }

    /**
     * First pickup pickdrops.id for a subtrip (pickdrops.trip_id = subtrips.id).
     */
    public static function resolveDefaultPickStandId(int $tripId, int $subtripId): int
    {
        if ($subtripId <= 0) {
            return 0;
        }

        $db = \Config\Database::connect();

        $row = $db->table('pickdrops')
            ->select('id')
            ->where('trip_id', $subtripId)
            ->where('type', 1)
            ->orderBy('id', 'ASC')
            ->get()
            ->getRow();

        if ($row) {
            return (int) $row->id;
        }

        $mainSub = $db->table('subtrips')
            ->select('id')
            ->where('trip_id', $tripId)
            ->where('type', 'main')
            ->get()
            ->getRow();

        if ($mainSub) {
            $row = $db->table('pickdrops')
                ->select('id')
                ->where('trip_id', (int) $mainSub->id)
                ->where('type', 1)
                ->orderBy('id', 'ASC')
                ->get()
                ->getRow();

            if ($row) {
                return (int) $row->id;
            }
        }

        return 0;
    }

    /**
     * All seat numbers on a trip departure (any segment). Use only for reporting — not for
     * segment seat maps; the same seat may be sold again after drop-off on a later leg.
     *
     * @return list<string>
     */
    public static function getAllBookedSeatsOnTripDeparture(
        int $tripId,
        int $subtripId,
        string $busStartDate,
        ?string $boardingDate = null,
        int $pickStandId = 0
    ): array {
        if ($tripId <= 0) {
            return [];
        }

        $dates = self::resolveDepartureDates($tripId, $subtripId, $busStartDate, $boardingDate, $pickStandId);

        $builder = (new TicketModel())->builder();
        $builder->select('tickets.seatnumber, tickets.booking_id')
            ->where('tickets.trip_id', $tripId)
            ->where('tickets.cancel_status', 0)
            ->where('tickets.refund', 0);

        self::applyTicketJourneyDateFilter($builder, $tripId, $dates);

        return self::flattenSeatNumbers($builder->get()->getResultArray(), 'seatnumber');
    }

    /**
     * Confirmed + temp overlap queries: match journeydata and journeylists on bus departure dates.
     *
     * @param array{bus_start?: string, boarding?: string, all?: list<string>}|list<string> $departureDates
     */
    public static function applyTicketJourneyDateFilter(BaseBuilder $builder, int $tripId, $departureDates): void
    {
        $dates = self::normalizeDepartureDates($departureDates);
        $matchDates = $dates['all'];

        if ($matchDates === []) {
            return;
        }

        $builder->groupStart();

        foreach ($matchDates as $i => $d) {
            if ($i === 0) {
                $builder->where('DATE(tickets.journeydata)', $d);
            } else {
                $builder->orWhere('DATE(tickets.journeydata)', $d);
            }
        }

        if ($tripId > 0) {
            $db = \Config\Database::connect();
            $jl = $db->table('journeylists')->select('journeylists.booking_id')
                ->join('tickets tjl', 'tjl.booking_id = journeylists.booking_id', 'inner')
                ->where('journeylists.trip_id', $tripId)
                ->where('tjl.trip_id', $tripId)
                ->where('tjl.cancel_status', 0)
                ->where('tjl.refund', 0)
                ->where('journeylists.deleted_at', null);

            $jl->groupStart();
            foreach ($matchDates as $j => $d) {
                if ($j === 0) {
                    $jl->where('DATE(journeylists.journeydate)', $d);
                } else {
                    $jl->orWhere('DATE(journeylists.journeydate)', $d);
                }
            }
            $jl->groupEnd();

            $bookingIds = array_values(array_unique(array_column($jl->get()->getResultArray(), 'booking_id')));
            if ($bookingIds !== []) {
                $builder->orWhereIn('tickets.booking_id', $bookingIds);
            }
        }

        $builder->groupEnd();
    }

    public static function applyTemporaryBookJourneyDateFilter(BaseBuilder $builder, $departureDates): void
    {
        $dates = self::normalizeDepartureDates($departureDates)['all'];

        if (count($dates) === 1) {
            $builder->where('DATE(temporarybooks.journey_date)', $dates[0]);

            return;
        }

        $builder->groupStart();
        foreach ($dates as $i => $d) {
            if ($i === 0) {
                $builder->where('DATE(temporarybooks.journey_date)', $d);
            } else {
                $builder->orWhere('DATE(temporarybooks.journey_date)', $d);
            }
        }
        $builder->groupEnd();
    }

    /**
     * Active admin hold seats for a trip departure (trip-level holds).
     *
     * @return list<string>
     */
    public static function getAdminHeldSeatsOnTripDeparture(
        int $tripId,
        string $busStartDate,
        ?string $boardingDate = null
    ): array {
        if ($tripId <= 0) {
            return [];
        }

        $dates = self::resolveDepartureDates($tripId, 0, $busStartDate, $boardingDate);

        return self::flattenSeatNumbers(
            HoldSeatSupport::fetchActiveHoldRowsForTrip($tripId, $dates['all']),
            'seat_number'
        );
    }

    /**
     * Same-bus seats + admin holds (segment overlap handled separately).
     *
     * @return list<string>
     */
    public static function getTripDepartureBlockedSeats(
        int $tripId,
        int $subtripId,
        string $busStartDate,
        ?string $boardingDate = null,
        int $pickStandId = 0
    ): array {
        return array_values(array_unique(array_merge(
            self::getAllBookedSeatsOnTripDeparture($tripId, $subtripId, $busStartDate, $boardingDate, $pickStandId),
            self::getAdminHeldSeatsOnTripDeparture($tripId, $busStartDate, $boardingDate)
        )));
    }

    public static function lockTripRowForUpdate(BaseConnection $db, int $tripId): void
    {
        if ($tripId > 0) {
            $db->query('SELECT id FROM trips WHERE id = ? FOR UPDATE', [$tripId]);
        }
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return list<string>
     */
    public static function flattenSeatNumbers(array $rows, string $seatColumn): array
    {
        $out = [];
        foreach ($rows as $row) {
            foreach (array_filter(array_map('trim', explode(',', (string) ($row[$seatColumn] ?? '')))) as $seat) {
                if ($seat !== '') {
                    $out[] = $seat;
                }
            }
        }

        return array_values(array_unique($out));
    }
}
