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

        // Midnight trips: bus departs day N (Cairo), passenger boards day N+1 (e.g. Ras Sudr).
        // Inventory must match tickets.journeydata on bus-start date AND boarding calendar date.
        if ($tripId > 0 && $subtripId > 0 && $boardingDate !== null && $boardingDate !== '') {
            if ($pickStandId <= 0) {
                $pickStandId = self::resolveDefaultPickStandId($tripId, $subtripId);
            }

            if ($pickStandId > 0) {
                $helper = new TripJourneyDateHelper();
                $busStart = $helper->getBusStartDateForJourneylist($tripId, $subtripId, $boarding, $pickStandId);
            }
            // If no pick stand: keep $busStart from $busStartDate (caller may have computed it already).
        }

        return [
            'bus_start' => $busStart,
            'boarding' => $boarding,
            'all' => array_values(array_unique([$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['all']);

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

    /**
     * Confirmed + temp overlap queries: match journeydata on both bus-start and boarding dates.
     */
    public static function applyTicketJourneyDateFilter(BaseBuilder $builder, int $tripId, array $departureDates): void
    {
        $dates = array_values(array_unique(array_filter(array_map(static function ($d) {
            return date('Y-m-d', strtotime((string) $d));
        }, $departureDates))));

        if (empty($dates)) {
            return;
        }

        $builder->groupStart();

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

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

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

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

        $builder->groupEnd();
    }

    public static function applyTemporaryBookJourneyDateFilter(BaseBuilder $builder, array $departureDates): void
    {
        $dates = array_values(array_unique(array_filter(array_map(static function ($d) {
            return date('Y-m-d', strtotime((string) $d));
        }, $departureDates))));

        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));
    }
}
