<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Transaction_model extends CI_Model {

    protected $table = 'transactions';

    public function __construct() {
        parent::__construct();
    }

    /**
     * Get transaction by ID
     */
    public function get($id) {
        return $this->db->get_where($this->table, ['id' => $id])->row();
    }

    /**
     * Get transaction by reference
     */
    public function get_by_reference($reference) {
        return $this->db->get_where($this->table, ['reference' => $reference])->row();
    }

    /**
     * Get transactions for an account
     */
    public function get_by_account($account_id, $limit = 20, $offset = 0) {
        $this->db->group_start();
        $this->db->where('source_account_id', $account_id);
        $this->db->or_where('destination_account_id', $account_id);
        $this->db->group_end();
        $this->db->order_by('created_at', 'DESC');
        $this->db->limit($limit, $offset);
        return $this->db->get($this->table)->result();
    }

    /**
     * Get transactions for a user (all accounts)
     */
    public function get_by_user($user_id, $filters = [], $limit = 20, $offset = 0) {
        $this->db->select('transactions.*, 
            sa.account_number as source_account_number, sa.account_name as source_account_name,
            da.account_number as dest_account_number, da.account_name as dest_account_name');
        $this->db->from($this->table);
        $this->db->join('accounts sa', 'sa.id = transactions.source_account_id', 'left');
        $this->db->join('accounts da', 'da.id = transactions.destination_account_id', 'left');
        
        $this->db->group_start();
        $this->db->where('sa.user_id', $user_id);
        $this->db->or_where('da.user_id', $user_id);
        $this->db->group_end();
        
        if (!empty($filters['type'])) {
            $this->db->where('transactions.type', $filters['type']);
        }
        if (!empty($filters['status'])) {
            $this->db->where('transactions.status', $filters['status']);
        }
        if (!empty($filters['date_from'])) {
            $this->db->where('transactions.created_at >=', $filters['date_from']);
        }
        if (!empty($filters['date_to'])) {
            $this->db->where('transactions.created_at <=', $filters['date_to']);
        }
        
        $this->db->order_by('transactions.created_at', 'DESC');
        $this->db->limit($limit, $offset);
        
        return $this->db->get()->result();
    }

    /**
     * Get all transactions with filters (admin)
     */
    public function get_all($filters = [], $limit = 50, $offset = 0) {
        $this->db->select('transactions.*, 
            sa.account_number as source_account_number, su.first_name as source_first_name, su.last_name as source_last_name,
            da.account_number as dest_account_number, du.first_name as dest_first_name, du.last_name as dest_last_name');
        $this->db->from($this->table);
        $this->db->join('accounts sa', 'sa.id = transactions.source_account_id', 'left');
        $this->db->join('users su', 'su.id = sa.user_id', 'left');
        $this->db->join('accounts da', 'da.id = transactions.destination_account_id', 'left');
        $this->db->join('users du', 'du.id = da.user_id', 'left');
        
        if (!empty($filters['type'])) {
            $this->db->where('transactions.type', $filters['type']);
        }
        if (!empty($filters['status'])) {
            $this->db->where('transactions.status', $filters['status']);
        }
        if (!empty($filters['search'])) {
            $this->db->group_start();
            $this->db->like('transactions.reference', $filters['search']);
            $this->db->or_like('sa.account_number', $filters['search']);
            $this->db->or_like('da.account_number', $filters['search']);
            $this->db->group_end();
        }
        
        $this->db->order_by('transactions.created_at', 'DESC');
        $this->db->limit($limit, $offset);
        
        return $this->db->get()->result();
    }

    /**
     * Create transaction
     */
    public function create($data) {
        $data['reference'] = $data['reference'] ?? $this->generate_reference();
        $data['created_at'] = date('Y-m-d H:i:s');
        
        $this->db->insert($this->table, $data);
        return $this->db->insert_id();
    }

    /**
     * Update transaction
     */
    public function update($id, $data) {
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->update($this->table, $data, ['id' => $id]);
    }

    /**
     * Update transaction status
     */
    public function update_status($id, $status, $failure_reason = null) {
        $data = [
            'status' => $status,
            'updated_at' => date('Y-m-d H:i:s')
        ];
        
        if ($status === 'completed') {
            $data['processed_at'] = date('Y-m-d H:i:s');
        }
        
        if ($failure_reason) {
            $data['failure_reason'] = $failure_reason;
        }
        
        return $this->db->update($this->table, $data, ['id' => $id]);
    }

    /**
     * Generate unique transaction reference
     */
    public function generate_reference($prefix = 'TXN') {
        $timestamp = date('YmdHis');
        $random = strtoupper(substr(md5(uniqid(mt_rand(), true)), 0, 6));
        return $prefix . $timestamp . $random;
    }

    /**
     * Process internal transfer
     */
    public function process_internal_transfer($source_account_id, $dest_account_id, $amount, $narration, $user_id) {
        $this->load->model('Account_model');
        $this->load->model('Wallet_model');
        
        $source_account = $this->Account_model->get($source_account_id);
        $dest_account = $this->Account_model->get($dest_account_id);
        
        if (!$source_account || !$dest_account) {
            return ['success' => false, 'message' => 'Invalid account'];
        }
        
        if ($source_account->status !== 'active') {
            return ['success' => false, 'message' => 'Source account is not active'];
        }
        
        if ($dest_account->status !== 'active') {
            return ['success' => false, 'message' => 'Destination account is not active'];
        }
        
        $source_wallet = $this->Wallet_model->get_by_account($source_account_id, $source_account->currency);
        $dest_wallet = $this->Wallet_model->get_by_account($dest_account_id, $dest_account->currency);
        
        if (!$source_wallet || !$dest_wallet) {
            return ['success' => false, 'message' => 'Wallet not found'];
        }
        
        if ($source_wallet->available_balance < $amount) {
            return ['success' => false, 'message' => 'Insufficient balance'];
        }
        
        // Get fee
        $fee = $this->calculate_fee('internal_transfer', $amount);
        $total_debit = $amount + $fee;
        
        if ($source_wallet->available_balance < $total_debit) {
            return ['success' => false, 'message' => 'Insufficient balance for transfer and fee'];
        }
        
        // Create transaction record
        $transaction_id = $this->create([
            'type' => 'transfer',
            'category' => 'internal',
            'source_account_id' => $source_account_id,
            'destination_account_id' => $dest_account_id,
            'source_wallet_id' => $source_wallet->id,
            'destination_wallet_id' => $dest_wallet->id,
            'amount' => $amount,
            'fee' => $fee,
            'currency' => $source_account->currency,
            'narration' => $narration,
            'status' => 'processing',
            'ip_address' => $this->input->ip_address()
        ]);
        
        if (!$transaction_id) {
            return ['success' => false, 'message' => 'Failed to create transaction'];
        }
        
        // Perform the transfer
        $result = $this->Wallet_model->transfer($source_wallet->id, $dest_wallet->id, $amount, $transaction_id);
        
        if ($result['success']) {
            // Deduct fee if any
            if ($fee > 0) {
                $this->Wallet_model->debit($source_wallet->id, $fee);
            }
            
            $this->update_status($transaction_id, 'completed');
            
            // Update account activity
            $this->Account_model->update_last_activity($source_account_id);
            $this->Account_model->update_last_activity($dest_account_id);
            
            return [
                'success' => true, 
                'message' => 'Transfer successful',
                'transaction_id' => $transaction_id,
                'reference' => $this->get($transaction_id)->reference
            ];
        } else {
            $this->update_status($transaction_id, 'failed', $result['message']);
            return ['success' => false, 'message' => $result['message']];
        }
    }

    /**
     * Create pending external (other bank) transfer. No wallet movement; status remains pending.
     */
    public function create_pending_external_transfer($source_account_id, $amount, $narration, $recipient_name, $recipient_account, $bank_code, $bank_name, $user_id) {
        $this->load->model('Account_model');
        $this->load->model('Wallet_model');

        $source_account = $this->Account_model->get($source_account_id);
        if (!$source_account || $source_account->user_id != $user_id) {
            return ['success' => false, 'message' => 'Invalid source account'];
        }
        if ($source_account->status !== 'active') {
            return ['success' => false, 'message' => 'Source account is not active'];
        }

        $source_wallet = $this->Wallet_model->get_by_account($source_account_id, $source_account->currency);
        if (!$source_wallet) {
            return ['success' => false, 'message' => 'Wallet not found'];
        }
        $fee = $this->calculate_fee('external_transfer', $amount);
        $total_debit = $amount + $fee;
        if ($source_wallet->available_balance < $total_debit) {
            return ['success' => false, 'message' => 'Insufficient balance for transfer and fee'];
        }

        $metadata = [
            'recipient_name' => $recipient_name,
            'recipient_account' => $recipient_account,
            'bank_code' => $bank_code,
            'bank_name' => $bank_name,
        ];
        $transaction_id = $this->create([
            'type' => 'transfer',
            'category' => 'external',
            'source_account_id' => $source_account_id,
            'destination_account_id' => null,
            'source_wallet_id' => $source_wallet->id,
            'destination_wallet_id' => null,
            'amount' => $amount,
            'fee' => $fee,
            'currency' => $source_account->currency,
            'narration' => $narration,
            'status' => 'pending',
            'metadata' => json_encode($metadata),
            'ip_address' => $this->input->ip_address(),
        ]);
        if (!$transaction_id) {
            return ['success' => false, 'message' => 'Failed to create transaction'];
        }
        return [
            'success' => true,
            'message' => 'Transfer submitted. It is pending and will be processed shortly.',
            'transaction_id' => $transaction_id,
            'reference' => $this->get($transaction_id)->reference,
        ];
    }

    /**
     * Create pending international transfer. No wallet movement; status remains pending.
     */
    public function create_pending_international_transfer($source_account_id, $amount, $narration, $recipient_name, $recipient_account, $recipient_bank_swift, $recipient_country, $recipient_currency, $exchange_rate, $user_id) {
        $this->load->model('Account_model');
        $this->load->model('Wallet_model');

        $source_account = $this->Account_model->get($source_account_id);
        if (!$source_account || $source_account->user_id != $user_id) {
            return ['success' => false, 'message' => 'Invalid source account'];
        }
        if ($source_account->status !== 'active') {
            return ['success' => false, 'message' => 'Source account is not active'];
        }

        $source_wallet = $this->Wallet_model->get_by_account($source_account_id, $source_account->currency);
        if (!$source_wallet) {
            return ['success' => false, 'message' => 'Wallet not found'];
        }
        $fee = $this->calculate_fee('international_transfer', $amount);
        $total_debit = $amount + $fee;
        if ($source_wallet->available_balance < $total_debit) {
            return ['success' => false, 'message' => 'Insufficient balance for transfer and fee'];
        }

        $metadata = [
            'recipient_name' => $recipient_name,
            'recipient_account' => $recipient_account,
            'recipient_bank_swift' => $recipient_bank_swift,
            'recipient_country' => $recipient_country,
            'recipient_currency' => $recipient_currency,
            'exchange_rate' => $exchange_rate,
        ];
        $transaction_id = $this->create([
            'type' => 'transfer',
            'category' => 'international',
            'source_account_id' => $source_account_id,
            'destination_account_id' => null,
            'source_wallet_id' => $source_wallet->id,
            'destination_wallet_id' => null,
            'amount' => $amount,
            'fee' => $fee,
            'currency' => $source_account->currency,
            'exchange_rate' => $exchange_rate ?: 1,
            'narration' => $narration,
            'status' => 'pending',
            'metadata' => json_encode($metadata),
            'ip_address' => $this->input->ip_address(),
        ]);
        if (!$transaction_id) {
            return ['success' => false, 'message' => 'Failed to create transaction'];
        }
        return [
            'success' => true,
            'message' => 'Transfer submitted. It is pending and will be processed shortly.',
            'transaction_id' => $transaction_id,
            'reference' => $this->get($transaction_id)->reference,
        ];
    }

    /**
     * Calculate transaction fee
     */
    public function calculate_fee($transaction_type, $amount) {
        $fee_config = $this->db->get_where('fee_configurations', [
            'transaction_type' => $transaction_type,
            'is_active' => 1
        ])->row();
        
        if (!$fee_config) {
            return 0;
        }
        
        $fee = 0;
        
        switch ($fee_config->fee_type) {
            case 'flat':
                $fee = $fee_config->flat_amount;
                break;
            case 'percentage':
                $fee = $amount * $fee_config->percentage;
                break;
            case 'flat_plus_percentage':
                $fee = $fee_config->flat_amount + ($amount * $fee_config->percentage);
                break;
        }
        
        // Apply min/max constraints
        if ($fee_config->min_fee && $fee < $fee_config->min_fee) {
            $fee = $fee_config->min_fee;
        }
        if ($fee_config->max_fee && $fee > $fee_config->max_fee) {
            $fee = $fee_config->max_fee;
        }
        
        return round($fee, 2);
    }

    /**
     * Reverse transaction
     */
    public function reverse($transaction_id, $reason, $admin_id = null) {
        $transaction = $this->get($transaction_id);
        
        if (!$transaction) {
            return ['success' => false, 'message' => 'Transaction not found'];
        }
        
        if ($transaction->status !== 'completed') {
            return ['success' => false, 'message' => 'Only completed transactions can be reversed'];
        }
        
        $this->load->model('Wallet_model');
        
        // Reverse the wallets
        if ($transaction->source_wallet_id && $transaction->destination_wallet_id) {
            // Credit back the source
            $this->Wallet_model->credit($transaction->source_wallet_id, $transaction->amount);
            // Debit the destination
            $this->Wallet_model->debit($transaction->destination_wallet_id, $transaction->amount);
            
            // Refund fee if any
            if ($transaction->fee > 0) {
                $this->Wallet_model->credit($transaction->source_wallet_id, $transaction->fee);
            }
        }
        
        // Update original transaction
        $this->update($transaction_id, ['status' => 'reversed']);
        
        // Create reversal record
        $reversal_id = $this->create([
            'type' => 'reversal',
            'source_account_id' => $transaction->destination_account_id,
            'destination_account_id' => $transaction->source_account_id,
            'source_wallet_id' => $transaction->destination_wallet_id,
            'destination_wallet_id' => $transaction->source_wallet_id,
            'amount' => $transaction->amount,
            'currency' => $transaction->currency,
            'narration' => 'Reversal: ' . $reason,
            'status' => 'completed',
            'metadata' => json_encode(['original_transaction' => $transaction_id, 'reversed_by' => $admin_id])
        ]);
        
        return ['success' => true, 'message' => 'Transaction reversed', 'reversal_id' => $reversal_id];
    }

    /**
     * Get transaction statistics
     */
    public function get_statistics($date_from = null, $date_to = null) {
        $date_from = $date_from ?: date('Y-m-01');
        $date_to = $date_to ?: date('Y-m-d 23:59:59');
        
        $this->db->select('
            COUNT(*) as total_count,
            SUM(CASE WHEN status = "completed" THEN 1 ELSE 0 END) as completed_count,
            SUM(CASE WHEN status = "failed" THEN 1 ELSE 0 END) as failed_count,
            SUM(CASE WHEN status = "completed" THEN amount ELSE 0 END) as total_amount,
            SUM(CASE WHEN status = "completed" THEN fee ELSE 0 END) as total_fees
        ');
        $this->db->where('created_at >=', $date_from);
        $this->db->where('created_at <=', $date_to);
        
        return $this->db->get($this->table)->row();
    }

    /**
     * Count transactions
     */
    public function count_all($filters = []) {
        if (!empty($filters['type'])) {
            $this->db->where('type', $filters['type']);
        }
        if (!empty($filters['status'])) {
            $this->db->where('status', $filters['status']);
        }
        return $this->db->count_all_results($this->table);
    }

    /**
     * Get recent transactions (for dashboard)
     */
    public function get_recent($limit = 10) {
        $this->db->order_by('created_at', 'DESC');
        $this->db->limit($limit);
        return $this->db->get($this->table)->result();
    }
}
