<?php

/**
 * @copyright © TMS-Plugins. All rights reserved.
 * @licence   See LICENCE.md for license details.
 */

namespace IvyForms\Repository\Entry;

// phpcs:disable PSR1.Files.SideEffects
if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly
}

use IvyForms\Common\Exceptions\InvalidArgumentException;
use IvyForms\Common\Exceptions\QueryExecutionException;
use IvyForms\Entity\Entry\Entry;
use IvyForms\Factory\Entry\EntryFactory;
use IvyForms\Repository\AbstractRepository;
use IvyForms\Services\InstallActions\DB\Entry\EntriesTable;
use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable;
use IvyForms\Services\InstallActions\DB\Form\FormsTable;
use IvyForms\Services\Translations\BackendStrings;

class EntryRepository extends AbstractRepository implements EntryRepositoryInterface
{
    public const FACTORY = EntryFactory::class;

    /**
     * Add entry to the database
     *
     * @param Entry $entity
     *
     * @return int
     *
     * @throws QueryExecutionException
     */
    public function add($entity): int
    {
        $data = $entity->toArray(true);

        $result = $this->wpdb->insert(
            $this->table,
            [
                'formId'        => $data['formId'],
                'userId'        => $data['userId'],
                'dateCreated'   => current_time('mysql'),
                'dateEdited'    => current_time('mysql'),
                'status'        => $data['status'],
                'ipAddress'     => $data['ipAddress'],
                'userAgent'     => $data['userAgent'],
                'sourceURL'     => $data['sourceURL'],
                'starred'       => $data['starred'] ? 1 : 0,
            ],
            ['%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d']
        );

        if ($result === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__
            );
        }

        return $this->wpdb->insert_id;
    }

    /**
     * Update entry in the database
     *
     * @param int  $id
     * @param Entry $entity
     *
     * @return bool
     *
     * @throws QueryExecutionException
     */
    public function update(int $id, $entity): bool
    {
        $result = $this->wpdb->update(
            $this->table,
            [
                'dateEdited'  => current_time('mysql'),
            ],
            ['id' => $id],
            ['%s'],
            ['%d']
        );

        if ($result === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__
            );
        }

        return (bool)$result;
    }

    /**
     * Update the starred status of an entry.
     *
     * @param int $entryId
     * @param bool $value
     *
     * @return void
     * @throws QueryExecutionException
     */
    public function updateEntryStarred(int $entryId, bool $value): void
    {
        $result = $this->wpdb->update(
            $this->table,
            [
                'starred' => $value ? 1 : 0,
                'dateEdited' => current_time('mysql'),
            ],
            ['id' => $entryId],
            ['%d', '%s'],
            ['%d']
        );

        if ($result === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_update_starred'] . __CLASS__
            );
        }
    }

    /**
     * Update the status of an entry.
     *
     * @param int $entryId
     * @param string $status
     *
     * @return void
     * @throws QueryExecutionException
     */
    public function updateEntryStatus(int $entryId, string $status): void
    {
        $result = $this->wpdb->update(
            $this->table,
            [
                'status' => $status,
                'dateEdited' => current_time('mysql'),
            ],
            ['id' => $entryId],
            ['%s', '%s'],
            ['%d']
        );

        if ($result === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_update_status'] . __CLASS__
            );
        }
    }

    /**
     * Search for entries with extended logic (field value, form name) and return paginated results with meta.
     *
     * @param array<string, mixed>|null $params
     * @return array<mixed>
     * @throws InvalidArgumentException|QueryExecutionException
     */
    public function search(?array $params): array
    {
        $queryParams = [];
        $whereClauses = $this->buildWhereClauses($params, $queryParams);

        // Always join entry fields and forms for search/filter/sort
        $entriesTable = EntriesTable::getTableName();
        $entryFieldsTable = EntryFieldsTable::getTableName();
        $formsTable = FormsTable::getTableName();
        $join = " LEFT JOIN {$entryFieldsTable} eft ON e.id = eft.entryId ";
        $join .= " LEFT JOIN {$formsTable} f ON e.formId = f.id ";

        // Sorting
        $sortableColumns = $this->getSortableColumns();
        $sortBy = $params['orderBy'] ?? 'id';
        $order = strtolower($params['order'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';
        // Map 'formName' to 'f.name' for sorting
        $sortColumn = 'e.id';
        if ($sortBy === 'formName') {
            $sortColumn = 'f.name';
        } elseif (in_array($sortBy, $sortableColumns, true)) {
            $sortColumn = 'e.' . $sortBy;
        }

        // Pagination
        $page = max(($params['page'] ?? 1), 1);
        $perPage = max(($params['perPage'] ?? 10), 1);
        $offset = ($page - 1) * $perPage;
        $queryParams[] = $perPage;
        $queryParams[] = $offset;

        // Main query
        $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join 
                    WHERE {$whereClauses} 
                    ORDER BY {$sortColumn} {$order} LIMIT %d OFFSET %d";
        $results = $this->wpdb->get_results($this->wpdb->prepare($sql, $queryParams), ARRAY_A);
        if ($results === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
            );
        }

        // Total count for pagination meta (exclude LIMIT/OFFSET)
        $countParams = $queryParams;
        array_pop($countParams); // offset
        array_pop($countParams); // perPage
        $countSql = "SELECT COUNT(DISTINCT e.id) FROM {$entriesTable} e $join WHERE {$whereClauses}";
        $total = (int)$this->wpdb->get_var($this->wpdb->prepare($countSql, $countParams));

        // Fetch form names for all unique formIds in the results
        $formIdToName = [];
        if (!empty($results)) {
            $formIds = array_unique(array_column($results, 'formId'));
            if (!empty($formIds)) {
                $placeholders = implode(',', array_fill(0, count($formIds), '%d'));
                $formSql = "SELECT id, name FROM {$formsTable} WHERE id IN ($placeholders)";
                $formRows = $this->wpdb->get_results($this->wpdb->prepare($formSql, $formIds), ARRAY_A);
                if ($formRows === false) {
                    throw new QueryExecutionException(
                        BackendStrings::getExceptionStrings()['unable_get_form_names'] . __CLASS__
                    );
                }
                foreach ($formRows as $row) {
                    $formIdToName[$row['id']] = $row['name'];
                }
            }
        }

        return [
            'data' => $results,
            'meta' => [
                'page' => $page,
                'perPage' => $perPage,
                'total' => $total,
            ],
            'formIdToName' => $formIdToName,
        ];
    }

    /**
     * Override addSearchClauses to support searching fieldValue and form name.
     * Extracted search conditions to a helper for complexity reduction.
     *
     * @param array<string, mixed>|null $params
     * @param array<string> $whereClauses
     * @param array<int|string, mixed> $queryParams
     *
     * @return void
     */
    protected function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
    {
        if (!empty($params['search'])) {
            $searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%';
            $searchConditions = EntryQueryHelper::getEntrySearchConditions($params);
            $whereClauses[] = '(' . implode(' OR ', $searchConditions) . ')';
            $queryParams = array_merge($queryParams, array_fill(0, count($searchConditions), $searchEscaped));
        }
    }

    /**
     * Override addFilterClauses to prefix columns with the correct alias (e.g., e.)
     * so that dynamic filtering works with JOINs and aliases.
     */
    protected function addFilterClauses(?array $params, array &$whereClauses, array &$queryParams): void
    {
        if (!empty($params['filters'])) {
            $filterableColumns = $this->getFilterableColumns();
            foreach ($params['filters'] as $key => $value) {
                if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') {
                    // Prefix with e. for the entries table
                    $whereClauses[] = "e.{$key} = %s";
                    $queryParams[] = sanitize_text_field($value);
                }
            }
        }
    }

    /**
     * Get the columns that can be searched in the database (including virtual columns for search).
     *
     * @return array<string>
     */
    protected function getSearchableColumns(): array
    {
        return [
            'id',
            'formId',
            'fieldValue',   // virtual, from EntryFields table
            'name',         // virtual, from Forms table
        ];
    }

    /**
     * Get filterable columns
     *
     * @return array<string>
     */
    protected function getFilterableColumns(): array
    {
        return ['starred', 'status', 'formId'];
    }

    /**
     * Get sortable columns
     *
     * @return array<string>
     */
    protected function getSortableColumns(): array
    {
        return ['id', 'dateCreated', 'formName'];
    }

    /**
     * Get the date column
     *
     * @return string
     */
    protected function getDateColumn(): string
    {
        return 'e.dateCreated';
    }

    /**
     * @param int $entryId
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function exists(int $entryId): bool
    {
        $query = $this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->table} WHERE id = %d",
            $entryId
        );

        $count = $this->wpdb->get_var($query);
        if ($count === false) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__
            );
        }
        return (int)$count > 0;
    }

    /**
     * Get the count of entries for a specific form.
     *
     * @param int $formId
     * @return int
     * @throws QueryExecutionException
     */
    public function getCountByFormId(int $formId): int
    {
        $queryData = EntryQueryHelper::getCountByFormIdQuery($this->table, $formId);
        $query = $this->wpdb->prepare($queryData['sql'], $queryData['params']);
        $count = $this->wpdb->get_var($query);
        if ($count === null) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__
            );
        }
        return (int)$count;
    }

    /**
     * Get the count of entries for the filter dropdown.
     *
     * @param array<string, mixed>|null $params
     * @return array<string, int>
     * @throws QueryExecutionException
     */
    public function getFilterCount(?array $params = null): array
    {
        $queryData = EntryQueryHelper::getFilterCountQuery($this->table, $params);
        $result = null;
        if (!empty($queryData['params'])) {
            $result = $this->wpdb->get_row(
                $this->wpdb->prepare($queryData['sql'], ...$queryData['params']),
                ARRAY_A
            );
        }
        if ($result === null) {
            $result = $this->wpdb->get_row($queryData['sql'], ARRAY_A);
        }
        if ($result === null) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
            );
        }
        return array_map('intval', $result ?: [
            'readTrueCount' => 0,
            'readFalseCount' => 0,
            'starredTrueCount' => 0,
            'starredFalseCount' => 0,
        ]);
    }

    /**
     * Get entry fields for the given entries.
     *
     * @param array<int, array<string, mixed>> $entries
     * @return array<mixed>
     *
     * @throws QueryExecutionException|InvalidArgumentException
     */
    public function getEntryFields(array $entries): array
    {
        $queryData = EntryQueryHelper::getEntryFieldsQuery($entries);
        if (empty($queryData['sql'])) {
            return [];
        }
        $results = $this->wpdb->get_results(
            $this->wpdb->prepare($queryData['sql'], $queryData['params']),
            ARRAY_A
        );
        if ($results === null) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_get_entry_fields'] . __CLASS__
            );
        }
        return $results;
    }

    /**
     * Get the count of entries for multiple form IDs.
     *
     * @param int[] $formIds
     * @return array<int, int> formId => count
     * @throws QueryExecutionException
     */
    public function getEntryCountByFormIds(array $formIds): array
    {
        $queryData = EntryQueryHelper::getEntryCountByFormIdsQuery($formIds);
        if (empty($queryData['sql'])) {
            return [];
        }
        $results = $this->wpdb->get_results(
            $this->wpdb->prepare($queryData['sql'], $queryData['params']),
            ARRAY_A
        );
        if ($results === null) {
            throw new QueryExecutionException(
                BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
            );
        }
        $counts = [];
        foreach ($results as $row) {
            $counts[(int)$row['formId']] = (int)$row['count'];
        }
        foreach ($queryData['formIds'] as $id) {
            if (!isset($counts[$id])) {
                $counts[$id] = 0;
            }
        }
        return $counts;
    }
}