<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Work Requests — lightweight ticket layer that sits *in front of* Work Orders.
 * Anyone (operator, kiosk, client) can raise a request; a PM/admin triages it
 * into a real WO (approved) or rejects it. Keeps the WO pipeline clean and
 * gives us a raw "demand" funnel for reporting.
 */
final class CreateWorkRequests extends AbstractMigration
{
    public function change(): void
    {
        $this->table('work_requests', ['id' => 'request_id', 'signed' => false])
            ->addColumn('tenant_id',       'integer', ['signed' => false])
            ->addColumn('code',            'string',  ['limit' => 40,  'null' => true])
            ->addColumn('title',           'string',  ['limit' => 200])
            ->addColumn('description',     'text',    ['null' => true])
            ->addColumn('priority',        'string',  ['limit' => 20,  'default' => 'medium'])
            ->addColumn('status',          'string',  ['limit' => 20,  'default' => 'new'])
            // new | approved | rejected | converted
            ->addColumn('asset_id',        'integer', ['null' => true, 'signed' => false])
            ->addColumn('site_id',         'integer', ['null' => true, 'signed' => false])
            ->addColumn('requested_by',    'integer', ['null' => true, 'signed' => false])
            ->addColumn('requested_email', 'string',  ['limit' => 160, 'null' => true])
            ->addColumn('reviewed_by',     'integer', ['null' => true, 'signed' => false])
            ->addColumn('reviewed_at',     'datetime',['null' => true])
            ->addColumn('review_note',     'text',    ['null' => true])
            ->addColumn('converted_wo_id', 'integer', ['null' => true, 'signed' => false])
            ->addColumn('created_at',      'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addColumn('updated_at',      'datetime',['default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'])
            ->addColumn('deleted_at',      'datetime',['null' => true])
            ->addIndex(['tenant_id', 'status'])
            ->addIndex(['tenant_id', 'priority'])
            ->addIndex(['asset_id'])
            ->addIndex(['converted_wo_id'])
            ->addForeignKey('tenant_id',       'tenants',     'tenant_id',     ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('asset_id',        'assets',      'asset_id',      ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('site_id',         'sites',       'site_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('requested_by',    'users',       'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('reviewed_by',     'users',       'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('converted_wo_id', 'work_orders', 'work_order_id', ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}
