<?php
// app/Services/CommissionService.php - Commission Calculation Service

class CommissionService {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Calculate commission for an order
     */
    public function calculate($order_amount, $commission_percentage = null) {
        $percentage = $commission_percentage ?? PLATFORM_COMMISSION;
        $commission_amount = ($order_amount * $percentage) / 100;
        $vendor_amount = $order_amount - $commission_amount;
        
        return [
            'order_amount' => $order_amount,
            'commission_percentage' => $percentage,
            'commission_amount' => round($commission_amount, 2),
            'vendor_amount' => round($vendor_amount, 2)
        ];
    }
    
    /**
     * Create commission record for an order
     */
    public function createCommission($order_id) {
        // Get order details
        $order_sql = "SELECT vendor_id, total_amount FROM orders WHERE id = ?";
        $order = $this->db->fetch($order_sql, [$order_id]);
        
        if (!$order) {
            return false;
        }
        
        // Check if commission already exists
        $check_sql = "SELECT id FROM commissions WHERE order_id = ?";
        $existing = $this->db->fetch($check_sql, [$order_id]);
        
        if ($existing) {
            return $existing['id'];
        }
        
        // Calculate commission
        $calculation = $this->calculate($order['total_amount']);
        
        // Create commission record
        $sql = "INSERT INTO commissions (order_id, vendor_id, order_amount, commission_percentage, 
                commission_amount, vendor_amount, status, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $order_id,
            $order['vendor_id'],
            $calculation['order_amount'],
            $calculation['commission_percentage'],
            $calculation['commission_amount'],
            $calculation['vendor_amount']
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Process commission payment to vendor
     */
    public function processCommissionPayment($order_id) {
        $this->db->beginTransaction();
        
        try {
            // Get commission record
            $comm_sql = "SELECT * FROM commissions WHERE order_id = ? AND status = 'pending'";
            $commission = $this->db->fetch($comm_sql, [$order_id]);
            
            if (!$commission) {
                throw new Exception("Commission not found or already processed");
            }
            
            // Get vendor user_id
            $vendor_sql = "SELECT user_id FROM vendors WHERE id = ?";
            $vendor = $this->db->fetch($vendor_sql, [$commission['vendor_id']]);
            
            if (!$vendor) {
                throw new Exception("Vendor not found");
            }
            
            // Credit vendor wallet
            $wallet = new Wallet();
            $reference = $wallet->generateTransactionReference('COMM');
            $description = "Commission payment for Order #" . $order_id;
            
            $wallet->credit(
                $vendor['user_id'],
                $commission['vendor_amount'],
                $description,
                $reference,
                $order_id,
                'commission'
            );
            
            // Update commission status
            $update_sql = "UPDATE commissions SET status = 'paid', paid_at = NOW() WHERE id = ?";
            $this->db->execute($update_sql, [$commission['id']]);
            
            $this->db->commit();
            
            log_message("Commission paid for order #{$order_id}: " . format_money($commission['vendor_amount']), 'info');
            
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            log_message("Commission payment error: " . $e->getMessage(), 'error');
            return false;
        }
    }
    
    /**
     * Get commission by order ID
     */
    public function getByOrderId($order_id) {
        $sql = "SELECT c.*, v.business_name as vendor_name, o.order_number
                FROM commissions c
                JOIN vendors v ON c.vendor_id = v.id
                JOIN orders o ON c.order_id = o.id
                WHERE c.order_id = ?";
        
        return $this->db->fetch($sql, [$order_id]);
    }
    
    /**
     * Get commissions by vendor
     */
    public function getByVendorId($vendor_id, $limit = 20, $offset = 0) {
        $sql = "SELECT c.*, o.order_number, o.start_date, o.end_date
                FROM commissions c
                JOIN orders o ON c.order_id = o.id
                WHERE c.vendor_id = ?
                ORDER BY c.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$vendor_id, $limit, $offset]);
    }
    
    /**
     * Get all commissions
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT c.*, v.business_name as vendor_name, o.order_number
                FROM commissions c
                JOIN vendors v ON c.vendor_id = v.id
                JOIN orders o ON c.order_id = o.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND c.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND c.status = ?";
            $params[] = $filters['status'];
        }
        
        $sql .= " ORDER BY c.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Get commission statistics
     */
    public function getStats($vendor_id = null) {
        if ($vendor_id) {
            $sql = "SELECT 
                    COUNT(*) as total_commissions,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_commissions,
                    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_commissions,
                    COALESCE(SUM(order_amount), 0) as total_order_amount,
                    COALESCE(SUM(commission_amount), 0) as total_commission_amount,
                    COALESCE(SUM(vendor_amount), 0) as total_vendor_amount,
                    COALESCE(SUM(CASE WHEN status = 'paid' THEN vendor_amount ELSE 0 END), 0) as total_paid_amount,
                    COALESCE(SUM(CASE WHEN status = 'pending' THEN vendor_amount ELSE 0 END), 0) as total_pending_amount
                    FROM commissions
                    WHERE vendor_id = ?";
            
            return $this->db->fetch($sql, [$vendor_id]);
        } else {
            $sql = "SELECT 
                    COUNT(*) as total_commissions,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_commissions,
                    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_commissions,
                    COALESCE(SUM(order_amount), 0) as total_order_amount,
                    COALESCE(SUM(commission_amount), 0) as total_platform_earnings,
                    COALESCE(SUM(vendor_amount), 0) as total_vendor_earnings,
                    COALESCE(SUM(CASE WHEN status = 'paid' THEN commission_amount ELSE 0 END), 0) as platform_earnings_paid,
                    COALESCE(SUM(CASE WHEN status = 'pending' THEN commission_amount ELSE 0 END), 0) as platform_earnings_pending
                    FROM commissions";
            
            return $this->db->fetch($sql);
        }
    }
    
    /**
     * Get commission earnings by period
     */
    public function getEarningsByPeriod($period = 'month', $vendor_id = null) {
        if ($period == 'week') {
            $date_format = "DATE(created_at)";
            $date_filter = "DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
        } else {
            $date_format = "DATE_FORMAT(created_at, '%Y-%m')";
            $date_filter = "DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 12 MONTH)";
        }
        
        $sql = "SELECT {$date_format} as date, 
                SUM(commission_amount) as platform_earnings,
                SUM(vendor_amount) as vendor_earnings
                FROM commissions
                WHERE status = 'paid' AND {$date_filter}";
        
        if ($vendor_id) {
            $sql .= " AND vendor_id = ?";
            $params = [$vendor_id];
        } else {
            $params = [];
        }
        
        $sql .= " GROUP BY date ORDER BY date ASC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Refund commission (when order is cancelled/refunded)
     */
    public function refundCommission($order_id) {
        $this->db->beginTransaction();
        
        try {
            // Get commission record
            $comm_sql = "SELECT * FROM commissions WHERE order_id = ?";
            $commission = $this->db->fetch($comm_sql, [$order_id]);
            
            if (!$commission) {
                throw new Exception("Commission not found");
            }
            
            // If commission was paid, deduct from vendor wallet
            if ($commission['status'] === 'paid') {
                $vendor_sql = "SELECT user_id FROM vendors WHERE id = ?";
                $vendor = $this->db->fetch($vendor_sql, [$commission['vendor_id']]);
                
                if ($vendor) {
                    $wallet = new Wallet();
                    $reference = $wallet->generateTransactionReference('REFUND');
                    $description = "Commission refund for cancelled Order #" . $order_id;
                    
                    $wallet->debit(
                        $vendor['user_id'],
                        $commission['vendor_amount'],
                        $description,
                        $reference,
                        $order_id,
                        'refund'
                    );
                }
            }
            
            // Update commission status
            $update_sql = "UPDATE commissions SET status = 'refunded' WHERE id = ?";
            $this->db->execute($update_sql, [$commission['id']]);
            
            $this->db->commit();
            
            log_message("Commission refunded for order #{$order_id}", 'info');
            
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            log_message("Commission refund error: " . $e->getMessage(), 'error');
            return false;
        }
    }
    
    /**
     * Get pending commissions count
     */
    public function getPendingCount($vendor_id = null) {
        $sql = "SELECT COUNT(*) as count FROM commissions WHERE status = 'pending'";
        
        if ($vendor_id) {
            $sql .= " AND vendor_id = ?";
            $result = $this->db->fetch($sql, [$vendor_id]);
        } else {
            $result = $this->db->fetch($sql);
        }
        
        return $result['count'] ?? 0;
    }
    
    /**
     * Get pending commission amount
     */
    public function getPendingAmount($vendor_id = null) {
        $sql = "SELECT COALESCE(SUM(vendor_amount), 0) as amount FROM commissions WHERE status = 'pending'";
        
        if ($vendor_id) {
            $sql .= " AND vendor_id = ?";
            $result = $this->db->fetch($sql, [$vendor_id]);
        } else {
            $result = $this->db->fetch($sql);
        }
        
        return $result['amount'] ?? 0;
    }
}