<?php
/**
 * Withdrawal Model
 * File: app/Models/Withdrawal.php
 * 
 * Manages withdrawal requests from vendors/customers
 */

class Withdrawal {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create withdrawal request
     */
    public function create($data) {
        $sql = "INSERT INTO withdrawal_requests (
                user_id, wallet_id, amount, wallet_type, bank_name, 
                account_number, account_name, reference, status, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $data['user_id'],
            $data['wallet_id'],
            $data['amount'],
            $data['wallet_type'] ?? 'main_balance',
            $data['bank_name'],
            $data['account_number'],
            $data['account_name'],
            $data['reference']
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find withdrawal by ID
     */
    public function find($id) {
        $sql = "SELECT wr.*, 
                w.balance as wallet_balance,
                u.email, u.user_type,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as user_name
                FROM withdrawal_requests wr
                JOIN wallets w ON wr.wallet_id = w.id
                JOIN users u ON wr.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 wr.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find withdrawal by reference
     */
    public function findByReference($reference) {
        $sql = "SELECT wr.*, 
                w.balance as wallet_balance,
                u.email, u.user_type
                FROM withdrawal_requests wr
                JOIN wallets w ON wr.wallet_id = w.id
                JOIN users u ON wr.user_id = u.id
                WHERE wr.reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$reference]);
    }
    
    /**
     * Get all withdrawals with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT wr.*, 
                w.balance as wallet_balance,
                u.email, u.user_type,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as user_name,
                CONCAT(p.first_name, ' ', p.last_name) as processed_by_name
                FROM withdrawal_requests wr
                JOIN wallets w ON wr.wallet_id = w.id
                JOIN users u ON wr.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'
                LEFT JOIN users pu ON wr.processed_by = pu.id
                LEFT JOIN customers p ON pu.id = p.user_id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND wr.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['user_id'])) {
            $sql .= " AND wr.user_id = ?";
            $params[] = $filters['user_id'];
        }
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (wr.reference LIKE ? OR wr.account_number LIKE ? OR wr.bank_name LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(wr.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(wr.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " ORDER BY wr.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count withdrawals
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM withdrawal_requests wr JOIN users u ON wr.user_id = u.id WHERE 1=1";
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND wr.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['user_id'])) {
            $sql .= " AND wr.user_id = ?";
            $params[] = $filters['user_id'];
        }
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Get pending withdrawals
     */
    public function getPending($limit = 20) {
        $sql = "SELECT wr.*, 
                w.balance as wallet_balance,
                u.email, u.user_type,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as user_name
                FROM withdrawal_requests wr
                JOIN wallets w ON wr.wallet_id = w.id
                JOIN users u ON wr.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 wr.status = 'pending'
                ORDER BY wr.created_at ASC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Approve withdrawal
     */
    public function approve($id, $admin_id, $transaction_id = null) {
        $sql = "UPDATE withdrawal_requests SET 
                status = 'approved', 
                processed_by = ?,
                processed_at = NOW(),
                transaction_id = ?,
                updated_at = NOW()
                WHERE id = ?";
        
        return $this->db->execute($sql, [$admin_id, $transaction_id, $id]);
    }
    
    /**
     * Mark as paid
     */
    public function markAsPaid($id, $admin_id, $transaction_id = null) {
        $sql = "UPDATE withdrawal_requests SET 
                status = 'paid', 
                processed_by = ?,
                processed_at = NOW(),
                transaction_id = ?,
                updated_at = NOW()
                WHERE id = ?";
        
        return $this->db->execute($sql, [$admin_id, $transaction_id, $id]);
    }
    
    /**
     * Reject withdrawal
     */
    public function reject($id, $admin_id, $reason) {
        $sql = "UPDATE withdrawal_requests SET 
                status = 'rejected', 
                processed_by = ?,
                processed_at = NOW(),
                rejection_reason = ?,
                updated_at = NOW()
                WHERE id = ?";
        
        return $this->db->execute($sql, [$admin_id, $reason, $id]);
    }
    
    /**
     * Cancel withdrawal (by user)
     */
    public function cancel($id) {
        $sql = "UPDATE withdrawal_requests SET 
                status = 'cancelled', 
                updated_at = NOW()
                WHERE id = ? AND status = 'pending'";
        
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Get user withdrawals
     */
    public function getByUserId($user_id, $limit = 10) {
        $sql = "SELECT wr.*
                FROM withdrawal_requests wr
                WHERE wr.user_id = ?
                ORDER BY wr.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$user_id, $limit]);
    }
    
    /**
     * Get withdrawal statistics
     */
    public function getStats($user_id = null) {
        $sql = "SELECT 
                COUNT(*) as total_requests,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_count,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
                COALESCE(SUM(CASE WHEN status IN ('approved', 'paid') THEN amount ELSE 0 END), 0) as total_approved_amount,
                COALESCE(SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END), 0) as total_pending_amount,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as requests_this_week
                FROM withdrawal_requests";
        
        $params = [];
        
        if ($user_id) {
            $sql .= " WHERE user_id = ?";
            $params[] = $user_id;
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Generate unique withdrawal reference
     */
    public function generateReference($prefix = 'WD') {
        do {
            $reference = $prefix . '-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -10));
            $exists = $this->findByReference($reference);
        } while ($exists);
        
        return $reference;
    }
    
    /**
     * Check if user has pending withdrawal
     */
    public function hasPendingWithdrawal($user_id) {
        $sql = "SELECT COUNT(*) as count FROM withdrawal_requests 
                WHERE user_id = ? AND status = 'pending'";
        
        $result = $this->db->fetch($sql, [$user_id]);
        return ($result['count'] ?? 0) > 0;
    }
}