<?php

namespace App\Models;

use CodeIgniter\Model;

class QueryVariableModel extends Model
{
    protected $table            = 'query_variables';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $protectFields    = true;

    protected $allowedFields = [
        'saved_query_id',
        'workspace_id',
        'name',
        'label',
        'type',
        'default_value',
        'options_json',
        'is_required',
        'sort_order',
    ];

    protected bool $allowEmptyInserts = false;
    protected bool $updateOnlyChanged = true;

    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';

    protected $validationRules = [
        'saved_query_id' => 'required|integer',
        'workspace_id'   => 'required|integer',
        'name'           => 'required|min_length[1]|max_length[100]',
        'label'          => 'permit_empty|max_length[120]',
        'type'           => 'required|in_list[text,number,date,date_range,select,multi_select]',
        'is_required'    => 'permit_empty|in_list[0,1]',
        'sort_order'     => 'permit_empty|integer|greater_than_equal_to[0]',
    ];

    public function replaceForQuery(int $savedQueryId, int $workspaceId, array $variableNames, array $config = []): void
    {
        $this->where('saved_query_id', $savedQueryId)->delete();

        $rows = [];
        foreach (array_values($variableNames) as $index => $name) {
            $label = trim((string) ($config['label'][$name] ?? ucwords(str_replace('_', ' ', $name))));
            $type = (string) ($config['type'][$name] ?? 'text');
            if (! in_array($type, ['text', 'number', 'date', 'date_range', 'select', 'multi_select'], true)) {
                $type = 'text';
            }

            $rows[] = [
                'saved_query_id' => $savedQueryId,
                'workspace_id'   => $workspaceId,
                'name'           => $name,
                'label'          => $label !== '' ? $label : ucwords(str_replace('_', ' ', $name)),
                'type'           => $type,
                'default_value'  => $config['default'][$name] ?? null,
                'options_json'   => $config['options'][$name] ?? null,
                'is_required'    => ! empty($config['required'][$name]) ? 1 : 0,
                'sort_order'     => $index + 1,
            ];
        }

        if ($rows !== []) {
            $this->skipValidation(true);
            $this->insertBatch($rows);
            $this->skipValidation(false);
        }
    }
}