<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Invoices, invoice line items, and payments.
 */
final class CreateInvoices extends AbstractMigration
{
    public function change(): void
    {
        $this->table('invoices', ['id' => 'invoice_id', 'signed' => false])
            ->addColumn('tenant_id',         'integer', ['signed' => false])
            ->addColumn('quote_id',          'integer', ['null' => true, 'signed' => false, 'comment' => 'Source quote if converted'])
            ->addColumn('customer_id',       'integer', ['signed' => false])
            ->addColumn('invoice_number',    'string',  ['limit' => 40])
            ->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('amount_paid',       'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('balance_due',       'decimal', ['precision' => 12, 'scale' => 2, 'default' => 0.00])
            ->addColumn('due_date',          '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', 'invoice_number'], ['unique' => true])
            ->addIndex(['tenant_id', 'due_date'])
            ->addIndex(['customer_id'])
            ->addIndex(['quote_id'])
            ->addForeignKey('tenant_id',   'tenants',   'tenant_id',   ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('quote_id',    'quotes',    'quote_id',    ['delete' => 'SET_NULL', '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('invoice_items', ['id' => 'invoice_item_id', 'signed' => false])
            ->addColumn('invoice_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(['invoice_id', 'sort_order'])
            ->addForeignKey('invoice_id', 'invoices',         'invoice_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('item_id',    'products_services', 'item_id',    ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();

        $this->table('payments', ['id' => 'payment_id', 'signed' => false])
            ->addColumn('tenant_id',        'integer', ['signed' => false])
            ->addColumn('invoice_id',       'integer', ['signed' => false])
            ->addColumn('amount',           'decimal', ['precision' => 12, 'scale' => 2])
            ->addColumn('payment_method',   'string',  ['limit' => 30, 'default' => 'bank_transfer'])
            ->addColumn('reference_number', 'string',  ['limit' => 100, 'null' => true])
            ->addColumn('payment_date',     'date')
            ->addColumn('notes',            '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'])
            ->addIndex(['tenant_id', 'payment_date'])
            ->addIndex(['invoice_id'])
            ->addForeignKey('tenant_id',  'tenants',  'tenant_id',  ['delete' => 'RESTRICT', 'update' => 'CASCADE'])
            ->addForeignKey('invoice_id', 'invoices', 'invoice_id', ['delete' => 'CASCADE',  'update' => 'CASCADE'])
            ->addForeignKey('created_by', 'users',    'user_id',    ['delete' => 'SET_NULL', 'update' => 'CASCADE'])
            ->create();
    }
}