<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateCandidateSubProfiles extends Migration
{
    public function up(): void
    {
        $this->createExperiences();
        $this->createEducation();
        $this->createCertifications();
        $this->createSkillsAndPivot();
        $this->createLanguages();
        $this->createProjects();
        $this->createPortfolioLinks();
        $this->createReferences();
        $this->createResumes();
    }

    private function createExperiences(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'job_title'             => ['type' => 'VARCHAR', 'constraint' => 150],
            'company_name'          => ['type' => 'VARCHAR', 'constraint' => 150],
            'location'              => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'start_date'            => ['type' => 'DATE'],
            'end_date'              => ['type' => 'DATE', 'null' => true],
            'is_current'            => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'description'           => ['type' => 'TEXT', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_experiences', true, ['ENGINE' => 'InnoDB']);
    }

    private function createEducation(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'institution'           => ['type' => 'VARCHAR', 'constraint' => 150],
            'degree'                => ['type' => 'VARCHAR', 'constraint' => 150],
            'field_of_study'        => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'start_year'            => ['type' => 'YEAR', 'null' => true],
            'end_year'              => ['type' => 'YEAR', 'null' => true],
            'grade'                 => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_education', true, ['ENGINE' => 'InnoDB']);
    }

    private function createCertifications(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'title'                 => ['type' => 'VARCHAR', 'constraint' => 150],
            'issuing_org'           => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'issue_date'            => ['type' => 'DATE', 'null' => true],
            'expiry_date'           => ['type' => 'DATE', 'null' => true],
            'credential_url'        => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_certifications', true, ['ENGINE' => 'InnoDB']);
    }

    private function createSkillsAndPivot(): void
    {
        $this->forge->addField([
            'id'       => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'name'     => ['type' => 'VARCHAR', 'constraint' => 100],
            'category' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('name');
        $this->forge->createTable('skills', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'candidate_profile_id' => ['type' => 'BIGINT', 'unsigned' => true],
            'skill_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'proficiency'          => ['type' => 'ENUM', 'constraint' => ['beginner', 'intermediate', 'advanced', 'expert'], 'default' => 'intermediate'],
            'years_experience'     => ['type' => 'TINYINT', 'unsigned' => true, 'null' => true],
        ]);
        $this->forge->addPrimaryKey(['candidate_profile_id', 'skill_id']);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('skill_id', 'skills', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_skills', true, ['ENGINE' => 'InnoDB']);
    }

    private function createLanguages(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'language'              => ['type' => 'VARCHAR', 'constraint' => 100],
            'proficiency'           => ['type' => 'ENUM', 'constraint' => ['basic', 'conversational', 'fluent', 'native'], 'default' => 'conversational'],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_languages', true, ['ENGINE' => 'InnoDB']);
    }

    private function createProjects(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'title'                 => ['type' => 'VARCHAR', 'constraint' => 150],
            'description'           => ['type' => 'TEXT', 'null' => true],
            'project_url'           => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'start_date'            => ['type' => 'DATE', 'null' => true],
            'end_date'              => ['type' => 'DATE', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_projects', true, ['ENGINE' => 'InnoDB']);
    }

    private function createPortfolioLinks(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'label'                 => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'url'                   => ['type' => 'VARCHAR', 'constraint' => 255],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_portfolio_links', true, ['ENGINE' => 'InnoDB']);
    }

    private function createReferences(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'name'                  => ['type' => 'VARCHAR', 'constraint' => 150],
            'relationship'          => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'company'               => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'email'                 => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'phone'                 => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_references', true, ['ENGINE' => 'InnoDB']);
    }

    private function createResumes(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'file_path'             => ['type' => 'VARCHAR', 'constraint' => 255],
            'original_filename'     => ['type' => 'VARCHAR', 'constraint' => 255],
            'is_primary'            => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'download_count'        => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'uploaded_at'           => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('resumes', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('resumes', true);
        $this->forge->dropTable('candidate_references', true);
        $this->forge->dropTable('candidate_portfolio_links', true);
        $this->forge->dropTable('candidate_projects', true);
        $this->forge->dropTable('candidate_languages', true);
        $this->forge->dropTable('candidate_skills', true);
        $this->forge->dropTable('skills', true);
        $this->forge->dropTable('candidate_certifications', true);
        $this->forge->dropTable('candidate_education', true);
        $this->forge->dropTable('candidate_experiences', true);
    }
}
