<?php
session_start();
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) {
        throw new Exception("Query failed: " . $conn->error);
    }
    return $result;
}

try {
    // Get total sales value
    $result = executeQuery($conn, "SELECT SUM(final_amount) as total FROM sales");
    $totalSales = $result->fetch_assoc()['total'] ?? 0;

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

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

    // 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;

    // Get average rate per kg
    $result = executeQuery($conn, "SELECT SUM(final_amount) / SUM(weight_tilapia + weight_small_fish + weight_big_fish) as avg_rate FROM sales");
    $averageRatePerKg = $result->fetch_assoc()['avg_rate'] ?? 0;

    // Get initial values
    $result = executeQuery($conn, "SELECT * FROM initial_values LIMIT 1");
    $initialValues = $result->fetch_assoc();

    // Calculate cash in hand
    $cashInHand = ($initialValues['cash_in_hand'] ?? 0) + $totalPayments - $totalExpenses;

    // Set headers for CSV download
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="dashboard_export.csv"');

    // Open the output stream
    $output = fopen('php://output', 'w');

    // Output summary data
    fputcsv($output, array('Dashboard Summary'));
    fputcsv($output, array('Total Sales Value', 'Total Expense Value', 'Total Payments Value', 'Cash in Hand', 'Total Weight Harvested', 'Average Rate/Kg'));
    fputcsv($output, array(
        formatCurrency($totalSales),
        formatCurrency($totalExpenses),
        formatCurrency($totalPayments),
        formatCurrency($cashInHand),
        number_format($totalWeightHarvested, 2),
        formatCurrency($averageRatePerKg)
    ));
    fputcsv($output, array()); // Empty line

    // Output Fortnightly Harvest Charges
    // Output Fortnightly Harvest Charges
fputcsv($output, array('Fortnightly Harvest Charges'));
fputcsv($output, array('Fortnight', 'Tilapia (kg)', 'Small Fish (kg)', 'Big Fish (kg)', 'Harvest Charges'));
$query = "SELECT 
            DATE_FORMAT(date, '%Y-%m') as month_year,
            CASE 
                WHEN DAY(date) <= 15 THEN 'First Half'
                ELSE 'Second Half'
            END as half_month,
            MIN(date) as start_date,
            MAX(date) as end_date,
            SUM(weight_tilapia) as total_tilapia,
            SUM(weight_small_fish) as total_small_fish,
            SUM(weight_big_fish) as total_big_fish,
            SUM(harvesting_charges) as total_charges
          FROM sales
          WHERE date BETWEEN '2024-10-01' AND '2025-09-30'
          GROUP BY 
            month_year, 
            half_month
          HAVING total_charges > 0
          ORDER BY start_date";
$result = executeQuery($conn, $query);
while ($row = $result->fetch_assoc()) {
    // Format the start and end dates
    $startDate = date('Y-m-d', strtotime($row['start_date']));
    $endDate = date('Y-m-d', strtotime($row['end_date']));
    
    // Create a descriptive fortnight name
    if ($row['half_month'] == 'First Half') {
        $fortnightName = date('M d', strtotime($startDate)) . " - " . date('M d', strtotime($row['month_year'] . '-15'));
    } else {
        $fortnightName = date('M d', strtotime($row['month_year'] . '-16')) . " - " . date('M d', strtotime($endDate));
    }
    
    fputcsv($output, array(
        $fortnightName,
        number_format($row['total_tilapia'], 2),
        number_format($row['total_small_fish'], 2),
        number_format($row['total_big_fish'], 2),
        formatCurrency($row['total_charges'])
    ));
}
fputcsv($output, array()); // Empty line

    // Output Buyer Data
    fputcsv($output, array('Buyer Data'));
    fputcsv($output, array('Buyer', 'Total Sales', 'Total Payments', 'Balance'));
    $query = "SELECT 
                b.name as buyer_name, 
                COALESCE(SUM(s.final_amount), 0) as total_sales, 
                COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) as total_payments,
                CASE 
                    WHEN b.name = 'Madhu' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + {$initialValues['madhu_balance']}
                    WHEN b.name = 'Rathna' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + {$initialValues['rathna_balance']}
                    WHEN b.name = 'Jambukkutti' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + {$initialValues['jambukkutti_balance']}
                    ELSE COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0)
                END as balance
              FROM buyers b
              LEFT JOIN sales s ON b.id = s.buyer_id
              GROUP BY b.id
              ORDER BY b.name";
    $result = executeQuery($conn, $query);
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, array(
            $row['buyer_name'],
            formatCurrency($row['total_sales']),
            formatCurrency($row['total_payments']),
            formatCurrency($row['balance'])
        ));
    }
    fputcsv($output, array()); // Empty line

    // Output Monthly Sales Data
    fputcsv($output, array('Monthly Sales Data (Oct 2024 - Sep 2025)'));
    fputcsv($output, array('Month', 'Sales Value', 'Expense Value', 'Payments Value', 'Harvested Weight', 'Rate/Kg'));
    $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) / SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish) as rate_per_kg
              FROM sales s
              WHERE s.date BETWEEN '2024-10-01' AND '2025-09-30'
              GROUP BY DATE_FORMAT(s.date, '%Y-%m')
              ORDER BY s.date";
    $result = executeQuery($conn, $query);
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, array(
            $row['month'],
            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);
} catch (Exception $e) {
    // In case of error, output error message
    header('Content-Type: text/plain');
    echo "An error occurred: " . $e->getMessage();
}

exit();
?>