<?php
session_start();

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

// Session timeout (30 minutes)
if (isset($_SESSION['last_activity']) && (time() - $_SESSION['last_activity'] > 1800)) {
    header("Location: logout.php");
    exit();
}
$_SESSION['last_activity'] = time();

require_once 'db_connection.php';

$pageTitle = "Dashboard";

// Helper function to safely format currency
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 {
    // Get total sales value - use override amount if set
    $result = executeQuery($conn, "SELECT SUM(COALESCE(NULLIF(final_amount_override, 0), 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 and breakdown by fish type
    $result = executeQuery($conn, "SELECT 
        SUM(weight_tilapia + weight_small_fish + weight_big_fish) as total,
        SUM(weight_tilapia) as total_tilapia,
        SUM(weight_small_fish) as total_small_fish,
        SUM(weight_big_fish) as total_big_fish
        FROM sales");
    $weightData = $result->fetch_assoc();
    $totalWeightHarvested = $weightData['total'] ?? 0;
    $totalTilapia = $weightData['total_tilapia'] ?? 0;
    $totalSmallFish = $weightData['total_small_fish'] ?? 0;
    $totalBigFish = $weightData['total_big_fish'] ?? 0;

    // Get average rate per kg - use override amount if set
    $result = executeQuery($conn, "SELECT SUM(COALESCE(NULLIF(final_amount_override, 0), 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;

    // Get initial values
    $result = executeQuery($conn, "SELECT * FROM initial_values LIMIT 1");
    $initialValues = $result->fetch_assoc();
    $initialCash = $initialValues['cash_in_hand'] ?? 0;

    // Calculate cash in hand
    $cashInHand = $initialCash + $totalPayments - $totalExpenses;

    // Format values for display
    $formattedTotalSales = formatCurrency($totalSales);
    $formattedTotalExpenses = formatCurrency($totalExpenses);
    $formattedTotalPayments = formatCurrency($totalPayments);
    $formattedCashInHand = formatCurrency($cashInHand);
    $formattedTotalWeightHarvested = number_format($totalWeightHarvested, 2);
    $formattedTotalTilapia = number_format($totalTilapia, 2);
    $formattedTotalSmallFish = number_format($totalSmallFish, 2);
    $formattedTotalBigFish = number_format($totalBigFish, 2);
    $formattedAverageRatePerKg = formatCurrency($averageRatePerKg);

    // Generate Fortnightly Harvest Charges rows - Starting from Oct 1, 2025
    $fortnightlyRows = '';
    $query = "SELECT 
                DATE_FORMAT(date, '%Y-%m') as month_year,
                YEAR(date) as year,
                MONTH(date) as month,
                CASE 
                    WHEN DAY(date) <= 15 THEN 1
                    ELSE 2
                END as half_number,
                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 >= '2025-10-01'
              GROUP BY 
                YEAR(date),
                MONTH(date),
                CASE 
                    WHEN DAY(date) <= 15 THEN 1
                    ELSE 2
                END
              ORDER BY YEAR(date) DESC, MONTH(date) DESC, half_number DESC";
    
    $result = executeQuery($conn, $query);
    while ($row = $result->fetch_assoc()) {
        $startDate = $row['start_date'];
        $endDate = $row['end_date'];
        $year = $row['year'];
        $month = $row['month'];
        $halfNumber = $row['half_number'];
        
        // Format the fortnight name
        if ($halfNumber == 1) {
            // First half: 1st to 15th
            $fortnightStart = date('M d', strtotime("$year-$month-01"));
            $fortnightEnd = date('M d', strtotime("$year-$month-15"));
        } else {
            // Second half: 16th to end of month
            $fortnightStart = date('M d', strtotime("$year-$month-16"));
            $lastDay = date('t', strtotime("$year-$month-01"));
            $fortnightEnd = date('M d', strtotime("$year-$month-$lastDay"));
        }
        
        $fortnightName = "$fortnightStart - $fortnightEnd";
        
        $totalWeight = $row['total_tilapia'] + $row['total_small_fish'] + $row['total_big_fish'];
        
        $fortnightlyRows .= "<tr>
            <td data-label='Fortnight'>" . htmlspecialchars($fortnightName) . "</td>
            <td data-label='Tilapia (kg)'>" . number_format($row['total_tilapia'], 2) . "</td>
            <td data-label='Small Fish (kg)'>" . number_format($row['total_small_fish'], 2) . "</td>
            <td data-label='Big Fish (kg)'>" . number_format($row['total_big_fish'], 2) . "</td>
            <td data-label='Total Weight (kg)'>" . number_format($totalWeight, 2) . "</td>
            <td data-label='Harvest Charges'>" . formatCurrency($row['total_charges']) . "</td>
        </tr>";
    }

    // Generate Buyer Data rows - use override amount if set
    $buyerRows = '';
    $madhuBalance = $initialValues['madhu_balance'] ?? 0;
    $rathnaBalance = $initialValues['rathna_balance'] ?? 0;
    $jambukkuttiBalance = $initialValues['jambukkutti_balance'] ?? 0;
    
    $query = "SELECT 
                b.name as buyer_name, 
                COALESCE(SUM(COALESCE(NULLIF(s.final_amount_override, 0), 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(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $madhuBalance
                    WHEN b.name = 'Rathna' THEN COALESCE(SUM(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $rathnaBalance
                    WHEN b.name = 'Jambukkutti' THEN COALESCE(SUM(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $jambukkuttiBalance
                    ELSE COALESCE(SUM(COALESCE(NULLIF(s.final_amount_override, 0), 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()) {
        $buyerRows .= "<tr>
            <td data-label='Buyer'>" . htmlspecialchars($row['buyer_name']) . "</td>
            <td data-label='Total Sales'>" . formatCurrency($row['total_sales']) . "</td>
            <td data-label='Total Payments'>" . formatCurrency($row['total_payments']) . "</td>
            <td data-label='Balance'>" . formatCurrency($row['balance']) . "</td>
        </tr>";
    }

    // Get monthly data for the table - use override amount if set
    $query = "SELECT 
                DATE_FORMAT(s.date, '%Y-%m') as month,
                SUM(COALESCE(NULLIF(s.final_amount_override, 0), 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(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)) / NULLIF(SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish), 0) as rate_per_kg
              FROM sales s
              GROUP BY DATE_FORMAT(s.date, '%Y-%m')
              ORDER BY s.date DESC";
    $monthlyData = executeQuery($conn, $query);

    // Generate monthly report rows
    $monthlyRows = '';
    while ($row = $monthlyData->fetch_assoc()) {
        $monthlyRows .= "<tr>
            <td data-label='Month'>" . date('M Y', strtotime($row['month'] . '-01')) . "</td>
            <td data-label='Sales'>" . formatCurrency($row['sales_value']) . "</td>
            <td data-label='Expenses'>" . formatCurrency($row['expense_value']) . "</td>
            <td data-label='Payments'>" . formatCurrency($row['payments_value']) . "</td>
            <td data-label='Weight'>" . number_format($row['harvested_weight'], 2) . " kg</td>
            <td data-label='Rate/Kg'>" . formatCurrency($row['rate_per_kg']) . "</td>
        </tr>";
    }

    $content = <<<HTML
<div class="stats-grid">
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-shopping-cart stat-icon"></i>
            <span>Total Sales Value</span>
        </div>
        <div class="stat-value">$formattedTotalSales</div>
    </div>
    
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-receipt stat-icon"></i>
            <span>Total Expenses</span>
        </div>
        <div class="stat-value">$formattedTotalExpenses</div>
    </div>
    
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-money-bill-wave stat-icon"></i>
            <span>Total Payments Received</span>
        </div>
        <div class="stat-value">$formattedTotalPayments</div>
    </div>
    
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-wallet stat-icon"></i>
            <span>Cash in Hand</span>
        </div>
        <div class="stat-value">$formattedCashInHand</div>
    </div>
    
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-weight stat-icon"></i>
            <span>Total Weight Harvested</span>
        </div>
        <div class="stat-value">$formattedTotalWeightHarvested kg</div>
        <div style="margin-top: 0.75rem; padding-top: 0.75rem; border-top: 1px solid var(--border-color); font-size: 0.85rem; color: var(--text-secondary);">
            <div style="display: flex; justify-content: space-between; margin-bottom: 0.25rem;">
                <span>🐟 Tilapia:</span>
                <strong>$formattedTotalTilapia kg</strong>
            </div>
            <div style="display: flex; justify-content: space-between; margin-bottom: 0.25rem;">
                <span>🐠 Small Fish:</span>
                <strong>$formattedTotalSmallFish kg</strong>
            </div>
            <div style="display: flex; justify-content: space-between;">
                <span>🐡 Big Fish:</span>
                <strong>$formattedTotalBigFish kg</strong>
            </div>
        </div>
    </div>
    
    <div class="stat-card">
        <div class="stat-header">
            <i class="fas fa-calculator stat-icon"></i>
            <span>Average Rate per Kg</span>
        </div>
        <div class="stat-value">$formattedAverageRatePerKg</div>
    </div>
</div>

<div class="card">
    <div class="card-header">
        <h2><i class="fas fa-chart-line"></i> Fortnightly Harvest Charges (from Oct 1, 2025)</h2>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>Fortnight</th>
                        <th>Tilapia (kg)</th>
                        <th>Small Fish (kg)</th>
                        <th>Big Fish (kg)</th>
                        <th>Total Weight (kg)</th>
                        <th>Harvest Charges</th>
                    </tr>
                </thead>
                <tbody>
                    $fortnightlyRows
                </tbody>
            </table>
        </div>
    </div>
</div>

<div class="card">
    <div class="card-header">
        <h2><i class="fas fa-users"></i> Buyer Data</h2>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>Buyer</th>
                        <th>Total Sales</th>
                        <th>Total Payments</th>
                        <th>Balance</th>
                    </tr>
                </thead>
                <tbody>
                    $buyerRows
                </tbody>
            </table>
        </div>
    </div>
</div>

<div class="card">
    <div class="card-header">
        <h2><i class="fas fa-chart-bar"></i> Monthly Report</h2>
        <a href="export_dashboard.php" class="btn btn-export btn-sm">
            <i class="fas fa-file-csv"></i> Export CSV
        </a>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>Month</th>
                        <th>Sales Value</th>
                        <th>Expenses</th>
                        <th>Payments</th>
                        <th>Harvested Weight</th>
                        <th>Rate/Kg</th>
                    </tr>
                </thead>
                <tbody>
                    $monthlyRows
                </tbody>
            </table>
        </div>
    </div>
</div>
HTML;

} catch (Exception $e) {
    $content = "<div class='alert alert-error'><i class='fas fa-exclamation-circle'></i> An error occurred while loading dashboard data.</div>";
    error_log("Dashboard error: " . $e->getMessage());
}

include 'main_layout.php';
$conn->close();
?>