<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateInvoicesAndPayments extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'               => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'        => ['type' => 'BIGINT', 'unsigned' => true],
            'invoice_number'   => ['type' => 'VARCHAR', 'constraint' => 50],
            'subtotal'         => ['type' => 'DECIMAL', 'constraint' => '12,2'],
            'tax_percentage'   => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 0],
            'tax_amount'       => ['type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0],
            'total'            => ['type' => 'DECIMAL', 'constraint' => '12,2'],
            'currency'         => ['type' => 'VARCHAR', 'constraint' => 10, 'default' => 'PKR'],
            'status'           => ['type' => 'ENUM', 'constraint' => ['draft', 'sent', 'paid', 'overdue', 'void'], 'default' => 'draft'],
            'due_date'         => ['type' => 'DATE', 'null' => true],
            'pdf_path'         => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at'       => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('invoice_number');
        $this->forge->addKey('status');
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('invoices', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'           => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'invoice_id'   => ['type' => 'BIGINT', 'unsigned' => true],
            'description'  => ['type' => 'VARCHAR', 'constraint' => 255],
            'quantity'     => ['type' => 'INT', 'unsigned' => true, 'default' => 1],
            'unit_price'   => ['type' => 'DECIMAL', 'constraint' => '12,2'],
            'line_total'   => ['type' => 'DECIMAL', 'constraint' => '12,2'],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('invoice_id', 'invoices', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('invoice_items', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                      => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'               => ['type' => 'BIGINT', 'unsigned' => true],
            'invoice_id'              => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'gateway'                 => ['type' => 'ENUM', 'constraint' => ['stripe', 'paypal', 'jazzcash', 'easypaisa', 'bank_transfer']],
            'gateway_transaction_id'  => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'purpose'                 => ['type' => 'ENUM', 'constraint' => ['subscription', 'credit_purchase', 'wallet_topup']],
            'amount'                  => ['type' => 'DECIMAL', 'constraint' => '12,2'],
            'currency'                => ['type' => 'VARCHAR', 'constraint' => 10, 'default' => 'PKR'],
            'status'                  => ['type' => 'ENUM', 'constraint' => ['pending', 'success', 'failed', 'refunded'], 'default' => 'pending'],
            'gateway_response'        => ['type' => 'JSON', 'null' => true],
            'created_at'              => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('status');
        $this->forge->addKey('gateway_transaction_id');
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('invoice_id', 'invoices', 'id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('payments', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'                  => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'payment_id'          => ['type' => 'BIGINT', 'unsigned' => true],
            'requested_by_user_id'=> ['type' => 'BIGINT', 'unsigned' => true],
            'reason'              => ['type' => 'TEXT'],
            'status'              => ['type' => 'ENUM', 'constraint' => ['pending', 'approved', 'rejected', 'processed'], 'default' => 'pending'],
            'processed_by'        => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'processed_at'        => ['type' => 'TIMESTAMP', 'null' => true],
            'created_at'          => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('payment_id', 'payments', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('requested_by_user_id', 'users', 'id', false, 'RESTRICT');
        $this->forge->createTable('refund_requests', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('refund_requests', true);
        $this->forge->dropTable('payments', true);
        $this->forge->dropTable('invoice_items', true);
        $this->forge->dropTable('invoices', true);
    }
}
