<?php

namespace App\Models;

use CodeIgniter\Model;

class SavedQueryModel extends Model
{
    protected $table            = 'saved_queries';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = true;
    protected $protectFields    = true;

    protected $allowedFields = [
        'workspace_id',
        'data_source_id',
        'name',
        'description',
        'query_type',
        'raw_sql',
        'visual_config',
        'api_endpoint',
        'api_params',
        'response_path',
        'field_map',
        'cache_ttl',
        'created_by',
    ];

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

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

    protected $validationRules = [
        'workspace_id'   => 'required|integer',
        'data_source_id' => 'required|integer',
        'name'           => 'required|min_length[3]|max_length[150]',
        'query_type'     => 'required|in_list[visual,raw_sql,api]',
        'cache_ttl'      => 'permit_empty|integer|greater_than_equal_to[0]',
        'created_by'     => 'required|integer',
    ];

    public function forWorkspace(int $workspaceId): array
    {
        return $this->select('saved_queries.*, data_sources.name as data_source_name')
            ->join('data_sources', 'data_sources.id = saved_queries.data_source_id', 'left')
            ->where('saved_queries.workspace_id', $workspaceId)
            ->orderBy('saved_queries.id', 'DESC')
            ->findAll();
    }
}