<?php

declare(strict_types=1);

namespace PpPhp\Core\Designer\FieldControl;

/**
 * Shared option-list resolver for choice controls (RadioGroup, Checkbox
 * group, future select/lookup controls). Two sources:
 *
 *  manual — comma-separated values, plain ("A, B") or value:Label pairs
 *           ("1:Active, 0:Inactive")
 *  table  — value/label columns of a database table, optionally filtered by
 *           a WHERE fragment written in the Designer (guarded to a single
 *           statement; invalid SQL falls back to the manual list)
 *
 * Ships in generated bundles next to FieldControlRuntime.
 */
final class LookupOptions
{
    /** @return array<string,string> value => label */
    public static function resolve(array $settings): array
    {
        if (($settings['source'] ?? 'manual') === 'table') {
            $fromDb = self::tableOptions($settings);
            if ($fromDb !== []) {
                return $fromDb;
            }
        }

        return self::manualOptions((string) ($settings['options'] ?? ''));
    }

    /** @return array<string,string> */
    public static function manualOptions(string $csv): array
    {
        $out = [];
        foreach (explode(',', $csv) as $part) {
            $part = trim($part);
            if ($part === '') {
                continue;
            }
            if (str_contains($part, ':')) {
                [$val, $label] = array_map('trim', explode(':', $part, 2));
                $out[$val !== '' ? $val : $label] = $label !== '' ? $label : $val;
            } else {
                $out[$part] = $part;
            }
        }

        return $out;
    }

    /** @return array<string,string> */
    private static function tableOptions(array $settings): array
    {
        $clean = static fn ($v): string => (string) preg_replace('/[^a-zA-Z0-9_]/', '', (string) $v);
        $table = $clean($settings['table'] ?? '');
        $valCol = $clean($settings['valueColumn'] ?? '');
        $lblCol = $clean($settings['labelColumn'] ?? '');
        if ($table === '' || $valCol === '') {
            return [];
        }
        if ($lblCol === '') {
            $lblCol = $valCol;
        }

        // Author-written filter — guarded against statement break-out.
        $cond = trim((string) ($settings['condition'] ?? ''));
        $cond = trim((string) preg_replace('/;|--|\/\*|#/', '', $cond));
        $where = $cond !== '' ? " WHERE ($cond)" : '';

        try {
            $rows = \PpPhp\Core\DB::FetchAll(
                "SELECT `$valCol` AS v, `$lblCol` AS l FROM `$table`$where GROUP BY `$valCol`, `$lblCol` ORDER BY `$lblCol` LIMIT 200"
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }

        $out = [];
        foreach ($rows as $row) {
            $v = (string) ($row['v'] ?? '');
            if ($v === '') {
                continue;
            }
            $out[$v] = (string) (($row['l'] ?? '') !== '' ? $row['l'] : $v);
        }

        return $out;
    }

    /** The shared settings-schema entries for lookup-capable controls. */
    public static function schema(): array
    {
        // `showIf` drives conditional visibility in the Designer inspector:
        // "manual" reveals only the free-text option list; "table" reveals the
        // lookup-table pickers. The two modes never show at once.
        $manualOnly = ['key' => 'source', 'in' => ['manual']];
        $tableOnly  = ['key' => 'source', 'in' => ['table']];

        return [
            ['key' => 'source', 'label' => 'Values from', 'type' => 'select', 'options' => ['manual', 'table'], 'default' => 'manual'],
            ['key' => 'options', 'label' => 'Manual options (A, B or 1:Active, 0:Off)', 'type' => 'text', 'default' => '', 'full' => true, 'showIf' => $manualOnly, 'placeholder' => 'Active, Inactive · or 1:Active, 0:Off', 'hint' => 'Comma-separated. Use value:Label pairs to store a code but show a friendly label (e.g. 1:Active, 0:Off).'],
            ['key' => 'table', 'label' => 'Lookup table', 'type' => 'table', 'default' => '', 'showIf' => $tableOnly, 'hint' => 'Table whose rows become the options (source = table).'],
            ['key' => 'valueColumn', 'label' => 'Value column', 'type' => 'column', 'tableKey' => 'table', 'default' => '', 'showIf' => $tableOnly, 'hint' => 'Stored in this field when the option is selected.'],
            ['key' => 'labelColumn', 'label' => 'Label column', 'type' => 'column', 'tableKey' => 'table', 'default' => '', 'showIf' => $tableOnly, 'hint' => 'Shown to the user. Leave empty to display the value.'],
            ['key' => 'condition', 'label' => 'Condition (WHERE)', 'type' => 'text', 'default' => '', 'full' => true, 'showIf' => $tableOnly, 'placeholder' => "id >= 3 AND name LIKE 'A%'", 'hint' => "Optional SQL filter on the lookup rows. Examples: id >= 3 · status = 'active' · name LIKE 'A%' AND id < 100 · deleted_at IS NULL"],
            // How the STORED value is shown on the list & view pages. Most apps
            // want the friendly label; pick "value" to show the raw stored code.
            ['key' => 'displayAs', 'label' => 'Show on list & view', 'type' => 'radiogroup', 'options' => ['label', 'value'], 'default' => 'label', 'inline' => true, 'newline' => true, 'hint' => 'label = the friendly text · value = the raw stored value (id / code).'],
        ];
    }
}
