<?php
session_start();

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

require_once 'db_connection.php';

// Helper function to safely format currency for CSV
function formatCurrency($value) {
    if (is_numeric($value)) {
        return number_format((float)$value, 2, '.', '');
    }
    return '0.00';
}

// Function to safely execute SQL queries
function executeQuery($conn, $query) {
    $result = $conn->query($query);
    if ($result === false) {
        error_log("Query failed: " . $conn->error);
        throw new Exception("Database query failed.");
    }
    return $result;
}

try {
    // Set headers for CSV download
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="dashboard_report_' . date('Y-m-d') . '.csv"');
    
    // Create output stream
    $output = fopen('php://output', 'w');
    
    // Add BOM for Excel UTF-8 support
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Summary section
    fputcsv($output, array('DASHBOARD SUMMARY - Generated on ' . date('Y-m-d H:i:s')));
    fputcsv($output, array(''));
    
    // Get total sales value
    $result = executeQuery($conn, "SELECT SUM(final_amount) as total FROM sales");
    $totalSales = $result->fetch_assoc()['total'] ?? 0;
    fputcsv($output, array('Total Sales Value', formatCurrency($totalSales)));

    // Get total expense value
    $result = executeQuery($conn, "SELECT SUM(amount) as total FROM expenses");
    $totalExpenses = $result->fetch_assoc()['total'] ?? 0;
    fputcsv($output, array('Total Expenses', formatCurrency($totalExpenses)));

    // Get total payments value
    $result = executeQuery($conn, "SELECT SUM(amount_paid) as total FROM payments");
    $totalPayments = $result->fetch_assoc()['total'] ?? 0;
    fputcsv($output, array('Total Payments Received', formatCurrency($totalPayments)));

    // Get initial values
    $result = executeQuery($conn, "SELECT * FROM initial_values LIMIT 1");
    $initialValues = $result->fetch_assoc();
    $initialCash = $initialValues['cash_in_hand'] ?? 0;
    $cashInHand = $initialCash + $totalPayments - $totalExpenses;
    fputcsv($output, array('Cash in Hand', formatCurrency($cashInHand)));

    // Get total weight harvested
    $result = executeQuery($conn, "SELECT SUM(weight_tilapia + weight_small_fish + weight_big_fish) as total FROM sales");
    $totalWeightHarvested = $result->fetch_assoc()['total'] ?? 0;
    fputcsv($output, array('Total Weight Harvested (kg)', number_format($totalWeightHarvested, 2)));

    // Get average rate per kg
    $result = executeQuery($conn, "SELECT SUM(final_amount) / NULLIF(SUM(weight_tilapia + weight_small_fish + weight_big_fish), 0) as avg_rate FROM sales");
    $averageRatePerKg = $result->fetch_assoc()['avg_rate'] ?? 0;
    fputcsv($output, array('Average Rate per Kg', formatCurrency($averageRatePerKg)));
    
    fputcsv($output, array(''));
    fputcsv($output, array(''));
    
    // Monthly Report Header
    fputcsv($output, array('MONTHLY REPORT (LAST 12 MONTHS)'));
    fputcsv($output, array('Month', 'Sales Value', 'Expenses', 'Payments', 'Harvested Weight (kg)', 'Rate per Kg'));
    
    // Monthly data
    $query = "SELECT 
                DATE_FORMAT(s.date, '%Y-%m') as month,
                SUM(s.final_amount) as sales_value,
                (SELECT SUM(amount) FROM expenses e WHERE DATE_FORMAT(e.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')) as expense_value,
                (SELECT SUM(amount_paid) FROM payments p WHERE DATE_FORMAT(p.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')) as payments_value,
                SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish) as harvested_weight,
                SUM(s.final_amount) / NULLIF(SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish), 0) as rate_per_kg
              FROM sales s
              WHERE s.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
              GROUP BY DATE_FORMAT(s.date, '%Y-%m')
              ORDER BY s.date DESC";
    $result = executeQuery($conn, $query);
    
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, array(
            date('M Y', strtotime($row['month'] . '-01')),
            formatCurrency($row['sales_value']),
            formatCurrency($row['expense_value']),
            formatCurrency($row['payments_value']),
            number_format($row['harvested_weight'], 2),
            formatCurrency($row['rate_per_kg'])
        ));
    }

    fclose($output);
    $conn->close();
} catch (Exception $e) {
    // In case of error, output error message
    header('Content-Type: text/plain');
    echo "An error occurred while exporting data. Please try again.";
    error_log("Dashboard export error: " . $e->getMessage());
}

exit();
?>