<?php
/**
 * WalletTransaction Model
 * File: app/Models/WalletTransaction.php
 * 
 * Manages wallet transaction records (single source of truth for all money movements)
 */

class WalletTransaction {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Get transactions by wallet ID
     */
    public function getByWalletId($wallet_id, $limit = 20, $offset = 0) {
        $sql = "SELECT wt.*, o.order_number
                FROM wallet_transactions wt
                LEFT JOIN orders o ON wt.order_id = o.id
                WHERE wt.wallet_id = ?
                ORDER BY wt.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$wallet_id, $limit, $offset]);
    }
    
    /**
     * Get transactions by user ID
     */
    public function getByUserId($user_id, $limit = 20, $offset = 0) {
        $sql = "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 ?";
        
        return $this->db->fetchAll($sql, [$user_id, $limit, $offset]);
    }
    
    /**
     * Get transaction by reference
     */
    public function getByReference($reference) {
        $sql = "SELECT wt.*, w.user_id
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                WHERE wt.reference = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$reference]);
    }
    
    /**
     * Get transaction by ID
     */
    public function find($id) {
        $sql = "SELECT wt.*, w.user_id, o.order_number
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                LEFT JOIN orders o ON wt.order_id = o.id
                WHERE wt.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Get all transactions with pagination and filters
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT wt.*, 
                w.user_id,
                u.email,
                u.user_type,
                o.order_number,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as user_name
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                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'
                LEFT JOIN orders o ON wt.order_id = o.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['user_id'])) {
            $sql .= " AND w.user_id = ?";
            $params[] = $filters['user_id'];
        }
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        if (!empty($filters['transaction_type'])) {
            $sql .= " AND wt.transaction_type = ?";
            $params[] = $filters['transaction_type'];
        }
        
        if (!empty($filters['category'])) {
            $sql .= " AND wt.category = ?";
            $params[] = $filters['category'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND wt.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['order_id'])) {
            $sql .= " AND wt.order_id = ?";
            $params[] = $filters['order_id'];
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(wt.created_at) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(wt.created_at) <= ?";
            $params[] = $filters['date_to'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (wt.reference LIKE ? OR wt.description LIKE ? OR o.order_number LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY wt.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count transactions
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count 
                FROM wallet_transactions wt 
                JOIN wallets w ON wt.wallet_id = w.id
                JOIN users u ON w.user_id = u.id
                WHERE 1=1";
        $params = [];
        
        if (!empty($filters['user_id'])) {
            $sql .= " AND w.user_id = ?";
            $params[] = $filters['user_id'];
        }
        
        if (!empty($filters['user_type'])) {
            $sql .= " AND u.user_type = ?";
            $params[] = $filters['user_type'];
        }
        
        if (!empty($filters['transaction_type'])) {
            $sql .= " AND wt.transaction_type = ?";
            $params[] = $filters['transaction_type'];
        }
        
        if (!empty($filters['category'])) {
            $sql .= " AND wt.category = ?";
            $params[] = $filters['category'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND wt.status = ?";
            $params[] = $filters['status'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Get transactions by order ID
     */
    public function getByOrderId($order_id) {
        $sql = "SELECT wt.*, w.user_id, u.user_type
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                JOIN users u ON w.user_id = u.id
                WHERE wt.order_id = ?
                ORDER BY wt.created_at ASC";
        
        return $this->db->fetchAll($sql, [$order_id]);
    }
    
    /**
     * Get transaction statistics
     */
    public function getStats($user_id = null, $period = '30 days') {
        $sql = "SELECT 
                COUNT(*) as total_transactions,
                COALESCE(SUM(CASE WHEN category = 'credit' THEN amount ELSE 0 END), 0) as total_credits,
                COALESCE(SUM(CASE WHEN category = 'debit' THEN amount ELSE 0 END), 0) as total_debits,
                COUNT(CASE WHEN category = 'credit' THEN 1 END) as credit_count,
                COUNT(CASE WHEN category = 'debit' THEN 1 END) as debit_count,
                COUNT(CASE WHEN transaction_type = 'vendor_earning' THEN 1 END) as earnings_count,
                COUNT(CASE WHEN transaction_type = 'withdrawal' THEN 1 END) as withdrawals_count,
                COUNT(CASE WHEN transaction_type = 'security_refund' THEN 1 END) as refunds_count
                FROM wallet_transactions wt";
        
        $params = [];
        
        if ($user_id) {
            $sql .= " WHERE wt.user_id = ?";
            $params[] = $user_id;
            
            if ($period) {
                $sql .= " AND wt.created_at >= DATE_SUB(NOW(), INTERVAL {$period})";
            }
        } else {
            if ($period) {
                $sql .= " WHERE wt.created_at >= DATE_SUB(NOW(), INTERVAL {$period})";
            }
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Get recent transactions
     */
    public function getRecent($limit = 10, $user_id = null) {
        $sql = "SELECT wt.*, 
                w.user_id,
                u.email,
                u.user_type,
                o.order_number,
                CONCAT(COALESCE(c.first_name, v.business_name, 'Admin'), ' ', COALESCE(c.last_name, '')) as user_name
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                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'
                LEFT JOIN orders o ON wt.order_id = o.id";
        
        $params = [];
        
        if ($user_id) {
            $sql .= " WHERE w.user_id = ?";
            $params[] = $user_id;
        }
        
        $sql .= " ORDER BY wt.created_at DESC LIMIT ?";
        $params[] = $limit;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Generate unique transaction reference
     */
    public function generateReference($prefix = 'TXN') {
        do {
            $reference = $prefix . '-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -10));
            $exists = $this->getByReference($reference);
        } while ($exists);
        
        return $reference;
    }
    
    /**
     * Get earnings summary for vendor
     */
    public function getVendorEarnings($vendor_id, $period = null) {
        $sql = "SELECT 
                COALESCE(SUM(amount), 0) as total_earnings,
                COUNT(*) as transaction_count,
                MIN(created_at) as first_earning,
                MAX(created_at) as last_earning
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                JOIN users u ON w.user_id = u.id
                JOIN vendors v ON u.id = v.user_id
                WHERE v.id = ? 
                AND wt.transaction_type = 'vendor_earning'
                AND wt.status = 'completed'";
        
        $params = [$vendor_id];
        
        if ($period) {
            $sql .= " AND wt.created_at >= DATE_SUB(NOW(), INTERVAL {$period})";
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Get customer spending summary
     */
    public function getCustomerSpending($customer_id, $period = null) {
        $sql = "SELECT 
                COALESCE(SUM(amount), 0) as total_spent,
                COUNT(*) as transaction_count
                FROM wallet_transactions wt
                JOIN wallets w ON wt.wallet_id = w.id
                JOIN users u ON w.user_id = u.id
                JOIN customers c ON u.id = c.user_id
                WHERE c.id = ? 
                AND wt.transaction_type = 'rental_payment'
                AND wt.status = 'completed'";
        
        $params = [$customer_id];
        
        if ($period) {
            $sql .= " AND wt.created_at >= DATE_SUB(NOW(), INTERVAL {$period})";
        }
        
        return $this->db->fetch($sql, $params);
    }
}