<?php
// app/Models/Payment.php - Payment Model

class Payment {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create payment record
     */
    public function create($data) {
        $sql = "INSERT INTO payments (order_id, transaction_reference, payment_gateway, amount, 
                payment_type, status, gateway_reference, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $result = $this->db->execute($sql, [
            $data['order_id'],
            $data['transaction_reference'],
            $data['payment_gateway'],
            $data['amount'],
            $data['payment_type'] ?? 'order_payment',
            $data['status'] ?? 'pending',
            $data['gateway_reference'] ?? null
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find payment by ID
     */
    public function find($id) {
        $sql = "SELECT p.*, o.order_number, o.customer_id, o.vendor_id
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                WHERE p.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find payment by transaction reference
     */
    public function findByReference($reference) {
        $sql = "SELECT p.*, o.order_number, o.customer_id, o.vendor_id
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                WHERE p.transaction_reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$reference]);
    }
    
    /**
     * Find payment by gateway reference
     */
    public function findByGatewayReference($gateway_reference) {
        $sql = "SELECT p.*, o.order_number
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                WHERE p.gateway_reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$gateway_reference]);
    }
    
    /**
     * Get all payments with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT p.*, o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                JOIN customers c ON o.customer_id = c.id
                JOIN vendors v ON o.vendor_id = v.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND p.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['payment_gateway'])) {
            $sql .= " AND p.payment_gateway = ?";
            $params[] = $filters['payment_gateway'];
        }
        
        if (!empty($filters['payment_type'])) {
            $sql .= " AND p.payment_type = ?";
            $params[] = $filters['payment_type'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND o.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 (p.transaction_reference LIKE ? OR o.order_number LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY p.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 payments p JOIN orders o ON p.order_id = o.id WHERE 1=1";
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND p.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update payment
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE payments SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Update payment status
     */
    public function updateStatus($id, $status) {
        $sql = "UPDATE payments SET status = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$status, $id]);
    }
    
    /**
     * Mark payment as completed
     */
    public function markAsCompleted($id, $gateway_response = null) {
        $sql = "UPDATE payments SET 
                status = 'completed', 
                paid_at = NOW(),
                gateway_response = ?,
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$gateway_response, $id]);
    }
    
    /**
     * Mark payment as failed
     */
    public function markAsFailed($id, $gateway_response = null) {
        $sql = "UPDATE payments SET 
                status = 'failed', 
                gateway_response = ?,
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$gateway_response, $id]);
    }
    
    /**
     * Process refund
     */
    public function processRefund($id, $refund_amount, $reason) {
        $sql = "UPDATE payments SET 
                status = 'refunded', 
                refund_amount = ?,
                refund_reason = ?,
                refunded_at = NOW(),
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$refund_amount, $reason, $id]);
    }
    
    /**
     * Generate unique transaction reference
     */
    public function generateReference($prefix = 'TXN') {
        do {
            $reference = $prefix . '-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -10));
            $exists = $this->findByReference($reference);
        } while ($exists);
        
        return $reference;
    }
    
    /**
     * Get payment statistics
     */
    public function getStats($filters = []) {
        $sql = "SELECT 
                COUNT(*) as total_payments,
                SUM(CASE WHEN status = 'completed' 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 = 'completed' THEN amount ELSE 0 END), 0) as total_amount,
                COALESCE(SUM(CASE WHEN status = 'refunded' THEN refund_amount ELSE 0 END), 0) as total_refunded,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as new_this_week
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Get payments by gateway
     */
    public function getByGateway($gateway, $limit = 20) {
        $sql = "SELECT p.*, o.order_number
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                WHERE p.payment_gateway = ?
                ORDER BY p.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$gateway, $limit]);
    }
    
    /**
     * Get recent payments
     */
    public function getRecent($limit = 10) {
        $sql = "SELECT p.*, o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name
                FROM payments p
                JOIN orders o ON p.order_id = o.id
                JOIN customers c ON o.customer_id = c.id
                JOIN vendors v ON o.vendor_id = v.id
                ORDER BY p.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
}