<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * One row per staff member per payroll run. `full_name_snapshot` and the
 * money fields are frozen at generation/edit time rather than derived
 * live from `users` — same reasoning bookings.rate_applied already
 * freezes a rate, so a later name change or basic_salary edit doesn't
 * rewrite payroll history. `total_allowances`/`total_deductions`/
 * `net_pay` are computed from this payslip's own payslip_line_items
 * (basic + allowances - deductions) and stored so history reads back
 * without re-summing every time.
 */
class CreatePayslipsTable extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'payroll_run_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'user_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'full_name_snapshot' => [
                'type'       => 'VARCHAR',
                'constraint' => 150,
            ],
            'basic_salary' => [
                'type'       => 'DECIMAL',
                'constraint' => '10,2',
                'default'    => 0,
            ],
            'total_allowances' => [
                'type'       => 'DECIMAL',
                'constraint' => '10,2',
                'default'    => 0,
            ],
            'total_deductions' => [
                'type'       => 'DECIMAL',
                'constraint' => '10,2',
                'default'    => 0,
            ],
            'net_pay' => [
                'type'       => 'DECIMAL',
                'constraint' => '10,2',
                'default'    => 0,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey('payroll_run_id');
        $this->forge->addKey('user_id');
        $this->forge->addForeignKey('payroll_run_id', 'payroll_runs', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('payslips');
    }

    public function down()
    {
        $this->forge->dropTable('payslips', true);
    }
}
