<?php
require_once '../config/database.php';
require_once '../config/auth_enhanced.php';

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

header('Content-Type: application/json');

try {
    $database = new Database();
    $pdo = $database->getConnection();
    
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    
    // Total income and expenses
    $stmt = $pdo->prepare("
        SELECT 
            SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as total_income,
            SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as total_expenses,
            COUNT(CASE WHEN type = 'income' THEN 1 END) as income_transactions,
            COUNT(CASE WHEN type = 'expense' THEN 1 END) as expense_transactions
        FROM transactions 
        WHERE created_at BETWEEN ? AND ?
    ");
    $stmt->execute([$start_date, $end_date]);
    $financial_overview = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Income by category
    $stmt = $pdo->prepare("
        SELECT 
            COALESCE(ec.name, 'General') as category,
            SUM(t.amount) as total_amount,
            COUNT(*) as transaction_count
        FROM transactions t
        LEFT JOIN expense_categories ec ON t.category_id = ec.id
        WHERE t.type = 'income' 
        AND t.created_at BETWEEN ? AND ?
        GROUP BY ec.id, ec.name
        ORDER BY total_amount DESC
        LIMIT 10
    ");
    $stmt->execute([$start_date, $end_date]);
    $income_categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Expenses by category
    $stmt = $pdo->prepare("
        SELECT 
            COALESCE(ec.name, 'General') as category,
            SUM(t.amount) as total_amount,
            COUNT(*) as transaction_count
        FROM transactions t
        LEFT JOIN expense_categories ec ON t.category_id = ec.id
        WHERE t.type = 'expense' 
        AND t.created_at BETWEEN ? AND ?
        GROUP BY ec.id, ec.name
        ORDER BY total_amount DESC
        LIMIT 10
    ");
    $stmt->execute([$start_date, $end_date]);
    $expense_categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Payment methods breakdown
    $stmt = $pdo->prepare("
        SELECT 
            COALESCE(payment_method, 'Unknown') as method,
            COUNT(*) as transaction_count,
            SUM(amount) as total_amount
        FROM transactions 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY payment_method
        ORDER BY total_amount DESC
    ");
    $stmt->execute([$start_date, $end_date]);
    $payment_methods = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate net income
    $net_income = $financial_overview['total_income'] - $financial_overview['total_expenses'];
    
    // Format numbers
    foreach ($income_categories as &$category) {
        $category['total_amount'] = number_format($category['total_amount'], 2);
    }
    
    foreach ($expense_categories as &$category) {
        $category['total_amount'] = number_format($category['total_amount'], 2);
    }
    
    foreach ($payment_methods as &$method) {
        $method['total_amount'] = number_format($method['total_amount'], 2);
    }
    
    // Format for frontend table display
    $table_data = [];
    
    // Add income categories
    foreach ($income_categories as $category) {
        $table_data[] = [
            'category' => $category['category'] . ' (Income)',
            'amount' => $category['total_amount'],
            'count' => $category['transaction_count']
        ];
    }
    
    // Add expense categories
    foreach ($expense_categories as $category) {
        $table_data[] = [
            'category' => $category['category'] . ' (Expense)',
            'amount' => $category['total_amount'],
            'count' => $category['transaction_count']
        ];
    }

    echo json_encode([
        'success' => true,
        'data' => $table_data,
        'period' => [
            'start_date' => $start_date,
            'end_date' => $end_date
        ],
        'overview' => [
            'total_income' => number_format($financial_overview['total_income'], 2),
            'total_expenses' => number_format($financial_overview['total_expenses'], 2),
            'net_income' => number_format($net_income, 2),
            'income_transactions' => $financial_overview['income_transactions'],
            'expense_transactions' => $financial_overview['expense_transactions']
        ],
        'income_categories' => $income_categories,
        'expense_categories' => $expense_categories,
        'payment_methods' => $payment_methods
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
