<?php

class KitchenIssue extends Model
{
    protected string $table = 'kitchen_issues';

    /**
     * $items = [['raw_material_id'=>, 'quantity'=>], ...]
     * Deducts from central raw material stock and logs a KITCHEN_ISSUE stock transaction.
     */
    public function createWithItems(string $notes, array $items, ?int $userId): int
    {
        $this->db->beginTransaction();
        try {
            $issueId = $this->insert([
                'issue_date' => date('Y-m-d'),
                'notes'      => $notes ?: null,
                'created_by' => $userId,
            ]);

            $itemStmt = $this->db->prepare(
                "INSERT INTO kitchen_issue_items (kitchen_issue_id, raw_material_id, quantity) VALUES (:issue_id, :material_id, :qty)"
            );
            $stockUpdateStmt = $this->db->prepare(
                "UPDATE raw_materials SET current_stock = current_stock - :qty WHERE id = :id"
            );
            $stockTxnStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:material_id, 'KITCHEN_ISSUE', :qty, 'KITCHEN_ISSUE', :issue_id, :notes, :user_id)"
            );

            foreach ($items as $item) {
                $qty = (float) $item['quantity'];
                if ($qty <= 0) {
                    continue;
                }
                $itemStmt->execute([
                    'issue_id'    => $issueId,
                    'material_id' => $item['raw_material_id'],
                    'qty'         => $qty,
                ]);
                $stockUpdateStmt->execute(['qty' => $qty, 'id' => $item['raw_material_id']]);
                $stockTxnStmt->execute([
                    'material_id' => $item['raw_material_id'],
                    'qty'         => -$qty,
                    'issue_id'    => $issueId,
                    'notes'       => 'Issued to kitchen' . ($notes ? " — {$notes}" : ''),
                    'user_id'     => $userId,
                ]);
            }

            $this->db->commit();
            return $issueId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function listRecent(int $limit = 30): array
    {
        $stmt = $this->db->query(
            "SELECT ki.*, u.full_name as created_by_name,
                    (SELECT COUNT(*) FROM kitchen_issue_items kii WHERE kii.kitchen_issue_id = ki.id) as item_count,
                    COALESCE((SELECT SUM(kii.quantity * rm.unit_cost)
                        FROM kitchen_issue_items kii
                        JOIN raw_materials rm ON rm.id = kii.raw_material_id
                        WHERE kii.kitchen_issue_id = ki.id), 0) as est_value
             FROM kitchen_issues ki LEFT JOIN users u ON u.id = ki.created_by
             ORDER BY ki.id DESC LIMIT " . (int) $limit
        );
        return $stmt->fetchAll();
    }

    public function findWithItems(int $issueId): array|false
    {
        $stmt = $this->db->prepare(
            "SELECT ki.*, u.full_name as created_by_name FROM kitchen_issues ki
             LEFT JOIN users u ON u.id = ki.created_by WHERE ki.id = :id"
        );
        $stmt->execute(['id' => $issueId]);
        $issue = $stmt->fetch();
        if (!$issue) {
            return false;
        }

        $itemStmt = $this->db->prepare(
            "SELECT kii.*, rm.material_name, rm.unit FROM kitchen_issue_items kii
             JOIN raw_materials rm ON rm.id = kii.raw_material_id WHERE kii.kitchen_issue_id = :id"
        );
        $itemStmt->execute(['id' => $issueId]);
        $issue['items'] = $itemStmt->fetchAll();

        return $issue;
    }

    public function todayCount(): int
    {
        $stmt = $this->db->query("SELECT COUNT(*) as cnt FROM kitchen_issues WHERE issue_date = CURDATE()");
        return (int) $stmt->fetch()['cnt'];
    }

    /**
     * Edit an existing kitchen issue: reverses the original stock deduction (restores materials),
     * replaces the line items with the new set, and re-deducts stock for the new quantities.
     * Keeps a full audit trail via stock_transactions rather than silently overwriting history.
     */
    public function updateWithItems(int $issueId, string $notes, array $newItems, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            // 1. Reverse the original deduction for every existing line item
            $oldItemsStmt = $this->db->prepare(
                "SELECT raw_material_id, quantity FROM kitchen_issue_items WHERE kitchen_issue_id = :id"
            );
            $oldItemsStmt->execute(['id' => $issueId]);
            $oldItems = $oldItemsStmt->fetchAll();

            $restoreStmt = $this->db->prepare("UPDATE raw_materials SET current_stock = current_stock + :qty WHERE id = :id");
            $reverseLogStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:material_id, 'ADJUSTMENT', :qty, 'KITCHEN_ISSUE', :issue_id, 'Kitchen issue edited — original deduction reversed', :user_id)"
            );
            foreach ($oldItems as $old) {
                $qty = (float) $old['quantity'];
                $restoreStmt->execute(['qty' => $qty, 'id' => $old['raw_material_id']]);
                $reverseLogStmt->execute([
                    'material_id' => $old['raw_material_id'],
                    'qty'         => $qty,
                    'issue_id'    => $issueId,
                    'user_id'     => $userId,
                ]);
            }

            // 2. Remove the old line items
            $this->db->prepare("DELETE FROM kitchen_issue_items WHERE kitchen_issue_id = :id")->execute(['id' => $issueId]);

            // 3. Insert the new line items and re-deduct stock
            $itemStmt = $this->db->prepare(
                "INSERT INTO kitchen_issue_items (kitchen_issue_id, raw_material_id, quantity) VALUES (:issue_id, :material_id, :qty)"
            );
            $deductStmt = $this->db->prepare("UPDATE raw_materials SET current_stock = current_stock - :qty WHERE id = :id");
            $deductLogStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:material_id, 'KITCHEN_ISSUE', :qty, 'KITCHEN_ISSUE', :issue_id, :notes, :user_id)"
            );

            foreach ($newItems as $item) {
                $qty = (float) $item['quantity'];
                if ($qty <= 0) {
                    continue;
                }
                $itemStmt->execute(['issue_id' => $issueId, 'material_id' => $item['raw_material_id'], 'qty' => $qty]);
                $deductStmt->execute(['qty' => $qty, 'id' => $item['raw_material_id']]);
                $deductLogStmt->execute([
                    'material_id' => $item['raw_material_id'],
                    'qty'         => -$qty,
                    'issue_id'    => $issueId,
                    'notes'       => 'Issued to kitchen (edited)' . ($notes ? " — {$notes}" : ''),
                    'user_id'     => $userId,
                ]);
            }

            // 4. Update notes
            $this->update($issueId, ['notes' => $notes ?: null]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /**
     * Given the raw materials issued in a kitchen issue, calculate how many units of each
     * recipe-tracked menu item ("packets") could be produced — limited by the scarcest
     * ingredient among those actually issued. Menu items whose recipe needs an ingredient
     * NOT included in this issue are skipped (their yield can't be determined from this batch alone).
     * Returns a full per-ingredient breakdown so the calculation is transparent, not just the final number.
     */
    public function estimatedYield(int $issueId): array
    {
        $issuedStmt = $this->db->prepare(
            "SELECT raw_material_id, quantity FROM kitchen_issue_items WHERE kitchen_issue_id = :id"
        );
        $issuedStmt->execute(['id' => $issueId]);
        $issuedMap = [];
        foreach ($issuedStmt->fetchAll() as $row) {
            $issuedMap[(int) $row['raw_material_id']] = (float) $row['quantity'];
        }
        if (empty($issuedMap)) {
            return [];
        }

        $menuItems = $this->db->query(
            "SELECT mi.id, mi.item_name FROM menu_items mi WHERE mi.track_stock = 1"
        )->fetchAll();

        $recipeStmt = $this->db->prepare(
            "SELECT r.raw_material_id, r.quantity_required, rm.material_name, rm.unit
             FROM recipes r JOIN raw_materials rm ON rm.id = r.raw_material_id
             WHERE r.menu_item_id = :id"
        );

        $results = [];
        foreach ($menuItems as $mi) {
            $recipeStmt->execute(['id' => $mi['id']]);
            $recipeRows = $recipeStmt->fetchAll();
            if (empty($recipeRows)) {
                continue;
            }

            $breakdown = [];
            $usesOnlyIssuedMaterials = true;
            foreach ($recipeRows as $r) {
                $matId = (int) $r['raw_material_id'];
                $needed = (float) $r['quantity_required'];
                if (!isset($issuedMap[$matId]) || $needed <= 0) {
                    $usesOnlyIssuedMaterials = false;
                    break;
                }
                $issuedQty = $issuedMap[$matId];
                $possibleUnits = floor($issuedQty / $needed);
                $breakdown[] = [
                    'material_name'   => $r['material_name'],
                    'unit'            => $r['unit'],
                    'needed_per_unit' => $needed,
                    'issued_qty'      => $issuedQty,
                    'possible_units'  => (int) $possibleUnits,
                ];
            }

            if (!$usesOnlyIssuedMaterials || empty($breakdown)) {
                continue;
            }

            // The limiting ingredient is whichever one produces the smallest possible_units
            $limiting = $breakdown[0];
            foreach ($breakdown as $row) {
                if ($row['possible_units'] < $limiting['possible_units']) {
                    $limiting = $row;
                }
            }

            $results[] = [
                'item_name'       => $mi['item_name'],
                'estimated_units' => $limiting['possible_units'],
                'limiting_material' => $limiting['material_name'],
                'breakdown'       => $breakdown,
            ];
        }

        // Highest yield first
        usort($results, fn($a, $b) => $b['estimated_units'] <=> $a['estimated_units']);
        return $results;
    }

    public function stats(): array
    {
        $stmt = $this->db->query(
            "SELECT
                (SELECT COUNT(*) FROM kitchen_issues WHERE issue_date = CURDATE()) as today_count,
                (SELECT COUNT(*) FROM kitchen_issues WHERE issue_date >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)) as week_count,
                COALESCE((SELECT SUM(kii.quantity)
                    FROM kitchen_issue_items kii
                    JOIN kitchen_issues ki ON ki.id = kii.kitchen_issue_id
                    WHERE ki.issue_date = CURDATE()), 0) as today_qty,
                COALESCE((SELECT SUM(kii.quantity * rm.unit_cost)
                    FROM kitchen_issue_items kii
                    JOIN kitchen_issues ki ON ki.id = kii.kitchen_issue_id
                    JOIN raw_materials rm ON rm.id = kii.raw_material_id
                    WHERE ki.issue_date = CURDATE()), 0) as today_value"
        );
        return $stmt->fetch();
    }

    public function mostRecentWithItems(): array|false
    {
        $stmt = $this->db->query("SELECT id FROM kitchen_issues ORDER BY id DESC LIMIT 1");
        $row = $stmt->fetch();
        if (!$row) {
            return false;
        }
        return $this->findWithItems((int) $row['id']);
    }
}
