paystack/models/Paystack_model.php

<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Paystack_model extends App_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Get all transactions
     */
    public function get_transactions($filter = [])
    {
        $this->db->select('*');
        $this->db->from(db_prefix() . 'paystack_transactions');

        if (isset($filter['start_date']) && $filter['start_date']) {
            $this->db->where('date_created >=', $filter['start_date'] . ' 00:00:00');
        }

        if (isset($filter['end_date']) && $filter['end_date']) {
            $this->db->where('date_created <=', $filter['end_date'] . ' 23:59:59');
        }

        $this->db->order_by('date_created', 'desc');
        return $this->db->get()->result_array();
    }

    /**
     * Get payment statistics
     */
    public function get_payment_stats()
    {
        $stats = [
            'total_transactions' => 0,
            'successful_transactions' => 0,
            'failed_transactions' => 0,
            'total_amount' => 0,
            'success_rate' => 0
        ];

        // Total transactions
        $this->db->select('COUNT(*) as total');
        $this->db->from(db_prefix() . 'paystack_transactions');
        $stats['total_transactions'] = $this->db->get()->row()->total;

        // Successful transactions
        $this->db->select('COUNT(*) as total, SUM(amount) as amount');
        $this->db->where('status', 'success');
        $this->db->from(db_prefix() . 'paystack_transactions');
        $result = $this->db->get()->row();
        $stats['successful_transactions'] = $result->total;
        $stats['total_amount'] = $result->amount;

        // Failed transactions
        $this->db->select('COUNT(*) as total');
        $this->db->where('status', 'failed');
        $this->db->from(db_prefix() . 'paystack_transactions');
        $stats['failed_transactions'] = $this->db->get()->row()->total;

        // Calculate success rate
        if ($stats['total_transactions'] > 0) {
            $stats['success_rate'] = ($stats['successful_transactions'] / $stats['total_transactions']) * 100;
        }

        return $stats;
    }

    /**
     * Get monthly chart data
     */
    public function get_monthly_chart_data()
    {
        $months = [];
        for ($m = 11; $m >= 0; $m--) {
            $months[date('Y-m', strtotime("-$m months"))] = [
                'successful' => 0,
                'failed' => 0,
                'amount' => 0
            ];
        }

        $this->db->select('DATE_FORMAT(date_created, "%Y-%m") as month, status, COUNT(*) as total, SUM(amount) as amount');
        $this->db->from(db_prefix() . 'paystack_transactions');
        $this->db->where('date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)');
        $this->db->group_by('month, status');
        $results = $this->db->get()->result_array();

        foreach ($results as $result) {
            if (isset($months[$result['month']])) {
                if ($result['status'] == 'success') {
                    $months[$result['month']]['successful'] = $result['total'];
                    $months[$result['month']]['amount'] = $result['amount'];
                } else {
                    $months[$result['month']]['failed'] = $result['total'];
                }
            }
        }

        return $months;
    }

    /**
     * Get recent transactions
     */
    public function get_recent_transactions($limit = 10)
    {
        $this->db->select('t.*, i.number as invoice_number');
        $this->db->from(db_prefix() . 'paystack_transactions t');
        $this->db->join(db_prefix() . 'invoices i', 'i.id = t.invoice_id', 'left');
        $this->db->order_by('t.date_created', 'desc');
        $this->db->limit($limit);
        return $this->db->get()->result_array();
    }

    /**
     * Add transaction log
     */
    public function add_log($data)
    {
        $this->db->insert(db_prefix() . 'paystack_payment_logs', $data);
        return $this->db->insert_id();
    }

    /**
     * Get transaction by reference
     */
    public function get_transaction_by_reference($reference)
    {
        $this->db->where('reference', $reference);
        return $this->db->get(db_prefix() . 'paystack_transactions')->row_array();
    }

    /**
     * Add webhook log
     */
    public function add_webhook_log($event, $payload, $status)
    {
        return $this->db->insert(db_prefix() . 'paystack_webhook_logs', [
            'event' => $event,
            'payload' => json_encode($payload),
            'status' => $status
        ]);
    }

    /**
     * Update settings
     */
    public function update_settings($data)
    {
        foreach ($data as $key => $value) {
            update_option($key, $value);
        }
        return true;
    }

    /**
     * Get recent failed attempts
     */
    public function get_recent_failed_attempts($invoice_id, $timeframe)
    {
        $this->db->where('invoice_id', $invoice_id);
        $this->db->where('log_type', 'failed_attempt');
        $this->db->where('date_created >=', date('Y-m-d H:i:s', strtotime("-$timeframe minutes")));
        return $this->db->count_all_results(db_prefix() . 'paystack_payment_logs');
    }

    /**
     * Add payment log with enhanced details
     */
    public function add_payment_log($data)
    {
        // Ensure required fields
        $data['date_created'] = date('Y-m-d H:i:s');

        // Add client IP if available
        if (!isset($data['ip_address']) && isset($_SERVER['REMOTE_ADDR'])) {
            $data['ip_address'] = $_SERVER['REMOTE_ADDR'];
        }

        // Add user agent if available
        if (!isset($data['user_agent']) && isset($_SERVER['HTTP_USER_AGENT'])) {
            $data['user_agent'] = $_SERVER['HTTP_USER_AGENT'];
        }

        return $this->db->insert(db_prefix() . 'paystack_payment_logs', $data);
    }

    /**
     * Get transaction logs
     */
    public function get_transaction_logs($transaction_id)
    {
        $this->db->where('transaction_id', $transaction_id);
        $this->db->order_by('date_created', 'desc');
        return $this->db->get(db_prefix() . 'paystack_payment_logs')->result_array();
    }

    /**
     * Clean old logs
     */
    public function clean_old_logs($days = 90)
    {
        $this->db->where('date_created <', date('Y-m-d H:i:s', strtotime("-$days days")));
        return $this->db->delete(db_prefix() . 'paystack_payment_logs');
    }
}