<?php
/**
 * User Report - Reports for all users based on their role
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/settings.php';
require_once __DIR__ . '/../includes/functions.php';

startSession();
requireLogin();

$user_id = $_SESSION['user_id'];
$role = $_SESSION['role'];

$from_date = isset($_POST['from_date']) ? $_POST['from_date'] : date('Y-m-01'); // First day of current month
$to_date = isset($_POST['to_date']) ? $_POST['to_date'] : date('Y-m-d'); // Today

try {
    $conn = getDbConnection();
    
    // Role-specific reports
    if ($role === 'supplier') {
        // SUPPLIER REPORT - Fixed calculation using subqueries
        
        // Get total supply for this supplier in date range
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total_supply,
                       COUNT(*) as supply_count
                FROM supply 
                WHERE supplier_id = ? 
                AND supply_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iss", $user_id, $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $supply_data = $result->fetch_assoc();
        
        // Get total payments received in date range
        $sql = "SELECT COALESCE(SUM(amount), 0) as total_paid,
                       COUNT(*) as payment_count
                FROM payment_settlements 
                WHERE supplier_id = ? 
                AND payment_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iss", $user_id, $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $payment_data = $result->fetch_assoc();
        
        // Get OVERALL pending (not just date range) - FIXED with subqueries
        $sql = "SELECT 
                    COALESCE((SELECT SUM(total_amount) FROM supply WHERE supplier_id = ?), 0) as total_supply_all,
                    COALESCE((SELECT SUM(amount) FROM payment_settlements WHERE supplier_id = ?), 0) as total_paid_all";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ii", $user_id, $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $overall_data = $result->fetch_assoc();
        
        $overall_pending = $overall_data['total_supply_all'] - $overall_data['total_paid_all'];
        
        // Get recent supplies in date range
        $sql = "SELECT s.*, f.fish_name, f.fish_name_tamil
                FROM supply s
                LEFT JOIN fish_types f ON s.fish_id = f.fish_id
                WHERE s.supplier_id = ? 
                AND s.supply_date BETWEEN ? AND ?
                ORDER BY s.supply_date DESC
                LIMIT 10";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iss", $user_id, $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $recent_supplies = [];
        while ($row = $result->fetch_assoc()) {
            $recent_supplies[] = $row;
        }
        
        // Get recent payments in date range
        $sql = "SELECT ps.*, u.full_name as settled_by_name
                FROM payment_settlements ps
                LEFT JOIN users u ON ps.settled_by = u.user_id
                WHERE ps.supplier_id = ? 
                AND ps.payment_date BETWEEN ? AND ?
                ORDER BY ps.payment_date DESC
                LIMIT 10";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iss", $user_id, $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $recent_payments = [];
        while ($row = $result->fetch_assoc()) {
            $recent_payments[] = $row;
        }
        
    } elseif ($role === 'manager' || $role === 'admin') {
        // MANAGER/ADMIN REPORT
        
        // Get sales summary
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total_sales,
                       COUNT(*) as sale_count
                FROM sales 
                WHERE sale_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $sales_data = $result->fetch_assoc();
        
        // Get supply summary
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total_supply,
                       COUNT(*) as supply_count
                FROM supply 
                WHERE supply_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $supply_summary = $result->fetch_assoc();
        
        // Get expense summary
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total_expenses,
                       COUNT(*) as expense_count
                FROM expenses 
                WHERE expense_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $expense_data = $result->fetch_assoc();
        
        // Get payment summary
        $sql = "SELECT COALESCE(SUM(amount), 0) as total_payments,
                       COUNT(*) as payment_count
                FROM payment_settlements 
                WHERE payment_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $from_date, $to_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $payment_summary = $result->fetch_assoc();
        
        // Calculate profit
        $profit = $sales_data['total_sales'] - $supply_summary['total_supply'] - $expense_data['total_expenses'];
    }
    
} catch (Exception $e) {
    $error = "Error generating report: " . $e->getMessage();
}

$page_title = 'Reports';
require_once __DIR__ . '/../includes/header.php';
?>

<h1 class="page-title">📊 Reports</h1>

<?php if (isset($error)): ?>
<div class="alert alert-error"><?php echo htmlspecialchars($error); ?></div>
<?php endif; ?>

<!-- Date Range Selector -->
<div class="card">
    <h2 class="card-title">Select Date Range</h2>
    <form method="POST" action="">
        <div style="display: grid; grid-template-columns: 1fr 1fr auto; gap: 1rem; align-items: end;">
            <div class="form-group">
                <label for="from_date">From Date</label>
                <input type="date" id="from_date" name="from_date" class="form-control" 
                       value="<?php echo htmlspecialchars($from_date); ?>" required>
            </div>
            
            <div class="form-group">
                <label for="to_date">To Date</label>
                <input type="date" id="to_date" name="to_date" class="form-control" 
                       value="<?php echo htmlspecialchars($to_date); ?>" required>
            </div>
            
            <button type="submit" class="btn btn-primary">Generate Report</button>
        </div>
    </form>
</div>

<?php if ($role === 'supplier'): ?>
<!-- SUPPLIER REPORT -->

<!-- Overall Pending Payment (Not Date-Restricted) -->
<div class="card" style="background: linear-gradient(135deg, #dc3545, #c82333); color: white;">
    <h2 style="color: white; margin-top: 0;">Your Pending Payment</h2>
    <div style="font-size: 3rem; font-weight: 700; margin: 1rem 0;">
        ₹<?php echo number_format($overall_pending, 2); ?>
    </div>
    <p style="opacity: 0.9; margin: 0;">This is the total amount pending to be paid to you.</p>
</div>

<!-- Period Statistics -->
<div class="stats-grid">
    <div class="stat-card" style="background: linear-gradient(135deg, #17a2b8, #138496);">
        <div class="stat-label">Total Supply (Period)</div>
        <div class="stat-value">₹<?php echo number_format($supply_data['total_supply'], 2); ?></div>
        <div class="stat-info"><?php echo $supply_data['supply_count']; ?> entries</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, #28a745, #218838);">
        <div class="stat-label">Payments Received (Period)</div>
        <div class="stat-value">₹<?php echo number_format($payment_data['total_paid'], 2); ?></div>
        <div class="stat-info"><?php echo $payment_data['payment_count']; ?> payments</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, #ffc107, #e0a800);">
        <div class="stat-label">Overall Pending</div>
        <div class="stat-value">₹<?php echo number_format($overall_pending, 2); ?></div>
        <div class="stat-info">All time</div>
    </div>
</div>

<!-- Recent Supplies -->
<?php if (!empty($recent_supplies)): ?>
<div class="card">
    <h2 class="card-title">Recent Supplies (<?php echo count($recent_supplies); ?>)</h2>
    <div class="table-responsive">
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Fish Type</th>
                    <th class="text-right">Weight (kg)</th>
                    <th class="text-right">Rate/kg</th>
                    <th class="text-right">Amount</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($recent_supplies as $supply): ?>
                <tr>
                    <td><?php echo date('d-m-Y', strtotime($supply['supply_date'])); ?></td>
                    <td>
                        <strong><?php echo htmlspecialchars($supply['fish_name']); ?></strong>
                        <?php if ($supply['fish_name_tamil']): ?>
                            <br><small style="color: #666;"><?php echo htmlspecialchars($supply['fish_name_tamil']); ?></small>
                        <?php endif; ?>
                    </td>
                    <td class="text-right"><?php echo number_format($supply['weight_kg'], 2); ?></td>
                    <td class="text-right">₹<?php echo number_format($supply['rate_per_kg'], 2); ?></td>
                    <td class="text-right"><strong>₹<?php echo number_format($supply['total_amount'], 2); ?></strong></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>
<?php endif; ?>

<!-- Recent Payments -->
<?php if (!empty($recent_payments)): ?>
<div class="card">
    <h2 class="card-title">Recent Payments (<?php echo count($recent_payments); ?>)</h2>
    <div class="table-responsive">
        <table>
            <thead>
                <tr>
                    <th>Payment Date</th>
                    <th class="text-right">Amount</th>
                    <th>Payment Mode</th>
                    <th>Settled By</th>
                    <th>Notes</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($recent_payments as $payment): ?>
                <tr>
                    <td><?php echo date('d-m-Y', strtotime($payment['payment_date'])); ?></td>
                    <td class="text-right" style="color: #28a745;">
                        <strong>₹<?php echo number_format($payment['amount'], 2); ?></strong>
                    </td>
                    <td>
                        <span class="badge badge-<?php echo $payment['payment_mode'] === 'cash' ? 'success' : 'info'; ?>">
                            <?php echo strtoupper($payment['payment_mode']); ?>
                        </span>
                    </td>
                    <td><?php echo htmlspecialchars($payment['settled_by_name'] ?? 'Unknown'); ?></td>
                    <td><?php echo $payment['notes'] ? htmlspecialchars($payment['notes']) : '-'; ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>
<?php endif; ?>

<?php elseif ($role === 'manager' || $role === 'admin'): ?>
<!-- MANAGER/ADMIN REPORT -->

<!-- Summary Statistics -->
<div class="stats-grid">
    <div class="stat-card" style="background: linear-gradient(135deg, #28a745, #218838);">
        <div class="stat-label">Total Sales</div>
        <div class="stat-value">₹<?php echo number_format($sales_data['total_sales'], 2); ?></div>
        <div class="stat-info"><?php echo $sales_data['sale_count']; ?> transactions</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, #17a2b8, #138496);">
        <div class="stat-label">Total Supply Cost</div>
        <div class="stat-value">₹<?php echo number_format($supply_summary['total_supply'], 2); ?></div>
        <div class="stat-info"><?php echo $supply_summary['supply_count']; ?> supplies</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, #dc3545, #c82333);">
        <div class="stat-label">Total Expenses</div>
        <div class="stat-value">₹<?php echo number_format($expense_data['total_expenses'], 2); ?></div>
        <div class="stat-info"><?php echo $expense_data['expense_count']; ?> expenses</div>
    </div>
    
    <div class="stat-card" style="background: linear-gradient(135deg, <?php echo $profit >= 0 ? '#6f42c1, #5a32a3' : '#dc3545, #c82333'; ?>);">
        <div class="stat-label"><?php echo $profit >= 0 ? 'Profit' : 'Loss'; ?></div>
        <div class="stat-value">₹<?php echo number_format(abs($profit), 2); ?></div>
        <div class="stat-info">Sales - Supply - Expenses</div>
    </div>
</div>

<!-- Payments Made -->
<div class="card">
    <h2 class="card-title">Supplier Payments Made</h2>
    <div class="stats-grid" style="grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));">
        <div class="stat-card" style="background: linear-gradient(135deg, #ffc107, #e0a800);">
            <div class="stat-label">Total Paid</div>
            <div class="stat-value">₹<?php echo number_format($payment_summary['total_payments'], 2); ?></div>
            <div class="stat-info"><?php echo $payment_summary['payment_count']; ?> payments</div>
        </div>
    </div>
</div>

<!-- Detailed Links -->
<div class="card">
    <h2 class="card-title">Detailed Reports</h2>
    <div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem;">
        <a href="/sales/view_sales.php" class="btn btn-success">📊 View All Sales</a>
        <a href="/supply/view_supplies.php" class="btn btn-info">📦 View All Supplies</a>
        <a href="/expenses/view_expenses.php" class="btn btn-warning">💸 View All Expenses</a>
        <a href="/supply/pending_payments.php" class="btn btn-danger">💰 Pending Payments</a>
    </div>
</div>

<?php endif; ?>

<!-- Print Button -->
<div style="text-align: center; margin-top: 2rem;">
    <button onclick="window.print()" class="btn btn-primary">
        🖨️ Print Report
    </button>
</div>

<?php require_once __DIR__ . '/../includes/footer.php'; ?>