<?php

namespace App\Models;

use CodeIgniter\Model;

class RefWilayahModel extends Model
{
    protected $table            = 'ref_wilayah';
    protected $primaryKey       = 'id_wilayah';
    protected $useAutoIncrement = false;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $protectFields    = true;
    protected $allowedFields    = [
        'id_wilayah',
        'nama_wilayah',
        'id_induk_wilayah'
    ];

    // Dates
    protected $useTimestamps = false;
    
    // Search wilayah with JOINs to get full name (Kecamatan - Kabupaten - Provinsi)
    public function searchWilayahFull($query, $limit = 15)
    {
        $db = \Config\Database::connect();
        
        $sql = "
            SELECT 
                kec.id_wilayah,
                kec.nama_wilayah as kec_name,
                kab.nama_wilayah as kab_name,
                prov.nama_wilayah as prov_name
            FROM ref_wilayah kec
            LEFT JOIN ref_wilayah kab ON kec.id_induk_wilayah = kab.id_wilayah
            LEFT JOIN ref_wilayah prov ON kab.id_induk_wilayah = prov.id_wilayah
            WHERE LOWER(kec.nama_wilayah) LIKE LOWER(?)
            LIMIT " . (int)$limit . "
        ";
        
        $searchTerm = "%{$query}%";
        $results = $db->query($sql, [$searchTerm])->getResultArray();
        
        $formatted = [];
        foreach ($results as $row) {
            $name = $row['kec_name'];
            if (!empty($row['kab_name'])) {
                $name .= ' - ' . $row['kab_name'];
            }
            if (!empty($row['prov_name'])) {
                $name .= ' - ' . $row['prov_name'];
            }
            $formatted[] = [
                'id_wilayah' => $row['id_wilayah'],
                'nama_wilayah' => $name
            ];
        }
        
        return $formatted;
    }
}
