<?php

namespace App\Models;

use CodeIgniter\Model;

class PersonnelModel extends Model
{
    protected $table            = 'personnel';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = true;
    protected $protectFields    = true;
    protected $allowedFields    = [
        'company_id',
        'unit_id',
        'name',
        'role',
        'email',
        'phone',
        'address',
        'status',
        'deleted_at'
    ];

    // Dates
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Validation
    protected $validationRules      = [
        'company_id'  => 'permit_empty|is_natural_no_zero',
        'unit_id'     => 'permit_empty|is_natural_no_zero',
        'name'        => 'required|max_length[255]',
        'role'        => 'required|in_list[Manager,HR,Owner,Other]',
        'email'       => 'permit_empty|valid_email',
        'phone'       => 'permit_empty|regex_match[/^(\+91)?[6-9]\d{9}$/]',
        'address'     => 'permit_empty',
        'status'      => 'required|in_list[Active,Left]',
    ];
    protected $validationMessages   = [
        'company_id' => [
            'is_natural_no_zero' => 'Please select a valid company.'
        ],
        'unit_id' => [
            'is_natural_no_zero' => 'Please select a valid unit.'
        ],
        'name' => [
            'required' => 'Personnel name is required.',
            'max_length' => 'Personnel name cannot exceed 255 characters.'
        ],
        'role' => [
            'required' => 'Role is required.',
            'in_list' => 'Please select a valid role.'
        ],
        'email' => [
            'valid_email' => 'Please enter a valid email address.'
        ],
        'phone' => [
            'regex_match' => 'Please enter a valid Indian phone number (10 digits starting with 6-9, optionally with +91 prefix).'
        ],
        'status' => [
            'required' => 'Status is required.',
            'in_list' => 'Please select a valid status.'
        ]
    ];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert   = [];
    protected $beforeUpdate   = [];

    /**
     * Get personnel with pagination and search
     *
     * @param int $perPage
     * @param string|null $search
     * @param int|null $companyId
     * @param int|null $unitId
     * @param string|null $role
     * @param string|null $status
     * @return array
     */
    public function getPersonnel($perPage = 10, $search = null, $companyId = null, $unitId = null, $role = null, $status = null)
    {
        $this->select('personnel.*, companies.company_name, units.unit_name');
        $this->join('companies', 'companies.id = personnel.company_id', 'left');
        $this->join('units', 'units.id = personnel.unit_id', 'left');

        // Apply search filter
        if ($search) {
            $this->groupStart()
                 ->like('personnel.name', $search)
                 ->orLike('personnel.role', $search)
                 ->orLike('companies.company_name', $search)
                 ->orLike('units.unit_name', $search)
                 ->groupEnd();
        }

        // Apply company filter
        if ($companyId) {
            $this->where('personnel.company_id', $companyId);
        }

        // Apply unit filter
        if ($unitId) {
            $this->where('personnel.unit_id', $unitId);
        }

        // Apply role filter
        if ($role) {
            $this->where('personnel.role', $role);
        }

        // Apply status filter
        if ($status) {
            $this->where('personnel.status', $status);
        }

        // Exclude soft deleted records
        $this->where('personnel.deleted_at', null);

        // Order by created_at descending
        $this->orderBy('personnel.created_at', 'DESC');

        // Return paginated results
        return $this->paginate($perPage);
    }

    /**
     * Get personnel by unit ID
     *
     * @param int $unitId
     * @return array
     */
    public function getPersonnelByUnit($unitId)
    {
        return $this->where('unit_id', $unitId)
                    ->where('deleted_at', null)
                    ->findAll();
    }

    /**
     * Get personnel for export
     *
     * @param string|null $search
     * @param int|null $companyId
     * @param int|null $unitId
     * @param string|null $role
     * @param string|null $status
     * @return array
     */
    public function getPersonnelForExport($search = null, $companyId = null, $unitId = null, $role = null, $status = null)
    {
        $builder = $this->builder();
        $builder->select('personnel.*, companies.company_name, units.unit_name');
        $builder->join('companies', 'companies.id = personnel.company_id', 'left');
        $builder->join('units', 'units.id = personnel.unit_id', 'left');

        // Apply search filter
        if ($search) {
            $builder->groupStart()
                    ->like('personnel.name', $search)
                    ->orLike('personnel.role', $search)
                    ->orLike('companies.company_name', $search)
                    ->orLike('units.unit_name', $search)
                    ->groupEnd();
        }

        // Apply company filter
        if ($companyId) {
            $builder->where('personnel.company_id', $companyId);
        }

        // Apply unit filter
        if ($unitId) {
            $builder->where('personnel.unit_id', $unitId);
        }

        // Apply role filter
        if ($role) {
            $builder->where('personnel.role', $role);
        }

        // Apply status filter
        if ($status) {
            $builder->where('personnel.status', $status);
        }

        // Exclude soft deleted records
        $builder->where('personnel.deleted_at', null);

        // Order by created_at descending
        $builder->orderBy('personnel.created_at', 'DESC');

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

    /**
     * Get personnel statistics
     *
     * @return array
     */
    public function getPersonnelStats()
    {
        $builder = $this->builder();
        
        // Get total personnel
        $totalPersonnel = $builder->where('deleted_at', null)->countAllResults(false);
        
        // Get active personnel
        $activePersonnel = $builder->where('status', 'Active')->countAllResults(false);
        
        // Get personnel by role
        $builder->select('role, COUNT(*) as count');
        $builder->where('deleted_at', null);
        $builder->groupBy('role');
        $personnelByRole = $builder->get()->getResultArray();
        
        return [
            'total_personnel' => $totalPersonnel,
            'active_personnel' => $activePersonnel,
            'by_role' => $personnelByRole
        ];
    }

    /**
     * Get recent personnel
     *
     * @param int $limit
     * @return array
     */
    public function getRecentPersonnel($limit = 5)
    {
        return $this->select('personnel.id, personnel.name, personnel.role, personnel.created_at, companies.company_name')
                    ->join('companies', 'companies.id = personnel.company_id', 'left')
                    ->where('personnel.deleted_at', null)
                    ->orderBy('personnel.created_at', 'DESC')
                    ->limit($limit)
                    ->findAll();
    }

    /**
     * Get personnel by company ID
     *
     * @param int $companyId
     * @return array
     */
    public function getPersonnelByCompany($companyId)
    {
        return $this->where('company_id', $companyId)
                    ->where('deleted_at', null)
                    ->findAll();
    }
}