<?php

declare(strict_types=1);

namespace App\Models;

use CodeIgniter\Model;

class BookingScheduleModel extends Model
{
    protected $table            = 'booking_schedules';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'title',
        'timezone',
        'meeting_duration_minutes',
        'break_duration_minutes',
        'days_json',
        'assigned_agent_ids_json',
        'is_active',
    ];
    protected $useTimestamps    = true;
    protected $createdField     = 'created_at';
    protected $updatedField     = 'updated_at';
    protected $validationRules  = [];

    public function listActiveWithCounts(): array
    {
        $builder = $this->builder();
        $builder->select('booking_schedules.*');
        $builder->select('(SELECT COUNT(*) FROM booking_shifts WHERE booking_shifts.schedule_id = booking_schedules.id) as shifts_count');
        $builder->select('(SELECT COUNT(*) FROM bookings WHERE bookings.schedule_id = booking_schedules.id) as bookings_count');
        $builder->where('booking_schedules.is_active', 1);
        $builder->orderBy('booking_schedules.title', 'ASC');
        return $builder->get()->getResultArray();
    }

    public function findForAgent(int $uid): array
    {
        $uidInt      = (int)$uid;
        $jsonInt     = (string)$uidInt;
        $jsonStr     = json_encode((string)$uidInt);
        $jsonIntLit  = (string)$uidInt;

        $builder = $this->builder();
        $builder->select('booking_schedules.*');
        $builder->where('booking_schedules.is_active', 1);
        $builder->groupStart();
        $builder->where(
            "booking_schedules.assigned_agent_ids_json IS NULL
             OR booking_schedules.assigned_agent_ids_json IN ('', '[]')
             OR JSON_LENGTH(booking_schedules.assigned_agent_ids_json) = 0
             OR JSON_CONTAINS(booking_schedules.assigned_agent_ids_json, " . $this->escape($jsonIntLit) . ")
             OR JSON_CONTAINS(booking_schedules.assigned_agent_ids_json, " . $this->escape($jsonStr) . ")
             OR JSON_SEARCH(booking_schedules.assigned_agent_ids_json, 'one', " . $this->escape('%' . (string)$uidInt . '%') . ") IS NOT NULL",
            null,
            false
        );
        $builder->groupEnd();
        $builder->orderBy('booking_schedules.title', 'ASC');
        return $builder->get()->getResultArray();
    }
}
