<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateFootballCatalogTables extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'timezone' => [
                'type' => 'VARCHAR',
                'constraint' => 64,
                'default' => 'Africa/Nairobi',
            ],
            'current_season' => [
                'type' => 'INT',
                'constraint' => 4,
                'default' => 2025,
            ],
            'season_ends_at' => [
                'type' => 'DATE',
                'null' => true,
            ],
            'last_reference_sync_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('football_settings');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'api_name' => [
                'type' => 'VARCHAR',
                'constraint' => 120,
            ],
            'code' => [
                'type' => 'VARCHAR',
                'constraint' => 16,
                'null' => true,
            ],
            'flag_url' => [
                'type' => 'VARCHAR',
                'constraint' => 512,
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('api_name');
        $this->forge->createTable('football_countries');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'api_league_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'name' => [
                'type' => 'VARCHAR',
                'constraint' => 160,
            ],
            'country_name' => [
                'type' => 'VARCHAR',
                'constraint' => 120,
            ],
            'country_code' => [
                'type' => 'VARCHAR',
                'constraint' => 16,
                'null' => true,
            ],
            'type' => [
                'type' => 'VARCHAR',
                'constraint' => 32,
                'null' => true,
            ],
            'logo_url' => [
                'type' => 'VARCHAR',
                'constraint' => 512,
                'null' => true,
            ],
            'is_current' => [
                'type' => 'TINYINT',
                'constraint' => 1,
                'default' => 1,
            ],
            'coverage_json' => [
                'type' => 'TEXT',
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('api_league_id');
        $this->forge->addKey('country_name');
        $this->forge->createTable('football_leagues');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'api_team_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'name' => [
                'type' => 'VARCHAR',
                'constraint' => 160,
            ],
            'logo_url' => [
                'type' => 'VARCHAR',
                'constraint' => 512,
                'null' => true,
            ],
            'country_name' => [
                'type' => 'VARCHAR',
                'constraint' => 120,
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('api_team_id');
        $this->forge->createTable('football_teams');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'league_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'team_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'season' => [
                'type' => 'INT',
                'constraint' => 4,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['league_id', 'team_id', 'season']);
        $this->forge->addKey(['league_id', 'season']);
        $this->forge->createTable('football_league_teams');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'api_fixture_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'league_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'season' => [
                'type' => 'INT',
                'constraint' => 4,
            ],
            'home_team_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'away_team_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'kickoff_utc' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'kickoff_local' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'status_short' => [
                'type' => 'VARCHAR',
                'constraint' => 16,
                'null' => true,
            ],
            'round' => [
                'type' => 'VARCHAR',
                'constraint' => 120,
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('api_fixture_id');
        $this->forge->addKey(['league_id', 'season', 'kickoff_local']);
        $this->forge->createTable('football_fixtures');

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'league_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
            ],
            'season' => [
                'type' => 'INT',
                'constraint' => 4,
            ],
            'synced_teams_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'synced_fixtures_from' => [
                'type' => 'DATE',
                'null' => true,
            ],
            'synced_fixtures_to' => [
                'type' => 'DATE',
                'null' => true,
            ],
            'synced_fixtures_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['league_id', 'season']);
        $this->forge->createTable('football_league_sync_log');

        $now = date('Y-m-d H:i:s');
        $this->db->table('football_settings')->insert([
            'timezone' => 'Africa/Nairobi',
            'current_season' => 2025,
            'season_ends_at' => '2026-05-30',
            'last_reference_sync_at' => null,
            'updated_at' => $now,
        ]);
    }

    public function down()
    {
        $this->forge->dropTable('football_league_sync_log', true);
        $this->forge->dropTable('football_fixtures', true);
        $this->forge->dropTable('football_league_teams', true);
        $this->forge->dropTable('football_teams', true);
        $this->forge->dropTable('football_leagues', true);
        $this->forge->dropTable('football_countries', true);
        $this->forge->dropTable('football_settings', true);
    }
}
