<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Storage;

defined('ABSPATH') || exit;

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

/**
 * Persists a store's primary location (latitude/longitude/address) as a single
 * composite field. The admin "Location" map field round-trips through here:
 * the value is an object { lat, lng, address } and is stored as the store's
 * is_primary = 1 row in wp_store_locations, so the spatial search and public
 * REST (which read the primary location) immediately see admin edits.
 */
class LocationStorageStrategy implements StorageStrategy
{
    /** Columns a single-column ("column" mode) field may bind to. */
    private const ALLOWED_COLUMNS = ['address_line1', 'address_line2', 'city', 'state', 'postal_code', 'country'];

    protected Connection $connection;
    protected string $table;
    /** When set, this strategy binds one text column on the primary row. */
    protected ?string $column;

    public function __construct(array $options, ?Connection $connection = null)
    {
        $this->connection = $connection ?? new Connection();
        $this->table = is_string($options['table'] ?? null) && $options['table'] !== ''
            ? $options['table']
            : 'store_locations';

        $column = is_string($options['column'] ?? null) ? $options['column'] : null;
        $this->column = in_array($column, self::ALLOWED_COLUMNS, true) ? $column : null;
    }

    public function saveOrUpdate(int $modelId, string $field, mixed $value): void
    {
        // Single-column mode: persist one address column on the primary row,
        // creating that row if the map hasn't already.
        if ($this->column !== null) {
            $this->saveColumn($modelId, (string) (is_scalar($value) ? $value : ''));

            return;
        }

        // Composite mode persists only the coordinates; the address columns are
        // their own fields (address_line1/line2/state/postal_code) so the map
        // pin and the address inputs never overwrite each other.
        $value = is_array($value) ? $value : [];
        $lat = isset($value['lat']) ? (float) $value['lat'] : 0.0;
        $lng = isset($value['lng']) ? (float) $value['lng'] : 0.0;

        $table = $this->connection->table($this->table);
        $existingId = $this->primaryRowId($modelId);

        // No coordinates: only drop the row if no address parts remain on it.
        if ($lat === 0.0 && $lng === 0.0) {
            if ($existingId !== null && $this->rowIsEmpty($existingId)) {
                $this->delete($modelId, $field);
            }

            return;
        }

        if ($existingId !== null) {
            $this->connection->statement(
                sprintf('UPDATE %s SET `latitude` = %%f, `longitude` = %%f WHERE `id` = %%d', $table),
                [$lat, $lng, $existingId]
            );

            return;
        }

        $this->connection->statement(
            sprintf(
                'INSERT INTO %s (`store_id`, `label`, `latitude`, `longitude`, `is_primary`) VALUES (%%d, %%s, %%f, %%f, 1)',
                $table
            ),
            [$modelId, 'Main', $lat, $lng]
        );
    }

    /** True when the primary row has no coordinates and no address parts. */
    private function rowIsEmpty(int $rowId): bool
    {
        $table = $this->connection->table($this->table);
        $rows = $this->connection->select(
            sprintf(
                'SELECT (COALESCE(`latitude`,0)=0 AND COALESCE(`longitude`,0)=0 AND COALESCE(`address_line1`,\'\')=\'\' AND COALESCE(`address_line2`,\'\')=\'\' AND COALESCE(`city`,\'\')=\'\' AND COALESCE(`state`,\'\')=\'\' AND COALESCE(`postal_code`,\'\')=\'\') AS empty FROM %s WHERE `id` = %%d',
                $table
            ),
            [$rowId]
        );

        return ! empty($rows) && (int) ($rows[0]['empty'] ?? 0) === 1;
    }

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

        if ($this->column !== null) {
            $rows = $this->connection->select(
                sprintf(
                    'SELECT `%s` AS val FROM %s WHERE `store_id` = %%d AND `is_primary` = 1 LIMIT 1',
                    $this->column,
                    $table
                ),
                [$modelId]
            );

            return empty($rows) ? '' : (string) ($rows[0]['val'] ?? '');
        }

        $rows = $this->connection->select(
            sprintf(
                'SELECT `latitude`, `longitude`, `address_line1` FROM %s WHERE `store_id` = %%d AND `is_primary` = 1 LIMIT 1',
                $table
            ),
            [$modelId]
        );

        if (empty($rows)) {
            return null;
        }

        $row = $rows[0];

        return [
            'lat' => (float) ($row['latitude'] ?? 0),
            'lng' => (float) ($row['longitude'] ?? 0),
            'address' => (string) ($row['address_line1'] ?? ''),
        ];
    }

    /**
     * Batched counterpart of {@see get()}: load the primary-location value for
     * MANY stores in one query (eliminates the per-row N+1 on list views).
     * Mirrors get() for both column mode (string per store) and composite mode
     * (lat/lng/address per store).
     *
     * @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, $this->column !== null ? '' : null);
        if ($ids === []) {
            return $out;
        }

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

        if ($this->column !== null) {
            $rows = $this->connection->select(
                sprintf(
                    'SELECT `store_id`, `%s` AS val FROM %s WHERE `store_id` IN (%s) AND `is_primary` = 1',
                    $this->column,
                    $table,
                    $placeholders
                ),
                $ids
            );
            foreach ($rows as $row) {
                $sid = (int) ($row['store_id'] ?? 0);
                if ($sid > 0 && ($out[$sid] ?? '') === '') {
                    $out[$sid] = (string) ($row['val'] ?? '');
                }
            }

            return $out;
        }

        $rows = $this->connection->select(
            sprintf(
                'SELECT `store_id`, `latitude`, `longitude`, `address_line1` FROM %s WHERE `store_id` IN (%s) AND `is_primary` = 1',
                $table,
                $placeholders
            ),
            $ids
        );
        foreach ($rows as $row) {
            $sid = (int) ($row['store_id'] ?? 0);
            if ($sid > 0 && ($out[$sid] ?? null) === null) {
                $out[$sid] = [
                    'lat' => (float) ($row['latitude'] ?? 0),
                    'lng' => (float) ($row['longitude'] ?? 0),
                    'address' => (string) ($row['address_line1'] ?? ''),
                ];
            }
        }

        return $out;
    }

    public function delete(int $modelId, string $field): void
    {
        // In column mode, clearing a single field must not drop the shared row.
        if ($this->column !== null) {
            $this->saveColumn($modelId, '');

            return;
        }

        $table = $this->connection->table($this->table);
        $this->connection->statement(
            sprintf('DELETE FROM %s WHERE `store_id` = %%d AND `is_primary` = 1', $table),
            [$modelId]
        );
    }

    /** Upsert a single address column on the store's primary location row. */
    private function saveColumn(int $modelId, string $value): void
    {
        $table = $this->connection->table($this->table);
        $existingId = $this->primaryRowId($modelId);

        if ($existingId !== null) {
            $this->connection->statement(
                sprintf('UPDATE %s SET `%s` = %%s WHERE `id` = %%d', $table, $this->column),
                [$value, $existingId]
            );

            return;
        }

        if ($value === '') {
            return;
        }

        $this->connection->statement(
            sprintf(
                'INSERT INTO %s (`store_id`, `label`, `%s`, `is_primary`) VALUES (%%d, %%s, %%s, 1)',
                $table,
                $this->column
            ),
            [$modelId, 'Main', $value]
        );
    }

    private function primaryRowId(int $modelId): ?int
    {
        $table = $this->connection->table($this->table);
        $rows = $this->connection->select(
            sprintf('SELECT `id` FROM %s WHERE `store_id` = %%d AND `is_primary` = 1 LIMIT 1', $table),
            [$modelId]
        );

        return isset($rows[0]['id']) ? (int) $rows[0]['id'] : null;
    }
}
