<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Operator Rounds — the daily patrol layer.
 *
 *   round_templates       one route definition, e.g. "Morning Plant Sweep"
 *   round_template_stops  ordered assets on that route (each may have expected reading limits)
 *   round_runs            one execution of a route by a specific operator
 *   round_run_stops       each stop in a run: reading collected, ok?, note, timestamps
 */
final class CreateOperatorRounds extends AbstractMigration
{
    public function change(): void
    {
        $this->table('round_templates', ['id' => 'round_template_id', 'signed' => false])
            ->addColumn('tenant_id',   'integer', ['signed' => false])
            ->addColumn('name',        'string',  ['limit' => 160])
            ->addColumn('description', 'text',    ['null' => true])
            ->addColumn('frequency',   'string',  ['limit' => 30,  'default' => 'daily'])
            // daily | shift | weekly | ad_hoc
            ->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', 'is_active'])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('round_template_stops', ['id' => 'stop_id', 'signed' => false])
            ->addColumn('round_template_id', 'integer', ['signed' => false])
            ->addColumn('asset_id',          'integer', ['signed' => false])
            ->addColumn('sort_order',        'integer', ['default' => 0])
            ->addColumn('reading_key',       'string',  ['limit' => 60,  'null' => true])
            ->addColumn('reading_unit',      'string',  ['limit' => 20,  'null' => true])
            ->addColumn('min_ok',            'decimal', ['precision' => 12, 'scale' => 3, 'null' => true])
            ->addColumn('max_ok',            'decimal', ['precision' => 12, 'scale' => 3, 'null' => true])
            ->addColumn('instruction',       'text',    ['null' => true])
            ->addIndex(['round_template_id', 'sort_order'])
            ->addForeignKey('round_template_id', 'round_templates', 'round_template_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('asset_id',          'assets',          'asset_id',          ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('round_runs', ['id' => 'run_id', 'signed' => false])
            ->addColumn('tenant_id',         'integer', ['signed' => false])
            ->addColumn('round_template_id', 'integer', ['signed' => false])
            ->addColumn('operator_id',       'integer', ['null' => true, 'signed' => false])
            ->addColumn('started_at',        'datetime',['null' => true])
            ->addColumn('finished_at',       'datetime',['null' => true])
            ->addColumn('status',            'string',  ['limit' => 20, 'default' => 'in_progress'])
            // in_progress | completed | abandoned
            ->addColumn('notes',             'text',    ['null' => true])
            ->addColumn('created_at',        'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['tenant_id', 'status'])
            ->addIndex(['round_template_id', 'started_at'])
            ->addForeignKey('tenant_id',         'tenants',         'tenant_id',         ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('round_template_id', 'round_templates', 'round_template_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('operator_id',       'users',           'user_id',           ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        $this->table('round_run_stops', ['id' => 'run_stop_id', 'signed' => false])
            ->addColumn('run_id',        'integer', ['signed' => false])
            ->addColumn('stop_id',       'integer', ['signed' => false])
            ->addColumn('asset_id',      'integer', ['signed' => false])
            ->addColumn('reading_value', 'decimal', ['precision' => 14, 'scale' => 3, 'null' => true])
            ->addColumn('reading_text',  'string',  ['limit' => 200, 'null' => true])
            ->addColumn('is_ok',         'boolean', ['default' => true])
            ->addColumn('note',          'text',    ['null' => true])
            ->addColumn('checked_at',    'datetime',['null' => true])
            ->addIndex(['run_id'])
            ->addForeignKey('run_id',   'round_runs',           'run_id',   ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('stop_id',  'round_template_stops', 'stop_id',  ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->addForeignKey('asset_id', 'assets',               'asset_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();
    }
}
