<?php
/**
 * Transaction Model
 * File: app/Models/Transaction.php
 * 
 * Logs all payment transactions
 */

class Transaction {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create transaction record
     */
    public function create($data) {
        $sql = "INSERT INTO transactions (
            customer_id, transaction_reference, payment_gateway,
            amount, currency, status, payment_type,
            customer_email, metadata, ip_address, user_agent,
            initiated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $params = [
            $data['customer_id'],
            $data['transaction_reference'],
            $data['payment_gateway'],
            $data['amount'],
            $data['currency'] ?? 'NGN',
            $data['status'] ?? 'pending',
            $data['payment_type'] ?? 'order_payment',
            $data['customer_email'],
            isset($data['metadata']) ? json_encode($data['metadata']) : null,
            $data['ip_address'] ?? $_SERVER['REMOTE_ADDR'] ?? null,
            $data['user_agent'] ?? $_SERVER['HTTP_USER_AGENT'] ?? null
        ];
        
        $this->db->execute($sql, $params);
        return $this->db->lastInsertId();
    }
    
    /**
     * Update transaction status
     */
    public function updateStatus($reference, $status, $data = []) {
        $sql = "UPDATE transactions SET status = ?";
        $params = [$status];
        
        // Add order_id if provided
        if (isset($data['order_id'])) {
            $sql .= ", order_id = ?";
            $params[] = $data['order_id'];
        }
        
        // Add gateway response
        if (isset($data['gateway_response'])) {
            $sql .= ", gateway_response = ?";
            $params[] = is_array($data['gateway_response']) 
                ? json_encode($data['gateway_response']) 
                : $data['gateway_response'];
        }
        
        // Add gateway reference
        if (isset($data['gateway_reference'])) {
            $sql .= ", gateway_reference = ?";
            $params[] = $data['gateway_reference'];
        }
        
        // Set completion timestamp based on status
        if ($status === 'successful') {
            $sql .= ", completed_at = NOW()";
        } elseif ($status === 'failed' || $status === 'cancelled') {
            $sql .= ", failed_at = NOW()";
            
            if (isset($data['error_message'])) {
                $sql .= ", error_message = ?";
                $params[] = $data['error_message'];
            }
        }
        
        $sql .= " WHERE transaction_reference = ?";
        $params[] = $reference;
        
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Find transaction by reference
     */
    public function findByReference($reference) {
        $sql = "SELECT * FROM transactions WHERE transaction_reference = ? LIMIT 1";
        return $this->db->fetch($sql, [$reference]);
    }
    
    /**
     * Get customer transactions
     */
    public function getByCustomer($customer_id, $limit = 10) {
        $sql = "SELECT t.*, o.order_number
                FROM transactions t
                LEFT JOIN orders o ON t.order_id = o.id
                WHERE t.customer_id = ?
                ORDER BY t.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$customer_id, $limit]);
    }
    
    /**
     * Get failed transactions
     */
    public function getFailedTransactions($limit = 50) {
        $sql = "SELECT t.*, 
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                c.phone as customer_phone
                FROM transactions t
                JOIN customers c ON t.customer_id = c.id
                WHERE t.status IN ('failed', 'cancelled')
                ORDER BY t.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get successful transactions
     */
    public function getSuccessfulTransactions($limit = 50) {
        $sql = "SELECT t.*, 
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                o.order_number
                FROM transactions t
                JOIN customers c ON t.customer_id = c.id
                LEFT JOIN orders o ON t.order_id = o.id
                WHERE t.status = 'successful'
                ORDER BY t.completed_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get transaction statistics
     */
    public function getStats($period = '30 days') {
        $sql = "SELECT 
                COUNT(*) as total_transactions,
                SUM(CASE WHEN status = 'successful' THEN 1 ELSE 0 END) as successful_count,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_count,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(CASE WHEN status = 'successful' THEN amount ELSE 0 END) as total_revenue,
                AVG(CASE WHEN status = 'successful' THEN amount ELSE NULL END) as avg_transaction
                FROM transactions
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL {$period})";
        
        return $this->db->fetch($sql);
    }
}