<?php

namespace classes;

use classes\CRUD\CRUDTrait;
use Exception;


/**
 * PartsInvestigation class is responsible for managing and interacting with data related to parts
 * investigation through various database operations. It provides functionality for retrieving,
 * updating, and creating records within related tables such as `pi_header`, `pi_body`, and `pi_detail`.
 */
class PartsInvestigation
{
    use CRUDTrait;

    protected MysqliVeu $database;
    private array $piHeaderDetails;
    private string $piReferenceCode;

    /**
     * Constructor for the class.
     *
     * @param MysqliVeu $database An instance of the MysqliVeu database connection.
     * @return void
     */
    public function __construct(MysqliVeu $database)
    {
        $this->database = $database;
        $this->setDatabaseConnection($database);
    }

    /**
     * Retrieves the PI header details.
     *
     * @return array An array containing the PI header details.
     */
    public function getPiHeaderDetails(): array
    {
        return $this->piHeaderDetails;
    }

    /**
     * Retrieves the Pi reference code.
     *
     * @return string The Pi reference code.
     */
    public function getPiReferenceCode(): string
    {
        return $this->piReferenceCode;
    }

    /**
     * Updates the PI header with the given data or calculates default data
     * from the `pi_detail` table based on the current PI reference.
     *
     * @param string|null $currentUser The username of the current user making the update. Defaults to an empty string.
     * @param array|null $updateDataArray An array of custom data to update the PI header. Defaults to an empty array.
     * @throws Exception
     */
    public function updatePiHeader(?string $currentUser = '', ?array $updateDataArray = []): void
    {
        // Gets values from pi_detail table
        $piDetailQuery = 'SELECT 
                SUM(expected_qty) AS expected_qty,
                SUM(difference) AS difference,
                SUM(actual_qty) AS actual_qty,
                SUM(expected_qty = 0 AND difference > 0 AND (deleted_date IS NULL OR deleted_by IS NULL)) AS serial_not_exist,
                SUM(expected_qty = 0 AND difference > 0) AS incorrect_location
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND (deleted_date IS NULL OR deleted_by IS NULL)';

        $piDetailParams = [$this->piReferenceCode];
        $piDetailTableResult = $this->database->queryParamsRow($piDetailQuery, $piDetailParams);

        // Creates update data array conditionally
        $piHeaderUpdateData = [
            'last_updated_date' => 'NOW()',
            'last_updated_by' => $currentUser
        ];

        if (empty($updateDataArray))
        {
            $standardPiHeaderUpdateData = [
                'actual_case_qty' => $piDetailTableResult['actual_qty'],
                'expected_case_qty' => $piDetailTableResult['expected_qty'],
                'difference' => $piDetailTableResult['difference'],
                'serial_not_exist' => $piDetailTableResult['serial_not_exist'],
                'incorrect_location' => $piDetailTableResult['incorrect_location'],
                'document_status_id' => Document::getDocumentStatusId('in progress', $this->database)
            ];
            $piHeaderUpdateData = array_merge($piHeaderUpdateData, $standardPiHeaderUpdateData);
        }
        else
        {
            $piHeaderUpdateData = array_merge($piHeaderUpdateData, $updateDataArray);
        }

        if (empty($this->piHeaderDetails['pi_reference']))
        {
            $piHeaderUpdateData['pi_reference'] = $this->piReferenceCode;
        }

        // Updates varience value conditionally
        $itemCost = $this->getItemCost();
        if (!empty($itemCost))
        {
            $varienceValue = $piDetailTableResult['difference'] * $this->getItemCost();
            $piHeaderUpdateData['varience_value'] = round($varienceValue, 2);
        }
        else
        {
            $piHeaderUpdateData['varience_value'] = null;
        }

        // Sets where conditions
        $whereConditions = ['id', '=', $this->piHeaderDetails['id']];

        // Updates pi_header record
        $this->update('pi_header', $piHeaderUpdateData, $whereConditions);

        // Updated PI header details on object
        $this->fetchPiHeaderDetailsByPiReference($this->piReferenceCode);
    }

    /**
     * Retrieves the details of a PI header based on the provided part number and prepares
     * a PI reference code if it does not already exist.
     *
     * @param string $partNumber The part number used to fetch the PI header details.
     * @return void
     * @throws Exception
     */
    public function fetchPiHeaderDetailsByPartNumber(string $partNumber): void
    {
        $sql = "SELECT
                *
            FROM pi_header
            WHERE 
                part_number = ?
                AND document_status_id NOT IN (?)
            ORDER BY id DESC";

        $params = [
            $partNumber,
            Document::getDocumentStatusId('closed', $this->database)
        ];

        $this->piHeaderDetails = $this->database->queryParamsRow($sql, $params);

        if (empty($this->piHeaderDetails))
        {
            RDTResponse::fail("No open document for part number: $partNumber");
        }
        elseif (empty($this->piHeaderDetails['pi_reference'])) // Creates PI Reference Code
        {
            $this->piReferenceCode = $this->createPiReferenceCode($this->piHeaderDetails['id']);
        }
        else
        {
            $this->piReferenceCode = $this->piHeaderDetails['pi_reference'];
        }
    }

    /**
     * Generates a PI reference code based on the provided PI header ID.
     *
     * @param int $piHeaderId The identifier for the PI header used to generate the reference code.
     * @return string Returns the generated PI reference code.
     */
    private function createPiReferenceCode(int $piHeaderId, string $prefix = "PI"): string
    {
        return Helpers::createReferenceCode($prefix, $piHeaderId);
    }

    /**
     * Fetches the details of a PI header using the provided PI reference.
     *
     * @param string $piReference The PI reference used to retrieve the PI header details.
     * @return void
     */
    public function fetchPiHeaderDetailsByPiReference(string $piReference): void
    {
        $this->piReferenceCode = $piReference;

        $sql = "SELECT *
            FROM pi_header
            WHERE pi_reference = ?";

        $params = [$this->piReferenceCode];
        $this->piHeaderDetails = $this->database->queryParamsRow($sql, $params);
    }

    /**
     * Retrieves the details of a PI body based on the provided PI body ID and the current PI reference code.
     *
     * @param int $piBodyId The ID of the PI body used to fetch its details.
     * @return array|null Returns an associative array containing the PI body details if found,
     * or null if no match is
     */
    public function getPiBodyDetailsById(int $piBodyId): ?array
    {
        $sql = "SELECT *
            FROM pi_body
            WHERE
                pi_reference = ?
                AND id = ?";

        $params = [
            $this->piReferenceCode,
            $piBodyId
        ];

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

    /**
     * Retrieves the PI detail record based on the provided serial reference and current PI reference code.
     *
     * @param string $serialReference The serial reference used to fetch the PI detail record.
     * @return array|null Returns an associative array containing the PI detail record if found
     * or null if no recordis found.
     */
    public function getPiDetailRecordBySerialReference(string $serialReference): ?array
    {
        $sql = "SELECT *
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND serial_reference = ?";

        $params = [
            $this->piReferenceCode,
            $serialReference
        ];

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

    public function getPiDetailRecordBySerialReferenceAndPiBodyId(string $serialReference, int $piBodyId): ?array
    {
        $sql = 'SELECT *
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND serial_reference = ?
                AND pi_body_id = ?';

        $params = [
            $this->piReferenceCode,
            $serialReference,
            $piBodyId
        ];

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

    /**
     * Retrieves the details of PI records associated with a specific PI body ID and PI reference code.
     *
     * @param int $piBodyId The ID of the PI body used to filter the detail records.
     * @return array|null An array of PI detail records if found, or null if no records exist.
     */
    public function getPiDetailRecordsByPiBodyId(int $piBodyId): ?array
    {
        $sql = "SELECT *
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND pi_body_id = ?
            ORDER BY inventory_date_created DESC";

        $params = [
            $this->piReferenceCode,
            $piBodyId
        ];

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

    /**
     * Creates a new PI detail record in the database and associates it with the current PI reference code.
     *
     * @param array $dataArray The array of data used to create the PI detail record.
     *                         Includes specific values and is augmented with the PI reference code.
     * @return int The ID of the newly created PI detail record.
     * @throws Exception
     */
    public function createPiDetailRecord(array $dataArray): int
    {
        $table = 'pi_detail';

        // Adds values to data array
        $dataArray['pi_reference'] = $this->piReferenceCode;

        return $this->create($table, $dataArray);
    }

    /**
     * Fetches all PI body records associated with the current PI reference code
     * and part number from the database.
     *
     * @return array|null An array of PI body records if found, or null if no records exist.
     */
    public function getAllPiBodyRecords(): ?array
    {
        $sql = "SELECT *
            FROM pi_body
            WHERE
                pi_reference = ?
                AND part_number = ?";

        $params = [
            $this->piReferenceCode,
            $this->piHeaderDetails['part_number']
        ];

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

    /**
     * Retrieves all PI detail records associated with the provided PI body ID
     * and the current PI reference code.
     *
     * @param int $piBodyId The ID of the PI body used to filter the PI detail records.
     * @return array|null An array of PI detail records if found, or null if no records are available.
     */
    public function getAllPiDetailRecordsByPiBodyId(int $piBodyId): ?array
    {
        $sql = "SELECT *
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND pi_body_id = ?";

        $params = [
            $this->piReferenceCode,
            $piBodyId
        ];

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

    /**
     * Checks if there are any unscanned records associated with the current PI reference.
     *
     * @return bool Returns true if there are unscanned records, otherwise false.
     */
    public function hasPiDetailUnscannedRecords(): bool
    {
        $sql = "SELECT COUNT(*) AS unscanned_records
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND scanned_status = 0";

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

        return $result['unscanned_records'] > 0;
    }

    /**
     * Updates all PI body records by aggregating data from the PI detail table
     * and applying the necessary adjustments, including setting appropriate
     * document statuses.
     *
     * @param string $currentUser The username of the current user performing the update,
     *                            used to track the last updated user for the records.
     * @return void
     * @throws Exception
     */
    public function updateAllPiBodyRecords(string $currentUser): void
    {
        // Gets values from pi_detail table
        $piDetailQuery = "SELECT 
                SUM(expected_qty > 0) AS expected_qty,
                SUM(actual_qty <> 0) - SUM(expected_qty > 0) AS difference,
                SUM(actual_qty <> 0) AS actual_qty,
                SUM(actual_qty >= 0 AND difference > 0 AND (deleted_date IS NULL OR deleted_by IS NULL)) AS serial_not_exist,
                SUM(expected_qty = 0 AND difference > 0) AS incorrect_location,
                pi_body_id
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND (deleted_date IS NULL OR deleted_by IS NULL)
            GROUP BY pi_body_id";

        $piDetailParams = [$this->piReferenceCode];
        $piDetailTableResult = $this->database->queryParamsArray($piDetailQuery, $piDetailParams);

        $closedDocumentStatusId = Document::getDocumentStatusId('closed', $this->database);

        foreach ($piDetailTableResult as $row)
        {
            $updateData = [
                'expected_qty' => $row['expected_qty'],
                'difference' => $row['difference'],
                'actual_qty' => $row['actual_qty'],
                'serial_not_exist' => $row['serial_not_exist'],
                'incorrect_location' => $row['incorrect_location'],
                'last_updated_date' => 'NOW()',
                'last_updated_by' => $currentUser
            ];

            // Sets document status closed for pi body record
            if ($row['difference'] >= 0)
            {
                $updateData['document_status_id'] = $closedDocumentStatusId;
            }

            $whereConditions = ['id', '=', $row['pi_body_id']];
            $this->updatePiBodyRecord($updateData, $whereConditions);
        }
    }

    /**
     * Updates a record in the PI body table based on the given data and conditions.
     *
     * @param array $dataArray The array containing the column-value pairs to update in the PI body table.
     * @param array $whereConditions An optional array of conditions to filter the records that need to be updated. If not provided, the default condition will be based on the current PI reference code.
     * @return bool Returns true if the update operation is successful, otherwise false.
     * @throws Exception
     */
    public function updatePiBodyRecord(array $dataArray, array $whereConditions = []): bool
    {
        if (!is_array($whereConditions[0])) {
            $whereConditions = [$whereConditions];
        }

        $table = 'pi_body';
        $whereConditions[] = ['pi_reference', '=', $this->piReferenceCode];

        return $this->update($table, $dataArray, $whereConditions);
    }

    /**
     * Updates a PI body record identified by the location code with the provided data
     * and recalculates the quantity and status details based on associated PI details.
     *
     * @param string $locationCode The location code used to identify the PI body record.
     * @param array $updateData Data to be merged and updated in the PI body record.
     * @return void
     * @throws Exception
     */
    public function updatePiBodyRecordByLocation(string $locationCode, array $updateData): void
    {
        $piBodyDetails = $this->getPiBodyDetailsByLocationCode($locationCode);

        // Gets counts from pi_detail for pi_body_id
        $sql = "SELECT
                SUM(expected_qty > 0) AS expected_qty,
                SUM(difference) AS difference,
                SUM(actual_qty <> 0) AS actual_qty,
                SUM(expected_qty = 0 AND actual_qty = 0 AND difference = 0) AS serial_not_exist,
                SUM(expected_qty = 0 AND difference > 0) AS incorrect_location
            FROM pi_detail
            WHERE
                pi_reference = ?
                AND pi_body_id = ?";

        $params = [
            $this->piReferenceCode,
            $piBodyDetails['id']
        ];

        $piDetailCounts = $this->database->queryParamsRow($sql, $params);

        $piBodyUpdateData = [
            'expected_qty' => $piDetailCounts['expected_qty'],
            'difference' => $piDetailCounts['difference'],
            'actual_qty' => $piDetailCounts['actual_qty'],
            'serial_not_exist' => $piDetailCounts['serial_not_exist'],
            'incorrect_location' => $piDetailCounts['incorrect_location']
        ];

        $updateData = array_merge($piBodyUpdateData, $updateData);
        $whereConditions = [
            ['id', '=', $piBodyDetails['id']],
        ];

        $this->updatePiBodyRecord($updateData, $whereConditions);
    }

    /**
     * Retrieves the details of a PI body based on the provided location code,
     * the current PI reference code, and the part number from the PI header details.
     *
     * @param string $locationCode The location code used to fetch the PI body details.
     * @return array|null An associative array containing the PI body details if found, or null if no matching record exists.
     */
    public function getPiBodyDetailsByLocationCode(string $locationCode): ?array
    {
        $sql = "SELECT *
            FROM pi_body
            WHERE
                pi_reference = ?
                AND part_number = ?
                AND location_code = ?";

        $params = [
            $this->piReferenceCode,
            $this->piHeaderDetails['part_number'],
            $locationCode
        ];

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

    /**
     * Creates a new record in the PI body table using the provided data array,
     * supplemented with additional information derived from the current PI reference
     * and header details.
     *
     * @param array $dataArray The data used to create the PI body record, excluding
     *                         internally generated fields.
     * @return int|null The ID of the newly created PI body record, or null if the
     *                  operation fails.
     * @throws Exception
     */
    public function createPiBodyRecord(array $dataArray): ?int
    {
        $table = 'pi_body';

        // Adds values to data array
        $dataArray['pi_reference'] = $this->piReferenceCode;
        $dataArray['part_number'] = $this->piHeaderDetails['part_number'];
        $dataArray['pi_header_id'] = $this->piHeaderDetails['id'];

        // Returns last inserted id
        return $this->create($table, $dataArray);
    }

    /**
     * Updates a record in the PI detail table based on the provided data and conditions.
     *
     * @param array $dataArray An associative array containing the column names and values to be updated.
     * @param array $whereConditions Optional array of conditions to identify the record(s) to update. Defaults to an empty array.
     * @return bool Returns true if the update was successful, otherwise false.
     * @throws Exception
     */
    public function updatePiDetailRecord(array $dataArray, array $whereConditions = []): bool
    {
        $table = 'pi_detail';
        $whereConditions[] = ['pi_reference', '=', $this->piReferenceCode];

        return $this->update($table, $dataArray, $whereConditions);
    }

    /**
     * Deletes a specific PI detail record based on the provided PI body ID and serial reference.
     *
     * @param int $piBodyId The ID of the PI body associated with the detail record.
     * @param string $serialReference The serial reference used to identify the detail record.
     * @return void
     * @throws Exception
     */
    public function deletePiDetailRecordBySerialReference(int $piBodyId, string $serialReference): void
    {
        $sql = "DELETE FROM pi_detail
            WHERE 
                pi_reference = ?
                AND pi_body_id = ?
                AND serial_reference = ?";

        $params = [
            $this->piReferenceCode,
            $piBodyId,
            $serialReference
        ];

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

    public function getPiLocationsWithDifferences(): ?array
    {
        $sql = "SELECT 
                location_code
            FROM
                pi_body
            WHERE
                (
                    SELECT COUNT(id)
                        FROM pi_detail
                        WHERE
                            difference <> 0
                            AND pi_detail.pi_body_id = pi_body.id
                ) > 0
                AND pi_reference = ?
            GROUP BY location_code
            ORDER BY location_code";

        $params = [$this->piReferenceCode];

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

    private function getItemCost(): ?float
    {
        $sql = 'SELECT item_cost FROM part WHERE part_number = ?';
        $result = $this->database->queryParamsRow($sql, [$this->piHeaderDetails['part_number']]);

        return (float) $result['item_cost'];
    }
}
