<?php
/**
 * OnlinePayment Model
 * File: app/Models/OnlinePayment.php
 * 
 * Manages online payment gateway transactions (Paystack, Flutterwave, etc.)
 */

class OnlinePayment {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create payment record
     */
    public function create($data) {
        $sql = "INSERT INTO online_payments (
                customer_id, transaction_reference, payment_gateway, amount, 
                currency, status, payment_type, customer_email, metadata, 
                ip_address, user_agent, initiated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $result = $this->db->execute($sql, [
            $data['customer_id'],
            $data['transaction_reference'],
            $data['payment_gateway'] ?? 'paystack',
            $data['amount'],
            $data['currency'] ?? 'NGN',
            $data['status'] ?? 'pending',
            $data['payment_type'] ?? 'order_payment',
            $data['customer_email'],
            isset($data['metadata']) ? json_encode($data['metadata']) : null,
            $data['ip_address'] ?? $_SERVER['REMOTE_ADDR'] ?? null,
            $data['user_agent'] ?? $_SERVER['HTTP_USER_AGENT'] ?? null
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find payment by ID
     */
    public function find($id) {
        $sql = "SELECT op.*, 
                o.order_number, o.vendor_id, o.item_id,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                LEFT JOIN customers c ON op.customer_id = c.id
                WHERE op.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find payment by transaction reference
     */
    public function findByReference($reference) {
        $sql = "SELECT op.*, 
                o.order_number, o.vendor_id, o.item_id
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                WHERE op.transaction_reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$reference]);
    }
    
    /**
     * Find payment by gateway reference
     */
    public function findByGatewayReference($gateway_reference) {
        $sql = "SELECT op.*, 
                o.order_number, o.vendor_id
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                WHERE op.gateway_reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$gateway_reference]);
    }
    
    /**
     * Update payment status
     */
    public function updateStatus($reference, $status, $data = []) {
        $sql = "UPDATE online_payments SET status = ?";
        $params = [$status];
        
        // Add order_id if provided
        if (isset($data['order_id'])) {
            $sql .= ", order_id = ?";
            $params[] = $data['order_id'];
        }
        
        // Add gateway response
        if (isset($data['gateway_response'])) {
            $sql .= ", gateway_response = ?";
            $params[] = is_array($data['gateway_response']) 
                ? json_encode($data['gateway_response']) 
                : $data['gateway_response'];
        }
        
        // Add gateway reference
        if (isset($data['gateway_reference'])) {
            $sql .= ", gateway_reference = ?";
            $params[] = $data['gateway_reference'];
        }
        
        // Set completion timestamp based on status
        if ($status === 'successful') {
            $sql .= ", completed_at = NOW()";
        } elseif ($status === 'failed' || $status === 'cancelled') {
            $sql .= ", failed_at = NOW()";
            
            if (isset($data['error_message'])) {
                $sql .= ", error_message = ?";
                $params[] = $data['error_message'];
            }
        }
        
        $sql .= ", updated_at = NOW() WHERE transaction_reference = ?";
        $params[] = $reference;
        
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Mark payment as successful
     */
    public function markAsSuccessful($reference, $gateway_reference, $gateway_response = null) {
        $data = [
            'gateway_reference' => $gateway_reference,
            'gateway_response' => $gateway_response
        ];
        
        return $this->updateStatus($reference, 'successful', $data);
    }
    
    /**
     * Mark payment as failed
     */
    public function markAsFailed($reference, $error_message = null, $gateway_response = null) {
        $data = [
            'error_message' => $error_message,
            'gateway_response' => $gateway_response
        ];
        
        return $this->updateStatus($reference, 'failed', $data);
    }
    
    /**
     * Link payment to order
     */
    public function linkToOrder($payment_id, $order_id) {
        $sql = "UPDATE online_payments SET order_id = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$order_id, $payment_id]);
    }
    
    /**
     * Get all payments with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT op.*, 
                o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                LEFT JOIN customers c ON op.customer_id = c.id
                LEFT JOIN vendors v ON o.vendor_id = v.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND op.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['payment_gateway'])) {
            $sql .= " AND op.payment_gateway = ?";
            $params[] = $filters['payment_gateway'];
        }
        
        if (!empty($filters['payment_type'])) {
            $sql .= " AND op.payment_type = ?";
            $params[] = $filters['payment_type'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND op.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (op.transaction_reference LIKE ? OR o.order_number LIKE ? OR op.customer_email LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(op.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(op.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " ORDER BY op.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count payments
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM online_payments op LEFT JOIN orders o ON op.order_id = o.id WHERE 1=1";
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND op.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['payment_type'])) {
            $sql .= " AND op.payment_type = ?";
            $params[] = $filters['payment_type'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND op.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Get payment statistics
     */
    public function getStats($filters = []) {
        $sql = "SELECT 
                COUNT(*) as total_payments,
                SUM(CASE WHEN status = 'successful' THEN 1 ELSE 0 END) as successful_payments,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_payments,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_payments,
                COALESCE(SUM(CASE WHEN status = 'successful' THEN amount ELSE 0 END), 0) as total_amount,
                COALESCE(AVG(CASE WHEN status = 'successful' THEN amount ELSE NULL END), 0) as avg_amount,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as payments_this_week,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as payments_this_month
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND op.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Get recent payments
     */
    public function getRecent($limit = 10, $customer_id = null) {
        $sql = "SELECT op.*, 
                o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                LEFT JOIN customers c ON op.customer_id = c.id";
        
        $params = [];
        
        if ($customer_id) {
            $sql .= " WHERE op.customer_id = ?";
            $params[] = $customer_id;
        }
        
        $sql .= " ORDER BY op.created_at DESC LIMIT ?";
        $params[] = $limit;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Generate unique transaction reference
     */
    public function generateReference($prefix = 'PAY') {
        do {
            $reference = $prefix . '-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -10));
            $exists = $this->findByReference($reference);
        } while ($exists);
        
        return $reference;
    }
    
    /**
     * Get successful payments by date range
     */
    public function getSuccessfulPayments($date_from = null, $date_to = null, $limit = 50) {
        $sql = "SELECT op.*, 
                o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name
                FROM online_payments op
                LEFT JOIN orders o ON op.order_id = o.id
                LEFT JOIN customers c ON op.customer_id = c.id
                LEFT JOIN vendors v ON o.vendor_id = v.id
                WHERE op.status = 'successful'";
        
        $params = [];
        
        if ($date_from) {
            $sql .= " AND DATE(op.completed_at) >= ?";
            $params[] = $date_from;
        }
        
        if ($date_to) {
            $sql .= " AND DATE(op.completed_at) <= ?";
            $params[] = $date_to;
        }
        
        $sql .= " ORDER BY op.completed_at DESC LIMIT ?";
        $params[] = $limit;
        
        return $this->db->fetchAll($sql, $params);
    }
}