<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateJobs extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                     => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'              => ['type' => 'BIGINT', 'unsigned' => true],
            'posted_by_user_id'      => ['type' => 'BIGINT', 'unsigned' => true],
            'title'                  => ['type' => 'VARCHAR', 'constraint' => 200],
            'description'            => ['type' => 'TEXT'],
            'department'             => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'category_id'            => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'experience_level'       => ['type' => 'ENUM', 'constraint' => ['entry', 'mid', 'senior', 'lead', 'executive'], 'default' => 'mid'],
            'salary_min'             => ['type' => 'DECIMAL', 'constraint' => '12,2', 'null' => true],
            'salary_max'             => ['type' => 'DECIMAL', 'constraint' => '12,2', 'null' => true],
            'salary_currency'        => ['type' => 'VARCHAR', 'constraint' => 10, 'default' => 'PKR'],
            'is_salary_visible'      => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'location'               => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'is_remote'              => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'is_hybrid'              => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'required_skills'        => ['type' => 'JSON', 'null' => true],
            'employment_type'        => ['type' => 'ENUM', 'constraint' => ['full_time', 'part_time', 'contract', 'internship', 'freelance'], 'default' => 'full_time'],
            'education_requirement'  => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'benefits'               => ['type' => 'TEXT', 'null' => true],
            'status'                 => ['type' => 'ENUM', 'constraint' => ['draft', 'published', 'expired', 'closed'], 'default' => 'draft'],
            'is_featured'            => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'is_urgent'              => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'views_count'            => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'expires_at'             => ['type' => 'DATE', 'null' => true],
            'seo_slug'               => ['type' => 'VARCHAR', 'constraint' => 220],
            'meta_title'             => ['type' => 'VARCHAR', 'constraint' => 200, 'null' => true],
            'meta_description'       => ['type' => 'VARCHAR', 'constraint' => 320, 'null' => true],
            'created_at'             => ['type' => 'TIMESTAMP', 'null' => true],
            'updated_at'             => ['type' => 'TIMESTAMP', 'null' => true],
            'deleted_at'             => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('seo_slug');
        $this->forge->addKey('agency_id');
        $this->forge->addKey(['status', 'expires_at']);
        $this->forge->addKey('location');
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('posted_by_user_id', 'users', 'id', false, 'RESTRICT');
        $this->forge->addForeignKey('category_id', 'job_categories', 'id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('jobs', true, ['ENGINE' => 'InnoDB']);

        $this->db->query('ALTER TABLE jobs ADD FULLTEXT INDEX ft_jobs_title_desc (title, description)');
    }

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