Custom pricing system for corporate clients

<?php

/**
 * Amusement park ticketing system — corporate client custom pricing.
 * Corporate client entity extending the base Client entity to support
 * contract-based accounts with personalized, per-product pricing.
 * Anonymized/condensed for portfolio purposes (unused getters/setters
 * and table-creation methods omitted for brevity).
 *
 * Note: on login, a corporate client's cart and checkout automatically
 * reflect their own negotiated rates rather than standard pricing —
 * this class holds the pricing data and lookups that logic relies on.
 *
 * Architectural Note & Self-Review (Portfolio context):
 * Built following the Active Record pattern to integrate with the project's existing internal framework.
 * Identified refactoring targets for future iteration:
 * - Decouple persistence logic into a dedicated Repository (SRP / Clean Architecture).
 * - Replace dynamic associative arrays with strongly-typed DTOs/Value Objects for payload safety.
 * - Add ID filtering / chunking to `getAllPricingGrouped()` to optimize performance.
 */

namespace AppShop;

use AppDB;
use PDO;

class CorporateClient extends Client
{
    private string $tableName = TABLE_PREFIX . 'shop_corporate_client';
    private string $tableNamePricing = TABLE_PREFIX . 'shop_corporate_client_pricing';

    private int $clientId;
    private string $logo;
    private int $paymentDelay;
    private int $companyType;
    private array $customPricing = [];

    // ... standard getters/setters omitted for brevity ...

    /**
     * Fetch this client's custom price for a single product, if one exists.
     */
    public function getCustomPriceForProduct(int $productId): object|bool
    {
        $sql = 'SELECT * FROM ' . $this->tableNamePricing . '
                WHERE clientId = :clientId AND productId = :productId';

        $result = DB::exec($sql, [':clientId' => $this->clientId, ':productId' => $productId]);

        return $result ? $result->fetch(PDO::FETCH_OBJ) : false;
    }

    /**
     * Fetch all custom prices for a given client, indexed by product ID
     * for fast lookup when rendering the cart/catalog.
     */
    public function getPricingIndexedByProduct(): array
    {
        $sql = 'SELECT * FROM ' . $this->tableNamePricing . ' WHERE clientId = :clientId';

        $result = DB::exec($sql, [':clientId' => $this->clientId]);
        if (!$result) {
            return [];
        }

        return array_column($result->fetchAll(PDO::FETCH_OBJ), null, 'productId');
    }

    /**
     * Bulk insert/update custom pricing for this client.
     * Uses ON DUPLICATE KEY UPDATE to upsert in a single query rather than
     * checking existence per product.
     */
    public function saveCustomPricing(): bool
    {
        if (empty($this->customPricing)) {
            return false;
        }

        $valuesSql = [];
        $params = [];

        foreach ($this->customPricing as $key => $price) {
            $clientParam = ":clientId_{$key}";
            $productParam = ":productId_{$key}";
            $priceIncParam = ":priceIncVat_{$key}";
            $priceExcParam = ":priceExcVat_{$key}";
            $vatParam = ":vatRate_{$key}";

            $valuesSql[] = "({$clientParam}, {$productParam}, {$priceIncParam}, {$priceExcParam}, {$vatParam})";

            $params[$clientParam] = $this->clientId;
            $params[$productParam] = (int) $price['productId'];
            $params[$priceIncParam] = (float) $price['priceIncVat'];
            $params[$priceExcParam] = (float) $price['priceExcVat'];
            $params[$vatParam] = (float) $price['vatRate'];
        }

        $sql = "INSERT INTO {$this->tableNamePricing} (clientId, productId, priceIncVat, priceExcVat, vatRate)
                VALUES " . implode(', ', $valuesSql) . "
                ON DUPLICATE KEY UPDATE
                    priceIncVat = VALUES(priceIncVat),
                    priceExcVat = VALUES(priceExcVat),
                    vatRate = VALUES(vatRate)";

        if (!DB::exec($sql, $params)) {
            return false;
        }

        appLog('Upserted custom pricing -> clientId: ' . $this->clientId);
        return true;
    }

    /**
     * Fetch all custom pricing across all corporate clients, grouped by client.
     * Used to preload pricing in bulk and avoid N+1 queries when rendering
     * catalog/cart views for multiple clients at once.
     */
    public function getAllPricingGrouped(): array
    {
        $sql = "SELECT clientId, productId, priceIncVat, priceExcVat, vatRate
                FROM {$this->tableNamePricing}";

        $result = DB::exec($sql);
        if (!$result) {
            return [];
        }

        $grouped = [];
        foreach ($result->fetchAll(PDO::FETCH_OBJ) as $row) {
            $grouped[$row->clientId][] = [
                'productId' => $row->productId,
                'priceIncVat' => $row->priceIncVat,
                'priceExcVat' => $row->priceExcVat,
                'vatRate' => $row->vatRate,
            ];
        }

        return $grouped;
    }
}