<?php

namespace App\Models;

use CodeIgniter\Model;

class ShortlistModel extends Model
{
    protected $table         = 'shortlists';
    protected $useTimestamps = false;
    protected $allowedFields = ['agency_id', 'created_by_user_id', 'name', 'job_id', 'created_at'];

    public function forAgency(int $agencyId): array
    {
        return $this->where('agency_id', $agencyId)->orderBy('created_at', 'DESC')->findAll();
    }

    public function addCandidate(int $shortlistId, int $candidateProfileId): void
    {
        $db = \Config\Database::connect();
        $exists = $db->table('shortlist_candidates')->where('shortlist_id', $shortlistId)->where('candidate_profile_id', $candidateProfileId)->get()->getRow();
        if (! $exists) {
            $db->table('shortlist_candidates')->insert([
                'shortlist_id'         => $shortlistId,
                'candidate_profile_id' => $candidateProfileId,
                'added_at'             => date('Y-m-d H:i:s'),
            ]);
        }
    }

    public function candidatesIn(int $shortlistId): array
    {
        return \Config\Database::connect()->table('shortlist_candidates sc')
            ->select('cp.*, u.name, u.email')
            ->join('candidate_profiles cp', 'cp.id = sc.candidate_profile_id')
            ->join('users u', 'u.id = cp.user_id')
            ->where('sc.shortlist_id', $shortlistId)
            ->get()->getResultArray();
    }
}
