<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateApplicationsAndAts extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                     => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'job_id'                 => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_profile_id'   => ['type' => 'BIGINT', 'unsigned' => true],
            'resume_id'              => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'cover_note'             => ['type' => 'TEXT', 'null' => true],
            'status'                 => ['type' => 'ENUM', 'constraint' => ['applied', 'under_review', 'shortlisted', 'interview_scheduled', 'interview_completed', 'rejected', 'hired'], 'default' => 'applied'],
            'assigned_recruiter_id'  => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'applied_at'             => ['type' => 'TIMESTAMP', 'null' => true],
            'updated_at'             => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['job_id', 'candidate_profile_id']);
        $this->forge->addKey('status');
        $this->forge->addForeignKey('job_id', 'jobs', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('resume_id', 'resumes', 'id', 'SET NULL', 'CASCADE');
        $this->forge->addForeignKey('assigned_recruiter_id', 'users', 'id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('job_applications', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'job_application_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'from_status'           => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'to_status'             => ['type' => 'VARCHAR', 'constraint' => 30],
            'changed_by_user_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'note'                  => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at'            => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('job_application_id', 'job_applications', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('changed_by_user_id', 'users', 'id', false, 'RESTRICT');
        $this->forge->createTable('application_status_history', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'job_application_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'author_user_id'        => ['type' => 'BIGINT', 'unsigned' => true],
            'note'                  => ['type' => 'TEXT'],
            'created_at'            => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('job_application_id', 'job_applications', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('author_user_id', 'users', 'id', false, 'RESTRICT');
        $this->forge->createTable('application_notes', true, ['ENGINE' => 'InnoDB']);
    }

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