<?php

declare(strict_types=1);

namespace PpPhp\Core;

/**
 * Search API – full control over search panel, advanced search, and filtering.
 *
 * Mirrors PHPRunner's SearchClause while adding:
 *  - Fluent interface (setters return $this)
 *  - JSON export/import (ideal for AJAX)
 *  - Automatic reading of per‑field search settings from project.json
 *  - Helper methods for quick WHERE clause generation
 *
 * Usage:
 *   $srch = Search::forTable('orders');
 *   $srch->setFieldValue('status', 'shipped')
 *        ->setSearchOption('status', 'equals')
 *        ->setFieldValue('total', '100')
 *        ->setSecondFieldValue('total', '500')
 *        ->setSearchOption('total', 'between');
 *
 *   $where = $srch->buildWhereClause();
 *   $rows = DB::Query("SELECT * FROM orders {$where}");
 */
class Search
{
    private string $table;
    private array $fields        = [];   // field => value
    private array $secondValues  = [];   // field => second value (BETWEEN)
    private array $options       = [];   // field => 'contains'|'equals'|'starts'|'greater'|'less'|'between'
    private array $customSql     = [];   // field => raw SQL expression
    private bool $searchStarted  = false;
    private string $allFieldsValue = '';

    // ── Factory ───────────────────────────────────────────────────────

    /**
     * Create a Search instance for a specific table.
     */
    public static function forTable(string $table): self
    {
        return new self($table);
    }

    private function __construct(string $table)
    {
        $this->table = $table;
    }

    // ── Field values ──────────────────────────────────────────────────

    /** Get the current value of a search field, or null if not set. */
    public function getFieldValue(string $field): ?string
    {
        return $this->fields[$field] ?? null;
    }

    /**
     * Set a search value for a field.
     * @return $this
     */
    public function setFieldValue(string $field, string $value): self
    {
        $this->fields[$field] = $value;
        $this->searchStarted = true;
        return $this;
    }

    /** Get the second value (BETWEEN). */
    public function getSecondFieldValue(string $field): ?string
    {
        return $this->secondValues[$field] ?? null;
    }

    /**
     * Set the second value for BETWEEN.
     * @return $this
     */
    public function setSecondFieldValue(string $field, string $value): self
    {
        $this->secondValues[$field] = $value;
        return $this;
    }

    // ── Search options ────────────────────────────────────────────────

    /** Get the search option for a field (default 'contains'). */
    public function getSearchOption(string $field): string
    {
        return $this->options[$field] ?? 'contains';
    }

    /**
     * Set the search option for a field.
     * Supported: 'contains', 'equals', 'starts', 'greater', 'less', 'between'
     * @return $this
     */
    public function setSearchOption(string $field, string $option): self
    {
        $allowed = ['contains', 'equals', 'starts', 'greater', 'less', 'between'];
        if (in_array($option, $allowed)) {
            $this->options[$field] = $option;
        }
        return $this;
    }

    // ── Custom SQL ────────────────────────────────────────────────────

    /**
     * Replace the generated search expression with custom SQL.
     * Use with caution – the SQL is not escaped.
     * @return $this
     */
    public function setSearchSQL(string $field, string $sql): self
    {
        $this->customSql[$field] = $sql;
        return $this;
    }

    // ── All fields search ─────────────────────────────────────────────

    /** Get the "All fields" search value. */
    public function getAllFieldsValue(): string
    {
        return $this->allFieldsValue;
    }

    /**
     * Set the "All fields" search value.
     * @return $this
     */
    public function setAllFieldsValue(string $value): self
    {
        $this->allFieldsValue = $value;
        return $this;
    }

    // ── Status / reset ────────────────────────────────────────────────

    /** Check if a search was started by the user. */
    public function isSearchStarted(): bool
    {
        return $this->searchStarted;
    }

    /** Clear all search conditions. */
    public function resetSearch(): self
    {
        $this->fields        = [];
        $this->secondValues  = [];
        $this->options       = [];
        $this->customSql     = [];
        $this->allFieldsValue = '';
        $this->searchStarted = false;
        return $this;
    }

    // ── WHERE clause builder ──────────────────────────────────────────

    /**
     * Build a SQL WHERE clause from the current search conditions.
     * Returns empty string if no conditions are set.
     */
    public function buildWhereClause(): string
    {
        $clauses = [];

        foreach ($this->fields as $field => $value) {
            if (isset($this->customSql[$field])) {
                $clauses[] = '(' . $this->customSql[$field] . ')';
                continue;
            }

            $option = $this->getSearchOption($field);
            $clauses[] = $this->buildCondition($field, $value, $option);
        }

        if (!empty($this->allFieldsValue)) {
            $allClauses = [];
            foreach ($this->getSearchableFields() as $f) {
                $allClauses[] = "`$f` LIKE '%" . addslashes($this->allFieldsValue) . "%'";
            }
            $clauses[] = '(' . implode(' OR ', $allClauses) . ')';
        }

        return !empty($clauses) ? ' WHERE ' . implode(' AND ', $clauses) : '';
    }

    private function buildCondition(string $field, string $value, string $option): string
    {
        $escaped = addslashes($value);

        return match ($option) {
            'equals'    => "`$field` = '$escaped'",
            'contains'  => "`$field` LIKE '%$escaped%'",
            'starts'    => "`$field` LIKE '$escaped%'",
            'greater'   => "`$field` > '$escaped'",
            'less'      => "`$field` < '$escaped'",
            'between'   => $this->buildBetween($field),
            default     => "`$field` LIKE '%$escaped%'",
        };
    }

    private function buildBetween(string $field): string
    {
        $v1 = addslashes($this->fields[$field] ?? '');
        $v2 = addslashes($this->secondValues[$field] ?? $v1);
        return "`$field` BETWEEN '$v1' AND '$v2'";
    }

    // ── Field list from project settings ──────────────────────────────

    /**
     * Return the list of fields that are searchable according to the Fields module
     * configuration stored in project.json.
     *
     * If no saved config is found, returns all fields that currently have values.
     */
    private function getSearchableFields(): array
    {
        // Try to load search settings from the project state
        $state = ProjectState::load($this->table);
        $searchSettings = $state['fields']['byTable'][$this->table]['searchSettings'] ?? [];

        if (!empty($searchSettings)) {
            $searchable = [];
            foreach ($searchSettings as $field => $settings) {
                if ($settings['searchable'] ?? true) {
                    $searchable[] = $field;
                }
            }
            if (!empty($searchable)) {
                return $searchable;
            }
        }

        // Fallback – all fields that currently have a value
        return array_keys($this->fields);
    }

    // ── JSON support (AJAX) ───────────────────────────────────────────

    /**
     * Export the current search state as a JSON‑serializable array.
     */
    public function toArray(): array
    {
        return [
            'table'          => $this->table,
            'fields'         => $this->fields,
            'secondValues'   => $this->secondValues,
            'options'        => $this->options,
            'customSql'      => $this->customSql,
            'allFieldsValue' => $this->allFieldsValue,
            'searchStarted'  => $this->searchStarted,
        ];
    }

    /**
     * Import search state from an array (e.g. from AJAX).
     */
    public static function fromArray(array $data): self
    {
        $srch = new self($data['table'] ?? 'unknown');
        $srch->fields         = $data['fields'] ?? [];
        $srch->secondValues   = $data['secondValues'] ?? [];
        $srch->options        = $data['options'] ?? [];
        $srch->customSql      = $data['customSql'] ?? [];
        $srch->allFieldsValue = $data['allFieldsValue'] ?? '';
        $srch->searchStarted  = $data['searchStarted'] ?? !empty($srch->fields);
        return $srch;
    }

    /**
     * Import search state from a JSON string.
     */
    public static function fromJson(string $json): self
    {
        $data = json_decode($json, true) ?? [];
        return self::fromArray($data);
    }
}