<?php
// FILE: helpers/inventory.php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/barcode_utils.php';

function inventory_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;
}

function inventory_stock_expression(string $alias = 'm'): string
{
    return "COALESCE(SUM(CASE WHEN {$alias}.movement_type = 'IN' THEN {$alias}.quantity ELSE 0 END), 0)
          - COALESCE(SUM(CASE WHEN {$alias}.movement_type = 'OUT' THEN {$alias}.quantity ELSE 0 END), 0)
          + COALESCE(SUM(CASE WHEN {$alias}.movement_type = 'ADJUST' THEN {$alias}.quantity ELSE 0 END), 0)";
}

function inventory_current_stock(PDO $pdo, int $productId): int
{
    $stmt = $pdo->prepare("SELECT " . inventory_stock_expression('m') . " AS current_stock FROM inventory_movements m WHERE m.product_id = :product_id");
    $stmt->execute([':product_id' => $productId]);
    return (int)($stmt->fetchColumn() ?: 0);
}

function inventory_sync_product_quantity(PDO $pdo, int $productId): int
{
    $stock = inventory_current_stock($pdo, $productId);
    $stmt = $pdo->prepare('UPDATE products SET quantity_on_hand = :stock WHERE id = :id');
    $stmt->execute([
        ':stock' => $stock,
        ':id' => $productId,
    ]);
    return $stock;
}

function inventory_find_product_by_code(PDO $pdo, string $scanCode, bool $activeOnly = true): ?array
{
    $raw = trim($scanCode);
    if ($raw === '') {
        return null;
    }

    $candidates = barcode_lookup_candidates($raw);
    if (!$candidates) {
        return null;
    }

    $conditions = [];
    $params = [];
    foreach ($candidates as $index => $candidate) {
        $param = ':code' . $index;
        $conditions[] = 'p.barcode_value = ' . $param;
        $conditions[] = 'p.sku = ' . $param;
        $params[$param] = $candidate;
    }

    $priorityCase = [];
    foreach ($candidates as $index => $candidate) {
        $param = ':code' . $index;
        $priorityCase[] = 'WHEN p.barcode_value = ' . $param . ' THEN ' . (200 - $index);
        $priorityCase[] = 'WHEN p.sku = ' . $param . ' THEN ' . (100 - $index);
    }

    $sql = "
        SELECT
            p.id,
            p.sku,
            p.name,
            p.unit,
            p.barcode_value,
            p.barcode_type,
            p.is_active,
            p.reorder_level,
            p.sell_price,
            p.supplier_id,
            s.name AS supplier_name,
            COALESCE(stock_summary.current_stock, p.quantity_on_hand, 0) AS current_stock
        FROM products p
        LEFT JOIN suppliers s ON s.id = p.supplier_id
        LEFT JOIN (
            SELECT m.product_id, " . inventory_stock_expression('m') . " AS current_stock
            FROM inventory_movements m
            GROUP BY m.product_id
        ) stock_summary ON stock_summary.product_id = p.id
        WHERE " . ($activeOnly ? 'p.is_active = 1 AND ' : '') . '(' . implode(' OR ', $conditions) . ")
        ORDER BY CASE " . implode(' ', $priorityCase) . " ELSE 0 END DESC, p.id DESC
        LIMIT 1
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        return null;
    }

    $row['id'] = (int)$row['id'];
    $row['is_active'] = (int)$row['is_active'];
    $row['reorder_level'] = (int)$row['reorder_level'];
    $row['current_stock'] = (int)$row['current_stock'];
    $row['sell_price'] = $row['sell_price'] !== null ? (float)$row['sell_price'] : null;
    $row['supplier_id'] = $row['supplier_id'] !== null ? (int)$row['supplier_id'] : null;
    return $row;
}

function inventory_get_product(PDO $pdo, int $productId): ?array
{
    $stmt = $pdo->prepare("
        SELECT
            p.id,
            p.sku,
            p.name,
            p.unit,
            p.barcode_value,
            p.barcode_type,
            p.is_active,
            p.reorder_level,
            p.sell_price,
            p.supplier_id,
            s.name AS supplier_name
        FROM products p
        LEFT JOIN suppliers s ON s.id = p.supplier_id
        WHERE p.id = :id
        LIMIT 1
    ");
    $stmt->execute([':id' => $productId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        return null;
    }

    $row['id'] = (int)$row['id'];
    $row['is_active'] = (int)$row['is_active'];
    $row['reorder_level'] = (int)$row['reorder_level'];
    $row['sell_price'] = $row['sell_price'] !== null ? (float)$row['sell_price'] : null;
    $row['supplier_id'] = $row['supplier_id'] !== null ? (int)$row['supplier_id'] : null;
    $row['current_stock'] = inventory_current_stock($pdo, $productId);
    return $row;
}

function inventory_create_movement(
    PDO $pdo,
    int $productId,
    string $movementType,
    int $quantity,
    ?float $unitCost = null,
    string $referenceType = 'MANUAL',
    ?int $referenceId = null,
    ?string $notes = null,
    ?int $createdBy = null,
    bool $preventNegative = true
): array {
    $movementType = strtoupper(trim($movementType));
    if (!in_array($movementType, ['IN', 'OUT', 'ADJUST'], true)) {
        throw new InvalidArgumentException('Invalid movement type.');
    }

    if ($movementType === 'ADJUST') {
        if ($quantity === 0) {
            throw new InvalidArgumentException('Adjustment quantity cannot be zero.');
        }
    } elseif ($quantity <= 0) {
        throw new InvalidArgumentException('Quantity must be greater than zero.');
    }

    $ownsTransaction = !$pdo->inTransaction();
    if ($ownsTransaction) {
        $pdo->beginTransaction();
    }

    try {
        $currentStock = inventory_current_stock($pdo, $productId);
        $projectedStock = $currentStock;

        if ($movementType === 'IN') {
            $projectedStock += $quantity;
        } elseif ($movementType === 'OUT') {
            $projectedStock -= $quantity;
        } elseif ($movementType === 'ADJUST') {
            $projectedStock += $quantity;
        }

        if ($preventNegative && $projectedStock < 0) {
            throw new RuntimeException('Not enough stock to complete this movement.');
        }

        $stmt = $pdo->prepare("
            INSERT INTO inventory_movements
                (product_id, movement_type, quantity, unit_cost, reference_type, reference_id, notes, created_by)
            VALUES
                (:product_id, :movement_type, :quantity, :unit_cost, :reference_type, :reference_id, :notes, :created_by)
        ");
        $stmt->execute([
            ':product_id' => $productId,
            ':movement_type' => $movementType,
            ':quantity' => $quantity,
            ':unit_cost' => $unitCost,
            ':reference_type' => $referenceType,
            ':reference_id' => $referenceId,
            ':notes' => $notes,
            ':created_by' => $createdBy,
        ]);

        $movementId = (int)$pdo->lastInsertId();
        $syncedStock = inventory_sync_product_quantity($pdo, $productId);

        if ($ownsTransaction) {
            $pdo->commit();
        }

        return [
            'movement_id' => $movementId,
            'current_stock' => $syncedStock,
        ];
    } catch (Throwable $e) {
        if ($ownsTransaction && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function supplier_orders_feature_ready(PDO $pdo): bool
{
    return inventory_table_exists($pdo, 'supplier_orders') && inventory_table_exists($pdo, 'supplier_order_items');
}

function supplier_order_load(PDO $pdo, int $orderId): ?array
{
    if (!supplier_orders_feature_ready($pdo)) {
        return null;
    }

    $stmt = $pdo->prepare("
        SELECT
            so.*,
            s.name AS supplier_name,
            s.contact_person,
            s.phone,
            s.email,
            s.address AS supplier_address,
            s.city AS supplier_city,
            s.country AS supplier_country
        FROM supplier_orders so
        INNER JOIN suppliers s ON s.id = so.supplier_id
        WHERE so.id = :id
        LIMIT 1
    ");
    $stmt->execute([':id' => $orderId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function supplier_order_items(PDO $pdo, int $orderId): array
{
    if (!supplier_orders_feature_ready($pdo)) {
        return [];
    }

    $stmt = $pdo->prepare("
        SELECT
            soi.id,
            soi.order_id,
            soi.product_id,
            soi.quantity_ordered,
            soi.quantity_received,
            soi.unit_cost,
            (soi.quantity_ordered - soi.quantity_received) AS quantity_remaining,
            p.sku,
            p.name,
            p.unit,
            p.barcode_value,
            COALESCE(stock_summary.current_stock, p.quantity_on_hand, 0) AS current_stock
        FROM supplier_order_items soi
        INNER JOIN products p ON p.id = soi.product_id
        LEFT JOIN (
            SELECT m.product_id, " . inventory_stock_expression('m') . " AS current_stock
            FROM inventory_movements m
            GROUP BY m.product_id
        ) stock_summary ON stock_summary.product_id = p.id
        WHERE soi.order_id = :order_id
        ORDER BY p.name ASC, soi.id ASC
    ");
    $stmt->execute([':order_id' => $orderId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function supplier_order_refresh_status(PDO $pdo, int $orderId): string
{
    $stmt = $pdo->prepare("
        SELECT
            COALESCE(SUM(quantity_ordered), 0) AS ordered_total,
            COALESCE(SUM(quantity_received), 0) AS received_total
        FROM supplier_order_items
        WHERE order_id = :order_id
    ");
    $stmt->execute([':order_id' => $orderId]);
    $totals = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

    $ordered = (int)($totals['ordered_total'] ?? 0);
    $received = (int)($totals['received_total'] ?? 0);

    $status = 'SCHEDULED';
    if ($ordered > 0 && $received >= $ordered) {
        $status = 'RECEIVED';
    } elseif ($received > 0) {
        $status = 'PARTIAL';
    }

    $up = $pdo->prepare('UPDATE supplier_orders SET status = :status WHERE id = :id LIMIT 1');
    $up->execute([
        ':status' => $status,
        ':id' => $orderId,
    ]);

    return $status;
}

function supplier_order_create(
    PDO $pdo,
    int $supplierId,
    ?string $expectedArrivalAt,
    ?string $referenceNo,
    ?string $notes,
    array $items,
    int $createdBy
): array {
    if (!supplier_orders_feature_ready($pdo)) {
        throw new RuntimeException('Supplier order tables are not installed yet. Run schema_update.sql first.');
    }

    if ($supplierId <= 0) {
        throw new InvalidArgumentException('Supplier is required.');
    }

    if (!$items) {
        throw new InvalidArgumentException('Add at least one product line for the supplier order.');
    }

    $ownsTransaction = !$pdo->inTransaction();
    if ($ownsTransaction) {
        $pdo->beginTransaction();
    }

    try {
        do {
            $orderCode = 'PO-' . date('Ymd') . '-' . random_int(100000, 999999);
            $check = $pdo->prepare('SELECT id FROM supplier_orders WHERE order_code = :code LIMIT 1');
            $check->execute([':code' => $orderCode]);
        } while ($check->fetch(PDO::FETCH_ASSOC));

        $stmt = $pdo->prepare("
            INSERT INTO supplier_orders
                (order_code, supplier_id, expected_arrival_at, reference_no, notes, status, created_by)
            VALUES
                (:order_code, :supplier_id, :expected_arrival_at, :reference_no, :notes, 'SCHEDULED', :created_by)
        ");
        $stmt->execute([
            ':order_code' => $orderCode,
            ':supplier_id' => $supplierId,
            ':expected_arrival_at' => $expectedArrivalAt,
            ':reference_no' => $referenceNo,
            ':notes' => $notes,
            ':created_by' => $createdBy,
        ]);
        $orderId = (int)$pdo->lastInsertId();

        $itemStmt = $pdo->prepare("
            INSERT INTO supplier_order_items
                (order_id, product_id, quantity_ordered, quantity_received, unit_cost)
            VALUES
                (:order_id, :product_id, :quantity_ordered, 0, :unit_cost)
            ON DUPLICATE KEY UPDATE
                quantity_ordered = quantity_ordered + VALUES(quantity_ordered),
                unit_cost = COALESCE(VALUES(unit_cost), unit_cost)
        ");

        foreach ($items as $item) {
            $itemStmt->execute([
                ':order_id' => $orderId,
                ':product_id' => (int)$item['product_id'],
                ':quantity_ordered' => (int)$item['quantity_ordered'],
                ':unit_cost' => $item['unit_cost'] !== null ? (float)$item['unit_cost'] : null,
            ]);
        }

        supplier_order_refresh_status($pdo, $orderId);

        if ($ownsTransaction) {
            $pdo->commit();
        }

        return [
            'order_id' => $orderId,
            'order_code' => $orderCode,
        ];
    } catch (Throwable $e) {
        if ($ownsTransaction && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function supplier_order_receive(PDO $pdo, int $orderId, array $receiveLines, int $createdBy, ?string $note = null): array
{
    if (!supplier_orders_feature_ready($pdo)) {
        throw new RuntimeException('Supplier order tables are not installed yet. Run schema_update.sql first.');
    }

    $ownsTransaction = !$pdo->inTransaction();
    if ($ownsTransaction) {
        $pdo->beginTransaction();
    }

    try {
        $order = supplier_order_load($pdo, $orderId);
        if (!$order) {
            throw new RuntimeException('Supplier order not found.');
        }
        if ((string)$order['status'] === 'CANCELLED') {
            throw new RuntimeException('Cancelled supplier orders cannot be received.');
        }

        $lineStmt = $pdo->prepare("
            SELECT id, order_id, product_id, quantity_ordered, quantity_received, unit_cost
            FROM supplier_order_items
            WHERE id = :id AND order_id = :order_id
            LIMIT 1
        ");

        $updateLineStmt = $pdo->prepare("
            UPDATE supplier_order_items
            SET quantity_received = quantity_received + :qty
            WHERE id = :id
            LIMIT 1
        ");

        $receivedTotal = 0;

        foreach ($receiveLines as $lineId => $qty) {
            $lineId = (int)$lineId;
            $qty = (int)$qty;

            if ($lineId <= 0 || $qty <= 0) {
                continue;
            }

            $lineStmt->execute([
                ':id' => $lineId,
                ':order_id' => $orderId,
            ]);
            $line = $lineStmt->fetch(PDO::FETCH_ASSOC);

            if (!$line) {
                continue;
            }

            $remaining = (int)$line['quantity_ordered'] - (int)$line['quantity_received'];
            if ($remaining <= 0) {
                continue;
            }
            if ($qty > $remaining) {
                throw new RuntimeException('Receive quantity is greater than remaining quantity for one of the order lines.');
            }

            inventory_create_movement(
                $pdo,
                (int)$line['product_id'],
                'IN',
                $qty,
                $line['unit_cost'] !== null ? (float)$line['unit_cost'] : null,
                'PURCHASE',
                $orderId,
                $note ?: ('Received from supplier order ' . (string)$order['order_code']),
                $createdBy,
                false
            );

            $updateLineStmt->execute([
                ':qty' => $qty,
                ':id' => $lineId,
            ]);

            $receivedTotal += $qty;
        }

        if ($receivedTotal <= 0) {
            throw new RuntimeException('Enter at least one receive quantity greater than zero.');
        }

        $newStatus = supplier_order_refresh_status($pdo, $orderId);

        if ($ownsTransaction) {
            $pdo->commit();
        }

        return [
            'order' => supplier_order_load($pdo, $orderId),
            'status' => $newStatus,
            'received_total' => $receivedTotal,
        ];
    } catch (Throwable $e) {
        if ($ownsTransaction && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function delivery_load(PDO $pdo, int $deliveryId): ?array
{
    $stmt = $pdo->prepare("
        SELECT
            d.*,
            c.name AS customer_name,
            u.full_name AS driver_name
        FROM deliveries d
        LEFT JOIN customers c ON c.id = d.customer_id
        LEFT JOIN users u ON u.id = d.assigned_driver_id
        WHERE d.id = :id
        LIMIT 1
    ");
    $stmt->execute([':id' => $deliveryId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function delivery_items(PDO $pdo, int $deliveryId): array
{
    $stmt = $pdo->prepare("
        SELECT
            di.id,
            di.product_id,
            di.quantity,
            di.unit_price,
            p.sku,
            p.name,
            p.unit,
            p.barcode_value,
            COALESCE(stock_summary.current_stock, p.quantity_on_hand, 0) AS current_stock
        FROM delivery_items di
        INNER JOIN products p ON p.id = di.product_id
        LEFT JOIN (
            SELECT m.product_id, " . inventory_stock_expression('m') . " AS current_stock
            FROM inventory_movements m
            GROUP BY m.product_id
        ) stock_summary ON stock_summary.product_id = p.id
        WHERE di.delivery_id = :delivery_id
        ORDER BY p.name ASC, di.id ASC
    ");
    $stmt->execute([':delivery_id' => $deliveryId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function delivery_stock_already_deducted(PDO $pdo, int $deliveryId): bool
{
    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM inventory_movements
        WHERE reference_type = 'DELIVERY'
          AND reference_id = :reference_id
          AND movement_type = 'OUT'
    ");
    $stmt->execute([':reference_id' => $deliveryId]);
    return (int)$stmt->fetchColumn() > 0;
}

function delivery_route_stats(PDO $pdo, int $deliveryId): array
{
    $stmt = $pdo->prepare("
        SELECT
            COUNT(*) AS point_count,
            MIN(recorded_at) AS first_recorded_at,
            MAX(recorded_at) AS last_recorded_at
        FROM delivery_locations
        WHERE delivery_id = :delivery_id
    ");
    $stmt->execute([':delivery_id' => $deliveryId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

    return [
        'point_count' => (int)($row['point_count'] ?? 0),
        'first_recorded_at' => $row['first_recorded_at'] ?? null,
        'last_recorded_at' => $row['last_recorded_at'] ?? null,
    ];
}

function delivery_latest_location(PDO $pdo, int $deliveryId): ?array
{
    $stmt = $pdo->prepare("
        SELECT lat, lng, accuracy_m, speed_mps, heading_deg, recorded_at
        FROM delivery_locations
        WHERE delivery_id = :delivery_id
        ORDER BY recorded_at DESC, id DESC
        LIMIT 1
    ");
    $stmt->execute([':delivery_id' => $deliveryId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function delivery_update_status(PDO $pdo, int $deliveryId, string $newStatus, int $actorUserId): array
{
    $allowed = ['PENDING', 'ASSIGNED', 'PICKED_UP', 'IN_TRANSIT', 'DELIVERED', 'CANCELLED'];
    $newStatus = strtoupper(trim($newStatus));

    if (!in_array($newStatus, $allowed, true)) {
        throw new InvalidArgumentException('Invalid delivery status.');
    }

    $ownsTransaction = !$pdo->inTransaction();
    if ($ownsTransaction) {
        $pdo->beginTransaction();
    }

    try {
        $delivery = delivery_load($pdo, $deliveryId);
        if (!$delivery) {
            throw new RuntimeException('Delivery not found.');
        }

        $stockDeducted = false;
        if ($newStatus === 'DELIVERED' && !delivery_stock_already_deducted($pdo, $deliveryId)) {
            $items = delivery_items($pdo, $deliveryId);
            if (!$items) {
                throw new RuntimeException('This delivery has no products/items yet.');
            }

            foreach ($items as $item) {
                $available = inventory_current_stock($pdo, (int)$item['product_id']);
                if ((int)$item['quantity'] > $available) {
                    throw new RuntimeException('Not enough stock for ' . (string)$item['name'] . ' (need ' . (int)$item['quantity'] . ', have ' . $available . ').');
                }
            }

            foreach ($items as $item) {
                inventory_create_movement(
                    $pdo,
                    (int)$item['product_id'],
                    'OUT',
                    (int)$item['quantity'],
                    null,
                    'DELIVERY',
                    $deliveryId,
                    'Delivered via ' . ((string)($delivery['delivery_code'] ?? ('delivery #' . $deliveryId))),
                    $actorUserId,
                    true
                );
            }

            $stockDeducted = true;
        }

        $sets = ['status = :status'];
        if ($newStatus === 'PICKED_UP') {
            $sets[] = 'picked_up_at = COALESCE(picked_up_at, NOW())';
        }
        if ($newStatus === 'IN_TRANSIT') {
            $sets[] = 'picked_up_at = COALESCE(picked_up_at, NOW())';
            $sets[] = 'in_transit_at = COALESCE(in_transit_at, NOW())';
        }
        if ($newStatus === 'DELIVERED') {
            $sets[] = 'picked_up_at = COALESCE(picked_up_at, NOW())';
            $sets[] = 'in_transit_at = COALESCE(in_transit_at, NOW())';
            $sets[] = 'delivered_at = COALESCE(delivered_at, NOW())';
        }

        $stmt = $pdo->prepare('UPDATE deliveries SET ' . implode(', ', $sets) . ' WHERE id = :id LIMIT 1');
        $stmt->execute([
            ':status' => $newStatus,
            ':id' => $deliveryId,
        ]);

        if ($ownsTransaction) {
            $pdo->commit();
        }

        return [
            'delivery' => delivery_load($pdo, $deliveryId),
            'stock_deducted' => $stockDeducted,
        ];
    } catch (Throwable $e) {
        if ($ownsTransaction && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}