<?php

namespace App\Models;

use CodeIgniter\Model;

class CandidateTagModel extends Model
{
    protected $table         = 'candidate_tags';
    protected $allowedFields = ['agency_id', 'name', 'color'];
    protected $validationRules = [
        'name' => 'required|max_length[60]',
    ];

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

    public function findOrCreate(int $agencyId, string $name, string $color = '#6c757d'): int
    {
        $existing = $this->where('agency_id', $agencyId)->where('name', $name)->first();
        if ($existing) {
            return $existing['id'];
        }
        return $this->insert(['agency_id' => $agencyId, 'name' => $name, 'color' => $color], true);
    }

    public function attachToCandidate(int $tagId, int $candidateProfileId, int $agencyId): void
    {
        $db = \Config\Database::connect();
        $exists = $db->table('candidate_tag_map')->where('tag_id', $tagId)->where('candidate_profile_id', $candidateProfileId)->get()->getRow();
        if (! $exists) {
            $db->table('candidate_tag_map')->insert(['tag_id' => $tagId, 'candidate_profile_id' => $candidateProfileId, 'agency_id' => $agencyId]);
        }
    }

    public function tagsForCandidate(int $candidateProfileId, int $agencyId): array
    {
        return \Config\Database::connect()->table('candidate_tag_map ctm')
            ->select('ct.id, ct.name, ct.color')
            ->join('candidate_tags ct', 'ct.id = ctm.tag_id')
            ->where('ctm.candidate_profile_id', $candidateProfileId)
            ->where('ctm.agency_id', $agencyId)
            ->get()->getResultArray();
    }
}
