<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Storage;

defined('ABSPATH') || exit;

use CliqThemes\ORM\Database\Connection;
use CliqThemes\ORM\Form\StorageStrategy;
use CliqThemes\StoreLocator\Support\StoredValue;

/**
 * Custom storage strategy for the store_meta table that handles both store_id and meta_key.
 */
class MetaTableStorageStrategy implements StorageStrategy
{
    protected Connection $connection;
    protected string $table;
    protected string $valueColumn;
    protected string $storeKeyColumn;
    protected string $metaKeyColumn;

    /**
     * When true, a list value is stored as one row per entry instead of a single
     * serialised row — see CustomFieldsRepository::MULTI_VALUE_TYPES.
     */
    protected bool $multi;

    public function __construct(array $options, ?Connection $connection = null)
    {
        $this->connection = $connection ?? new Connection();

        $table = $options['table'] ?? null;

        if (!is_string($table) || $table === '') {
            throw new \InvalidArgumentException('MetaTableStorageStrategy requires a non-empty "table" option.');
        }

        $this->table = $table;
        $this->valueColumn = $options['column'] ?? 'meta_value';
        $this->storeKeyColumn = $options['store_key_column'] ?? 'store_id';
        $this->metaKeyColumn = $options['meta_key_column'] ?? 'meta_key';
        $this->multi = ! empty($options['multi']);
    }

    public function saveOrUpdate(int $modelId, string $field, mixed $value): void
    {
        if ($value === null) {
            $this->delete($modelId, $field);
            return;
        }

        $table = $this->connection->table($this->table);
        $valueColumn = $this->wrapIdentifier($this->valueColumn);
        $storeKeyColumn = $this->wrapIdentifier($this->storeKeyColumn);
        $metaKeyColumn = $this->wrapIdentifier($this->metaKeyColumn);

        // A multi-value field stores one row per selection, so a facet can match
        // a single value in SQL rather than unpicking a serialised list.
        $values = $this->multi && is_array($value) && array_is_list($value)
            ? array_values(array_filter(
                array_map(static fn ($v): string => is_scalar($v) ? (string) $v : '', $value),
                static fn (string $v): bool => $v !== ''
            ))
            : [$this->prepareValue($value)];

        // Replace rather than upsert: (store key, meta key) is deliberately not
        // unique on store_meta, so ON DUPLICATE KEY UPDATE has nothing to key on
        // and would append a row on every save.
        $this->delete($modelId, $field);

        $insert = sprintf(
            'INSERT INTO %s (%s, %s, %s) VALUES (%%d, %%s, %%s)',
            $table,
            $storeKeyColumn,
            $metaKeyColumn,
            $valueColumn
        );
        foreach ($values as $single) {
            $this->connection->statement($insert, [$modelId, $field, $single]);
        }
    }

    public function get(int $modelId, string $field): mixed
    {
        $table = $this->connection->table($this->table);
        $valueColumn = $this->wrapIdentifier($this->valueColumn);
        $storeKeyColumn = $this->wrapIdentifier($this->storeKeyColumn);
        $metaKeyColumn = $this->wrapIdentifier($this->metaKeyColumn);

        // No LIMIT 1 for a multi-value field: its selections are separate rows,
        // and taking only the first would silently drop the rest.
        $sql = sprintf(
            'SELECT %s FROM %s WHERE %s = %%d AND %s = %%s%s',
            $valueColumn,
            $table,
            $storeKeyColumn,
            $metaKeyColumn,
            $this->multi ? ' ORDER BY id ASC' : ' LIMIT 1'
        );

        $results = $this->connection->select($sql, [$modelId, $field]);

        if (empty($results)) {
            return $this->multi ? [] : null;
        }

        if ($this->multi) {
            return array_values(array_map(
                fn (array $row): mixed => $this->restoreValue($row[$this->valueColumn] ?? null),
                $results
            ));
        }

        $value = $results[0][$this->valueColumn] ?? null;

        return $this->restoreValue($value);
    }

    /**
     * Batched counterpart of {@see get()}: load one meta key's value for MANY
     * stores in a single query (eliminates the per-row N+1 on list views).
     * Returns storeId => restored value, null for stores without the key.
     *
     * @param int[] $modelIds
     *
     * @return array<int, mixed>
     */
    public function getMany(array $modelIds, string $field): array
    {
        $ids = array_values(array_unique(array_map('intval', $modelIds)));
        $out = array_fill_keys($ids, null);
        if ($ids === []) {
            return $out;
        }

        $table = $this->connection->table($this->table);
        $valueColumn = $this->wrapIdentifier($this->valueColumn);
        $storeKeyColumn = $this->wrapIdentifier($this->storeKeyColumn);
        $metaKeyColumn = $this->wrapIdentifier($this->metaKeyColumn);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        $sql = sprintf(
            'SELECT %s AS cliq_sk, %s AS cliq_mv FROM %s WHERE %s IN (%s) AND %s = %%s',
            $storeKeyColumn,
            $valueColumn,
            $table,
            $storeKeyColumn,
            $placeholders,
            $metaKeyColumn
        );

        if ($this->multi) {
            $out = array_fill_keys($ids, []);
        }

        $args = $ids;
        $args[] = $field;
        foreach ($this->connection->select($sql, $args) as $row) {
            $sk = (int) ($row['cliq_sk'] ?? 0);
            if ($sk <= 0 || ! array_key_exists($sk, $out)) {
                continue;
            }
            // A multi-value field contributes several rows per store; accumulate
            // rather than letting the last one win.
            if ($this->multi) {
                $out[$sk][] = $this->restoreValue($row['cliq_mv'] ?? null);
                continue;
            }
            $out[$sk] = $this->restoreValue($row['cliq_mv'] ?? null);
        }

        return $out;
    }

    public function delete(int $modelId, string $field): void
    {
        $table = $this->connection->table($this->table);
        $storeKeyColumn = $this->wrapIdentifier($this->storeKeyColumn);
        $metaKeyColumn = $this->wrapIdentifier($this->metaKeyColumn);

        $sql = sprintf(
            'DELETE FROM %s WHERE %s = %%d AND %s = %%s',
            $table,
            $storeKeyColumn,
            $metaKeyColumn
        );

        $this->connection->statement($sql, [$modelId, $field]);
    }

    protected function prepareValue(mixed $value): mixed
    {
        if (is_array($value) || is_object($value)) {
            if (function_exists('maybe_serialize')) {
                return maybe_serialize($value);
            }

            return serialize($value);
        }

        return $value;
    }

    protected function restoreValue(mixed $value): mixed
    {
        return StoredValue::decode($value);
    }

    protected function wrapIdentifier(string $identifier): string
    {
        $identifier = trim($identifier, '`');

        return '`' . str_replace('`', '``', $identifier) . '`';
    }
}
