<?php

namespace Modules\AdministrativeUnits\Models;

use codeigniter\Model;

class VillageModel extends Model
{
    private $tbl_district = "districts";
    private $tbl_county = "counties";
    private $tbl_subcounty = "subcounties";
    private $tbl_parish = "parishes";

    public function __construct()
    {
        parent::__construct();

        $this->table = "villages";
        $this->primaryKey = "village_id";
       
    }
    
    protected function initialize()
    {
       $this->validationRules    = [
            'village_name'     => 'required|alpha_numeric_space|min_length[3]',
            
        ];

        
        $this->allowedFields = ['village_name','parish_id'];
    }

    	
	public function findByName($name,$parishId)
    {
        $builder = $this->db->table($this->table);
        $builder->select('*');
        $builder->where("{$this->table}.village_name",$name);
		$builder->where("{$this->table}.parish_id",$parishId);
        return $builder->get()->getRowArray();
    }

    /**public function findAll(int $limit = 0, int $offset = 0)
    {
        $builder = $this->db->table($this->table);
        $builder->select('*');
        if($limit > 0){
            $builder->limit($limit, $offset);
        }
        $builder->join($this->tbl_parish, "{$this->tbl_parish}.parish_id = {$this->table}.parish_id");
        $builder->join($this->tbl_subcounty, "{$this->tbl_subcounty}.subcounty_id = {$this->tbl_parish}.subcounty_id");
        $builder->join($this->tbl_county, "{$this->tbl_county}.county_id = {$this->tbl_subcounty}.county_id");
        $builder->join($this->tbl_district, "{$this->tbl_district}.district_id = {$this->tbl_county}.district_id");
        return $builder->get()->getResultArray();
    }**/

    public function findByDistrictId($id)
    {
        $builder = $this->db->table($this->table);
        $builder->select('*');
        $builder->where("{$this->tbl_district}.district_id",$id);
        $builder->join($this->tbl_parish, "{$this->tbl_parish}.parish_id = {$this->table}.parish_id");
        $builder->join($this->tbl_subcounty, "{$this->tbl_subcounty}.subcounty_id = {$this->tbl_parish}.subcounty_id");
        $builder->join($this->tbl_county, "{$this->tbl_county}.county_id = {$this->tbl_subcounty}.county_id");
        $builder->join($this->tbl_district, "{$this->tbl_district}.district_id = {$this->tbl_county}.district_id");
        return $builder->get()->getResultArray();
    }
    

    public function findByCountyId($id)
    {
        $builder = $this->db->table($this->table);
        $builder->select('*');
        $builder->where("{$this->tbl_county}.county_id",$id);
        $builder->join($this->tbl_parish, "{$this->tbl_parish}.parish_id = {$this->table}.parish_id");
        $builder->join($this->tbl_subcounty, "{$this->tbl_subcounty}.subcounty_id = {$this->tbl_parish}.subcounty_id");
        $builder->join($this->tbl_county, "{$this->tbl_county}.county_id = {$this->tbl_subcounty}.county_id");
        $builder->join($this->tbl_district, "{$this->tbl_district}.district_id = {$this->tbl_county}.district_id");
        return $builder->get()->getResultArray();
    }

    public function findByParishId($id)
    {
        $builder = $this->db->table($this->table);
        $builder->select('*');
        $builder->where("{$this->table}.parish_id",$id);
        $builder->join($this->tbl_parish, "{$this->tbl_parish}.parish_id = {$this->table}.parish_id");
        $builder->join($this->tbl_subcounty, "{$this->tbl_subcounty}.subcounty_id = {$this->tbl_parish}.subcounty_id");
        $builder->join($this->tbl_county, "{$this->tbl_county}.county_id = {$this->tbl_subcounty}.county_id");
        $builder->join($this->tbl_district, "{$this->tbl_district}.district_id = {$this->tbl_county}.district_id");
        return $builder->get()->getResultArray();
    }
	
	
	public function like($name)
    {
        $builder = $this->db->table("{$this->table} as v");
        $builder->select('*');
        $builder->like('v.village_name',$name, 'after');
		$builder->join("{$this->tbl_parish} as p", "p.parish_id = v.parish_id");
        $builder->join("{$this->tbl_subcounty} as sc", "sc.subcounty_id = p.subcounty_id");
        $builder->join("{$this->tbl_county} as c", "c.county_id = sc.county_id");
        $builder->join("{$this->tbl_district} as d", "d.district_id = c.district_id");
         
        return $builder->get()->getResultArray();
    }

    public function findWhereNameLike($name)
    {
        $builder = $this->db->table("{$this->table} as v");
        $builder->select('*');
        $builder->like('v.village_name',$name, 'after');
		$builder->join("{$this->tbl_parish} as p", "p.parish_id = v.parish_id");
        $builder->join("{$this->tbl_subcounty} as sc", "sc.subcounty_id = p.subcounty_id");
        $builder->join("{$this->tbl_county} as c", "c.county_id = sc.county_id");
        $builder->join("{$this->tbl_district} as d", "d.district_id = c.district_id");
         
        return $builder->get()->getResultArray();
    }
    
}