<?php

namespace App\Models;

use CodeIgniter\Model;

class TransportContractSiteModel extends Model
{
    protected $table         = 'transport_contract_sites';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';

    protected $allowedFields = [
        'contract_id', 'transport_location_id', 'default_shift_id',
        'status', 'notes', 'created_by', 'updated_by',
    ];

    public const STATUSES = ['active', 'inactive'];

    public function scopeForList(?string $search = null, ?string $status = null): self
    {
        $this->resetQuery();

        $this->select(
            'transport_contract_sites.*,
             contracts.number AS contract_number,
             contracts.title AS contract_title,
             contracts.status AS contract_status,
             contracts.start_date AS contract_start_date,
             contracts.end_date AS contract_end_date,
             locations.name AS location_name,
             locations.city AS location_city,
             locations.latitude AS location_latitude,
             locations.longitude AS location_longitude,
             shifts.name AS shift_name,
             shifts.start_time AS shift_start_time,
             shifts.end_time AS shift_end_time'
        )
            ->join('contracts', 'contracts.id = transport_contract_sites.contract_id', 'left')
            ->join('transport_locations AS locations', 'locations.id = transport_contract_sites.transport_location_id', 'left')
            ->join('attendance_shifts AS shifts', 'shifts.id = transport_contract_sites.default_shift_id', 'left');

        if ($search) {
            $this->groupStart()
                ->like('contracts.number', $search)
                ->orLike('contracts.title', $search)
                ->orLike('locations.name', $search)
                ->groupEnd();
        }

        if ($status) {
            $this->where('transport_contract_sites.status', $status);
        }

        return $this;
    }

    public function getForContract(int $contractId): ?array
    {
        return $this->resetQuery()->where('contract_id', $contractId)->first();
    }

    public const BATCH_LIMITS = [10, 20, 40];

    /**
     * Distinct site regions (city) with plannable contract counts.
     *
     * @return list<array{region: string, site_count: int}>
     */
    public function getPlannableRegions(): array
    {
        $rows = $this->db->table('transport_contract_sites tcs')
            ->select('locations.city AS region, COUNT(*) AS site_count', false)
            ->join('contracts c', 'c.id = tcs.contract_id')
            ->join('transport_locations locations', 'locations.id = tcs.transport_location_id')
            ->where('tcs.status', 'active')
            ->where('c.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL')
            ->where('locations.city IS NOT NULL')
            ->where('locations.city !=', '')
            ->groupBy('locations.city')
            ->orderBy('locations.city', 'ASC')
            ->get()
            ->getResultArray();

        return array_map(static fn (array $row): array => [
            'region'     => (string) $row['region'],
            'site_count' => (int) $row['site_count'],
        ], $rows);
    }

    /**
     * @param int[]|null $contractIds Restrict to these contracts when provided.
     */
    public function getActivePlannable(?array $contractIds = null, ?string $region = null): array
    {
        $builder = $this->scopeForList(null, 'active')
            ->where('contracts.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL');

        if ($region !== null && $region !== '') {
            $builder->where('locations.city', $region);
        }

        if ($contractIds !== null && $contractIds !== []) {
            $builder->whereIn('transport_contract_sites.contract_id', $contractIds);
        }

        return $builder->orderBy('contracts.title', 'ASC')->findAll();
    }

    /**
     * Contract ids for a region (or all), capped for one plan run.
     *
     * @return list<int>
     */
    public function getActivePlannableContractIds(?string $region = null, int $limit = 40): array
    {
        $limit = max(1, $limit);

        $builder = $this->scopeForList(null, 'active')
            ->select('transport_contract_sites.contract_id')
            ->where('contracts.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL');

        if ($region !== null && $region !== '') {
            $builder->where('locations.city', $region);
        }

        $rows = $builder->orderBy('contracts.title', 'ASC')->limit($limit)->findAll();

        return array_values(array_map(static fn (array $row): int => (int) $row['contract_id'], $rows));
    }

    public function countActivePlannable(?string $region = null): int
    {
        $builder = $this->scopeForList(null, 'active')
            ->where('contracts.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL');

        if ($region !== null && $region !== '') {
            $builder->where('locations.city', $region);
        }

        return $builder->countAllResults(false);
    }

    public function countActivePlannableWithShift(): int
    {
        return $this->scopeForList(null, 'active')
            ->where('contracts.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL')
            ->where('transport_contract_sites.default_shift_id IS NOT NULL')
            ->countAllResults(false);
    }

    public function countAssignedActiveStaff(): int
    {
        $db = $this->db;

        return (int) $db->table('contract_employees ce')
            ->join('employees e', 'e.id = ce.employee_id')
            ->join('contracts c', 'c.id = ce.contract_id')
            ->join('transport_contract_sites tcs', 'tcs.contract_id = c.id AND tcs.status = "active"')
            ->join('transport_locations l', 'l.id = tcs.transport_location_id')
            ->where('c.status', 'active')
            ->where('e.status', 'active')
            ->where('e.deleted_at', null)
            ->where('l.latitude IS NOT NULL')
            ->where('l.longitude IS NOT NULL')
            ->countAllResults();
    }

    /**
     * Lightweight list for UI dropdowns (search + optional region + limit).
     */
    public function getActivePlannableOptions(?string $search = null, ?string $region = null, int $limit = 200): array
    {
        $builder = $this->scopeForList($search, 'active')
            ->where('contracts.status', 'active')
            ->where('locations.latitude IS NOT NULL')
            ->where('locations.longitude IS NOT NULL');

        if ($region !== null && $region !== '') {
            $builder->where('locations.city', $region);
        }

        return $builder->orderBy('contracts.title', 'ASC')->limit($limit)->findAll();
    }
}
