<?php

namespace Tenant\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Full workforce management (explicit user choice over the simpler
 * "assignment calendar only" option): shifts, staff-initiated swap
 * requests, time-off requests, and overtime. Overtime is not inferred
 * from a time clock (no clock-in/out feature exists or is in scope) —
 * it's `actual_hours` logged manually against the shift's
 * `standard_hours`, the same honest-not-fabricated approach used
 * throughout this app for anything without a real data source.
 */
class CreateShiftSchedulingTables extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'user_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'shift_date' => [
                'type' => 'DATE',
            ],
            'shift_block' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'comment'    => 'morning|afternoon|night — see Config\\Lookups::$shiftBlocks.',
            ],
            'standard_hours' => [
                'type'       => 'DECIMAL',
                'constraint' => '4,2',
                'default'    => 8.00,
            ],
            'actual_hours' => [
                'type'       => 'DECIMAL',
                'constraint' => '4,2',
                'null'       => true,
                'comment'    => 'Logged manually after the shift; overtime = max(0, actual_hours - standard_hours).',
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'scheduled',
                'comment'    => 'scheduled|completed|no_show|cancelled',
            ],
            'notes' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
            ],
            'created_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        // One person can't be scheduled twice for the same block on the
        // same day — catches double-booking at the schema level, not
        // just in the assign() form.
        $this->forge->addUniqueKey(['user_id', 'shift_date', 'shift_block']);
        $this->forge->addKey('shift_date');
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('created_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('shifts');

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'requesting_shift_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'requested_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'target_user_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'comment'    => 'Staff member being asked to take the shift.',
            ],
            'reason' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'pending',
                'comment'    => 'pending|approved|denied',
            ],
            'reviewed_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'reviewed_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('requesting_shift_id', 'shifts', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('requested_by', 'users', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('target_user_id', 'users', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('reviewed_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('shift_swap_requests');

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'user_id' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
            ],
            'date_from' => [
                'type' => 'DATE',
            ],
            'date_to' => [
                'type' => 'DATE',
            ],
            'reason' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => true,
            ],
            'status' => [
                'type'       => 'VARCHAR',
                'constraint' => 20,
                'default'    => 'pending',
                'comment'    => 'pending|approved|denied',
            ],
            'reviewed_by' => [
                'type'       => 'INT',
                'constraint' => 11,
                'unsigned'   => true,
                'null'       => true,
            ],
            'reviewed_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'created_at' => [
                'type' => 'DATETIME',
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('reviewed_by', 'users', 'id', '', 'SET NULL');
        $this->forge->createTable('time_off_requests');
    }

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