<?php

namespace App\Models;

use CodeIgniter\Model;

class FootballFixtureModel extends Model
{
    protected $table = 'football_fixtures';
    protected $primaryKey = 'id';
    protected $allowedFields = [
        'api_fixture_id',
        'league_id',
        'season',
        'home_team_id',
        'away_team_id',
        'kickoff_utc',
        'kickoff_local',
        'status_short',
        'round',
        'created_at',
        'updated_at',
    ];
    protected $useTimestamps = true;
    protected $createdField = 'created_at';
    protected $updatedField = 'updated_at';

    public function forLeagueInRange(int $leagueId, int $season, string $from, string $to, ?string $q = null): array
    {
        $builder = $this->select('football_fixtures.*, ht.name AS home_name, ht.logo_url AS home_logo, at.name AS away_name, at.logo_url AS away_logo, fl.name AS league_name, fl.logo_url AS league_logo, fl.country_name')
            ->join('football_teams ht', 'ht.id = football_fixtures.home_team_id', 'left')
            ->join('football_teams at', 'at.id = football_fixtures.away_team_id', 'left')
            ->join('football_leagues fl', 'fl.id = football_fixtures.league_id', 'left')
            ->where('football_fixtures.league_id', $leagueId)
            ->where('football_fixtures.season', $season)
            ->where('DATE(football_fixtures.kickoff_local) >=', $from)
            ->where('DATE(football_fixtures.kickoff_local) <=', $to)
            ->whereNotIn('football_fixtures.status_short', ['CANC', 'ABD', 'PST']);

        if ($q !== null && strlen(trim($q)) >= 2) {
            $term = trim($q);
            $builder->groupStart()
                ->like('ht.name', $term)
                ->orLike('at.name', $term)
                ->groupEnd();
        }

        return $builder->orderBy('football_fixtures.kickoff_local', 'ASC')->findAll();
    }

    public function findByApiFixtureId(int $apiFixtureId): ?array
    {
        return $this->where('api_fixture_id', $apiFixtureId)->first();
    }

    public function findWithLogosByApiFixtureId(int $apiFixtureId): ?array
    {
        return $this->select('football_fixtures.*, ht.name AS home_name, ht.logo_url AS home_logo, at.name AS away_name, at.logo_url AS away_logo, fl.name AS league_name, fl.logo_url AS league_logo, fl.country_name')
            ->join('football_teams ht', 'ht.id = football_fixtures.home_team_id', 'left')
            ->join('football_teams at', 'at.id = football_fixtures.away_team_id', 'left')
            ->join('football_leagues fl', 'fl.id = football_fixtures.league_id', 'left')
            ->where('football_fixtures.api_fixture_id', $apiFixtureId)
            ->first();
    }
}
