<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Repository;

defined('ABSPATH') || exit;

// This repository is the data-access layer for plugin-owned custom tables.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

use CliqThemes\StoreLocator\Models\CustomField;
use CliqThemes\StoreLocator\Support\ColumnInput;

/**
 * Manages admin-defined custom fields and their category scoping, and converts
 * them into the form-field schema consumed by the cliq-ui form renderer.
 */
final class CustomFieldsRepository
{
    /**
     * Field types whose value is a list of independent choices, and so is stored
     * as one store_meta ROW PER VALUE rather than one serialised row.
     *
     * Only `checkboxes` qualifies. `repeater`, `gallery` and `hours` are also
     * arrays, but structured ones — a gallery item is a media object, an hours
     * value is a weekly schedule — and splitting those across rows would destroy
     * their shape. They stay a single JSON row.
     */
    private const MULTI_VALUE_TYPES = ['checkboxes'];

    /** @var array<int, string>|null */
    private static ?array $multiValueKeys = null;

    /**
     * Meta keys that store one row per value.
     *
     * Both writers consult this, so the admin form and the REST/import path
     * cannot end up storing the same field two different ways. Cached per
     * request; {@see forgetMultiValueKeys()} clears it after a field changes.
     *
     * @return array<int, string>
     */
    public static function multiValueKeys(): array
    {
        if (self::$multiValueKeys !== null) {
            return self::$multiValueKeys;
        }

        $keys = [];
        foreach (CustomField::query()->where('is_active', '=', 1)->raw() as $row) {
            if (in_array((string) ($row['type'] ?? ''), self::MULTI_VALUE_TYPES, true)) {
                $key = (string) ($row['field_key'] ?? '');
                if ($key !== '') {
                    $keys[] = $key;
                }
            }
        }

        return self::$multiValueKeys = $keys;
    }

    public static function isMultiValueKey(string $key): bool
    {
        return in_array($key, self::multiValueKeys(), true);
    }

    /** Drop the cached list after a custom field is created, edited or removed. */
    public static function forgetMultiValueKeys(): void
    {
        self::$multiValueKeys = null;
    }

    /**
     * All active custom fields, ordered, each as an array including its
     * resolved category ids.
     *
     * @return array<int, array<string, mixed>>
     */
    public function all(bool $onlyActive = true): array
    {
        $query = CustomField::query()->orderBy('ordering', 'ASC');
        if ($onlyActive) {
            $query->where('is_active', '=', 1);
        }

        $fields = [];
        foreach ($query->raw() as $row) {
            $fields[] = $this->hydrate($row);
        }

        return $fields;
    }

    /**
     * Active custom fields scoped to a single entity (store | brand), ordered.
     *
     * @return array<int, array<string, mixed>>
     */
    public function forEntity(string $entity = 'store', bool $onlyActive = true): array
    {
        $query = CustomField::query()
            ->where('entity', '=', $entity)
            ->orderBy('ordering', 'ASC');
        if ($onlyActive) {
            $query->where('is_active', '=', 1);
        }

        $fields = [];
        foreach ($query->raw() as $row) {
            $fields[] = $this->hydrate($row);
        }

        return $fields;
    }

    /**
     * @return array<string, mixed>|null
     */
    public function find(int $id): ?array
    {
        $field = CustomField::find($id);

        return $field ? $this->hydrate($field->toArray()) : null;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    public function create(array $data): array
    {
        $field = CustomField::create($this->writable($data));
        $id = (int) $field->getKey();

        if (isset($data['categories']) && is_array($data['categories'])) {
            $this->syncCategories($id, $data['categories']);
        }

        return $this->find($id) ?? [];
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>|null
     */
    public function update(int $id, array $data): ?array
    {
        $field = CustomField::find($id);
        if (! $field) {
            return null;
        }

        $field->fill($this->writable($data));
        $field->save();

        if (array_key_exists('categories', $data) && is_array($data['categories'])) {
            $this->syncCategories($id, $data['categories']);
        }

        return $this->find($id);
    }

    public function delete(int $id): bool
    {
        global $wpdb;
        $wpdb->delete($wpdb->prefix . 'cliq_sl_custom_field_category', ['field_id' => $id], ['%d']);

        $field = CustomField::find($id);

        return $field ? $field->delete() : false;
    }

    /**
     * @param int[] $categoryIds
     */
    public function syncCategories(int $fieldId, array $categoryIds): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_custom_field_category';

        $wpdb->delete($table, ['field_id' => $fieldId], ['%d']);

        foreach (array_unique(array_map('intval', $categoryIds)) as $categoryId) {
            if ($categoryId > 0) {
                $wpdb->insert($table, ['field_id' => $fieldId, 'category_id' => $categoryId], ['%d', '%d']);
            }
        }
    }

    /**
     * @return int[]
     */
    public function categoriesFor(int $fieldId): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_custom_field_category';
        $ids = $wpdb->get_col($wpdb->prepare('SELECT category_id FROM %i WHERE field_id = %d', $table, $fieldId));

        return array_map('intval', $ids);
    }

    /**
     * Convert a hydrated custom field into a cliq-ui form-field definition.
     * Fields scoped to categories become conditional on the store's selected
     * categories (the form must expose a `categories` checkbox/multiselect).
     *
     * @param array<string, mixed> $field
     * @param string               $metaTable prefixed store_meta table name
     *
     * @return array<string, mixed>
     */
    public function toFormField(array $field, string $metaTable): array
    {
        $definition = $this->baseDefinition($field);

        $definition['storage'] = [
            'target' => 'meta_table',
            'table' => $metaTable,
            'column' => 'meta_value',
            'store_key_column' => 'store_id',
            'meta_key_column' => 'meta_key',
            'key' => $field['field_key'],
            'cast' => in_array($field['type'], ['repeater', 'gallery', 'checkboxes', 'hours'], true) ? 'json' : null,
            // Store each selection as its own row, so a facet can match one of
            // them in SQL. Off for every other type, which stay a single row.
            'multi' => in_array($field['type'], self::MULTI_VALUE_TYPES, true),
        ];

        $categoryIds = $field['categories'] ?? [];
        if (! empty($categoryIds)) {
            // Wrap as conditional: only show when the store belongs to one of
            // the linked categories. The form's `categories` field holds ids.
            return [
                'name' => $field['field_key'] . '__wrap',
                'type' => 'conditional',
                'conditions' => array_map(static fn ($id): array => [
                    'field' => 'categories',
                    'operator' => 'contains',
                    'value' => (string) $id,
                ], $categoryIds),
                'conditionLogic' => 'or',
                'fields' => [$definition],
            ];
        }

        return $definition;
    }

    /**
     * Convert a brand custom field definition into a form field whose value is
     * stored per-brand in the brands.meta JSON column under custom_fields.<key>.
     * Brand fields are global (no category scoping), so every brand form shows
     * the same set of admin-defined fields.
     *
     * @param array<string, mixed> $field
     *
     * @return array<string, mixed>
     */
    public function toBrandFormField(array $field, string $brandsTable): array
    {
        $definition = $this->baseDefinition($field);

        $definition['storage'] = [
            'target' => 'json',
            'table' => $brandsTable,
            'column' => 'meta',
            'key_column' => 'id',
            'path' => ['custom_fields', $field['field_key']],
        ];

        return $definition;
    }

    /**
     * Build the shared field definition (name/label/type/options/sub-fields)
     * common to every entity, before storage is attached.
     *
     * @param array<string, mixed> $field
     *
     * @return array<string, mixed>
     */
    private function baseDefinition(array $field): array
    {
        $config = is_array($field['config'] ?? null) ? $field['config'] : [];

        // "Checkboxes" is the storage/field-definition name, but a long stack
        // of individual checkboxes is a poor record-editing control. Cliq UI's
        // multiselect uses WordPress FormTokenField: searchable suggestions,
        // compact selected tokens and the same array value contract.
        $renderType = $field['type'] === 'checkboxes' ? 'multiselect' : $field['type'];
        $definition = [
            'name' => $field['field_key'],
            'label' => $field['label'],
            'type' => $renderType,
        ];

        // `config` holds the choice list for select-style fields. It may be
        // stored either as { options: [...] } (manager) or as a bare list.
        $options = [];
        if (isset($config['options']) && is_array($config['options'])) {
            $options = $config['options'];
        } elseif (array_is_list($config) && $config !== []) {
            $options = $config;
        }
        if ($options !== []) {
            $definition['options'] = array_values($options);
        }

        // Repeater fields carry their sub-field definitions in config.fields.
        if ($field['type'] === 'repeater' && isset($config['fields']) && is_array($config['fields'])) {
            $definition['fields'] = array_values(array_map(static function ($sub): array {
                $name = (string) ($sub['name'] ?? '');

                return [
                    'name' => $name,
                    'label' => (string) ($sub['label'] ?? $name),
                    'type' => (string) ($sub['type'] ?? 'text'),
                    'default' => '',
                ];
            }, array_filter($config['fields'], static fn ($sub): bool => ! empty($sub['name']))));
        }

        return $definition;
    }

    /**
     * @param array<string, mixed> $row
     *
     * @return array<string, mixed>
     */
    private function hydrate(array $row): array
    {
        $config = $row['config'] ?? null;
        if (is_string($config) && $config !== '') {
            $decoded = json_decode($config, true);
            $row['config'] = is_array($decoded) ? $decoded : [];
        } elseif (! is_array($config)) {
            $row['config'] = [];
        }

        $row['id'] = (int) ($row['id'] ?? 0);
        $row['entity'] = (string) ($row['entity'] ?? 'store');
        $row['is_active'] = (bool) ($row['is_active'] ?? true);
        $row['is_repeatable'] = (bool) ($row['is_repeatable'] ?? false);
        $row['ordering'] = (int) ($row['ordering'] ?? 0);
        $row['categories'] = $this->categoriesFor($row['id']);

        return $row;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    private function writable(array $data): array
    {
        $writable = [];
        foreach (['field_key', 'label', 'type', 'entity', 'section', 'is_repeatable', 'ordering', 'is_active'] as $key) {
            if (array_key_exists($key, $data)) {
                $writable[$key] = ColumnInput::sanitize($key, $data[$key]);
            }
        }

        if (array_key_exists('config', $data)) {
            // Through the same rules the generic REST path uses: decoded,
            // every leaf sanitised, re-encoded. This route reaches the same
            // column, so it cannot have weaker rules.
            $writable['config'] = ColumnInput::sanitize('config', $data['config']);
        }

        return $writable;
    }
}
