<?php

namespace apexl\Io\operators;

use apexl\entityCore\enums\Casts;
use apexl\entityCore\interfaces\EntityOperatorInterface;
use apexl\Io\dto\EntityField;
use apexl\Io\exceptions\RecordNotFoundException;
use apexl\Io\includes\Hook;
use apexl\Io\services\Database;
use apexl\Vault\enums\SortDirection;
use apexl\Vault\exceptions\ExecutionException;
use apexl\Vault\interfaces\driver;
use apexl\Vault\interfaces\operators;
use apexl\Vault\Vault;
use app\vendor\apexl\vault\src\Vault\exceptions\VaultException;
use CuyZ\Valinor\Mapper\MappingError;
use CuyZ\Valinor\Mapper\Source\Source;
use CuyZ\Valinor\MapperBuilder;
use Exception;

class entityDatabaseOperator implements EntityOperatorInterface
{
    protected Vault $vault;
    protected array $columns = [];
    protected string $defaultOperator;
    protected string $accessControlType = 'default';

    public function __construct(
        protected string $dbTable,
        protected string $primaryKey = 'id',
        protected string $operatorVault = '',
        protected $extends = null
    ) {
        $this->vault = Vault::getInstance();
        $this->defaultOperator = $this->vault->getDefaultVault();
    }

    /**
     * Set the primary key field
     * @param $key
     * @return $this
     */
    public function setPrimaryKey($key): entityDatabaseOperator
    {
        $this->primaryKey = $key;

        return $this;
    }

    public function setAccessControlType(string $type): entityDatabaseOperator
    {
        $this->accessControlType = $type;

        return $this;
    }

    /**
     * @throws ExecutionException
     * @throws VaultException
     */
    public function loadMultiple(
        array $conditions = [],
        array $orderBy = [],
        ?int $limit = null,
        ?int $offset = null,
    ): array {
        $this->useOperatorVault();
        $query = $this->genericQuery('entityLoadMultiple');
        // Do access control pass

        foreach ($conditions as $condition) {
            $operator = $condition[2] ?? '=';
            $type = $condition[3] ?? 'AND';
            $postCondition = $condition[4] ?? '';
            $query->where($condition[0], $condition[1], $operator, $type, $postCondition);
        }

        /** @var driver $query */
        $query = Hook::processHook('entityLoadMultipleQueryExecute', $query, $this->dbTable, $this->extends);

        if (!empty($orderBy)) {
            if (!is_array($orderBy)) {
                //convert to an array
                $temp = $orderBy;
                $orderBy = [];
                $orderBy[0] = $temp;
            }

            for ($i = 0; $i < 12; $i += 2) { // Allow multiple Order By clauses
                if (isset($orderBy[$i]) && isset($orderBy[$i + 1])) {
                    $direction = $orderBy[$i + 1] ?? SortDirection::DESC;
                    $query->orderBy($orderBy[$i], $direction);
                }
            }
        }

        if ($limit !== null) {
            $query->limit($limit, $offset ?? 0);
        }

        $results = $query->execute()->fetchAll();
        $this->useOperatorVault(true);

        return $results;
    }

    /**
     * @throws VaultException
     */
    public function useOperatorVault($reset = false): void
    {
        if ($this->operatorVault !== '') {
            if ($reset) {
                $this->setOperatorVault($this->defaultOperator);

                return;
            }

            $this->setOperatorVault($this->operatorVault);
        }
    }

    /**
     * @throws VaultException
     */
    public function setOperatorVault($vaultName): entityDatabaseOperator
    {
        $this->vault->setDefaultVault($vaultName);

        return $this;
    }

    protected function genericQuery($hook = 'entityLoad', $skipAccessCheck = false): driver
    {
        //clone here to prevent access checks interfering.
        $query = clone $this->vault->select($this->dbTable);
        $queryFields = Hook::processHook($hook . 'Fields', [$this->dbTable => ['*']], $this->dbTable, $this->extends);
        $fields = [];
        foreach ($queryFields as $table => $queryField) {
            foreach ($queryField as $tableColumn) {
                $fields[] = str_contains((string) $tableColumn, '.') ? $tableColumn : $table . '.' . $tableColumn;
            }
        }
        $query->fields($fields);
        if (!$skipAccessCheck) {
            $query = Hook::processHook('accessControlJoins', $query, $this->accessControlType, $this->dbTable);
        }
        $query = Hook::processHook(
            'permissionRestrictViewEntities',
            $query,
            $this->dbTable,
            $this->getCurrentEntityName()
        );
        $query = Hook::processHook($hook . 'Query', $query, $this->dbTable, $this->extends);
        if (!$skipAccessCheck) {
            $query = Hook::processHook('accessControlClauses', $query, $this->accessControlType, $this->dbTable);
        }

        return $query;
    }

    protected function getCurrentEntityName(): string
    {
        $instantiatedClass = get_called_class();

        return str_ireplace(
            "operator",
            "",
            substr($instantiatedClass, strrpos($instantiatedClass, '\\') + 1)
        );
    }

    /**
     * Function to return the total number of entity records available
     * @throws VaultException
     */
    public function totalEntities($conditions = []): int
    {
        $this->useOperatorVault();
        $query = $this->vault->select($this->dbTable)->fields(
            sprintf('count(%s.%s) AS count', $this->dbTable, $this->primaryKey)
        );
        $query = Hook::processHook('accessControlJoins', $query, $this->accessControlType, $this->dbTable);
        $query = Hook::processHook(
            'permissionRestrictViewEntities',
            $query,
            $this->dbTable,
            $this->getCurrentEntityName()
        );
        $query = Hook::processHook('entityLoadMultipleQuery', $query, $this->dbTable, $this->extends, true);
        $query = Hook::processHook('accessControlClauses', $query, $this->accessControlType, $this->dbTable);
        if (!empty($conditions)) {
            foreach ($conditions as $condition) {
                $operator = $condition[2] ?? '=';
                $type = $condition[3] ?? 'AND';
                $postCondition = $condition[4] ?? '';
                $query->where($condition[0], $condition[1], $operator, $type, $postCondition);
            }
        }
        $query = Hook::processHook('entityLoadMultipleQueryExecute', $query, $this->dbTable, $this->extends, true);
        $result = $query->execute()->fetchAssoc();
        $this->useOperatorVault(true);

        return $result['count'];
    }

    /**
     * Function to store entity data - Accounts for metadata storage on the entity
     * @throws Exception
     */
    public function store(array $data): bool|array
    {
        $this->useOperatorVault();
        $meta = null;
        //first, are we inserting or updating
        if (empty($data)) {
            throw new Exception("Cannot store empty entity data");
        }
        //store and strip metadata if its available. Allows entities to contain processing and maintain easy store methods.
        if (isset($data['_metadata'])) {
            //we need to store this for update methods
            $meta = $data['_metadata'];
            unset($data['_metadata']);
        }
        //check for any extra fields that we don't have in the schema
        //make sure we know what the table supports
        $this->getTableColumns();
        $extraStore = [];
        foreach ($data as $field => $value) {
            if (!isset($this->columns[$field])) {
                $extraStore[$field] = $value;
                //remove it from data for now.
                unset($data[$field]);
            } elseif (is_bool($value)) {
                //add a bool check as we're already looping.
                //allow us to reset to bool after, in case this is important.
                //allow us to reset to bool after, in case this is important.
                //allow us to reset to bool after, in case this is important.
                //allow us to reset to bool after, in case this is important.
                $extraStore[$field] = $value;
                $data[$field] = (int) $value;
            }
        }

        $entity = [];
        if (isset($data[$this->primaryKey]) && !empty($data[$this->primaryKey])) {
            //we're storing a record, skip the access check to prevent data store issues.
            $entity = $this->cleanLoad($data[$this->primaryKey]);
        }
        if (isset($entity['data']) && !empty($entity['data'])) {
            //not empty? we have a record, so update.
            $data = Hook::processHook('preEntityUpdate', $data, $this->dbTable, $extraStore, $this->extends);
            $this->useOperatorVault();
            $this->vault->update($this->dbTable)->fields($data)->where(
                $this->primaryKey,
                $data[$this->primaryKey]
            )->execute();
            $this->useOperatorVault(true);
            Hook::processHook('postEntityUpdate', $data, $this->dbTable, $data[$this->primaryKey]);

            return true;
        } else {
            $data = Hook::processHook('preEntityInsert', $data, $this->dbTable, $extraStore, $this->extends);
            $this->useOperatorVault();
            //we're inserting to force an entity update in case we have a new primary key.
            $this->vault->insert($this->dbTable)->fields($data)->execute();
            $lastStored = $this->getLastNewRecord(true);
            $this->useOperatorVault(true);

            //force the local entity to be updated with the new data (id etc)
            $updatedData = $this->load($lastStored[$this->primaryKey], true)['data'];
            if ($meta) {
                $updatedData['_metadata'] = $meta;
            }
            //re-add any extra fields for use by other modules.
            if ($extraStore !== []) {
                foreach ($extraStore as $field => $value) {
                    $data[$field] = $value;
                }
            }
            $this->useOperatorVault(true);
            Hook::processHook(
                sprintf('postEntityInsert:%s', $this->dbTable),
                $data,
                $lastStored[$this->primaryKey]
            );
            Hook::processHook('postEntityInsert', $data, $this->dbTable, $lastStored[$this->primaryKey]);

            return ['updateEntityData' => true, 'data' => $updatedData];
        }
    }

    /**
     * Describe method to generate known  table columns for validation and safe data storage
     * @throws VaultException
     */
    public function getTableColumns(): array
    {
        $this->useOperatorVault();
        if (!$this->columns) {
            $columns = $this->describe();
            //build the data column.
            foreach ($columns as $col) {
                $this->columns[$col->Field] = $col;
            }
        }
        $this->useOperatorVault(true);

        return $this->columns;
    }

    protected function describe()
    {
        return Database::describe($this->dbTable);
    }

    /**
     * Query for loading entity data by id, but skipping all hook and access control calls.
     * @throws VaultException
     * @throws ExecutionException
     */
    public function cleanLoad($id): array
    {
        $this->useOperatorVault();
        $query = clone $this->vault->select($this->dbTable);
        $entity = $query->fields()->where($this->dbTable . '.' . $this->primaryKey, $id)->execute()->fetchAssoc();

        $this->useOperatorVault(true);

        return ['updateEntityData' => true, 'data' => $entity];
    }

    /**
     * Function to get the last created record for the given entity (by DB table)
     * @throws VaultException
     * @throws ExecutionException
     */
    public function getLastNewRecord(bool $skipAccess = false): array
    {
        $this->useOperatorVault();
        $query = $this->genericQuery();
        $result = $query->orderBy($this->primaryKey, SortDirection::DESC)
            ->limit(1)
            ->execute()
            ->fetchAssoc();
        if (!$skipAccess) {
            $result = Hook::processHook('entityAccess', $result, $this->dbTable);
        }
        $this->useOperatorVault(true);

        return $result;
    }

    /**
     * Query for loading entity data by id
     * @throws VaultException
     */
    public function load(int|string $id, bool $skipAccessCheck = false): array
    {
        $this->useOperatorVault();
        $query = $this->genericQuery('entityLoad', $skipAccessCheck);
        $query->where($this->dbTable . '.' . $this->primaryKey, $id);
        $query = Hook::processHook('entityLoadQueryExecute', $query, $this->dbTable, $this->extends);
        $entity = $query->execute()->fetchAssoc();
        if (!$skipAccessCheck) {
            $entity = Hook::processHook('entityAccess', $entity, $this->dbTable);
        }

        $this->useOperatorVault(true);

        return ['updateEntityData' => true, 'data' => $entity];
    }

    /**
     * @throws RecordNotFoundException
     */
    public function loadBy(string $field, mixed $value, bool $skipAccessCheck = false): array
    {
        $exception = null;
        try {
            $record = $this->vault
                ->select($this->dbTable)
                ->fields([$this->primaryKey])
                ->where($field, $value)
                ->execute()->fetch();

            if ($record) {
                return $this->load($record->{$this->primaryKey}, $skipAccessCheck);
            }
        } catch (Exception $exception) {
        }

        throw new RecordNotFoundException(
            sprintf(
                'No record found for %s with [%s => %s]',
                $this::class,
                $field,
                $value,
            ),
            $exception ? $exception->getCode() : 404,
            $exception,
        );
    }

    /**
     * Function to delete data by ID
     * @throws VaultException
     * @throws ExecutionException
     */
    public function delete(int|string $id): operators
    {
        $this->useOperatorVault();
        $deleted = $this->vault->delete($this->dbTable)->where($this->primaryKey, $id)->execute();
        $this->useOperatorVault(true);

        return $deleted;
    }

    /**
     * Function to load all data available
     * @throws VaultException
     * @throws ExecutionException
     */
    public function loadAll(): array
    {
        $this->useOperatorVault();
        $query = $this->vault->select($this->dbTable);
        $result = $query->execute()->fetchAll();
        $result = Hook::processHook('entityAccess', $result, $this->dbTable);
        $this->useOperatorVault(true);

        return $result;
    }

    public function entityFields(): array
    {
        $fields = [];

        try {
            $describe = Database::describe($this->dbTable);

            foreach ($describe as $fieldData) {
                $type = $fieldData->Type;
                if (preg_match('/^([a-z]+)(?:\(([\d,]+)\))?/', $type, $match)) {
                    [, $type, $length] = array_pad($match, 3, null);
                }
                $type = strtolower($type);
                $fields[] = (new MapperBuilder())->mapper()->map(
                    EntityField::class,
                    Source::array([
                        'field' => $fieldData->Field,
                        'sqlType' => $type,
                        'required' => strtoupper($fieldData->Null) === 'NO',
                        'length' => $length ?? null,
                        'type' => match ($type) {
                            'date' => Casts::DATE,
                            'datetime' => Casts::DATETIME,
                            'int', 'smallint', 'mediumint', 'bigint' => Casts::INT,
                            'tinyint' => Casts::BOOL,
                            'float', 'decimal' => Casts::FLOAT,
                            default => Casts::STRING,
                        },
                    ])
                );
            }
        } catch (ExecutionException|MappingError $e) {
            logger('error')->warning('Unable to fetch table description: {message}', [
                'message' => $e->getMessage(),
                'exception' => $e,
            ]);
        }

        return $fields;
    }
}
