<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Spare Parts inventory + WO consumption.
 *
 *   parts                catalog row per SKU (name, unit cost, min/max stock, on-hand)
 *   stock_movements      immutable ledger — every in/out/adjustment on a part
 *   wo_parts_used        parts consumed on a specific WO (feeds material cost)
 *
 * on_hand on `parts` is denormalised for speed — the ledger is the source of
 * truth and can rebuild it if we ever suspect drift.
 */
final class CreateSpareParts extends AbstractMigration
{
    public function change(): void
    {
        $this->table('parts', ['id' => 'part_id', 'signed' => false])
            ->addColumn('tenant_id',    'integer', ['signed' => false])
            ->addColumn('sku',          'string',  ['limit' => 60])
            ->addColumn('name',         'string',  ['limit' => 160])
            ->addColumn('description',  'text',    ['null' => true])
            ->addColumn('category',     'string',  ['limit' => 60,  'null' => true])
            ->addColumn('unit',         'string',  ['limit' => 20,  'default' => 'ea']) // ea | m | kg | L
            ->addColumn('unit_cost',    'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0])
            ->addColumn('on_hand',      'decimal', ['precision' => 14, 'scale' => 3, 'default' => 0])
            ->addColumn('min_stock',    'decimal', ['precision' => 14, 'scale' => 3, 'default' => 0])
            ->addColumn('max_stock',    'decimal', ['precision' => 14, 'scale' => 3, 'null' => true])
            ->addColumn('reorder_qty',  'decimal', ['precision' => 14, 'scale' => 3, 'null' => true])
            ->addColumn('location',     'string',  ['limit' => 100, 'null' => true])
            ->addColumn('supplier',     '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', 'sku'], ['unique' => true])
            ->addIndex(['tenant_id', 'category'])
            ->addIndex(['tenant_id', 'is_active'])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('stock_movements', ['id' => 'movement_id', 'signed' => false])
            ->addColumn('tenant_id',    'integer', ['signed' => false])
            ->addColumn('part_id',      'integer', ['signed' => false])
            ->addColumn('direction',    'string',  ['limit' => 10])  // in | out | adjust
            ->addColumn('quantity',     'decimal', ['precision' => 14, 'scale' => 3])
            ->addColumn('unit_cost',    'decimal', ['precision' => 12, 'scale' => 2, 'null' => true])
            ->addColumn('reason',       'string',  ['limit' => 100, 'null' => true])
            ->addColumn('work_order_id','integer', ['null' => true, 'signed' => false])
            ->addColumn('user_id',      'integer', ['null' => true, 'signed' => false])
            ->addColumn('created_at',   'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['tenant_id', 'part_id', 'created_at'])
            ->addIndex(['work_order_id'])
            ->addForeignKey('tenant_id',    'tenants',     'tenant_id',     ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('part_id',      'parts',       'part_id',       ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('work_order_id','work_orders', 'work_order_id', ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('user_id',      'users',       'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        // wo_parts_used is a convenience denorm — one row per (WO, part) with
        // qty + unit_cost captured at consumption time. The ledger row above
        // is the source of truth; this table just makes "what parts on this WO"
        // fast to render on the WO show page.
        $this->table('wo_parts_used', ['id' => 'wo_part_id', 'signed' => false])
            ->addColumn('work_order_id', 'integer', ['signed' => false])
            ->addColumn('part_id',       'integer', ['signed' => false])
            ->addColumn('quantity',      'decimal', ['precision' => 14, 'scale' => 3])
            ->addColumn('unit_cost',     'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0])
            ->addColumn('user_id',       'integer', ['null' => true, 'signed' => false])
            ->addColumn('created_at',    'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['work_order_id'])
            ->addIndex(['part_id'])
            ->addForeignKey('work_order_id', 'work_orders', 'work_order_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('part_id',       'parts',       'part_id',       ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('user_id',       'users',       'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}
