<?php

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

namespace IvyForms\Repository;

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

use IvyForms\Common\Exceptions\InvalidArgumentException;

/**
 * Class AbstractRepository
 *
 * @package IvyForms\Repository
 */
class AbstractRepository
{
    public const FACTORY = '';

    /** @var string */
    protected string $table;

    /** @var mixed */
    protected $wpdb;

    public function __construct(string $table)
    {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table = $table;
    }

    /**
     * @param int $id
     * @return object|null
     *
     */
    public function getById(int $id): ?object
    {
        $row =  $this->wpdb->get_row(
            $this->wpdb->prepare(
                $this->selectQuery() . " WHERE $this->table.id = %d",
                $id
            ),
            ARRAY_A
        );

        if (!$row) {
            return null;
        }

        return call_user_func([static::FACTORY, 'create'], $row);
    }

    /**
     * @return mixed[]
     * @throws InvalidArgumentException
     */
    public function getAll(): array
    {
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare($this->selectQuery()),
            ARRAY_A
        );

        $result = [];

        foreach ($rows as $row) {
            $result[] = call_user_func([static::FACTORY, 'create'], $row);
        }

        return $result;
    }

    public function delete(int $id): int
    {
        return $this->wpdb->query(
            $this->wpdb->prepare(
                "DELETE FROM $this->table WHERE id = %d",
                $id
            )
        );
    }

    /**
     * @param array<string, mixed>|null $params
     * @return array<string, mixed>
     */
    public function search(?array $params): array
    {
        $queryParams = [];
        $whereClauses = $this->buildWhereClauses($params, $queryParams);

        $query = $this->buildSearchQuery($whereClauses, $params, $queryParams);
        $results = $this->wpdb->get_results($this->wpdb->prepare($query, $queryParams));

        $total = $this->getTotalCount($whereClauses, $queryParams);

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

    /**
     * @param array<string, mixed>|null $params
     * @param array<int|string, mixed> $queryParams
     * @return string
     */
    private function buildWhereClauses(?array $params, array &$queryParams): string
    {
        $whereClauses = ['1=1'];

        $this->addSearchClauses($params, $whereClauses, $queryParams);
        $this->addFilterClauses($params, $whereClauses, $queryParams);
        $this->addDateRangeClauses($params['dateRange'] ?? [], $whereClauses, $queryParams);

        return implode(' AND ', $whereClauses);
    }

    /**
     * @param array<string, mixed>|null $params
     * @param array<int, string> $whereClauses
     * @param array<int|string, mixed> $queryParams
     */
    private function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
    {
        if (!empty($params['search'])) {
            $searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%';
            $searchableColumns = $this->getSearchableColumns();
            $searchConditions = array_map(fn($col) => "$col LIKE %s", $searchableColumns);
            $whereClauses[] = '(' . implode(' OR ', $searchConditions) . ')';
            foreach ($searchableColumns as $col) {
                $queryParams[] = $searchEscaped;
            }
        }
    }

    /**
     * @param array<string, mixed>|null $params
     * @param array<int, string> $whereClauses
     * @param array<int|string, mixed> $queryParams
     */
    private 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) {
                    $whereClauses[] = "{$key} = %s";
                    $queryParams[] = sanitize_text_field($value);
                }
            }
        }
    }

    /**
     * @param array<int, string> $dateRange
     * @param array<int, string> $whereClauses
     * @param array<int|string, mixed> $queryParams
     */
    private function addDateRangeClauses(array $dateRange, array &$whereClauses, array &$queryParams): void
    {
        $dateColumn = $this->getDateColumn();

        if (!empty($dateRange[0])) {
            $whereClauses[] = "$dateColumn >= %s";
            $queryParams[] = gmdate('Y-m-d H:i:s', strtotime($dateRange[0]));
        }

        if (!empty($dateRange[1])) {
            $whereClauses[] = "$dateColumn <= %s";
            $queryParams[] = gmdate('Y-m-d H:i:s', strtotime($dateRange[1]));
        }
    }

    /**
     * @param string $whereClauses
     * @param array<string, mixed>|null $params
     * @param array<int|string, mixed> $queryParams
     * @return string
     */
    private function buildSearchQuery(string $whereClauses, ?array $params, array &$queryParams): string
    {
        $sortableColumns = $this->getSortableColumns();
        $sortBy = in_array($params['orderBy'] ?? 'id', $sortableColumns, true) ? $params['orderBy'] : 'id';
        $order = strtolower($params['order'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';
        $page = max(($params['page'] ?? 1), 1);
        $perPage = max(($params['perPage'] ?? 10), 1);
        $offset = ($page - 1) * $perPage;

        $queryParams[] = $perPage;
        $queryParams[] = $offset;

        return $this->selectQuery() . " WHERE {$whereClauses} ORDER BY {$sortBy} {$order} LIMIT %d OFFSET %d";
    }

    /**
     * @param string $whereClauses
     * @param array<int|string, mixed> $queryParams
     * @return int
     */
    private function getTotalCount(string $whereClauses, array $queryParams): int
    {
        // Exclude only LIMIT and OFFSET, keeping filter parameters intact
        $countParams = $queryParams;
        if (isset($queryParams[count($queryParams) - 2]) && isset($queryParams[count($queryParams) - 1])) {
            $countParams = array_slice($queryParams, 0, -2);
        }

        $countQuery = "SELECT COUNT(*) FROM $this->table WHERE {$whereClauses}";
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log("Total Count Query: " . $countQuery);
            error_log("Total Count Query Params: " . json_encode($countParams));
        }
        return (int)$this->wpdb->get_var($this->wpdb->prepare($countQuery, $countParams));
    }

    public function selectQuery(): string
    {
        return "SELECT * FROM " . $this->table;
    }

    /**
     * Start a database transaction.
     *
     * @return bool
     */
    public function beginTransaction(): bool
    {
        return (bool) $this->wpdb->query('START TRANSACTION');
    }

    /**
     * Commit a database transaction.
     *
     * @return bool
     */
    public function commit(): bool
    {
        return (bool) $this->wpdb->query('COMMIT');
    }

    /**
     * Rollback a database transaction.
     *
     * @return bool
     */
    public function rollback(): bool
    {
        return (bool) $this->wpdb->query('ROLLBACK');
    }

    /**
     * @return string[]
     */
    protected function getSearchableColumns(): array
    {
        return ['id'];
    }

    /**
     * @return array<string, string>
     */
    protected function getFilterableColumns(): array
    {
        return [];
    }

    /**
     * @return string[]
     */
    protected function getSortableColumns(): array
    {
        return ['id'];
    }

    /**
     * @return string
     */
    protected function getDateColumn(): string
    {
        return 'dateCreated';
    }
}