<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * HSE Library — Safety Data Sheets + Training Records.
 *
 *   sds                   one row per controlled substance / material
 *   training_courses      catalog of certifications (e.g. "Working at Height")
 *   training_records      per-user completion, with expiry = completed + validity_months
 *
 * Attachments (PDFs, certs, etc) piggy-back on the polymorphic attachments
 * table via owner_type='sds' | 'training_record'.
 */
final class CreateHseLibrary extends AbstractMigration
{
    public function change(): void
    {
        $this->table('sds', ['id' => 'sds_id', 'signed' => false])
            ->addColumn('tenant_id',      'integer', ['signed' => false])
            ->addColumn('code',           'string',  ['limit' => 40,  'null' => true])
            ->addColumn('name',           'string',  ['limit' => 200])
            ->addColumn('manufacturer',   'string',  ['limit' => 160, 'null' => true])
            ->addColumn('supplier',       'string',  ['limit' => 160, 'null' => true])
            ->addColumn('cas_number',     'string',  ['limit' => 40,  'null' => true])
            ->addColumn('hazard_class',   'string',  ['limit' => 60,  'null' => true])
            // flammable | toxic | corrosive | oxidiser | health | environment | other
            ->addColumn('storage_notes',  'text',    ['null' => true])
            ->addColumn('first_aid',      'text',    ['null' => true])
            ->addColumn('disposal_notes', 'text',    ['null' => true])
            ->addColumn('ppe_required',   'string',  ['limit' => 255, 'null' => true])
            ->addColumn('hazards',        'text',    ['null' => true])
            ->addColumn('issued_at',      'date',    ['null' => true])
            ->addColumn('review_at',      'date',    ['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', 'name'])
            ->addIndex(['tenant_id', 'review_at'])
            ->addIndex(['tenant_id', 'hazard_class'])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('training_courses', ['id' => 'course_id', 'signed' => false])
            ->addColumn('tenant_id',        'integer', ['signed' => false])
            ->addColumn('code',             'string',  ['limit' => 40,  'null' => true])
            ->addColumn('name',             'string',  ['limit' => 160])
            ->addColumn('description',      'text',    ['null' => true])
            ->addColumn('validity_months',  'integer', ['default' => 12])
            ->addColumn('issuer',           'string',  ['limit' => 160, 'null' => true])
            ->addColumn('is_active',        'boolean', ['default' => 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', 'name'])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('training_records', ['id' => 'record_id', 'signed' => false])
            ->addColumn('tenant_id',          'integer', ['signed' => false])
            ->addColumn('course_id',          'integer', ['signed' => false])
            ->addColumn('user_id',            'integer', ['null' => true, 'signed' => false])
            // external_name lets us track contractor / visitor certs without
            // needing a full user account for every third-party attendee.
            ->addColumn('external_name',      'string',  ['limit' => 160, 'null' => true])
            ->addColumn('completed_at',       'date')
            ->addColumn('expires_at',         'date',    ['null' => true])
            ->addColumn('certificate_number', 'string',  ['limit' => 80,  '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', 'expires_at'])
            ->addIndex(['tenant_id', 'user_id'])
            ->addIndex(['course_id'])
            ->addForeignKey('tenant_id', 'tenants',          'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('course_id', 'training_courses', 'course_id', ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('user_id',   'users',            'user_id',   ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        // Seed a starter catalogue for the primary tenant so admins have
        // something concrete to see the first time they open the page.
        $tenantId = (int) ($this->fetchRow(
            "SELECT tenant_id FROM tenants WHERE subdomain = 'orchid' LIMIT 1"
        )['tenant_id'] ?? 0);
        if ($tenantId > 0) {
            $courses = [
                ['HSE-INDUCTION', 'HSE Site Induction',        'Mandatory induction for anyone working on site.',      12, 'Internal'],
                ['FIRST-AID',     'First Aid at Work',         'Basic first-aid certification.',                       36, 'External'],
                ['WORK-HEIGHT',   'Working at Height',         'Fall prevention, harness inspection, rescue plan.',    24, 'External'],
                ['HOT-WORK',      'Hot Work / Welding',        'Fire watch, extinguisher, spark containment.',         24, 'External'],
                ['CONF-SPACE',    'Confined Space Entry',      'Atmosphere testing, permit issue, rescue drill.',      12, 'External'],
                ['LOTO',          'Lockout / Tagout',          'Energy isolation for maintenance.',                    24, 'Internal'],
                ['FORKLIFT',      'Forklift Operator',         'Powered industrial-truck operation.',                  36, 'External'],
                ['CHEM-SAFETY',   'Chemical Handling',         'COSHH, spill response, PPE selection.',                24, 'External'],
                ['ELEC-LV',       'Low-Voltage Electrical',    'LV switching, isolation, PPE.',                        24, 'External'],
                ['FIRE-WARDEN',   'Fire Warden',               'Evacuation, extinguisher use, roll-call.',             24, 'Internal'],
            ];
            foreach ($courses as $c) {
                $this->table('training_courses')->insert([
                    'tenant_id'       => $tenantId,
                    'code'            => $c[0],
                    'name'            => $c[1],
                    'description'     => $c[2],
                    'validity_months' => $c[3],
                    'issuer'          => $c[4],
                    'is_active'       => 1,
                ])->saveData();
            }
        }
    }
}
