<?php

use app\services\utilities\Js;
use app\services\utilities\Str;

defined('BASEPATH') or exit('No direct script access allowed');

class App_table
{
    public $ci;
    protected $viewPath;
    protected $view;
    protected $rules = [];
    protected $id;
    protected $tableName;
    protected $outputUsing;
    protected $relatedTo;
    protected $primaryKeyName = 'id';
    protected $customfieldable;
    protected $excludedCustomFields  = ['colorpicker', 'link'];
    protected $customFieldsToRuleMap = [
        'input'            => 'TextRule',
        'number'           => 'NumberRule',
        'select'           => 'SelectRule',
        'multiselect'      => 'MultiSelectRule',
        'checkbox'         => 'CheckboxRule',
        'date_picker'      => 'DateRule',
        'date_picker_time' => 'DateRule',
    ];
    protected static $registered  = [];
    protected static $customRules = [];
    protected static $compatibility;
    protected static $customFields;

    /**
     * Available query operators.
     */
    protected array $operators = [
        'dynamic'          => ['apply_to' => 'DateRule'],
        'equal'            => ['apply_to' => ['TextRule', 'NumberRule', 'DateRule', 'RadioRule', 'SelectRule']],
        'not_equal'        => ['apply_to' => ['TextRule', 'NumberRule', 'DateRule', 'SelectRule']],
        'in'               => ['apply_to' => ['MultiSelectRule', 'CheckboxRule']],
        'not_in'           => ['apply_to' => ['MultiSelectRule', 'CheckboxRule']],
        'less'             => ['apply_to' => ['NumberRule', 'DateRule']],
        'less_or_equal'    => ['apply_to' => ['NumberRule', 'DateRule']],
        'greater'          => ['apply_to' => ['NumberRule', 'DateRule']],
        'greater_or_equal' => ['apply_to' => ['NumberRule', 'DateRule']],
        'between'          => ['apply_to' => ['NumberRule', 'DateRule']],
        'not_between'      => ['apply_to' => ['NumberRule', 'DateRule']],
        'begins_with'      => ['apply_to' => ['TextRule']],
        'not_begins_with'  => ['apply_to' => ['TextRule']],
        'contains'         => ['apply_to' => ['TextRule']],
        'not_contains'     => ['apply_to' => ['TextRule']],
        'ends_with'        => ['apply_to' => ['TextRule']],
        'not_ends_with'    => ['apply_to' => ['TextRule']],
        'is_empty'         => ['apply_to' => []],
        'is_not_empty'     => ['apply_to' => []],
    ];

    /**
     * SQL Operators.
     */
    protected array $operator_sql = [
        'equal'            => ['operator' => '='],
        'not_equal'        => ['operator' => '!='],
        'in'               => ['operator' => 'IN'],
        'not_in'           => ['operator' => 'NOT IN'],
        'less'             => ['operator' => '<'],
        'less_or_equal'    => ['operator' => '<='],
        'greater'          => ['operator' => '>'],
        'greater_or_equal' => ['operator' => '>='],
        'between'          => ['operator' => 'BETWEEN'],
        'not_between'      => ['operator' => 'NOT BETWEEN'],
        'begins_with'      => ['operator' => 'LIKE', 'append' => '%'],
        'not_begins_with'  => ['operator' => 'NOT LIKE', 'append' => '%'],
        'contains'         => ['operator' => 'LIKE', 'prepend' => '%', 'append' => '%'],
        'not_contains'     => ['operator' => 'NOT LIKE', 'prepend' => '%', 'append' => '%'],
        'ends_with'        => ['operator' => 'LIKE', 'prepend' => '%'],
        'not_ends_with'    => ['operator' => 'NOT LIKE', 'prepend' => '%'],
        'is_empty'         => ['operator' => '='],
        'is_not_empty'     => ['operator' => '!='],
    ];

    /**
     * The operator that needs array.
     */
    protected array $needs_array = ['IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'];

    public function __construct($id = null, $viewPath = null)
    {
        $this->ci       = &get_instance();
        $this->id       = $id;
        $this->viewPath = $viewPath ?? $id;

        if (is_null(static::$compatibility)) {
            static::$compatibility = json_decode(get_option('v310_incompatible_tables') ?: '{}', true);
        }
    }

    /**
     * @param App_table $table
     *
     * @return void
     */
    public static function register($table)
    {
        if (! $table->id) {
            throw new Exception('A table must have an ID.');
        }

        if (array_key_exists($table->id, static::$registered)) {
            throw new Exception('A table with ID: "' . $table->id . '" is already registered.');
        }

        static::$registered[$table->id] = $table;

        $table->init();
    }

    protected function init()
    {
        $incompatible         = false;
        $comp_check_key       = 'my_' . $this->viewPath . '.php';
        $comp_check_view_path = VIEWPATH . DIRECTORY_SEPARATOR . 'admin' . DIRECTORY_SEPARATOR . 'tables' . DIRECTORY_SEPARATOR . $comp_check_key;

        if (isset(static::$compatibility[$comp_check_key]) && is_file($comp_check_key)) {
            $incompatible = static::$compatibility[$comp_check_key] == filemtime(
                $comp_check_view_path
            );
        }

        include_once $this->ci->app->get_table_path($this->viewPath, $incompatible === false);
    }

    public function relatedTo($tableId)
    {
        $this->relatedTo = $tableId;

        return $this;
    }

    /**
     * @param string $id
     *
     * @return App_table
     */
    public static function find($id)
    {
        return static::$registered[$id] ?? null;
    }

    public static function new($id, $viewPath = null)
    {
        return new static($id, $viewPath);
    }

    public static function rule($tableId, $rule)
    {
        if (! array_key_exists($tableId, static::$customRules)) {
            static::$customRules[$tableId] = [];
        }

        static::$customRules[$tableId][] = $rule;
    }

    public function getDbTableName()
    {
        return $this->tableName ?? ($this->relatedTo ? static::find($this->relatedTo)->getDbTableName() : $this->id());
    }

    public function setDbTableName($table)
    {
        $this->tableName = $table;

        return $this;
    }

    protected function operatorRequiresArray($operator): bool
    {
        return in_array($operator, $this->needs_array);
    }

    protected function rememberCustomFields()
    {
        if (! is_null(static::$customFields)) {
            return static::$customFields;
        }

        $this->ci->db->where('active', 1);
        $this->ci->db->order_by('field_order', 'asc');

        $result = $this->ci->db->get('customfields')->result_array();

        foreach ($result as $key => $cf) {
            $result[$key]['name'] = _maybe_translate_custom_field_name($cf['name'], $cf['slug']);
        }

        return static::$customFields = collect($result);
    }

    protected function getCustomFields()
    {
        return $this->rememberCustomFields()->filter(function ($field) {
            return $field['fieldto'] == $this->customfieldIdentifier();
        });
    }

    protected function createRulesFromCustomFields()
    {
        $rulesMap = hooks()->apply_filters('table_custom_field_rules_map', $this->customFieldsToRuleMap);
        $isAdmin  = is_admin();

        if (! $this->customfieldable) {
            return [];
        }

        return $this->getCustomFields()->reject(function ($field) use ($rulesMap) {
            return in_array($field['type'], $this->excludedCustomFields)
                || ! array_key_exists($field['type'], $rulesMap);
        })->map(function ($field) use ($rulesMap, $isAdmin) {
            $options = collect(explode(',', $field['options'] ?? ''))
                ->map(fn ($option) => trim($option))
                ->filter()
                ->map(fn ($option) => ['value' => $option, 'label' => $option]);

            $rule = App_table_filter::new(
                $field['slug'],
                $rulesMap[$field['type']]
            )->label($field['name']);

            if (! $isAdmin && $field['only_admin'] == '1') {
                $rule->isVisible(fn () => false);
            }

            if (! empty($options->all())) {
                $rule->options($options);
            }

            $rule->raw(function ($value, $operator, $operatorSql, $ruleInstance, $table) use ($field, $options) {
                $prefix = 'exists';

                if (! empty($options->all())) {
                    if ($ruleInstance->type === 'MultiSelectRule' || $ruleInstance->type === 'CheckboxRule') {
                        $whereValueSql = '(';

                        foreach ($value as $option) {
                            $whereValueSql .= ' value LIKE "%' . $option . '%" OR';
                        }

                        $whereValueSql = trim(substr_replace($whereValueSql, '', -3));
                        $whereValueSql .= ')';
                    } else {
                        $whereValueSql = ' value = ' . $this->wrapValueInQuotes($value);
                    }

                    if ($operator === 'not_in' || $operator === 'not_equal') {
                        $prefix = 'not exists';
                    }
                } else {
                    $valueSqlColumn = 'value';

                    if ($ruleInstance->type === 'NumberRule') {
                        $valueSqlColumn = 'CAST(value as DECIMAL(10, ' . get_decimal_places() . '))';
                    } elseif ($ruleInstance->type === 'DateRule') {
                        $valueSqlColumn = 'CAST(value as DATE)';
                    }

                    $whereValueSql = $this->toSql($value, $operator, $operatorSql, $valueSqlColumn, $ruleInstance);
                }

                return $prefix . $this->customFieldWhereSql($whereValueSql, $table, $field['fieldto'], $field['id']);
            });

            return hooks()->apply_filters('table_' . $this->id() . '_custom_field_rule', $rule, $field);
        })->all();
    }

    protected function customFieldWhereSql($valueSql, $table, $fieldTo, $fieldId)
    {
        return ' (SELECT value FROM ' . db_prefix() . 'customfieldsvalues WHERE fieldto="' . $fieldTo . '" AND fieldid=' . $fieldId . ' and ' . $table . '.' . $this->getPrimaryKeyName() . ' = relid AND ' . $valueSql . ')';
    }

    public function getPrimaryKeyName()
    {
        return $this->primaryKeyName;
    }

    public function setPrimaryKeyName($name)
    {
        $this->primaryKeyName = $name;

        return $this;
    }

    public function findRule($ruleId)
    {
        foreach ($this->rules() as $rule) {
            if ($rule->id == $ruleId) {
                return $rule;
            }
        }
    }

    public function customfieldable($identifier)
    {
        $this->customfieldable = $identifier;

        return $this;
    }

    public function customfieldIdentifier()
    {
        return $this->customfieldable ?? $this->id();
    }

    public function id()
    {
        return $this->id;
    }

    public function setRules($rules)
    {
        $this->rules = $rules;

        return $this;
    }

    public function rules()
    {
        return hooks()->apply_filters(
            'table_' . $this->id() . '_rules',
            array_merge($this->rules, static::$customRules[$this->id()] ?? [], $this->createRulesFromCustomFields())
        );
    }

    public function viewName()
    {
        return $this->view ?? $this->id();
    }

    public function setViewName($view)
    {
        return $this->view = $view;

        return $this;
    }

    protected function allRelatedTables()
    {
        $related = [];

        foreach (static::$registered as $table) {
            if ($table->relatedTo === $this->id() || $this->relatedTo === $table->id()) {
                $related[] = $table;
            }
        }

        return $related;
    }

    public function filters($view = null, $related = true)
    {
        $this->ci->load->model('filters_model');

        $filters = $this->castRules($this->ci->filters_model->get_for_staff(
            $this->id(),
            $view ?? $this->viewName(),
            get_staff_user_id()
        ), $this);

        if ($related) {
            foreach ($this->allRelatedTables() as $table) {
                $filters = array_merge($filters, $this->castRules($table->filters($this->viewName(), false), $table));
            }
        }

        return $filters;
    }

    protected function castRules($filters, $table)
    {
        foreach ($filters as $key => $filter) {
            if (! isset($filter['builder']['rules'])) {
                $filters[$key]['builder']['rules'] = [];
                $filter['builder']['rules']        = [];
            }

            foreach ($filter['builder']['rules'] as $rk => $rule) {
                if ($rule instanceof App_table_filter) {
                    continue;
                }

                if ($ruleInstance = $table->findRule($rule['id'])) {
                    $ruleInstance = clone $ruleInstance;

                    $filters[$key]['builder']['rules'][$rk] = $ruleInstance
                        ->setValue($rule['value'])
                        ->dynamic($rule['has_dynamic_value'])
                        ->setOperator($rule['operator'] ?? null);
                } else {
                    unset($filters[$key]['builder']['rules'][$rk]);
                }
            }
        }

        return $filters;
    }

    protected function getRuleValueForSql($rule)
    {
        $value = $rule['value'];

        if (is_array($value)) {
            $value = array_map(fn ($v) => $this->ci->db->escape_str($v), $value);
        } else {
            $value = $this->ci->db->escape_str($value);
        }

        if ($rule['type'] === 'DateRule') {
            if (filter_var($rule['has_dynamic_value'], FILTER_VALIDATE_BOOL)) {
                if (is_array($value)) {
                    $value = [date('Y-m-d', strtotime($value[0])), date('Y-m-d', strtotime($value[1]))];
                } else {
                    $value = date('Y-m-d', strtotime($value));
                }
            } else {
                if (is_array($value)) {
                    $value = [to_sql_date($value[0]), to_sql_date($value[1])];
                } else {
                    $value = to_sql_date($value);
                }
            }
        }

        return $value;
    }

    public function outputUsing($closure)
    {
        $this->outputUsing = $closure;

        return $this;
    }

    public function filtersJs()
    {
        return Js::from($this->filters());
    }

    public function rulesJs()
    {
        return Js::from($this->rules());
    }

    public function getWhereFromRules()
    {
        $whereSqls    = [];
        $matchType    = $this->ci->input->post('filters')['match_type'] ?? 'and';
        $appliedRules = $this->ci->input->post('filters')['rules'] ?? [];

        $fullTableName = db_prefix() . $this->getDbTableName();

        foreach ($appliedRules as $rule) {
            $ruleInstance = $this->findRule($rule['id'])->dynamic($rule['has_dynamic_value']);

            if (! $ruleInstance) {
                continue;
            }

            $sqlColumn = $ruleInstance->column ?? $ruleInstance->id;

            if (! $ruleInstance->column && ! Str::startsWith($fullTableName . '.', $sqlColumn)) {
                $sqlColumn = $fullTableName . '.' . $sqlColumn;
            }

            $operator    = ($rule['operator'] ?? '') ?: 'equal';
            $operatorSql = $this->operator_sql[$operator] ?? null;

            if ($ruleInstance->hasDynamicValue && ! $operatorSql && $operator === 'dynamic') {
                $operatorSql = ['operator' => '='];
            }

            $value = $this->getRuleValueForSql($rule);

            if (is_callable($ruleInstance->callback)) {
                $whereSqls[] = call_user_func_array($ruleInstance->callback, [
                    $value,
                    $operator,
                    $operatorSql,
                    $ruleInstance,
                    $fullTableName,
                ]);

                continue;
            }

            $whereSqls[] = $this->toSql($value, $operator, $operatorSql, $sqlColumn, $ruleInstance);
        }

        $whereSqls = array_filter($whereSqls);

        foreach ($whereSqls as $key => $sql) {
            if ($key === 0) {
                continue;
            }

            $whereSqls[$key] = $matchType . ' ' . $sql;
        }

        if (count($whereSqls) > 0) {
            return 'AND (' . implode(' ', $whereSqls) . ')';
        }

        return null;
    }

    protected function toSql($value, $operator, $operatorSql, $sqlColumn, $rule)
    {
        if ($rule->type === 'DateRule') {
            $sqlColumn = 'CAST(' . $sqlColumn . ' as DATE)';
        }

        if (
            $this->operatorRequiresArray(
                strtoupper($operatorSql['operator'])
            )
        ) {
            if (
                $operatorSql['operator'] === 'IN'
                || $operatorSql['operator'] === 'NOT IN'
            ) {
                $value = "'" . implode("','", $value) . "'";

                return
                    $sqlColumn .
                    ' ' .
                    $operatorSql['operator'] .
                    ' (' .
                    $value .
                    ')';
            }
            if (
                $operatorSql['operator'] === 'BETWEEN'
                || $operatorSql['operator'] === 'NOT BETWEEN'
            ) {
                if ($value[0] == $value[1]) {
                    return $sqlColumn . ' = ' . $this->wrapValueInQuotes($value[0]);
                }

                return
                    $sqlColumn .
                    ' ' .
                    $operatorSql['operator'] . ' ' .
                    $this->wrapValueInQuotes($value[0]) .
                    ' AND ' .
                    $this->wrapValueInQuotes($value[1]);
            }
        }

        $appendAfter = '';

        if (
            $operatorSql['operator'] === 'LIKE'
            || $operatorSql['operator'] === 'NOT LIKE'
        ) {
            $value       = $this->ci->db->escape_like_str($value);
            $appendAfter = " ESCAPE '!'";
        }

        if (in_array($operator, ['is_empty', 'is_not_empty'])) {
            if ($rule->emptyOperatorValue === null) {
                $operatorSql['operator'] = $operator === 'is_empty' ? 'IS NULL' : 'IS NOT NULL';
                $value                   = null;
            } else {
                $value = $rule->emptyOperatorValue;
            }
        }

        $sql = ($sqlColumn .
                ' ' .
                $operatorSql['operator']) .
            ($value !== null ? $this->wrapValueInQuotes((($operatorSql['prepend'] ?? '') .
                $value .
                ($operatorSql['append'] ?? ''))) : '');

        $sql .= $appendAfter;

        return $sql;
    }

    protected function wrapValueInQuotes($value)
    {
        // If not wrapped in mysql function matches uppercase functions and ( e.q. CAST(
        if (preg_match('/[A-Z]+\(/', $value)) {
            return $value;
        }

        return '"' . $value . '"';
    }

    public function output($params = [])
    {
        $params = apply_filters_deprecated('table_params', [$params, $this->viewPath], '3.0.7', 'table_[TABLE_ID]_output_params');
        $params = hooks()->apply_filters('table_' . $this->id() . '_output_params', $params);

        $closure = $this->outputUsing;

        $closure = $closure->bindTo($this);

        $params = array_merge(['customFieldsColumns' => []], $params);

        echo json_encode($closure($params, $this->rules()));

        exit();
    }
}
