<?php
/**
 * pos_recipe_deduction.php
 *
 * Reusable POS stock deduction engine.
 *
 * Transaction-safe version:
 * - If called from pos.php while a transaction is already active, this file does NOT commit/rollback it.
 * - If called standalone, it starts and closes its own transaction safely.
 *
 * Save as:
 * /rooms/modules/pos/pos_recipe_deduction.php
 *
 * How to use inside your POS sale posting code, AFTER pos_orders and pos_order_items are saved:
 *
 * require_once __DIR__ . '/pos_recipe_deduction.php';
 * pos_deduct_stock_for_order($pdo, $posOrderId);
 *
 * What it does:
 * 1. If POS item has a recipe in inventory_recipes:
 *      deducts each ingredient from stock_movements
 * 2. If POS item has no recipe but is_stock_item = 1:
 *      deducts the sold item itself
 * 3. Prevents duplicate deduction for the same POS order
 * 4. Uses movement_type = SALE
 * 5. Uses reference_type = POS_ORDER
 * 6. Uses reference_id = pos_orders.id
 */

if (!function_exists('pos_recipe_column_exists')) {
    function pos_recipe_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
              AND column_name = :column_name
        ");
        $stmt->execute([
            ':table_name' => $tableName,
            ':column_name' => $columnName,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('pos_recipe_table_exists')) {
    function pos_recipe_table_exists(PDO $pdo, string $tableName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('pos_recipe_prepare_tables')) {
    function pos_recipe_prepare_tables(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS stock_movements (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                item_id INT UNSIGNED NOT NULL,
                store_id INT UNSIGNED NOT NULL,
                movement_type ENUM('OPENING','GRN','ISSUE','SALE','TRANSFER_IN','TRANSFER_OUT','ADJUSTMENT_IN','ADJUSTMENT_OUT','WASTAGE','RETURN') NOT NULL,
                qty_in DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                qty_out DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                reference_type VARCHAR(60) NULL,
                reference_id BIGINT UNSIGNED NULL,
                notes TEXT NULL,
                movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_stock_item_store (item_id, store_id),
                INDEX idx_stock_reference (reference_type, reference_id),
                INDEX idx_stock_movement_date (movement_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS inventory_recipes (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                recipe_item_id INT UNSIGNED NOT NULL,
                recipe_name VARCHAR(180) NOT NULL,
                yield_qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
                default_store_id INT UNSIGNED NULL,
                preparation_notes TEXT NULL,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                created_by INT UNSIGNED NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uq_recipe_item (recipe_item_id),
                INDEX idx_recipe_active (is_active)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS inventory_recipe_lines (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                recipe_id BIGINT UNSIGNED NOT NULL,
                ingredient_item_id INT UNSIGNED NOT NULL,
                qty_required DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                wastage_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
                notes TEXT NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_recipe_line_recipe (recipe_id),
                INDEX idx_recipe_line_ingredient (ingredient_item_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS pos_stock_deductions (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                pos_order_id BIGINT UNSIGNED NOT NULL,
                pos_order_item_id BIGINT UNSIGNED NULL,
                sold_item_id INT UNSIGNED NOT NULL,
                deducted_item_id INT UNSIGNED NOT NULL,
                recipe_id BIGINT UNSIGNED NULL,
                store_id INT UNSIGNED NOT NULL,
                qty_sold DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                qty_deducted DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                stock_movement_id BIGINT UNSIGNED NULL,
                deduction_type ENUM('recipe','direct_stock') NOT NULL DEFAULT 'direct_stock',
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_pos_deduction_line_item (pos_order_item_id, deducted_item_id, deduction_type),
                INDEX idx_pos_deduction_order (pos_order_id),
                INDEX idx_pos_deduction_stock_movement (stock_movement_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }
}

if (!function_exists('pos_recipe_get_stock_balance')) {
    function pos_recipe_get_stock_balance(PDO $pdo, int $itemId, int $storeId): float
    {
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(qty_in - qty_out), 0)
            FROM stock_movements
            WHERE item_id = :item_id
              AND store_id = :store_id
        ");
        $stmt->execute([
            ':item_id' => $itemId,
            ':store_id' => $storeId,
        ]);
        return (float)$stmt->fetchColumn();
    }
}

if (!function_exists('pos_recipe_get_unit_cost')) {
    function pos_recipe_get_unit_cost(PDO $pdo, int $itemId): float
    {
        $stmt = $pdo->prepare("
            SELECT COALESCE(NULLIF(avg_cost,0), NULLIF(cost_price,0), 0)
            FROM inventory_items
            WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $itemId]);
        return (float)$stmt->fetchColumn();
    }
}

if (!function_exists('pos_recipe_order_already_deducted')) {
    function pos_recipe_order_already_deducted(PDO $pdo, int $posOrderId): bool
    {
        if (!pos_recipe_table_exists($pdo, 'pos_stock_deductions')) {
            return false;
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM pos_stock_deductions
            WHERE pos_order_id = :pos_order_id
        ");
        $stmt->execute([':pos_order_id' => $posOrderId]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('pos_recipe_insert_stock_movement')) {
    function pos_recipe_insert_stock_movement(
        PDO $pdo,
        int $itemId,
        int $storeId,
        float $qtyOut,
        float $unitCost,
        int $posOrderId,
        string $notes,
        ?string $movementDate = null
    ): int {
        $totalCost = round($qtyOut * $unitCost, 4);

        $stmt = $pdo->prepare("
            INSERT INTO stock_movements
            (item_id, store_id, movement_type, qty_in, qty_out, unit_cost, total_cost, reference_type, reference_id, notes, movement_date)
            VALUES
            (:item_id, :store_id, 'SALE', 0, :qty_out, :unit_cost, :total_cost, 'POS_ORDER', :reference_id, :notes, :movement_date)
        ");

        $stmt->execute([
            ':item_id' => $itemId,
            ':store_id' => $storeId,
            ':qty_out' => $qtyOut,
            ':unit_cost' => $unitCost,
            ':total_cost' => $totalCost,
            ':reference_id' => $posOrderId,
            ':notes' => $notes,
            ':movement_date' => $movementDate ?: date('Y-m-d H:i:s'),
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('pos_deduct_stock_for_order')) {
    /**
     * Deduct stock for a POS order.
     *
     * @param PDO $pdo
     * @param int $posOrderId
     * @param bool $allowNegativeStock If false, throws error when stock is insufficient.
     * @return array Summary of deductions.
     * @throws Throwable
     */
    function pos_deduct_stock_for_order(PDO $pdo, int $posOrderId, bool $allowNegativeStock = true): array
    {
        pos_recipe_prepare_tables($pdo);

        if ($posOrderId <= 0) {
            throw new RuntimeException('Invalid POS order id for stock deduction.');
        }

        if (pos_recipe_order_already_deducted($pdo, $posOrderId)) {
            return [
                'success' => true,
                'already_deducted' => true,
                'message' => 'Stock was already deducted for this POS order.',
                'deductions' => [],
            ];
        }

        $stmt = $pdo->prepare("
            SELECT po.*
            FROM pos_orders po
            WHERE po.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $posOrderId]);
        $order = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$order) {
            throw new RuntimeException('POS order not found for stock deduction.');
        }

        if (($order['status'] ?? '') === 'void') {
            return [
                'success' => true,
                'already_deducted' => false,
                'message' => 'Void POS order skipped. No stock deducted.',
                'deductions' => [],
            ];
        }

        $storeId = (int)($order['store_id'] ?? 0);
        if ($storeId <= 0) {
            throw new RuntimeException('POS order has no store location. Cannot deduct stock.');
        }

        $stmt = $pdo->prepare("
            SELECT
                poi.*,
                ii.is_stock_item,
                ii.avg_cost,
                ii.cost_price,
                ii.item_type
            FROM pos_order_items poi
            INNER JOIN inventory_items ii ON ii.id = poi.item_id
            WHERE poi.pos_order_id = :pos_order_id
            ORDER BY poi.id ASC
        ");
        $stmt->execute([':pos_order_id' => $posOrderId]);
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$items) {
            return [
                'success' => true,
                'already_deducted' => false,
                'message' => 'No POS order items found. No stock deducted.',
                'deductions' => [],
            ];
        }

        $startedTransaction = false;
        if (!$pdo->inTransaction()) {
            $pdo->beginTransaction();
            $startedTransaction = true;
        }

        $deductions = [];

        try {
            /*
             * Re-check inside the transaction to avoid duplicate stock deduction
             * when a receipt is submitted twice or a browser repeats the POST.
             */
            $stmt = $pdo->prepare("
                SELECT COUNT(*)
                FROM pos_stock_deductions
                WHERE pos_order_id = :pos_order_id
            ");
            $stmt->execute([':pos_order_id' => $posOrderId]);
            if ((int)$stmt->fetchColumn() > 0) {
                if ($startedTransaction && $pdo->inTransaction()) {
                    $pdo->commit();
                }

                return [
                    'success' => true,
                    'already_deducted' => true,
                    'message' => 'Stock was already deducted for this POS order.',
                    'deductions' => [],
                ];
            }

            foreach ($items as $item) {
                $posOrderItemId = (int)$item['id'];
                $soldItemId = (int)$item['item_id'];
                $qtySold = (float)$item['qty'];

                if ($qtySold <= 0) {
                    continue;
                }

                /*
                 * First check if this sold item has an active recipe.
                 */
                $stmt = $pdo->prepare("
                    SELECT *
                    FROM inventory_recipes
                    WHERE recipe_item_id = :recipe_item_id
                      AND is_active = 1
                    LIMIT 1
                ");
                $stmt->execute([':recipe_item_id' => $soldItemId]);
                $recipe = $stmt->fetch(PDO::FETCH_ASSOC);

                if ($recipe) {
                    $recipeId = (int)$recipe['id'];
                    $yieldQty = max(1, (float)$recipe['yield_qty']);

                    /*
                     * Store priority:
                     * 1. Recipe default_store_id
                     * 2. POS order store_id
                     */
                    $deductionStoreId = (int)($recipe['default_store_id'] ?? 0);
                    if ($deductionStoreId <= 0) {
                        $deductionStoreId = $storeId;
                    }

                    $stmt = $pdo->prepare("
                        SELECT
                            rl.*,
                            ing.item_name,
                            COALESCE(NULLIF(ing.avg_cost,0), NULLIF(ing.cost_price,0), 0) AS unit_cost
                        FROM inventory_recipe_lines rl
                        INNER JOIN inventory_items ing ON ing.id = rl.ingredient_item_id
                        WHERE rl.recipe_id = :recipe_id
                        ORDER BY rl.id ASC
                    ");
                    $stmt->execute([':recipe_id' => $recipeId]);
                    $recipeLines = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    foreach ($recipeLines as $line) {
                        $ingredientId = (int)$line['ingredient_item_id'];
                        $qtyRequired = (float)$line['qty_required'];
                        $wastagePercent = (float)$line['wastage_percent'];
                        $unitCost = (float)$line['unit_cost'];

                        $effectiveQtyPerYield = $qtyRequired + ($qtyRequired * ($wastagePercent / 100));
                        $qtyToDeduct = ($effectiveQtyPerYield / $yieldQty) * $qtySold;

                        if ($qtyToDeduct <= 0) {
                            continue;
                        }

                        $stockBalance = pos_recipe_get_stock_balance($pdo, $ingredientId, $deductionStoreId);

                        if (!$allowNegativeStock && $stockBalance + 0.0001 < $qtyToDeduct) {
                            throw new RuntimeException(
                                'Insufficient stock for ingredient ' . $line['item_name'] .
                                '. Available: ' . number_format($stockBalance, 4) .
                                ', Required: ' . number_format($qtyToDeduct, 4)
                            );
                        }

                        $notes = 'POS recipe deduction: ' . ($recipe['recipe_name'] ?? 'Recipe') .
                            ' | Sold item: ' . ($item['item_name'] ?? '') .
                            ' | POS: ' . ($order['pos_no'] ?? $posOrderId);

                        $movementId = pos_recipe_insert_stock_movement(
                            $pdo,
                            $ingredientId,
                            $deductionStoreId,
                            $qtyToDeduct,
                            $unitCost,
                            $posOrderId,
                            $notes,
                            $order['created_at'] ?? date('Y-m-d H:i:s')
                        );

                        $stmt = $pdo->prepare("
                            INSERT INTO pos_stock_deductions
                            (pos_order_id, pos_order_item_id, sold_item_id, deducted_item_id, recipe_id, store_id, qty_sold, qty_deducted, unit_cost, total_cost, stock_movement_id, deduction_type)
                            VALUES
                            (:pos_order_id, :pos_order_item_id, :sold_item_id, :deducted_item_id, :recipe_id, :store_id, :qty_sold, :qty_deducted, :unit_cost, :total_cost, :stock_movement_id, 'recipe')
                        ");
                        $stmt->execute([
                            ':pos_order_id' => $posOrderId,
                            ':pos_order_item_id' => $posOrderItemId,
                            ':sold_item_id' => $soldItemId,
                            ':deducted_item_id' => $ingredientId,
                            ':recipe_id' => $recipeId,
                            ':store_id' => $deductionStoreId,
                            ':qty_sold' => $qtySold,
                            ':qty_deducted' => $qtyToDeduct,
                            ':unit_cost' => $unitCost,
                            ':total_cost' => round($qtyToDeduct * $unitCost, 4),
                            ':stock_movement_id' => $movementId,
                        ]);

                        $deductions[] = [
                            'type' => 'recipe',
                            'sold_item_id' => $soldItemId,
                            'deducted_item_id' => $ingredientId,
                            'recipe_id' => $recipeId,
                            'store_id' => $deductionStoreId,
                            'qty_sold' => $qtySold,
                            'qty_deducted' => $qtyToDeduct,
                            'unit_cost' => $unitCost,
                            'movement_id' => $movementId,
                        ];
                    }

                    continue;
                }

                /*
                 * If there is no recipe, deduct the sold item itself if it is a stock item.
                 */
                if ((int)($item['is_stock_item'] ?? 0) === 1) {
                    $unitCost = (float)($item['avg_cost'] ?: $item['cost_price'] ?: $item['unit_cost'] ?: 0);
                    $qtyToDeduct = $qtySold;

                    $stockBalance = pos_recipe_get_stock_balance($pdo, $soldItemId, $storeId);

                    if (!$allowNegativeStock && $stockBalance + 0.0001 < $qtyToDeduct) {
                        throw new RuntimeException(
                            'Insufficient stock for ' . ($item['item_name'] ?? 'item') .
                            '. Available: ' . number_format($stockBalance, 4) .
                            ', Required: ' . number_format($qtyToDeduct, 4)
                        );
                    }

                    $notes = 'POS direct stock deduction: ' .
                        ($item['item_name'] ?? '') .
                        ' | POS: ' . ($order['pos_no'] ?? $posOrderId);

                    $movementId = pos_recipe_insert_stock_movement(
                        $pdo,
                        $soldItemId,
                        $storeId,
                        $qtyToDeduct,
                        $unitCost,
                        $posOrderId,
                        $notes,
                        $order['created_at'] ?? date('Y-m-d H:i:s')
                    );

                    $stmt = $pdo->prepare("
                        INSERT INTO pos_stock_deductions
                        (pos_order_id, pos_order_item_id, sold_item_id, deducted_item_id, recipe_id, store_id, qty_sold, qty_deducted, unit_cost, total_cost, stock_movement_id, deduction_type)
                        VALUES
                        (:pos_order_id, :pos_order_item_id, :sold_item_id, :deducted_item_id, NULL, :store_id, :qty_sold, :qty_deducted, :unit_cost, :total_cost, :stock_movement_id, 'direct_stock')
                    ");
                    $stmt->execute([
                        ':pos_order_id' => $posOrderId,
                        ':pos_order_item_id' => $posOrderItemId,
                        ':sold_item_id' => $soldItemId,
                        ':deducted_item_id' => $soldItemId,
                        ':store_id' => $storeId,
                        ':qty_sold' => $qtySold,
                        ':qty_deducted' => $qtyToDeduct,
                        ':unit_cost' => $unitCost,
                        ':total_cost' => round($qtyToDeduct * $unitCost, 4),
                        ':stock_movement_id' => $movementId,
                    ]);

                    $deductions[] = [
                        'type' => 'direct_stock',
                        'sold_item_id' => $soldItemId,
                        'deducted_item_id' => $soldItemId,
                        'recipe_id' => null,
                        'store_id' => $storeId,
                        'qty_sold' => $qtySold,
                        'qty_deducted' => $qtyToDeduct,
                        'unit_cost' => $unitCost,
                        'movement_id' => $movementId,
                    ];
                }
            }

            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'success' => true,
                'already_deducted' => false,
                'message' => 'Stock deducted successfully.',
                'deductions' => $deductions,
            ];

        } catch (Throwable $e) {
            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }
}

if (!function_exists('pos_reverse_stock_deduction_for_order')) {
    /**
     * Reverse stock deduction for a voided POS order.
     * Creates RETURN movements for each previous deduction.
     */
    function pos_reverse_stock_deduction_for_order(PDO $pdo, int $posOrderId, string $reason = ''): array
    {
        pos_recipe_prepare_tables($pdo);

        if ($posOrderId <= 0) {
            throw new RuntimeException('Invalid POS order id for stock reversal.');
        }

        $stmt = $pdo->prepare("
            SELECT d.*
            FROM pos_stock_deductions d
            WHERE d.pos_order_id = :pos_order_id
            ORDER BY d.id ASC
        ");
        $stmt->execute([':pos_order_id' => $posOrderId]);
        $deductions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$deductions) {
            return [
                'success' => true,
                'message' => 'No deductions found to reverse.',
                'reversals' => [],
            ];
        }

        /*
         * Prevent duplicate reversal.
         */
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM stock_movements
            WHERE reference_type = 'POS_ORDER_REVERSAL'
              AND reference_id = :pos_order_id
        ");
        $stmt->execute([':pos_order_id' => $posOrderId]);
        if ((int)$stmt->fetchColumn() > 0) {
            return [
                'success' => true,
                'message' => 'Stock reversal already exists for this POS order.',
                'reversals' => [],
            ];
        }

        $startedTransaction = false;
        if (!$pdo->inTransaction()) {
            $pdo->beginTransaction();
            $startedTransaction = true;
        }

        $reversals = [];

        try {
            /*
             * Re-check inside the transaction to avoid duplicate reversal.
             */
            $stmt = $pdo->prepare("
                SELECT COUNT(*)
                FROM stock_movements
                WHERE reference_type = 'POS_ORDER_REVERSAL'
                  AND reference_id = :pos_order_id
            ");
            $stmt->execute([':pos_order_id' => $posOrderId]);
            if ((int)$stmt->fetchColumn() > 0) {
                if ($startedTransaction && $pdo->inTransaction()) {
                    $pdo->commit();
                }

                return [
                    'success' => true,
                    'message' => 'Stock reversal already exists for this POS order.',
                    'reversals' => [],
                ];
            }

            foreach ($deductions as $d) {
                $qty = (float)$d['qty_deducted'];
                $unitCost = (float)$d['unit_cost'];
                $totalCost = round($qty * $unitCost, 4);

                $stmt = $pdo->prepare("
                    INSERT INTO stock_movements
                    (item_id, store_id, movement_type, qty_in, qty_out, unit_cost, total_cost, reference_type, reference_id, notes, movement_date)
                    VALUES
                    (:item_id, :store_id, 'RETURN', :qty_in, 0, :unit_cost, :total_cost, 'POS_ORDER_REVERSAL', :reference_id, :notes, NOW())
                ");
                $stmt->execute([
                    ':item_id' => (int)$d['deducted_item_id'],
                    ':store_id' => (int)$d['store_id'],
                    ':qty_in' => $qty,
                    ':unit_cost' => $unitCost,
                    ':total_cost' => $totalCost,
                    ':reference_id' => $posOrderId,
                    ':notes' => 'POS stock reversal. ' . ($reason ?: 'No reason supplied'),
                ]);

                $reversals[] = [
                    'deducted_item_id' => (int)$d['deducted_item_id'],
                    'store_id' => (int)$d['store_id'],
                    'qty_returned' => $qty,
                    'movement_id' => (int)$pdo->lastInsertId(),
                ];
            }

            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'success' => true,
                'message' => 'Stock reversal completed.',
                'reversals' => $reversals,
            ];

        } catch (Throwable $e) {
            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }
}
