<?php

namespace classes;

use Exception;

class Inventory
{
    private array $inventoryRecords;
    private array $filters;
    private string $table = "inventory_master";
    private MysqliVeu $database;

    /**
     * Constructor for the class.
     *
     * @param array $filters An array of filters to be applied on inventory_master
     * @return void
     */
    public function __construct(array $whereConditions, MysqliVeu $database)
    {
        $this->database = $database;

        $this->filters = $whereConditions;

        $this->inventoryRecords = $this->fetchInventoryRecords();
    }

    /**
     * Fetches inventory records based on applied filters.
     *
     * @return array An array of inventory records fetched from the database.
     */
    private function fetchInventoryRecords(): array
    {
        $result = $this->buildQueryFromFilters();
        $sql = $result['sql'];
        $params = $result['params'];

        return $this->database->queryParamsArray($sql, $params);
    }

    /**
     * Constructs a SQL query and its parameters based on the specified filters.
     *
     * @return array An associative array containing the SQL query string and its parameters.
     */
    private function buildQueryFromFilters(): array
    {
        $sql = 'SELECT * FROM inventory_master';
        $sql .= ' WHERE ';

        $conditions = [];
        $params = [];

        foreach ($this->filters as $column => $value)
        {
            $conditions[] = "{$column} = ?";
            $params[] = $value;
        }

        $sql .= implode(' AND ', $conditions);

        $sql .= ' ORDER BY date_created';

        return ['sql' => $sql, 'params' => $params];
    }

    /**
     * Retrieves the status ID corresponding to the given inventory status code.
     *
     * @param string $inventoryStatusCode The inventory status code to look up.
     * @return int The ID of the inventory status.
     */
    public static function getStatusIdFromStatusCode(string $inventoryStatusCode, MysqliVeu $database): int
    {
        $inventoryStatusCode = strtoupper($inventoryStatusCode);

        $sql = "SELECT id FROM inventory_status WHERE inventory_status_code = ?";

        ['id' => $inventoryStatusId] = $database->queryParamsRow($sql, [$inventoryStatusCode]);

        return (int) $inventoryStatusId;
    }

    /**
     * Retrieves the inventory records.
     *
     * @return array An array of inventory records.
     */
    public function getInventoryRecords(): ?array
    {
        return $this->inventoryRecords;
    }

    /**
     * Retrieves the first record from the inventory records.
     *
     * @return array The first record in the inventory records.
     */
    public function getInventoryFirstRecord(): ?array
    {
        return $this->inventoryRecords[0];
    }

    /**
     * Retrieves the total quantity of inventory at the specified location.
     *
     * @param string    $locationCode The code of the location for which the inventory quantity is calculated.
     * @param MysqliVeu $database The database connection instance used to execute the query.
     * @return int The total quantity of inventory at the specified location.
     */
    public static function getTotalQuantityOnLocation(string $locationCode, MysqliVeu $database): int {
        $sql = "SELECT SUM(inventory_qty) AS total_qty
                FROM inventory_master
                WHERE location_code = ?";

        $result = $database->queryParamsRow($sql, [$locationCode]);

        return $result['total_qty'];
    }
}
