<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Work orders drive field work. A WO can be:
 *   - preventive (from a schedule)
 *   - corrective (from an incident)
 *   - adhoc     (ad-hoc PM-created request)
 *
 * Tasks are the checklist inside — the field tech marks each done from the mobile app.
 */
final class CreateWorkOrders extends AbstractMigration
{
    public function change(): void
    {
        $this->table('work_orders', ['id' => 'work_order_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('wo_type',     'string',  ['limit' => 20, 'default' => 'corrective']) // preventive|corrective|adhoc|inspection
            ->addColumn('priority',    'string',  ['limit' => 20, 'default' => 'medium'])     // low|medium|high|urgent
            ->addColumn('status',      'string',  ['limit' => 30, 'default' => 'draft'])      // draft|assigned|in_progress|on_hold|completed|cancelled
            ->addColumn('site_id',     'integer', ['null' => true, 'signed' => false])
            ->addColumn('asset_id',    'integer', ['null' => true, 'signed' => false])
            ->addColumn('incident_id', 'integer', ['null' => true, 'signed' => false])
            ->addColumn('assigned_to', 'integer', ['null' => true, 'signed' => false])
            ->addColumn('created_by',  'integer', ['null' => true, 'signed' => false])
            ->addColumn('scheduled_at','datetime',['null' => true])
            ->addColumn('due_at',      'datetime',['null' => true])
            ->addColumn('started_at',  'datetime',['null' => true])
            ->addColumn('completed_at','datetime',['null' => true])
            ->addColumn('estimated_hours','decimal',['precision' => 6, 'scale' => 2, 'null' => true])
            ->addColumn('actual_hours','decimal', ['precision' => 6, 'scale' => 2, 'null' => true])
            ->addColumn('notes',       'text',    ['null' => true])
            ->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', 'wo_type', 'status'])
            ->addIndex(['tenant_id', 'assigned_to', 'status'])
            ->addIndex(['tenant_id', 'due_at'])
            ->addIndex(['asset_id'])
            ->addIndex(['site_id'])
            ->addIndex(['incident_id'])
            ->addForeignKey('tenant_id',   'tenants',   'tenant_id',   ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('site_id',     'sites',     'site_id',     ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('asset_id',    'assets',    'asset_id',    ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('incident_id', 'incidents', 'incident_id', ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('assigned_to', 'users',     'user_id',     ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('created_by',  'users',     'user_id',     ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        $this->table('work_order_tasks', ['id' => 'task_id', 'signed' => false])
            ->addColumn('work_order_id', 'integer', ['signed' => false])
            ->addColumn('sort_order',    'integer', ['default' => 0])
            ->addColumn('title',         'string',  ['limit' => 200])
            ->addColumn('description',   'text',    ['null' => true])
            ->addColumn('is_done',       'boolean', ['default' => false])
            ->addColumn('done_by',       'integer', ['null' => true, 'signed' => false])
            ->addColumn('done_at',       'datetime',['null' => true])
            ->addColumn('result_note',   'text',    ['null' => true])
            ->addColumn('created_at',    'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addColumn('updated_at',    'datetime',['default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['work_order_id', 'sort_order'])
            ->addForeignKey('work_order_id', 'work_orders', 'work_order_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('done_by',       'users',       'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}
