<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateJobCategoriesAndAgencyExtras extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'        => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'name'      => ['type' => 'VARCHAR', 'constraint' => 100],
            'slug'      => ['type' => 'VARCHAR', 'constraint' => 120],
            'parent_id' => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('slug');
        $this->forge->createTable('job_categories', true, ['ENGINE' => 'InnoDB']);
        $this->db->query('ALTER TABLE job_categories ADD CONSTRAINT fk_job_cat_parent FOREIGN KEY (parent_id) REFERENCES job_categories(id) ON DELETE SET NULL');

        // company_reviews — candidate reviews of an agency (public profile feature)
        $this->forge->addField([
            'id'                => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'         => ['type' => 'BIGINT', 'unsigned' => true],
            'reviewer_user_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'rating'            => ['type' => 'TINYINT', 'unsigned' => true],
            'title'             => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'review_text'       => ['type' => 'TEXT', 'null' => true],
            'status'            => ['type' => 'ENUM', 'constraint' => ['pending', 'approved', 'rejected'], 'default' => 'pending'],
            'created_at'        => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('reviewer_user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('company_reviews', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('company_reviews', true);
        $this->db->query('ALTER TABLE job_categories DROP FOREIGN KEY fk_job_cat_parent');
        $this->forge->dropTable('job_categories', true);
    }
}
