<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Quotes and quote line items.
 */
final class CreateQuotes extends AbstractMigration
{
    public function change(): void
    {
        $this->table('quotes', ['id' => 'quote_id', 'signed' => false])
            ->addColumn('tenant_id',         'integer', ['signed' => false])
            ->addColumn('quote_number',      'string',  ['limit' => 40])
            ->addColumn('customer_id',       'integer', ['signed' => false])
            ->addColumn('status',            'string',  ['limit' => 20, 'default' => 'draft'])
            ->addColumn('subtotal',          'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('discount_percent',  'decimal', ['precision' => 5,  'scale' => 2, 'default' => 0.00])
            ->addColumn('discount_amount',   'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('tax_total',         'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('grand_total',       'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('valid_until',       'date',    ['null' => true])
            ->addColumn('notes',             'text',    ['null' => true])
            ->addColumn('terms',             'text',    ['null' => true])
            ->addColumn('created_by',        'integer', ['null' => true, 'signed' => false])
            ->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', 'quote_number'], ['unique' => true])
            ->addIndex(['customer_id'])
            ->addForeignKey('tenant_id',   'tenants',   'tenant_id',   ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('customer_id', 'customers', 'customer_id', ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('created_by',  'users',     'user_id',     ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        $this->table('quote_items', ['id' => 'quote_item_id', 'signed' => false])
            ->addColumn('quote_id',         'integer', ['signed' => false])
            ->addColumn('item_id',          'integer', ['null' => true, 'signed' => false, 'comment' => 'FK to products_services, NULL for ad-hoc'])
            ->addColumn('description',      'string',  ['limit' => 300])
            ->addColumn('quantity',         'decimal', ['precision' => 10, 'scale' => 2, 'default' => 1.00])
            ->addColumn('unit_price',       'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('discount_percent', 'decimal', ['precision' => 5,  'scale' => 2, 'default' => 0.00])
            ->addColumn('line_total',       'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('sort_order',       'integer', ['default' => 0])
            ->addColumn('created_at',       'datetime',['default' => 'CURRENT_TIMESTAMP'])
            ->addIndex(['quote_id', 'sort_order'])
            ->addForeignKey('quote_id', 'quotes',           'quote_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('item_id',  'products_services', 'item_id',  ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}