<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class BackfillJobPostingResponsibilities extends Migration
{
    private array $primaryKeys = [
        'job_templates' => 'template_id',
        'job_template_versions' => 'version_id',
        'job_positions' => 'position_id',
    ];

    public function up()
    {
        foreach ($this->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) {
                $parts = preg_split('/\R\RResponsibilities:\R/', (string) ($row['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]),
                    ]);
            }
        }
    }

    public function down()
    {
        // Data-only migration; do not merge responsibilities back into descriptions on rollback.
    }
}