<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateExpenses extends AbstractMigration
{
    public function change(): void
    {
        $this->table('expense_categories', ['id' => 'category_id', 'signed' => false])
            ->addColumn('tenant_id', 'integer', ['signed' => false])
            ->addColumn('code',      'string',  ['limit' => 40])
            ->addColumn('name',      'string',  ['limit' => 120])
            ->addColumn('is_active', 'boolean', ['default' => true])
            ->addColumn('created_at','datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['tenant_id', 'code'], ['unique' => true])
            ->addForeignKey('tenant_id', 'tenants', 'tenant_id', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->create();

        $this->table('expenses', ['id' => 'expense_id', 'signed' => false])
            ->addColumn('tenant_id',      'integer', ['signed' => false])
            ->addColumn('code',           'string',  ['limit' => 40,  'null' => true])
            ->addColumn('title',          'string',  ['limit' => 200])
            ->addColumn('description',    'text',    ['null' => true])
            ->addColumn('category_id',    'integer', ['null' => true, 'signed' => false])
            ->addColumn('work_order_id',  'integer', ['null' => true, 'signed' => false])
            ->addColumn('site_id',        'integer', ['null' => true, 'signed' => false])
            ->addColumn('asset_id',       'integer', ['null' => true, 'signed' => false])
            ->addColumn('submitted_by',   'integer', ['null' => true, 'signed' => false])
            ->addColumn('approved_by',    'integer', ['null' => true, 'signed' => false])
            ->addColumn('expense_date',   'date',    ['null' => true])
            ->addColumn('amount',         'decimal', ['precision' => 14, 'scale' => 2, 'default' => 0])
            ->addColumn('currency',       'string',  ['limit' => 5, 'default' => 'TZS'])
            ->addColumn('vendor',         'string',  ['limit' => 160, 'null' => true])
            ->addColumn('receipt_ref',    'string',  ['limit' => 80,  'null' => true])
            ->addColumn('payment_method', 'string',  ['limit' => 40,  'null' => true])
            ->addColumn('status',         'string',  ['limit' => 30, 'default' => 'draft']) // draft|submitted|approved|rejected|paid|cancelled
            ->addColumn('approval_note',  'text',    ['null' => true])
            ->addColumn('submitted_at',   'datetime',['null' => true])
            ->addColumn('approved_at',    'datetime',['null' => true])
            ->addColumn('paid_at',        'datetime',['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', 'submitted_by', 'status'])
            ->addIndex(['tenant_id', 'expense_date'])
            ->addIndex(['work_order_id'])
            ->addForeignKey('tenant_id',     'tenants',            'tenant_id',     ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('category_id',   'expense_categories', 'category_id',   ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('work_order_id', 'work_orders',        'work_order_id', ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('site_id',       'sites',              'site_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('asset_id',      'assets',             'asset_id',      ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('submitted_by',  'users',              'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->addForeignKey('approved_by',   'users',              'user_id',       ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        // Seed categories per tenant.
        $tenantId = (int) $this->fetchRow(
            "SELECT tenant_id FROM tenants WHERE subdomain = 'orchid' LIMIT 1"
        )['tenant_id'];

        foreach ([
            ['fuel',       'Fuel'],
            ['parts',      'Spare Parts'],
            ['labour',     'Labour'],
            ['tools',      'Tools'],
            ['transport',  'Transport'],
            ['permits',    'Permits & Fees'],
            ['other',      'Other'],
        ] as [$code, $name]) {
            $this->table('expense_categories')->insert([
                'tenant_id' => $tenantId,
                'code'      => $code,
                'name'      => $name,
                'is_active' => 1,
            ])->saveData();
        }
    }
}
