<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddResponsibilitiesToJobPostings extends Migration
{
    private array $tables = [
        'job_templates',
        'job_template_versions',
        'job_positions',
    ];

    public function up()
    {
        foreach ($this->tables as $table) {
            if (!$this->db->tableExists($table) || $this->db->fieldExists('responsibilities', $table)) {
                continue;
            }

            $this->forge->addColumn($table, [
                'responsibilities' => [
                    'type' => 'TEXT',
                    'null' => true,
                    'after' => 'employment_type',
                ],
            ]);
        }

        $this->backfillResponsibilities();
    }

    public function down()
    {
        foreach ($this->tables as $table) {
            if ($this->db->tableExists($table) && $this->db->fieldExists('responsibilities', $table)) {
                $this->forge->dropColumn($table, 'responsibilities');
            }
        }
    }

    private function backfillResponsibilities(): void
    {
        $primaryKeys = [
            'job_templates' => 'template_id',
            'job_template_versions' => 'version_id',
            'job_positions' => 'position_id',
        ];

        foreach ($primaryKeys as $table => $primaryKey) {
            if (!$this->db->tableExists($table) || !$this->db->fieldExists('responsibilities', $table)) {
                continue;
            }

            $rows = $this->db->table($table)
                ->select($primaryKey . ', description, responsibilities')
                ->groupStart()
                    ->where('responsibilities', null)
                    ->orWhere('responsibilities', '')
                ->groupEnd()
                ->like('description', 'Responsibilities:')
                ->get()
                ->getResultArray();

            foreach ($rows as $row) {
                $description = (string) ($row['description'] ?? '');
                $parts = preg_split('/\R\RResponsibilities:\R/', $description, 2);

                if (!is_array($parts) || count($parts) !== 2) {
                    continue;
                }

                $this->db->table($table)
                    ->where($primaryKey, $row[$primaryKey])
                    ->update([
                        'description' => trim($parts[0]),
                        'responsibilities' => trim($parts[1]),
                    ]);
            }
        }
    }
}