<?php
/**
 * ================================================================
 * WALLET STATS V4.0 - DASHBOARD CARDS CALCULATOR
 * ================================================================
 * File: app/Core/WalletStats.php
 * 
 * Provides methods to calculate wallet card metrics for:
 * - Customer Dashboard (4 cards)
 * - Vendor Dashboard (4 cards)
 * - Admin Dashboard (8+ cards)
 * 
 * All calculations optimized for performance with direct queries
 * ================================================================
 */

class WalletStats {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * ================================================================
     * CUSTOMER WALLET CARDS
     * ================================================================
     * Returns:
     * 1. Available Balance
     * 2. Withhold (Security) Deposit
     * 3. Total Spent (Lifetime)
     * 4. Total Spent (This Month)
     * 5. Total Withdrawn
     * ================================================================
     */
    public function getCustomerWalletCards($customer_user_id) {
        // Get wallet data
        $wallet = $this->db->fetch("
            SELECT balance, security_onhold, total_spent, total_withdrawn
            FROM wallets 
            WHERE user_id = ?
        ", [$customer_user_id]);
        
        // If no wallet exists, return zeros
        if (!$wallet) {
            return [
                'available_balance' => 0.00,
                'withhold_deposit' => 0.00,
                'total_spent_lifetime' => 0.00,
                'total_spent_this_month' => 0.00,
                'total_withdrawn' => 0.00
            ];
        }
        
        // Calculate this month's spending
        $monthly_spent = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM wallet_transactions 
            WHERE user_id = ? 
            AND transaction_type = 'item_rental'
            AND category = 'debit'
            AND MONTH(created_at) = MONTH(CURRENT_DATE())
            AND YEAR(created_at) = YEAR(CURRENT_DATE())
        ", [$customer_user_id]);
        
        return [
            'available_balance' => floatval($wallet['balance']),
            'withhold_deposit' => floatval($wallet['security_onhold']),
            'total_spent_lifetime' => floatval($wallet['total_spent']),
            'total_spent_this_month' => floatval($monthly_spent['total']),
            'total_withdrawn' => floatval($wallet['total_withdrawn'])
        ];
    }
    
    /**
     * ================================================================
     * VENDOR WALLET CARDS
     * ================================================================
     * Returns:
     * 1. Available Balance
     * 2. Pending Earning (orders in progress)
     * 3. Total Earned (Lifetime)
     * 4. Total Withdrawn
     * ================================================================
     */
    public function getVendorWalletCards($vendor_user_id) {
        // Get wallet data
        $wallet = $this->db->fetch("
            SELECT balance, total_earned, total_withdrawn
            FROM wallets 
            WHERE user_id = ?
        ", [$vendor_user_id]);
        
        if (!$wallet) {
            return [
                'available_balance' => 0.00,
                'pending_earning' => 0.00,
                'total_earned' => 0.00,
                'total_withdrawn' => 0.00
            ];
        }
        
        // Calculate pending earnings (onhold breakdown records)
        $pending = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM order_payment_breakdown 
            WHERE user_id = ? 
            AND type = 'rental'
            AND status = 'onhold'
        ", [$vendor_user_id]);
        
        return [
            'available_balance' => floatval($wallet['balance']),
            'pending_earning' => floatval($pending['total']),
            'total_earned' => floatval($wallet['total_earned']),
            'total_withdrawn' => floatval($wallet['total_withdrawn'])
        ];
    }
    
    /**
     * ================================================================
     * ADMIN WALLET CARDS
     * ================================================================
     * Returns comprehensive platform analytics
     * ================================================================
     */
    public function getAdminWalletCards() {
        // Get admin wallet
        $admin = $this->db->fetch("SELECT id FROM users WHERE user_type = 'admin' ORDER BY id ASC LIMIT 1");
        $admin_user_id = $admin['id'] ?? 1;
        
        $wallet = $this->db->fetch("
            SELECT balance, total_earned, total_withdrawn
            FROM wallets 
            WHERE user_id = ?
        ", [$admin_user_id]);
        
        if (!$wallet) {
            $wallet = [
                'balance' => 0.00,
                'total_earned' => 0.00,
                'total_withdrawn' => 0.00
            ];
        }
        
        // 1. Current Rent (orders ongoing right now)
        $current_rent = $this->db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) as total
            FROM orders 
            WHERE status = 'ongoing'
            AND start_date <= CURDATE() 
            AND end_date >= CURDATE()
            AND payment_status = 'paid'
        ");
        
        // 2. Pending Rent (booked but not started)
        $pending_rent = $this->db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) as total
            FROM orders 
            WHERE status IN ('confirmed', 'pending')
            AND start_date > CURDATE()
            AND payment_status = 'paid'
        ");
        
        // 3. Current Security (all held deposits)
        $current_security = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM order_payment_breakdown 
            WHERE type = 'security_deposit'
            AND status = 'onhold'
        ");
        
        // 4. Total Customers Spent (all time)
        $customers_spent = $this->db->fetch("
            SELECT COALESCE(SUM(total_spent), 0) as total
            FROM wallets 
            WHERE user_type = 'customer'
        ");
        
        // 5. Total Vendors Earning (all time)
        $vendors_earning = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM wallet_transactions 
            WHERE transaction_type = 'rental_earning' && category = 'credit' && status = 'completed'
        ");
        
        // 5b. Vendor Earnings Onhold (pending earnings from active orders)
        $vendors_earning_onhold = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM order_payment_breakdown 
            WHERE type = 'rental'
            AND status = 'onhold'
        ");
        
        // 5c. Current Rent Onhold (all rent amounts for ongoing orders)
        $current_rent_onhold = $this->db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) as total
            FROM orders 
            WHERE status IN ('confirmed', 'ongoing')
            AND payment_status = 'paid'
        ");
        
        // 6. Platform Statistics
        $platform_stats = $this->db->fetch("
            SELECT 
                COUNT(DISTINCT CASE WHEN user_type = 'customer' THEN user_id END) as total_customers,
                COUNT(DISTINCT CASE WHEN user_type = 'vendor' THEN user_id END) as total_vendors,
                COALESCE(SUM(CASE WHEN user_type = 'customer' THEN balance ELSE 0 END), 0) as customers_wallet_balance,
                COALESCE(SUM(CASE WHEN user_type = 'vendor' THEN balance ELSE 0 END), 0) as vendors_wallet_balance
            FROM wallets
        ");
        
        // 7. Order Statistics
        $order_stats = $this->db->fetch("
            SELECT 
                COUNT(*) as total_orders,
                COUNT(CASE WHEN status IN ('confirmed', 'ongoing') 
                          AND start_date <= CURDATE() 
                          AND end_date >= CURDATE() 
                          AND payment_status = 'paid' THEN 1 END) as active_orders,
                COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
                COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
                COALESCE(SUM(CASE WHEN status != 'cancelled' THEN total_amount ELSE 0 END), 0) as total_revenue
            FROM orders 
            WHERE payment_status = 'paid'
        ");
        
        if (!$order_stats) {
            $order_stats = [
                'total_orders' => 0,
                'active_orders' => 0,
                'completed_orders' => 0,
                'cancelled_orders' => 0,
                'total_revenue' => 0
            ];
        }
        
        // 8a. Total Refunded - Sum both refund transaction types
        // $refund_stats = $this->db->fetch("
        //     SELECT COALESCE(SUM(amount), 0) as total_refunded
        //     FROM wallet_transactions
        //     WHERE transaction_type = 'rental_cancellation_refund', 'security_deposit_cancellation_refund')
        //     AND category = 'credit'
        // ");

        $refund_stats = $this->db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) as total_refunded
            FROM orders
            WHERE status = 'cancelled'
        ");
        
        if (!$refund_stats) {
            $refund_stats = ['total_refunded' => 0];
        }
        

        // 8b. Total Cancellation Charged (all time)
        $cancellation_penalty = $this->db->fetch("
            SELECT COALESCE(SUM(amount), 0) as total_penalty
            FROM wallet_transactions 
            WHERE transaction_type IN ('admin_cancellation_penalty_received', 'vendor_cancellation_penalty_received')
            AND category = 'credit'
        ");
        if (!$cancellation_penalty) {
            $cancellation_penalty = ['total_penalty' => 0];
        }
        


        // 9. Withdrawal Statistics
        $withdrawal_stats = $this->db->fetch("
            SELECT 
                COALESCE(SUM(CASE WHEN status IN ('approved', 'paid') THEN amount ELSE 0 END), 0) as total_withdrawals,
                COALESCE(SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END), 0) as pending_withdrawals
            FROM withdrawal_requests
        ");
        
        if (!$withdrawal_stats) {
            $withdrawal_stats = [
                'total_withdrawals' => 0,
                'pending_withdrawals' => 0
            ];
        }
        
        // 10. Recent Activity
        $recent_activity = $this->db->fetch("
            SELECT 
                COUNT(CASE WHEN status != 'cancelled' THEN 1 END) as orders_last_30_days,
                COALESCE(SUM(CASE WHEN status != 'cancelled' THEN total_amount ELSE 0 END), 0) as revenue_last_30_days
            FROM orders 
            WHERE payment_status = 'paid'
            AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        ");
        
        if (!$recent_activity) {
            $recent_activity = [
                'orders_last_30_days' => 0,
                'revenue_last_30_days' => 0
            ];
        }
        
        return [
            'available_balance' => floatval($wallet['balance'] ?? 0),
            'total_earned' => floatval($wallet['total_earned'] ?? 0),
            'total_withdrawn' => floatval($wallet['total_withdrawn'] ?? 0),
            'current_rent' => floatval($current_rent['total'] ?? 0),
            'pending_rent' => floatval($pending_rent['total'] ?? 0),
            'current_security' => floatval($current_security['total'] ?? 0),
            'current_rent_onhold' => floatval($current_rent_onhold['total'] ?? 0),
            'vendors_earning_onhold' => floatval($vendors_earning_onhold['total'] ?? 0),
            'total_customers_spent' => floatval($customers_spent['total'] ?? 0),
            'total_vendors_earning' => floatval($vendors_earning['total'] ?? 0),
            'total_customers' => intval($platform_stats['total_customers'] ?? 0),
            'total_vendors' => intval($platform_stats['total_vendors'] ?? 0),
            'customers_wallet_balance' => floatval($platform_stats['customers_wallet_balance'] ?? 0),
            'vendors_wallet_balance' => floatval($platform_stats['vendors_wallet_balance'] ?? 0),
            'total_orders' => intval($order_stats['total_orders'] ?? 0),
            'active_orders' => intval($order_stats['active_orders'] ?? 0),
            'completed_orders' => intval($order_stats['completed_orders'] ?? 0),
            'cancelled_orders' => intval($order_stats['cancelled_orders'] ?? 0),
            'total_revenue' => floatval($order_stats['total_revenue'] ?? 0),
            'total_refunded' => floatval($refund_stats['total_refunded'] ?? 0),
            'total_cancellation_penalty' => floatval($cancellation_penalty['total_penalty'] ?? 0),
            'total_withdrawals' => floatval($withdrawal_stats['total_withdrawals'] ?? 0),
            'pending_withdrawals' => floatval($withdrawal_stats['pending_withdrawals'] ?? 0),
            'orders_last_30_days' => intval($recent_activity['orders_last_30_days'] ?? 0),
            'revenue_last_30_days' => floatval($recent_activity['revenue_last_30_days'] ?? 0)
        ];
    }


    
    /**
     * ================================================================
     * GET CUSTOMER TRANSACTION HISTORY
     * ================================================================
     */
    public function getCustomerTransactions($customer_user_id, $limit = 50, $offset = 0) {
        return $this->db->fetchAll("
            SELECT 
                wt.*,
                o.order_number
            FROM wallet_transactions wt
            LEFT JOIN orders o ON wt.order_id = o.id
            WHERE wt.user_id = ?
            ORDER BY wt.created_at DESC
            LIMIT ? OFFSET ?
        ", [$customer_user_id, $limit, $offset]);
    }
    
    /**
     * ================================================================
     * GET VENDOR TRANSACTION HISTORY
     * ================================================================
     */
    public function getVendorTransactions($vendor_user_id, $limit = 50, $offset = 0) {
        return $this->db->fetchAll("
            SELECT 
                wt.*,
                o.order_number
            FROM wallet_transactions wt
            LEFT JOIN orders o ON wt.order_id = o.id
            WHERE wt.user_id = ?
            ORDER BY wt.created_at DESC
            LIMIT ? OFFSET ?
        ", [$vendor_user_id, $limit, $offset]);
    }
    
    /**
     * ================================================================
     * GET PENDING EARNINGS DETAILS (for vendor)
     * ================================================================
     */
    public function getVendorPendingEarnings($vendor_user_id) {
        return $this->db->fetchAll("
            SELECT 
                opb.id,
                opb.order_id,
                opb.amount,
                opb.created_at,
                o.order_number,
                o.start_date,
                o.end_date,
                o.status as order_status,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
            FROM order_payment_breakdown opb
            JOIN orders o ON opb.order_id = o.id
            JOIN customers c ON o.customer_id = c.id
            WHERE opb.user_id = ?
            AND opb.type = 'rental'
            AND opb.status = 'onhold'
            ORDER BY opb.created_at DESC
        ", [$vendor_user_id]);
    }
    
    /**
     * ================================================================
     * GET CUSTOMER SECURITY DEPOSITS HELD
     * ================================================================
     */
    public function getCustomerSecurityDeposits($customer_user_id) {
        return $this->db->fetchAll("
            SELECT 
                opb.id,
                opb.order_id,
                opb.amount,
                opb.created_at,
                o.order_number,
                o.start_date,
                o.end_date,
                o.status as order_status,
                v.business_name as vendor_name,
                i.name as item_name
            FROM order_payment_breakdown opb
            JOIN orders o ON opb.order_id = o.id
            JOIN vendors v ON o.vendor_id = v.id
            JOIN items i ON o.item_id = i.id
            WHERE opb.user_id = ?
            AND opb.type = 'security_deposit'
            AND opb.status = 'onhold'
            ORDER BY opb.created_at DESC
        ", [$customer_user_id]);
    }
    
    /**
     * ================================================================
     * FORMAT CURRENCY
     * ================================================================
     */
    public function formatCurrency($amount) {
        return '₦' . number_format($amount, 2);
    }
    
    /**
     * ================================================================
     * GET TRANSACTION TYPE LABEL
     * ================================================================
     */
    public function getTransactionTypeLabel($type) {
        $labels = [
            'item_rental' => 'Rental Payment',
            'security_deposit_held' => 'Security Held',
            'security_deposit_refund' => 'Security Refund',
            'rental_earning' => 'Rental Earning',
            'platform_commission' => 'Commission',
            'withdrawal' => 'Withdrawal',
            'top_up' => 'Wallet Top-up',
            'rental_refund' => 'Rental Refund',
            'rental_cancellation_refund' => 'Rental Cancellation Refund',
        ];
        
        return $labels[$type] ?? ucwords(str_replace('_', ' ', $type));
    }
}