<?php

namespace App\Services;

use CodeIgniter\Database\BaseConnection;

/**
 * Allocates PO quantities to monthly production capacity.
 *
 * An order remains one commercial order (and therefore keeps one invoice and
 * payment history), while its production quantity may occupy more than one
 * month.
 */
class MonthlyPoScheduler
{
    private const TABLE = 'order_production_schedules';

    public function __construct(private ?BaseConnection $db = null)
    {
        $this->db ??= \Config\Database::connect();
    }

    /**
     * Replaces every planned production batch for an order and returns the new
     * allocation. This method is intended to run inside the caller's DB
     * transaction.
     *
     * @return list<array{scheduled_month:string, quantity:int}>
     */
    public function replaceOrderSchedule(
        int $orderId,
        int $quantity,
        int $monthlyLimit,
        ?string $requestedDate = null
    ): array {
        $this->requireScheduleTable();

        $existingRows = $this->db->table(self::TABLE . ' ops')
            ->select('ops.scheduled_month, ops.quantity, o.order_status, p.status AS production_status')
            ->join('orders o', 'o.id_order = ops.id_order')
            ->join('productions p', 'p.id_order = ops.id_order AND p.scheduled_month = ops.scheduled_month', 'left')
            ->where('ops.id_order', $orderId)
            ->get()
            ->getResultArray();

        $lockedQuantity = 0;
        foreach ($existingRows as $row) {
            $month = self::monthStart($row['scheduled_month']);
            if ($this->isLockedBatch($row, $month)) {
                $lockedQuantity += max(0, (int) $row['quantity']);
                continue;
            }

            $this->db->table(self::TABLE)
                ->where('id_order', $orderId)
                ->where('scheduled_month', $month)
                ->delete();
        }

        $remainingQuantity = max(0, $quantity);
        if ($remainingQuantity < $lockedQuantity) {
            throw new \RuntimeException('Jumlah order tidak boleh lebih kecil dari batch produksi yang sudah dimulai.');
        }
        $remainingQuantity -= $lockedQuantity;

        $allocations = self::allocate(
            $remainingQuantity,
            $monthlyLimit,
            $requestedDate,
            // Schedule yang sedang diganti sudah dihapus, sehingga harus
            // diabaikan dari fallback data legacy tanpa mengabaikan batch
            // yang sudah terkunci pada order yang sama.
            fn (string $month): int => $this->quantityForMonth($month, null, [$orderId])
        );

        if ($allocations === []) {
            return $this->getOrderSchedule($orderId);
        }

        $now = date('Y-m-d H:i:s');
        $rows = array_map(static fn (array $allocation): array => [
            'id_order'        => $orderId,
            'scheduled_month' => $allocation['scheduled_month'],
            'quantity'        => $allocation['quantity'],
            'created_at'      => $now,
            'updated_at'      => $now,
        ], $allocations);

        if (! $this->db->table(self::TABLE)->insertBatch($rows)) {
            throw new \RuntimeException('Gagal menyimpan jadwal kapasitas PO bulanan.');
        }

        return $this->getOrderSchedule($orderId);
    }

    public function deleteOrderSchedule(int $orderId): void
    {
        if ($this->hasScheduleTable()) {
            $this->db->table(self::TABLE)->where('id_order', $orderId)->delete();
        }
    }

    /**
     * Repack every batch that has not started yet. This is called after an
     * order edit or a monthly-limit change so freed capacity is immediately
     * reused: one order stays in one month whenever that month can fit it.
     * Started, completed, cancelled, and historical batches are locked.
     *
     * @return array<int, list<array{scheduled_month:string, quantity:int}>> schedules by order ID
     */
    public function rebalance(int $monthlyLimit): array
    {
        $this->requireScheduleTable();

        $rows = $this->db->table(self::TABLE . ' ops')
            ->select('ops.id_order, ops.scheduled_month, ops.quantity, o.target_date, o.order_status, p.status AS production_status')
            ->join('orders o', 'o.id_order = ops.id_order')
            ->join('productions p', 'p.id_order = ops.id_order AND p.scheduled_month = ops.scheduled_month', 'left')
            ->where('o.order_type', 'po')
            ->where('o.order_status !=', 'cancelled')
            ->orderBy('ops.scheduled_month', 'ASC')
            ->orderBy('ops.id_order', 'ASC')
            ->get()
            ->getResultArray();

        $movable = [];
        $orderPlans = [];
        foreach ($rows as $row) {
            $orderId = (int) $row['id_order'];
            $month = self::monthStart($row['scheduled_month']);
            if ($this->isLockedBatch($row, $month)) {
                continue;
            }

            $movable[] = ['id_order' => $orderId, 'scheduled_month' => $month];
            if (! isset($orderPlans[$orderId])) {
                $orderPlans[$orderId] = [
                    'quantity' => 0,
                    'requested_month' => $month,
                ];
            }

            $orderPlans[$orderId]['quantity'] += max(0, (int) $row['quantity']);
            if ($month < $orderPlans[$orderId]['requested_month']) {
                $orderPlans[$orderId]['requested_month'] = $month;
            }
        }

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

        // Remove only batches that are still safe to move. Locked rows remain
        // in place and therefore still consume capacity during allocation.
        foreach ($movable as $batch) {
            $this->db->table(self::TABLE)
                ->where('id_order', $batch['id_order'])
                ->where('scheduled_month', $batch['scheduled_month'])
                ->delete();
        }

        uasort($orderPlans, static fn (array $left, array $right): int =>
            [$left['requested_month'], $left['quantity']] <=> [$right['requested_month'], $right['quantity']]
        );

        $result = [];
        $now = date('Y-m-d H:i:s');
        foreach ($orderPlans as $orderId => $plan) {
            $allocations = self::allocate(
                (int) $plan['quantity'],
                $monthlyLimit,
                $plan['requested_month'],
                fn (string $month): int => $this->quantityForMonth(
                    $month,
                    null,
                    array_map('intval', array_keys($orderPlans))
                )
            );

            $rowsToInsert = array_map(static fn (array $allocation): array => [
                'id_order' => (int) $orderId,
                'scheduled_month' => $allocation['scheduled_month'],
                'quantity' => $allocation['quantity'],
                'created_at' => $now,
                'updated_at' => $now,
            ], $allocations);

            if ($rowsToInsert !== [] && ! $this->db->table(self::TABLE)->insertBatch($rowsToInsert)) {
                throw new \RuntimeException('Gagal menyimpan penyesuaian jadwal PO bulanan.');
            }

            $fullSchedule = $this->getOrderSchedule((int) $orderId);
            $result[(int) $orderId] = $fullSchedule;
            if ($fullSchedule !== []) {
                $this->db->table('orders')
                    ->where('id_order', (int) $orderId)
                    ->update(['target_date' => $fullSchedule[0]['scheduled_month']]);
            }
        }

        return $result;
    }

    /** @return list<array{scheduled_month:string, quantity:int}> */
    public function getOrderSchedule(int $orderId): array
    {
        if (! $this->hasScheduleTable()) {
            return [];
        }

        return $this->db->table(self::TABLE)
            ->select('scheduled_month, quantity')
            ->where('id_order', $orderId)
            ->orderBy('scheduled_month', 'ASC')
            ->get()
            ->getResultArray();
    }

    /**
     * Quantity already assigned to a calendar month. Legacy PO records that
     * have not yet been edited are included through their target date, so a
     * new schedule cannot overbook an existing database.
     */
    public function quantityForMonth(
        string $monthStart,
        ?int $excludeOrderId = null,
        array $excludeLegacyOrderIds = []
    ): int
    {
        $monthStart = self::monthStart($monthStart);
        $monthEnd = date('Y-m-t', strtotime($monthStart));

        $scheduledQuantity = 0;
        if ($this->hasScheduleTable()) {
            $scheduled = $this->db->table(self::TABLE . ' ops')
                ->selectSum('ops.quantity', 'total_qty')
                ->join('orders o', 'o.id_order = ops.id_order')
                ->where('ops.scheduled_month', $monthStart)
                ->where('o.order_type', 'po')
                ->groupStart()
                    ->where('o.order_status IS NULL', null, false)
                    ->orWhere('o.order_status !=', 'cancelled')
                ->groupEnd();

            if ($excludeOrderId !== null) {
                $scheduled->where('ops.id_order !=', $excludeOrderId);
            }

            $scheduledQuantity = (int) (($scheduled->get()->getRowArray()['total_qty'] ?? 0));
        }

        // Orders made before this feature have no schedule rows. Their item
        // quantity still occupies their original target month until they are
        // edited or recreated by an admin.
        $sql = 'SELECT COALESCE(SUM(oi.qty), 0) AS total_qty
            FROM order_items oi
            INNER JOIN orders o ON o.id_order = oi.id_order
            WHERE o.order_type = ?
              AND (o.order_status IS NULL OR o.order_status != ?)
              AND o.target_date >= ?
              AND o.target_date <= ?';
        $bindings = ['po', 'cancelled', $monthStart, $monthEnd];

        if ($excludeOrderId !== null) {
            $sql .= ' AND o.id_order != ?';
            $bindings[] = $excludeOrderId;
        }

        $legacyOrderIdsToExclude = array_values(array_unique(array_filter(array_merge(
            $excludeOrderId === null ? [] : [$excludeOrderId],
            $excludeLegacyOrderIds
        ), static fn (mixed $id): bool => (int) $id > 0)));
        if ($legacyOrderIdsToExclude !== []) {
            $sql .= ' AND o.id_order NOT IN (' . implode(', ', array_fill(0, count($legacyOrderIdsToExclude), '?')) . ')';
            foreach ($legacyOrderIdsToExclude as $id) {
                $bindings[] = (int) $id;
            }
        }

        if ($this->hasScheduleTable()) {
            $sql .= ' AND NOT EXISTS (
                SELECT 1 FROM ' . self::TABLE . ' legacy_schedule
                WHERE legacy_schedule.id_order = o.id_order
            )';
        }

        $legacyQuantity = (int) (($this->db->query($sql, $bindings)->getRowArray()['total_qty'] ?? 0));

        return $scheduledQuantity + $legacyQuantity;
    }

    /**
     * Pure allocation helper kept public so the capacity rules can be tested
     * without a database. A zero limit means that the feature is disabled and
     * all quantity stays in the requested month.
     *
     * @param callable(string):int $usedForMonth
     * @return list<array{scheduled_month:string, quantity:int}>
     */
    public static function allocate(
        int $quantity,
        int $monthlyLimit,
        ?string $requestedDate,
        callable $usedForMonth
    ): array {
        $remaining = max(0, $quantity);
        if ($remaining === 0) {
            return [];
        }

        $month = self::monthStart($requestedDate);
        if ($monthlyLimit <= 0) {
            return [[
                'scheduled_month' => $month,
                'quantity' => $remaining,
            ]];
        }

        $allocations = [];
        // The guard prevents a corrupt setting/callback from causing an
        // endless request while still permitting very large orders.
        for ($attempt = 0; $remaining > 0 && $attempt < 1200; $attempt++) {
            $used = max(0, (int) $usedForMonth($month));
            $available = max(0, $monthlyLimit - $used);

            if ($available > 0) {
                $allocated = min($remaining, $available);
                $allocations[] = [
                    'scheduled_month' => $month,
                    'quantity' => $allocated,
                ];
                $remaining -= $allocated;
            }

            $month = date('Y-m-01', strtotime($month . ' +1 month'));
        }

        if ($remaining > 0) {
            throw new \RuntimeException('Jadwal PO tidak dapat menemukan kapasitas produksi yang tersedia.');
        }

        return $allocations;
    }

    /**
     * Break order items into the already calculated monthly batches. Items
     * without a product ID (custom work) still consume capacity, even though
     * the production-queue table cannot create a product row for them.
     *
     * @param list<array<string, mixed>> $items
     * @param list<array{scheduled_month:string, quantity:int}> $schedules
     * @return list<array{item:array<string, mixed>, scheduled_month:string, quantity:int}>
     */
    public static function distributeItems(array $items, array $schedules, ?string $fallbackDate = null): array
    {
        $schedules = array_values(array_filter($schedules, static fn (array $schedule): bool =>
            (int) ($schedule['quantity'] ?? 0) > 0
        ));

        if ($schedules === []) {
            $month = self::monthStart($fallbackDate);

            return array_values(array_filter(array_map(static function (array $item) use ($month): ?array {
                $quantity = max(0, (int) ($item['qty'] ?? 0));
                if ($quantity === 0) {
                    return null;
                }

                return [
                    'item' => $item,
                    'scheduled_month' => $month,
                    'quantity' => $quantity,
                ];
            }, $items)));
        }

        $result = [];
        $scheduleIndex = 0;
        $available = (int) $schedules[$scheduleIndex]['quantity'];

        foreach ($items as $item) {
            $remaining = max(0, (int) ($item['qty'] ?? 0));

            while ($remaining > 0) {
                while ($available <= 0 && isset($schedules[++$scheduleIndex])) {
                    $available = (int) $schedules[$scheduleIndex]['quantity'];
                }

                if ($available <= 0 || !isset($schedules[$scheduleIndex])) {
                    throw new \RuntimeException('Jumlah jadwal PO tidak sesuai dengan jumlah item order.');
                }

                $quantity = min($remaining, $available);
                $result[] = [
                    'item' => $item,
                    'scheduled_month' => $schedules[$scheduleIndex]['scheduled_month'],
                    'quantity' => $quantity,
                ];
                $remaining -= $quantity;
                $available -= $quantity;
            }
        }

        return $result;
    }

    private static function monthStart(?string $date): string
    {
        $timestamp = $date ? strtotime($date) : false;

        return date('Y-m-01', $timestamp === false ? time() : $timestamp);
    }

    private function hasScheduleTable(): bool
    {
        return $this->db->tableExists(self::TABLE);
    }

    private function requireScheduleTable(): void
    {
        if (! $this->hasScheduleTable()) {
            throw new \RuntimeException(
                'Tabel jadwal PO belum tersedia. Jalankan migrasi database terlebih dahulu.'
            );
        }
    }

    private function isLockedBatch(array $row, string $month): bool
    {
        $productionStatus = strtolower((string) ($row['production_status'] ?? ''));

        return $month < date('Y-m-01')
            || in_array((string) ($row['order_status'] ?? ''), ['completed', 'cancelled'], true)
            || ($productionStatus !== '' && $productionStatus !== 'waiting');
    }
}
