<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Forge;
use CodeIgniter\Database\Migration;

class CreateAcademyLevelCriteria extends Migration
{
    private readonly array $attributes;

    public function __construct(?Forge $forge = null)
    {
        parent::__construct($forge);

        $this->attributes = $this->db->getPlatform() === 'MySQLi' ? ['ENGINE' => 'InnoDB'] : [];
    }

    public function up(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'academy_level_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_type_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_criteria_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'weight' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 1],
            'is_required' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'sort_order' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['academy_level_id', 'evaluation_type_id', 'evaluation_criteria_id'], 'level_type_criteria');
        $this->forge->addForeignKey('academy_level_id', 'academy_levels', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluation_type_id', 'evaluation_types', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluation_criteria_id', 'evaluation_criteria', 'id', '', 'CASCADE');
        $this->forge->createTable('academy_level_criteria', true, $this->attributes);

        $levels = $this->db->table('academy_levels')->select('id')->get()->getResultArray();
        $globalRows = $this->db->table('evaluation_type_criteria')->get()->getResultArray();

        $rows = [];
        foreach ($levels as $level) {
            foreach ($globalRows as $row) {
                $rows[] = [
                    'academy_level_id' => (int) $level['id'],
                    'evaluation_type_id' => (int) $row['evaluation_type_id'],
                    'evaluation_criteria_id' => (int) $row['evaluation_criteria_id'],
                    'weight' => $row['weight'],
                    'is_required' => (int) $row['is_required'],
                    'sort_order' => (int) $row['sort_order'],
                ];
            }
        }

        if ($rows !== []) {
            $this->db->table('academy_level_criteria')->insertBatch($rows);
        }
    }

    public function down(): void
    {
        $this->forge->dropTable('academy_level_criteria', true);
    }
}
