<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Plant readings — time-series measurement data (voltages, power, current).
 *
 *   plant_reading_sessions  — one row per data-collection round (photo of meter, remarks)
 *   plant_readings          — long-format: one row per (session, parameter_key)
 *
 * Photos attach to sessions via the polymorphic `attachments` table
 * (owner_type = 'reading_session').
 */
final class CreatePlantReadings extends AbstractMigration
{
    public function change(): void
    {
        $this->table('plant_reading_sessions', ['id' => 'session_id', 'signed' => false])
            ->addColumn('tenant_id',  'integer', ['signed' => false])
            ->addColumn('asset_id',   'integer', ['null' => true, 'signed' => false])
            ->addColumn('site_id',    'integer', ['null' => true, 'signed' => false])
            ->addColumn('taken_at',   'datetime')
            ->addColumn('taken_by',   'integer', ['null' => true, 'signed' => false])
            ->addColumn('status',     'string',  ['limit' => 20,  'default' => 'closed']) // draft|closed|reviewed
            ->addColumn('remarks',    'text',    ['null' => true])
            ->addColumn('supervisor_remarks','text',['null' => true])
            ->addColumn('legacy_id',  'integer', ['null' => true, 'signed' => false]) // rookpmgt_orch_view.power_plant_params.c0d
            ->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', 'taken_at'])
            ->addIndex(['legacy_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('taken_by',  'users',   'user_id',   ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        $this->table('plant_readings', ['id' => 'reading_id', 'signed' => false])
            ->addColumn('tenant_id',     'integer', ['signed' => false])
            ->addColumn('session_id',    'integer', ['signed' => false])
            ->addColumn('asset_id',      'integer', ['null' => true, 'signed' => false])
            ->addColumn('parameter_key', 'string',  ['limit' => 40])   // e.g. v_l_1, kw_l_2, kva_l_3, a_1
            ->addColumn('parameter_group','string', ['limit' => 20])   // voltage|power_kw|power_kvar|power_kva|current
            ->addColumn('value_number',  'decimal', ['precision' => 12, 'scale' => 3, 'null' => true])
            ->addColumn('unit',          'string',  ['limit' => 10])   // V, kW, kVAr, kVA, A
            ->addColumn('taken_at',      'datetime')
            ->addColumn('created_at',    'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['tenant_id', 'parameter_key', 'taken_at'])
            ->addIndex(['session_id'])
            ->addIndex(['taken_at'])
            ->addForeignKey('tenant_id',  'tenants',                 'tenant_id',  ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('session_id', 'plant_reading_sessions',  'session_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('asset_id',   'assets',                  'asset_id',   ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}
