<?php

declare(strict_types=1);

namespace App\Models;

use CodeIgniter\Model;

class BookingModel extends Model
{
    protected $table            = 'bookings';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'lead_id',
        'schedule_id',
        'booked_by_user_id',
        'assigned_agent_id',
        'starts_at',
        'ends_at',
        'timezone',
        'duration_minutes',
        'notes',
        'status',
        'guest_name',
        'guest_email',
        'guest_phone',
        'cal_link',
        'google_event_id',
    ];
    protected $useTimestamps    = true;
    protected $createdField     = 'created_at';
    protected $updatedField     = 'updated_at';
    protected $validationRules  = [];

    public function getForLead(int $lid): array
    {
        $builder = $this->builder();
        $builder->select('bookings.*');
        $builder->select('bookings.starts_at as booking_date, bookings.starts_at as start_time');
        $builder->select('leads.name as lead_name, leads.email as lead_email, leads.phone as lead_phone');
        $builder->select('agent.full_name as agent_name, agent.email as agent_email');
        $builder->select('agent.full_name as assigned_name, agent.email as assigned_email');
        $builder->select('agent.full_name as assigned_agent_name, agent.email as assigned_agent_email');
        $builder->select('booker.full_name as booked_by_name, booker.email as booked_by_email');
        $builder->select('booking_schedules.title as schedule_title, booking_schedules.title as schedule_name');
        $builder->join('leads', 'leads.id = bookings.lead_id', 'left');
        $builder->join('users as agent', 'agent.id = bookings.assigned_agent_id', 'left');
        $builder->join('users as booker', 'booker.id = bookings.booked_by_user_id', 'left');
        $builder->join('booking_schedules', 'booking_schedules.id = bookings.schedule_id', 'left');
        $builder->where('bookings.lead_id', $lid);
        $builder->orderBy('bookings.starts_at', 'DESC');
        return $builder->get()->getResultArray();
    }

    public function getBetweenRange(string $start, string $end, ?int $agentId = null, ?int $scheduleId = null): array
    {
        $builder = $this->builder();
        $builder->select('bookings.*');
        $builder->where('bookings.starts_at >=', $start);
        $builder->where('bookings.starts_at <=', $end);
        if ($agentId !== null) {
            $builder->where('bookings.assigned_agent_id', $agentId);
        }
        if ($scheduleId !== null) {
            $builder->where('bookings.schedule_id', $scheduleId);
        }
        $builder->where('bookings.status !=', 'cancelled');
        $builder->orderBy('bookings.starts_at', 'ASC');
        return $builder->get()->getResultArray();
    }

    public function getPaginatedWithJoins(int $perPage = 20, ?int $agentId = null, ?string $status = null): array
    {
        $builder = $this->builder();
        $builder->select('bookings.*, leads.name as lead_name, leads.email as lead_email, leads.phone as lead_phone');
        $builder->select('agent.full_name as agent_name, booker.full_name as booked_by_name');
        $builder->select('booking_schedules.title as schedule_title');
        $builder->join('leads', 'leads.id = bookings.lead_id', 'left');
        $builder->join('users as agent', 'agent.id = bookings.assigned_agent_id', 'left');
        $builder->join('users as booker', 'booker.id = bookings.booked_by_user_id', 'left');
        $builder->join('booking_schedules', 'booking_schedules.id = bookings.schedule_id', 'left');
        if ($agentId !== null) {
            $builder->where('bookings.assigned_agent_id', $agentId);
        }
        if ($status !== null) {
            $builder->where('bookings.status', $status);
        }
        $builder->orderBy('bookings.starts_at', 'DESC');
        return [
            'list'  => $builder->limit($perPage)->get()->getResultArray(),
            'total' => $builder->countAllResults(false),
        ];
    }

    public function getAnalyticsSummary(string $groupBy = 'day', string $start = '', string $end = ''): array
    {
        $builder = $this->builder();
        $selectField = '';
        $selectAlias = '';

        switch ($groupBy) {
            case 'agent':
                $selectField = 'bookings.assigned_agent_id';
                $selectAlias = 'group_key';
                $builder->select('agent.full_name as group_label');
                $builder->join('users as agent', 'agent.id = bookings.assigned_agent_id', 'left');
                break;
            case 'status':
                $selectField = 'bookings.status';
                $selectAlias = 'group_key';
                $builder->select('bookings.status as group_label');
                break;
            case 'day':
            default:
                $selectField = "DATE(bookings.starts_at)";
                $selectAlias = 'group_key';
                $builder->select("DATE(bookings.starts_at) as group_label");
                break;
        }

        $builder->select("$selectField as $selectAlias");
        $builder->select('COUNT(*) as total_bookings');
        $builder->select("SUM(CASE WHEN bookings.status = 'booked' THEN 1 ELSE 0 END) as count_booked");
        $builder->select("SUM(CASE WHEN bookings.status = 'cancelled' THEN 1 ELSE 0 END) as count_cancelled");
        $builder->select("SUM(CASE WHEN bookings.status = 'rescheduled' THEN 1 ELSE 0 END) as count_rescheduled");
        $builder->select('AVG(bookings.duration_minutes) as avg_duration_minutes');

        if ($start !== '') {
            $builder->where('bookings.starts_at >=', $start);
        }
        if ($end !== '') {
            $builder->where('bookings.starts_at <=', $end);
        }

        $builder->groupBy("$selectField");
        $builder->orderBy("$selectAlias", 'ASC');
        return $builder->get()->getResultArray();
    }
}
