<?php

namespace App\Models\Dashboard;

use App\Models\BaseModel;

class LokasikantorModel extends BaseModel
{
    /**
     * Ambil data kantor Pusat dan Wilayah untuk view utama
     */
    public function getPusatWilayah()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker');

        $builder->select('id, satker, tipe, alamat, lati, longi, tel1, mail1');
        $builder->whereIn('tipe', ['Pusat', 'Wilayah']);
        $builder->where('lati IS NOT NULL');
        $builder->where('longi IS NOT NULL');
        $builder->where('lati !=', '');
        $builder->where('longi !=', '');
        $builder->orderBy('tipe', 'ASC');
        $builder->orderBy('satker', 'ASC');

        return $builder->get()->getResult();
    }

    /**
     * Ambil data kantor Area berdasarkan wilayah
     */
    public function getAreaByWilayah($id_wilayah)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as s');

        $builder->select('s.id, s.satker, s.tipe, s.alamat, s.lati, s.longi, s.tel1, s.mail1, w.satker as nama_wilayah');
        $builder->join('pdk_satker as w', 's.id_kanwil = w.id', 'left');
        $builder->where('s.tipe', 'Area');
        $builder->where('s.id_kanwil', $id_wilayah);
        $builder->where('s.lati IS NOT NULL');
        $builder->where('s.longi IS NOT NULL');
        $builder->where('s.lati !=', '');
        $builder->where('s.longi !=', '');
        $builder->orderBy('s.satker', 'ASC');

        return $builder->get()->getResult();
    }

    /**
     * Ambil data kantor Cabang berdasarkan area
     */
    public function getCabangByArea($id_area)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as s');

        $builder->select('s.id, s.satker, s.tipe, s.alamat, s.lati, s.longi, s.tel1, s.mail1, a.satker as nama_area, w.satker as nama_wilayah');
        $builder->join('pdk_satker as a', 's.id_area = a.id', 'left');
        $builder->join('pdk_satker as w', 'a.id_kanwil = w.id', 'left');
        $builder->where('s.tipe', 'Cabang/ UPC/ UPS');
        $builder->where('s.id_area', $id_area);
        $builder->where('s.lati IS NOT NULL');
        $builder->where('s.longi IS NOT NULL');
        $builder->where('s.lati !=', '');
        $builder->where('s.longi !=', '');
        $builder->orderBy('s.satker', 'ASC');

        return $builder->get()->getResult();
    }

    /**
     * Ambil detail kantor berdasarkan ID
     */
    public function getKantorDetail($id)
    {
        $db = \Config\Database::connect();
        $builder = $db->table('pdk_satker as s');

        $builder->select('s.*, p.nama_prov, k.nama_kako');
        $builder->join('pdk_prov as p', 's.prov = p.id', 'left');
        $builder->join('pdk_kako as k', 's.kako = k.id', 'left');
        $builder->where('s.id', $id);

        return $builder->get()->getRow();
    }

    /**
     * Ambil data wilayah dengan area di dalamnya
     */
    public function getWilayahWithArea($id_wilayah)
    {
        $wilayah = $this->getKantorDetail($id_wilayah);
        $areas = $this->getAreaByWilayah($id_wilayah);

        return [
            'wilayah' => $wilayah,
            'areas' => $areas
        ];
    }

    /**
     * Ambil data area dengan cabang di dalamnya
     */
    public function getAreaWithCabang($id_area)
    {
        $area = $this->getKantorDetail($id_area);
        $cabangs = $this->getCabangByArea($id_area);

        return [
            'area' => $area,
            'cabangs' => $cabangs
        ];
    }
}