<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Unified incidents table replacing legacy OrchView's three parallel tables:
 *   equipments_incidents, external_incidents, h_n_s_incidents.
 *
 * `category` = equipment | external | health_safety
 * `details_json` holds the category-specific fields the old tables had.
 */
final class CreateIncidents extends AbstractMigration
{
    public function change(): void
    {
        // Central lookup for incident types + category-specific causes.
        $this->table('incident_types', ['id' => 'incident_type_id', 'signed' => false])
            ->addColumn('tenant_id', 'integer', ['signed' => false])
            ->addColumn('category',  'string',  ['limit' => 30])
            ->addColumn('code',      'string',  ['limit' => 60])
            ->addColumn('name',      'string',  ['limit' => 160])
            ->addColumn('created_at','datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['tenant_id', 'category', 'code'], ['unique' => true])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('incidents', ['id' => 'incident_id', 'signed' => false])
            ->addColumn('tenant_id',        'integer', ['signed' => false])
            ->addColumn('category',         'string',  ['limit' => 30]) // equipment | external | health_safety
            ->addColumn('incident_type_id', 'integer', ['null' => true, 'signed' => false])
            ->addColumn('asset_id',         'integer', ['null' => true, 'signed' => false])
            ->addColumn('site_id',          'integer', ['null' => true, 'signed' => false])
            ->addColumn('code',             'string',  ['limit' => 40,  'null' => true])
            ->addColumn('title',            'string',  ['limit' => 200])
            ->addColumn('description',      'text',    ['null' => true])
            ->addColumn('severity',         'string',  ['limit' => 20,  'default' => 'medium']) // low|medium|high|critical
            ->addColumn('impact',           'string',  ['limit' => 20,  'null' => true])
            ->addColumn('urgency',          'string',  ['limit' => 20,  'null' => true])
            ->addColumn('status',           'string',  ['limit' => 30,  'default' => 'draft'])  // draft|open|investigating|rca|closed_permanent|closed_temporary|cancelled
            ->addColumn('reported_by',      'integer', ['null' => true, 'signed' => false])
            ->addColumn('assigned_to',      'integer', ['null' => true, 'signed' => false])
            ->addColumn('start_at',         'datetime',['null' => true])
            ->addColumn('end_at',           'datetime',['null' => true])
            ->addColumn('closed_at',        'datetime',['null' => true])
            ->addColumn('root_cause',       'text',    ['null' => true])
            ->addColumn('resolution',       'text',    ['null' => true])
            ->addColumn('details_json',     'json',    ['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', 'category', 'status'])
            ->addIndex(['tenant_id', 'severity'])
            ->addIndex(['asset_id'])
            ->addIndex(['site_id'])
            ->addIndex(['assigned_to'])
            ->addIndex(['tenant_id', 'start_at'])
            ->addForeignKey('tenant_id',        'tenants',        'tenant_id',        ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('incident_type_id', 'incident_types', 'incident_type_id', ['delete' => 'SET_NULL', '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('reported_by',      'users',          'user_id',          ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('assigned_to',      'users',          'user_id',          ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        // Timeline / activity feed per incident. Every state change or note is a row.
        $this->table('incident_events', ['id' => 'event_id', 'signed' => false])
            ->addColumn('incident_id', 'integer', ['signed' => false])
            ->addColumn('user_id',     'integer', ['null' => true, 'signed' => false])
            ->addColumn('event_type',  'string',  ['limit' => 40]) // note|status_change|assignment|attachment
            ->addColumn('from_value',  'string',  ['limit' => 80,  'null' => true])
            ->addColumn('to_value',    'string',  ['limit' => 80,  'null' => true])
            ->addColumn('message',     'text',    ['null' => true])
            ->addColumn('created_at',  'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['incident_id', 'created_at'])
            ->addForeignKey('incident_id', 'incidents', 'incident_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('user_id',     'users',     'user_id',     ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        // Starter incident types per tenant — mirror legacy OrchView categorisation.
        $tenantId = (int) $this->fetchRow(
            "SELECT tenant_id FROM tenants WHERE subdomain = 'orchid' LIMIT 1"
        )['tenant_id'];

        $starter = [
            // equipment
            ['equipment', 'mechanical_failure', 'Mechanical Failure'],
            ['equipment', 'electrical_failure', 'Electrical Failure'],
            ['equipment', 'overheating',        'Overheating'],
            ['equipment', 'oil_leak',           'Oil Leak'],
            ['equipment', 'coolant_leak',       'Coolant Leak'],
            ['equipment', 'wear_and_tear',      'Wear and Tear'],
            // external
            ['external',  'power_outage',       'Power Outage'],
            ['external',  'fuel_shortage',      'Fuel Shortage'],
            ['external',  'weather',            'Adverse Weather'],
            ['external',  'vandalism',          'Vandalism'],
            // health_safety
            ['health_safety', 'injury',         'Injury'],
            ['health_safety', 'near_miss',      'Near Miss'],
            ['health_safety', 'fire',           'Fire'],
            ['health_safety', 'chemical_spill', 'Chemical Spill'],
        ];
        foreach ($starter as $r) {
            $this->table('incident_types')->insert([
                'tenant_id' => $tenantId,
                'category'  => $r[0],
                'code'      => $r[1],
                'name'      => $r[2],
            ])->saveData();
        }
    }
}
