<?php
/**
 * Wallet Model
 * File: app/Models/Wallet.php
 * 
 * Manages user wallets and balances
 */

class Wallet {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create wallet for user
     */
    public function create($user_id) {
        $sql = "INSERT INTO wallets (user_id, balance, total_earned, total_withdrawn, pending_balance, created_at) 
                VALUES (?, 0.00, 0.00, 0.00, 0.00, NOW())";
        
        $result = $this->db->execute($sql, [$user_id]);
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find wallet by ID
     */
    public function find($id) {
        $sql = "SELECT w.*, u.email, u.user_type
                FROM wallets w
                JOIN users u ON w.user_id = u.id
                WHERE w.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find wallet by user ID
     */
    public function findByUserId($user_id) {
        $sql = "SELECT w.*, u.email, u.user_type
                FROM wallets w
                JOIN users u ON w.user_id = u.id
                WHERE w.user_id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$user_id]);
    }
    
    /**
     * Get or create wallet for user
     */
    public function getOrCreate($user_id) {
        $wallet = $this->findByUserId($user_id);
        
        if (!$wallet) {
            $wallet_id = $this->create($user_id);
            $wallet = $this->find($wallet_id);
        }
        
        return $wallet;
    }
    
    /**
     * Get wallet balance
     */
    public function getBalance($user_id) {
        $wallet = $this->findByUserId($user_id);
        return $wallet ? floatval($wallet['balance']) : 0.00;
    }
    
    /**
     * Get pending balance
     */
    public function getPendingBalance($user_id) {
        $wallet = $this->findByUserId($user_id);
        return $wallet ? floatval($wallet['pending_balance']) : 0.00;
    }
    
    /**
     * Credit wallet
     * @param int $user_id User ID
     * @param float $amount Amount to credit
     * @param string $transaction_type Type of transaction
     * @param string $description Description
     * @param string $reference Unique reference
     * @param int|null $order_id Related order ID
     * @return bool Success status
     */
    public function credit($user_id, $amount, $transaction_type, $description, $reference, $order_id = null) {
        $this->db->beginTransaction();
        
        try {
            // Get or create wallet
            $wallet = $this->getOrCreate($user_id);
            
            $balance_before = floatval($wallet['balance']);
            $balance_after = $balance_before + floatval($amount);
            
            // Update wallet balance
            $update_sql = "UPDATE wallets SET 
                          balance = ?, 
                          total_earned = total_earned + ?,
                          updated_at = NOW() 
                          WHERE id = ?";
            
            $this->db->execute($update_sql, [$balance_after, $amount, $wallet['id']]);
            
            // Create transaction record
            $trans_sql = "INSERT INTO wallet_transactions (
                         wallet_id, user_id, order_id, transaction_type, category, 
                         amount, balance_before, balance_after, description, reference, 
                         status, created_at
                         ) VALUES (?, ?, ?, ?, 'credit', ?, ?, ?, ?, ?, 'completed', NOW())";
            
            $this->db->execute($trans_sql, [
                $wallet['id'],
                $user_id,
                $order_id,
                $transaction_type,
                $amount,
                $balance_before,
                $balance_after,
                $description,
                $reference
            ]);
            
            $this->db->commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Wallet credit error: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Debit wallet
     * @param int $user_id User ID
     * @param float $amount Amount to debit
     * @param string $transaction_type Type of transaction
     * @param string $description Description
     * @param string $reference Unique reference
     * @param int|null $order_id Related order ID
     * @return bool Success status
     */
    public function debit($user_id, $amount, $transaction_type, $description, $reference, $order_id = null) {
        $this->db->beginTransaction();
        
        try {
            // Get wallet
            $wallet = $this->findByUserId($user_id);
            
            if (!$wallet) {
                throw new Exception("Wallet not found");
            }
            
            $balance_before = floatval($wallet['balance']);
            
            // Check sufficient balance
            if ($balance_before < floatval($amount)) {
                throw new Exception("Insufficient balance");
            }
            
            $balance_after = $balance_before - floatval($amount);
            
            // Update wallet balance
            $update_sql = "UPDATE wallets SET 
                          balance = ?, 
                          total_withdrawn = total_withdrawn + ?,
                          updated_at = NOW() 
                          WHERE id = ?";
            
            $this->db->execute($update_sql, [$balance_after, $amount, $wallet['id']]);
            
            // Create transaction record
            $trans_sql = "INSERT INTO wallet_transactions (
                         wallet_id, user_id, order_id, transaction_type, category, 
                         amount, balance_before, balance_after, description, reference, 
                         status, created_at
                         ) VALUES (?, ?, ?, ?, 'debit', ?, ?, ?, ?, ?, 'completed', NOW())";
            
            $this->db->execute($trans_sql, [
                $wallet['id'],
                $user_id,
                $order_id,
                $transaction_type,
                $amount,
                $balance_before,
                $balance_after,
                $description,
                $reference
            ]);
            
            $this->db->commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Wallet debit error: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Update pending balance
     */
    public function updatePendingBalance($user_id, $amount) {
        $sql = "UPDATE wallets SET pending_balance = ?, updated_at = NOW() WHERE user_id = ?";
        return $this->db->execute($sql, [$amount, $user_id]);
    }
    
    /**
     * Add to pending balance
     */
    public function addPendingBalance($user_id, $amount) {
        $sql = "UPDATE wallets SET pending_balance = pending_balance + ?, updated_at = NOW() WHERE user_id = ?";
        return $this->db->execute($sql, [$amount, $user_id]);
    }
    
    /**
     * Subtract from pending balance
     */
    public function subtractPendingBalance($user_id, $amount) {
        $sql = "UPDATE wallets SET pending_balance = pending_balance - ?, updated_at = NOW() WHERE user_id = ?";
        return $this->db->execute($sql, [$amount, $user_id]);
    }
    
    /**
     * Get wallet statistics
     */
    public function getStats($user_id = null) {
        if ($user_id) {
            // User-specific stats
            $sql = "SELECT 
                    w.balance,
                    w.total_earned,
                    w.total_withdrawn,
                    w.pending_balance,
                    (SELECT COUNT(*) FROM wallet_transactions WHERE wallet_id = w.id) as total_transactions,
                    (SELECT COUNT(*) FROM wallet_transactions WHERE wallet_id = w.id AND category = 'credit') as credit_count,
                    (SELECT COUNT(*) FROM wallet_transactions WHERE wallet_id = w.id AND category = 'debit') as debit_count,
                    (SELECT COALESCE(SUM(amount), 0) FROM wallet_transactions WHERE wallet_id = w.id AND category = 'credit' AND DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) as earnings_30_days
                    FROM wallets w
                    WHERE w.user_id = ?";
            
            return $this->db->fetch($sql, [$user_id]);
        } else {
            // Platform-wide stats
            $sql = "SELECT 
                    COUNT(*) as total_wallets,
                    COALESCE(SUM(balance), 0) as total_balance,
                    COALESCE(SUM(total_earned), 0) as total_earned,
                    COALESCE(SUM(total_withdrawn), 0) as total_withdrawn,
                    COALESCE(SUM(pending_balance), 0) as total_pending
                    FROM wallets";
            
            return $this->db->fetch($sql);
        }
    }
    
    /**
     * Get all wallets with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT w.*, 
                u.email, u.user_type,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as name
                FROM wallets w
                JOIN users u ON w.user_id = u.id
                LEFT JOIN customers c ON u.id = c.user_id AND u.user_type = 'customer'
                LEFT JOIN vendors v ON u.id = v.user_id AND u.user_type = 'vendor'
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        if (isset($filters['min_balance'])) {
            $sql .= " AND w.balance >= ?";
            $params[] = $filters['min_balance'];
        }
        
        $sql .= " ORDER BY w.balance DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count wallets
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM wallets w JOIN users u ON w.user_id = u.id WHERE 1=1";
        $params = [];
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
}