<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class EnhanceTransactionsTable extends Migration
{
    public function up()
    {
        // Create a new payment_transactions table for comprehensive payment tracking
        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'transaction_id' => [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'comment' => 'Our internal transaction ID'
            ],
            'reference' => [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'null' => true,
                'comment' => 'External reference from payment gateway'
            ],
            'msisdn' => [
                'type' => 'VARCHAR',
                'constraint' => 20,
                'comment' => 'Phone number'
            ],
            'amount' => [
                'type' => 'DECIMAL',
                'constraint' => '15,2',
                'comment' => 'Transaction amount'
            ],
            'status' => [
                'type' => 'ENUM',
                'constraint' => ['PENDING', 'SUCCESS', 'FAILED', 'CANCELLED'],
                'default' => 'PENDING',
                'comment' => 'Transaction status'
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'comment' => 'Transaction creation time'
            ]
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey('transaction_id');
        $this->forge->addKey('reference');
        $this->forge->addKey('msisdn');
        $this->forge->addKey('status');
        $this->forge->addKey('created_at');
        $this->forge->createTable('payment_transactions');

        // Add new columns to the payment_transactions table for comprehensive tracking
        $fields = [
            'currency' => [
                'type' => 'VARCHAR',
                'constraint' => 10,
                'default' => 'UGX',
                'comment' => 'Transaction currency'
            ],
            'description' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'Payment description'
            ],
            'payment_method' => [
                'type' => 'VARCHAR',
                'constraint' => 50,
                'default' => 'mobile_money',
                'comment' => 'Payment method used'
            ],
            'gateway_reference' => [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'null' => true,
                'comment' => 'Reference from payment gateway'
            ],
            'gateway_response' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'Full response from payment gateway'
            ],
            'client_ip' => [
                'type' => 'VARCHAR',
                'constraint' => 45,
                'null' => true,
                'comment' => 'Client IP address'
            ],
            'user_agent' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'Client user agent'
            ],
            'failure_reason' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'Reason for transaction failure'
            ],
            'processed_at' => [
                'type' => 'DATETIME',
                'null' => true,
                'comment' => 'When transaction was processed'
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
                'comment' => 'Last update timestamp'
            ],
            'chargeback_status' => [
                'type' => 'ENUM',
                'constraint' => ['none', 'pending', 'disputed', 'won', 'lost'],
                'default' => 'none',
                'comment' => 'Chargeback status'
            ],
            'chargeback_reason' => [
                'type' => 'TEXT',
                'null' => true,
                'comment' => 'Chargeback reason if applicable'
            ],
            'chargeback_date' => [
                'type' => 'DATETIME',
                'null' => true,
                'comment' => 'Date chargeback was initiated'
            ],
            'refund_status' => [
                'type' => 'ENUM',
                'constraint' => ['none', 'pending', 'processed', 'failed'],
                'default' => 'none',
                'comment' => 'Refund status'
            ],
            'refund_amount' => [
                'type' => 'DECIMAL',
                'constraint' => '15,2',
                'default' => 0.00,
                'comment' => 'Refunded amount'
            ],
            'refund_reference' => [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'null' => true,
                'comment' => 'Refund reference from gateway'
            ],
            'webhook_attempts' => [
                'type' => 'INT',
                'constraint' => 3,
                'default' => 0,
                'comment' => 'Number of webhook delivery attempts'
            ],
            'last_webhook_attempt' => [
                'type' => 'DATETIME',
                'null' => true,
                'comment' => 'Last webhook delivery attempt'
            ],
            'metadata' => [
                'type' => 'JSON',
                'null' => true,
                'comment' => 'Additional transaction metadata'
            ]
        ];

        $this->forge->addColumn('payment_transactions', $fields);

        // Add indexes for better performance
        $this->forge->addKey(['status', 'created_at']);
        $this->forge->addKey(['chargeback_status']);
        $this->forge->addKey(['refund_status']);
        $this->forge->addKey(['gateway_reference']);
        $this->forge->addKey(['msisdn', 'created_at']);
    }

    public function down()
    {
        // Drop the payment_transactions table
        $this->forge->dropTable('payment_transactions');
    }
}
