<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateSearchAndTalentPoolTables extends Migration
{
    public function up(): void
    {
        // --- Candidate Tags ---
        $this->forge->addField([
            'id'         => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'name'       => ['type' => 'VARCHAR', 'constraint' => 60],
            'color'      => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => '#6c757d'],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['agency_id', 'name']);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_tags', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'tag_id'                => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'agency_id'             => ['type' => 'BIGINT', 'unsigned' => true],
        ]);
        $this->forge->addPrimaryKey(['tag_id', 'candidate_profile_id']);
        $this->forge->addForeignKey('tag_id', 'candidate_tags', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_tag_map', true, ['ENGINE' => 'InnoDB']);

        // --- Saved Candidates ---
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'recruiter_id'          => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'created_at'            => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['recruiter_id', 'candidate_profile_id']);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('recruiter_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('saved_candidates', true, ['ENGINE' => 'InnoDB']);

        // --- Shortlists ---
        $this->forge->addField([
            'id'                => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'         => ['type' => 'BIGINT', 'unsigned' => true],
            'created_by_user_id'=> ['type' => 'BIGINT', 'unsigned' => true],
            'name'              => ['type' => 'VARCHAR', 'constraint' => 150],
            'job_id'            => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'created_at'        => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('job_id', 'jobs', 'id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('shortlists', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'shortlist_id'          => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'added_at'              => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addPrimaryKey(['shortlist_id', 'candidate_profile_id']);
        $this->forge->addForeignKey('shortlist_id', 'shortlists', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('shortlist_candidates', true, ['ENGINE' => 'InnoDB']);

        // --- Saved Searches ---
        $this->forge->addField([
            'id'             => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'      => ['type' => 'BIGINT', 'unsigned' => true],
            'user_id'        => ['type' => 'BIGINT', 'unsigned' => true],
            'name'           => ['type' => 'VARCHAR', 'constraint' => 150],
            'search_type'    => ['type' => 'ENUM', 'constraint' => ['candidate', 'job']],
            'filters'        => ['type' => 'JSON'],
            'alert_enabled'  => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'created_at'     => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('saved_searches', true, ['ENGINE' => 'InnoDB']);

        // --- Candidate Unlocks (contact reveal / resume download / full profile) ---
        // credits_spent recorded here now; actual credit-ledger enforcement
        // (CreditService::debit) wires in during Phase 4 — this table is the
        // audit record either way.
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'recruiter_id'          => ['type' => 'BIGINT', 'unsigned' => true],
            'candidate_profile_id'  => ['type' => 'BIGINT', 'unsigned' => true],
            'unlock_type'           => ['type' => 'ENUM', 'constraint' => ['contact_reveal', 'resume_download', 'full_profile']],
            'credits_spent'         => ['type' => 'INT', 'unsigned' => true, 'default' => 0],
            'unlocked_at'           => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['agency_id', 'candidate_profile_id']);
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('recruiter_id', 'users', 'id', false, 'RESTRICT');
        $this->forge->addForeignKey('candidate_profile_id', 'candidate_profiles', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('candidate_unlocks', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('candidate_unlocks', true);
        $this->forge->dropTable('saved_searches', true);
        $this->forge->dropTable('shortlist_candidates', true);
        $this->forge->dropTable('shortlists', true);
        $this->forge->dropTable('saved_candidates', true);
        $this->forge->dropTable('candidate_tag_map', true);
        $this->forge->dropTable('candidate_tags', true);
    }
}
