<?php

namespace CliqThemes\ORM\Form\Storage;

use CliqThemes\ORM\Database\Connection;
use CliqThemes\ORM\Form\StorageStrategy;
use InvalidArgumentException;

class PivotStorageStrategy implements StorageStrategy
{
    protected Connection $connection;
    protected string $table;
    protected string $foreignKey;
    protected string $relatedKey;
    protected array $extraColumns;

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

        $table = $options['table'] ?? null;
        $foreignKey = $options['foreign_key'] ?? null;
        $relatedKey = $options['related_key'] ?? null;

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

        if (!is_string($foreignKey) || $foreignKey === '') {
            throw new InvalidArgumentException('PivotStorageStrategy requires a non-empty "foreign_key" option.');
        }

        if (!is_string($relatedKey) || $relatedKey === '') {
            throw new InvalidArgumentException('PivotStorageStrategy requires a non-empty "related_key" option.');
        }

        $this->table = $table;
        $this->foreignKey = $foreignKey;
        $this->relatedKey = $relatedKey;
        $this->extraColumns = is_array($options['extra_columns'] ?? null) ? $options['extra_columns'] : [];
    }

    public function saveOrUpdate(int $modelId, string $field, mixed $value): void
    {
        $desired = $this->normaliseValues($value);
        $existing = $this->normaliseValues($this->get($modelId, $field));

        $toDelete = array_diff($existing, $desired);
        $toInsert = array_diff($desired, $existing);

        foreach ($toDelete as $relatedId) {
            $this->connection->delete($this->table, [
                $this->foreignKey => $modelId,
                $this->relatedKey => $relatedId,
            ]);
        }

        foreach ($toInsert as $relatedId) {
            $payload = array_merge([
                $this->foreignKey => $modelId,
                $this->relatedKey => $relatedId,
            ], $this->extraColumns);

            $this->connection->insert($this->table, $payload);
        }
    }

    public function get(int $modelId, string $field): array
    {
        $table = $this->connection->table($this->table);
        $sql = sprintf('SELECT %s FROM %s WHERE %s = %%d', $this->wrapIdentifier($this->relatedKey), $table, $this->wrapIdentifier($this->foreignKey));
        $rows = $this->connection->select($sql, [$modelId]);

        if (empty($rows)) {
            return [];
        }

        $values = array_map(fn ($row) => $row[$this->relatedKey] ?? null, $rows);

        return array_values(array_filter($values, static fn ($value) => $value !== null));
    }

    public function delete(int $modelId, string $field): void
    {
        $this->connection->delete($this->table, [$this->foreignKey => $modelId]);
    }

    /**
     * Batched counterpart of {@see get()}: load related ids for MANY models in a
     * single query (eliminates the per-row N+1 on list views). Returns
     * modelId => relatedId[], with an empty array for models that have no rows.
     *
     * @param int[] $modelIds
     *
     * @return array<int, 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, []);
        if ($ids === []) {
            return $out;
        }

        $table = $this->connection->table($this->table);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $sql = sprintf(
            'SELECT %s, %s FROM %s WHERE %s IN (%s)',
            $this->wrapIdentifier($this->foreignKey),
            $this->wrapIdentifier($this->relatedKey),
            $table,
            $this->wrapIdentifier($this->foreignKey),
            $placeholders
        );

        foreach ($this->connection->select($sql, $ids) as $row) {
            $fk = (int) ($row[$this->foreignKey] ?? 0);
            $rk = $row[$this->relatedKey] ?? null;
            if ($fk > 0 && $rk !== null && isset($out[$fk])) {
                $out[$fk][] = $rk;
            }
        }

        return $out;
    }

    protected function normaliseValues(mixed $value): array
    {
        if ($value === null) {
            return [];
        }

        if (!is_array($value)) {
            $value = [$value];
        }

        return array_values(array_filter(array_map(function ($item) {
            if (is_scalar($item) || (is_object($item) && method_exists($item, '__toString'))) {
                return (string) $item;
            }

            return null;
        }, $value), static fn ($item) => $item !== null));
    }

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

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