<?php

namespace App\Models\Potensiwilayah;

use App\Models\BaseModel;

class DatapromosiModel extends BaseModel
{
    protected $table            = 'promosi_main';
    protected $primaryKey       = 'id';
    protected $allowedFields    = ['id_jenis', 'id_katelok', 'id_lokasi', 'nama_giat', 'piclok_nama', 'piclok_kontak', 'tgl_1', 'tgl_2', 'status', 'id_kantor', 'id_rek', 'wkt_rek'];

    public function find_by_id($id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('promosi_main as m');
        $builder->select('m.*, p.nama_kegiatan, k.nama_kat, l.nama_lokasi, l.lati, l.longi');
        $builder->join('pdk_promo as p', 'm.id_jenis = p.id', 'left');
        $builder->join('lokasi_kate as k', 'm.id_katelok = k.id', 'left');
        $builder->join('lokasi_main as l', 'm.id_lokasi = l.id', 'left');
        $builder->where('m.id', $id);

        return $builder->get()->getRow();
    }
    
    public function kantor_user($id_user)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('users');
        $builder->select('id_kantor');
        $builder->where('id', $id_user);
        $res = $builder->get()->getRow();
        return $res->id_kantor;
    }

    function dd_jenis()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_promo');
        $builder->select('id, nama_kegiatan');
        $builder->orderBy('nama_kegiatan', 'ASC');
        return $builder->get()->getResult();
    }

    function dd_katelok()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('lokasi_kate');
        $builder->select('id, nama_kat');
        $builder->orderBy('nama_kat', 'ASC');
        return $builder->get()->getResult();
    }

    function dd_lokasi($id_katelok, $id_kantor = null)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('lokasi_main');
        $builder->select('id, nama_lokasi, lati, longi');
        $builder->where('id_kategori', $id_katelok);
        
        if ($id_kantor) {
            $builder->where('id_kantor', $id_kantor);
        }
        
        $builder->orderBy('nama_lokasi', 'ASC');
        return $builder->get()->getResult();
    }

    function get_lokasi_detail($id_lokasi)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('lokasi_main');
        $builder->select('id, nama_lokasi, lati, longi');
        $builder->where('id', $id_lokasi);
        return $builder->get()->getRow();
    }

    function get_rows2($limit, $start = 0, $filterRules = NULL, $sortOrder = NULL, $id_kantor) {
        $db = \Config\Database::connect();
        $builder = $db->table('promosi_main as m');
        $builder->select('m.id, m.nama_giat, m.piclok_nama, m.piclok_kontak, m.tgl_1, m.tgl_2, m.status, p.nama_kegiatan, k.nama_kat, l.nama_lokasi');
        $builder->join('pdk_promo as p', 'm.id_jenis = p.id', 'left');
        $builder->join('lokasi_kate as k', 'm.id_katelok = k.id', 'left');
        $builder->join('lokasi_main as l', 'm.id_lokasi = l.id', 'left');
        $builder->where('m.id_kantor', $id_kantor);
        
        if ($filterRules) {
            $filterRules = json_decode($filterRules);

            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'nama_giat') {
                                $field = 'm.nama_giat';
                            }
                            if (trim($filter->field) == 'piclok_nama') {
                                $field = 'm.piclok_nama';
                            }
                            if (trim($filter->field) == 'piclok_kontak') {
                                $field = 'm.piclok_kontak';
                            }
                            if (trim($filter->field) == 'nama_kegiatan') {
                                $field = 'p.nama_kegiatan';
                            }
                            if (trim($filter->field) == 'nama_kat') {
                                $field = 'k.nama_kat';
                            }
                            if (trim($filter->field) == 'nama_lokasi') {
                                $field = 'l.nama_lokasi';
                            }
                            if (trim($filter->field) == 'status') {
                                $field = 'm.status';
                            }
                            
                            // Untuk field status, gunakan where exact match
                            if (trim($filter->field) == 'status') {
                                $builder->orWhere($field, trim(urldecode($p)));
                            } else {
                                $builder->orLike($field, trim(urldecode($p)));
                            }
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'nama_giat') {
                        $field = 'm.nama_giat';
                    }
                    if (trim($filter->field) == 'piclok_nama') {
                        $field = 'm.piclok_nama';
                    }
                    if (trim($filter->field) == 'piclok_kontak') {
                        $field = 'm.piclok_kontak';
                    }
                    if (trim($filter->field) == 'nama_kegiatan') {
                        $field = 'p.nama_kegiatan';
                    }
                    if (trim($filter->field) == 'nama_kat') {
                        $field = 'k.nama_kat';
                    }
                    if (trim($filter->field) == 'nama_lokasi') {
                        $field = 'l.nama_lokasi';
                    }
                    if (trim($filter->field) == 'status') {
                        $field = 'm.status';
                    }
                    
                    // Untuk field status, gunakan where exact match
                    if (trim($filter->field) == 'status') {
                        $builder->where($field, trim(urldecode($value)));
                    } else {
                        $builder->like($field, trim(urldecode($value)));
                    }
                }
            }
        }

        if ($sortOrder) {
            $builder->orderBy($sortOrder);
        } else {
            if ($this->orderBy <> '') {
                $builder->orderBy($this->orderBy);
            } else {
                $builder->orderBy($this->primaryKey, $this->sort);
            }
        }

        $builder->limit($limit, $start);
                
        return $builder->get()->getResult();
    }

    public function get_total2($filterRules = null, $id_kantor)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('promosi_main as m');
        $builder->select('m.id, m.nama_giat, m.piclok_nama, m.piclok_kontak, m.tgl_1, m.tgl_2, m.status, p.nama_kegiatan, k.nama_kat, l.nama_lokasi');
        $builder->join('pdk_promo as p', 'm.id_jenis = p.id', 'left');
        $builder->join('lokasi_kate as k', 'm.id_katelok = k.id', 'left');
        $builder->join('lokasi_main as l', 'm.id_lokasi = l.id', 'left');
        $builder->where('m.id_kantor', $id_kantor);

        if ($filterRules) {
            $filterRules = json_decode($filterRules);
            
            foreach ($filterRules as $filter) {
                $value = $filter->value;
                $pecah = explode(';', $value);
                if (count($pecah) > 1) {
                    $builder->groupStart();
                    foreach ($pecah as $p) {
                        if (trim($p) <> '') {
                            $field = $filter->field;
                            if (trim($filter->field) == 'nama_giat') {
                                $field = 'm.nama_giat';
                            }
                            if (trim($filter->field) == 'piclok_nama') {
                                $field = 'm.piclok_nama';
                            }
                            if (trim($filter->field) == 'piclok_kontak') {
                                $field = 'm.piclok_kontak';
                            }
                            if (trim($filter->field) == 'nama_kegiatan') {
                                $field = 'p.nama_kegiatan';
                            }
                            if (trim($filter->field) == 'nama_kat') {
                                $field = 'k.nama_kat';
                            }
                            if (trim($filter->field) == 'nama_lokasi') {
                                $field = 'l.nama_lokasi';
                            }
                            if (trim($filter->field) == 'status') {
                                $field = 'm.status';
                            }
                            
                            // Untuk field status, gunakan where exact match
                            if (trim($filter->field) == 'status') {
                                $builder->orWhere($field, trim(urldecode($p)));
                            } else {
                                $builder->orLike($field, trim(urldecode($p)));
                            }
                        }
                    }
                    $builder->groupEnd();
                } else {
                    $field = $filter->field;
                    if (trim($filter->field) == 'nama_giat') {
                        $field = 'm.nama_giat';
                    }
                    if (trim($filter->field) == 'piclok_nama') {
                        $field = 'm.piclok_nama';
                    }
                    if (trim($filter->field) == 'piclok_kontak') {
                        $field = 'm.piclok_kontak';
                    }
                    if (trim($filter->field) == 'nama_kegiatan') {
                        $field = 'p.nama_kegiatan';
                    }
                    if (trim($filter->field) == 'nama_kat') {
                        $field = 'k.nama_kat';
                    }
                    if (trim($filter->field) == 'nama_lokasi') {
                        $field = 'l.nama_lokasi';
                    }
                    if (trim($filter->field) == 'status') {
                        $field = 'm.status';
                    }
                    
                    // Untuk field status, gunakan where exact match
                    if (trim($filter->field) == 'status') {
                        $builder->where($field, trim(urldecode($value)));
                    } else {
                        $builder->like($field, trim(urldecode($value)));
                    }
                }
            }
        }

        return $builder->countAllResults();
    }

    function get_user_kantor($id_user)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('users');
        $builder->select('id_kantor');
        $builder->where('id', $id_user);
        $result = $builder->get()->getRow();
        return $result ? $result->id_kantor : null;
    }
}
