<?php
/**
 * ============================================================================
 * classes/Transaction.php - Financial Transaction Management Class
 * ============================================================================
 */

namespace App;

use PDO;
use PDOException;

class Transaction {
    private $db;
    private $table = 'transactions';
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    /**
     * Create new transaction
     */
    public function create($userId, $data) {
        try {
            $sql = "INSERT INTO {$this->table} 
                    (user_id, enterprise_id, transaction_type, category, amount, 
                     description, transaction_date, receipt_url)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([
                $userId,
                $data['enterprise_id'] ?? null,
                $data['transaction_type'],
                sanitize($data['category'] ?? ''),
                $data['amount'],
                sanitize($data['description'] ?? ''),
                $data['transaction_date'] ?? date('Y-m-d'),
                $data['receipt_url'] ?? null
            ]);
            
            if ($result) {
                $transactionId = $this->db->lastInsertId();
                logActivity($userId, 'create_transaction', 
                    "{$data['transaction_type']} transaction: " . formatCurrency($data['amount']));
                
                return [
                    'success' => true,
                    'message' => 'Transaction recorded successfully',
                    'transaction_id' => $transactionId
                ];
            }
            
            return ['success' => false, 'message' => 'Failed to record transaction'];
            
        } catch (PDOException $e) {
            error_log("Transaction creation error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Database error occurred'];
        }
    }
    
    /**
     * Get user transactions
     */
    public function getUserTransactions($userId, $filters = []) {
        $sql = "SELECT t.*, e.enterprise_name, e.enterprise_type
                FROM {$this->table} t
                LEFT JOIN enterprises e ON t.enterprise_id = e.id
                WHERE t.user_id = ?";
        
        $params = [$userId];
        
        if (!empty($filters['type'])) {
            $sql .= " AND t.transaction_type = ?";
            $params[] = $filters['type'];
        }
        
        if (!empty($filters['enterprise_id'])) {
            $sql .= " AND t.enterprise_id = ?";
            $params[] = $filters['enterprise_id'];
        }
        
        if (!empty($filters['start_date'])) {
            $sql .= " AND t.transaction_date >= ?";
            $params[] = $filters['start_date'];
        }
        
        if (!empty($filters['end_date'])) {
            $sql .= " AND t.transaction_date <= ?";
            $params[] = $filters['end_date'];
        }
        
        $sql .= " ORDER BY t.transaction_date DESC, t.created_at DESC";
        
        if (!empty($filters['limit'])) {
            $sql .= " LIMIT ?";
            $params[] = (int)$filters['limit'];
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get transaction by ID
     */
    public function getById($transactionId, $userId = null) {
        $sql = "SELECT t.*, e.enterprise_name, e.enterprise_type
                FROM {$this->table} t
                LEFT JOIN enterprises e ON t.enterprise_id = e.id
                WHERE t.id = ?";
        
        if ($userId) {
            $sql .= " AND t.user_id = ?";
        }
        
        $stmt = $this->db->prepare($sql);
        $userId ? $stmt->execute([$transactionId, $userId]) : $stmt->execute([$transactionId]);
        
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Update transaction
     */
    public function update($transactionId, $userId, $data) {
        try {
            $sql = "UPDATE {$this->table} SET 
                    enterprise_id = ?,
                    transaction_type = ?,
                    category = ?,
                    amount = ?,
                    description = ?,
                    transaction_date = ?,
                    receipt_url = ?
                    WHERE id = ? AND user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([
                $data['enterprise_id'] ?? null,
                $data['transaction_type'],
                sanitize($data['category']),
                $data['amount'],
                sanitize($data['description']),
                $data['transaction_date'],
                $data['receipt_url'] ?? null,
                $transactionId,
                $userId
            ]);
            
            if ($result) {
                return ['success' => true, 'message' => 'Transaction updated successfully'];
            }
            
            return ['success' => false, 'message' => 'Failed to update transaction'];
            
        } catch (PDOException $e) {
            error_log("Transaction update error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Database error occurred'];
        }
    }
    
    /**
     * Delete transaction
     */
    public function delete($transactionId, $userId) {
        try {
            $sql = "DELETE FROM {$this->table} WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            
            if ($stmt->execute([$transactionId, $userId])) {
                logActivity($userId, 'delete_transaction', "Deleted transaction ID: {$transactionId}");
                return ['success' => true, 'message' => 'Transaction deleted successfully'];
            }
            
            return ['success' => false, 'message' => 'Failed to delete transaction'];
            
        } catch (PDOException $e) {
            error_log("Transaction deletion error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Database error occurred'];
        }
    }
    
    /**
     * Get financial summary
     */
    public function getSummary($userId, $startDate = null, $endDate = null) {
        $sql = "SELECT 
                SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) as total_income,
                SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) as total_expenses,
                SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE -amount END) as net_profit,
                COUNT(CASE WHEN transaction_type = 'income' THEN 1 END) as income_count,
                COUNT(CASE WHEN transaction_type = 'expense' THEN 1 END) as expense_count
                FROM {$this->table}
                WHERE user_id = ?";
        
        $params = [$userId];
        
        if ($startDate) {
            $sql .= " AND transaction_date >= ?";
            $params[] = $startDate;
        }
        
        if ($endDate) {
            $sql .= " AND transaction_date <= ?";
            $params[] = $endDate;
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get monthly summary
     */
    public function getMonthlySummary($userId, $months = 6) {
        $sql = "SELECT 
                DATE_FORMAT(transaction_date, '%Y-%m') as month,
                SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) as income,
                SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) as expenses,
                SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE -amount END) as profit
                FROM {$this->table}
                WHERE user_id = ?
                AND transaction_date >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
                GROUP BY month
                ORDER BY month DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId, $months]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get category breakdown
     */
    public function getCategoryBreakdown($userId, $type = 'expense') {
        $sql = "SELECT 
                category,
                SUM(amount) as total,
                COUNT(*) as count
                FROM {$this->table}
                WHERE user_id = ?
                AND transaction_type = ?
                AND transaction_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
                GROUP BY category
                ORDER BY total DESC
                LIMIT 10";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId, $type]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>
