<?php

namespace Modules\Redd\Models;

use CodeIgniter\Model;
use CodeIgniter\I18n\Time;

class ProjectsModel extends Model
{

  protected $table = 'redd_projects';
    protected $primaryKey = 'id';

    protected $allowedFields = ['client_id','name', 'project_number', 'size', 'location_description', 'geo_lat', 'geo_long', 'start_date', 'end_date', 'project_status_id', 'annual_emissions', 'project_validator', 'boundary_file', 'district_id', 'county_id', 'subcounty_id','parish_id', 'village_id', 'description', 'created_by', 'updated_by'];

    protected $returnType     = 'array';
    protected $useTimestamps  = true;
    protected $useSoftDeletes = true;
    
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';


    public function getAll()
    {
        $builder = $this->db->table('redd_projects');
        $builder->where('deleted_at', null);
        $result = $builder->get()->getResult();
        return $result;
    }

    public function getSingleProject($project)
    {
      $builder = $this->db->table('redd_projects');
      $builder->select('redd_projects.*, fname, lname, district_name, county_name, subcounty_name, parish_name, village_name')
              ->join('redd_clients', 'redd_clients.id=redd_projects.client_id')
              ->join('districts', 'districts.district_id=redd_projects.district_id')
              ->join('counties', 'counties.county_id=redd_projects.county_id')
              ->join('subcounties', 'subcounties.subcounty_id=redd_projects.subcounty_id')
              ->join('parishes', 'parishes.parish_id=redd_projects.parish_id')
              ->join('villages', 'villages.village_id=redd_projects.village_id')
              ->where('redd_projects.id', $project);
      return $results = $builder->get()->getRow();
    }

    public function getSingleClientProjects($client)
    {
        $builder = $this->db->table('redd_projects');
        $builder->select('redd_projects.*, fname, lname, district_name, county_name, subcounty_name, parish_name, village_name')
                ->join('redd_clients', 'redd_clients.id = redd_projects.client_id')
                ->join('districts', 'districts.district_id = redd_projects.district_id')
                ->join('counties', 'counties.county_id = redd_projects.county_id')
                ->join('subcounties', 'subcounties.subcounty_id = redd_projects.subcounty_id')
                ->join('parishes', 'parishes.parish_id = redd_projects.parish_id')
                ->join('villages', 'villages.village_id = redd_projects.village_id')
                ->where('redd_projects.client_id', $client)
                ->orderBy('redd_projects.id', 'ASC');

        $projects = $builder->get()->getResult();

        // Load related models
        $carbonDataModel        = new ProjectCarbonDataModel();
        $projectTypesModel      = new ProjectTypesModel();
        $projectGasesModel      = new ProjectGasesModel();
        $projectMethodsModel    = new ProjectMethodologiesModel();
        $projectPoolsModel      = new ProjectCarbonPoolsModel();
        $projectActivitiesModel = new ProjectActivitiesModel();
        $projectStatusModel     = new ProjectStatusModel();

        foreach ($projects as &$project) {
            $projectId = $project->id;

            // Get latest carbon data (by year or created_at desc)
            $project->carbon_data = $carbonDataModel
                ->where('project_id', $projectId)
                ->orderBy('created_at', 'DESC')
                ->first();

            // Attach related items (one-to-many / many-to-many)
            $project->types        = $projectTypesModel->getByProject($projectId);
            $project->gases        = $projectGasesModel->getByProject($projectId);
            $project->methodologies= $projectMethodsModel->getByProject($projectId);
            $project->carbon_pools = $projectPoolsModel->getByProject($projectId);
            $project->activities   = $projectActivitiesModel->getByProject($projectId);
            $project->status       = $projectStatusModel->getByProject($projectId);
            $project->singleStatus = $projectStatusModel->showProjectCurrentStatus($projectId);
        }

        return $projects;
    }


    public function getAllProjects()
    {
      $builder = $this->db->table('redd_projects');
        $builder->select('redd_projects.*, fname, lname, district_name, county_name, subcounty_name, parish_name, village_name')
                ->join('redd_clients', 'redd_clients.id = redd_projects.client_id')
                ->join('districts', 'districts.district_id = redd_projects.district_id')
                ->join('counties', 'counties.county_id = redd_projects.county_id')
                ->join('subcounties', 'subcounties.subcounty_id = redd_projects.subcounty_id')
                ->join('parishes', 'parishes.parish_id = redd_projects.parish_id')
                ->join('villages', 'villages.village_id = redd_projects.village_id')
                ->where('redd_projects.deleted_at', null)
                ->orderBy('redd_projects.id', 'ASC');

        $projects = $builder->get()->getResult();

        // Load related models
        $carbonDataModel        = new ProjectCarbonDataModel();
        $projectTypesModel      = new ProjectTypesModel();
        $projectGasesModel      = new ProjectGasesModel();
        $projectMethodsModel    = new ProjectMethodologiesModel();
        $projectPoolsModel      = new ProjectCarbonPoolsModel();
        $projectActivitiesModel = new ProjectActivitiesModel();
        $projectStatusModel     = new ProjectStatusModel();

        foreach ($projects as &$project) {
            $projectId = $project->id;

            // Get latest carbon data (by year or created_at desc)
            

            // Attach related items (one-to-many / many-to-many)
            $project->carbon_data = $carbonDataModel->getByProject($projectId);
            $project->types        = $projectTypesModel->getByProject($projectId);
            $project->gases        = $projectGasesModel->getByProject($projectId);
            $project->methodologies= $projectMethodsModel->getByProject($projectId);
            $project->carbon_pools = $projectPoolsModel->getByProject($projectId);
            $project->activities   = $projectActivitiesModel->getByProject($projectId);
            $project->status       = $projectStatusModel->getByProject($projectId);
            $project->singleStatus = $projectStatusModel->showProjectCurrentStatus($projectId);
        }

        return $projects;
    }

}