<?php
session_start();

// Redirect to login page if the user is not an admin
if (!isset($_SESSION['username']) || $_SESSION['role'] != 'admin') {
    header("Location: login.php");
    exit();
}
// Include database connection
include '../config/db.php';
$results = [];
$payment_totals = []; // Array to store total per payment method
$usernames = []; // Array to store usernames for the dropdown

// Fetch pharmacy name from the settings table
$query = "SELECT pharmacy_name FROM settings LIMIT 1"; // Assuming only one entry
$stmt = $pdo->prepare($query);
$stmt->execute();
$setting = $stmt->fetch(PDO::FETCH_ASSOC);
$pharmacy_name = $setting['pharmacy_name'] ?? 'Default Pharmacy Name'; // Fallback to a default if not found


// Fetch all usernames for the filter dropdown
try {
    $stmt = $pdo->query("SELECT DISTINCT username FROM sales ORDER BY username ASC");
    $usernames = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Error fetching usernames: " . $e->getMessage());
}

// Assuming the dates are passed as GET parameters
$startDate = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$endDate = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$username_filter = isset($_GET['username']) ? $_GET['username'] : '';

// Initialize the parameters
$params = [];

$query = "SELECT receipt, SUM(total) AS total, SUM(profit) AS profit, 
          SUM(discount) AS discount, username, payment_method1, sale_date, 
          SUM(payment_amount1) AS payment_amount1, payment_method2, 
          SUM(payment_amount2) AS payment_amount2
          FROM sales WHERE 1=1"; // Base query

// Add filter condition (date range)
if ($startDate && $endDate) {
    $endDate = date('Y-m-d H:i:s', strtotime($endDate . ' 23:59:59')); // Ensure full day
    $query .= " AND sale_date BETWEEN :start AND :end";
    $params['start'] = $startDate;
    $params['end'] = $endDate;
}

// Apply username filter if provided
if (!empty($username_filter)) {
    $query .= " AND username = :username";
    $params['username'] = $username_filter;
}

// Group by receipt and order by sale date
$query .= " GROUP BY receipt ORDER BY sale_date DESC";

// Prepare and execute the query
$stmt = $pdo->prepare($query);
$stmt->execute($params);

// Fetch all results
$sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Initialize total amounts for payment methods
$payment_totals = [];
$total_income = 0;
$total_profit = 0;
foreach ($sales as $row) {
    if (!empty($row['payment_method1'])) {
        if (!isset($payment_totals[$row['payment_method1']])) {
            $payment_totals[$row['payment_method1']] = 0;
        }
        $payment_totals[$row['payment_method1']] += $row['payment_amount1'];
    }
    if (!empty($row['payment_method2'])) {
        if (!isset($payment_totals[$row['payment_method2']])) {
            $payment_totals[$row['payment_method2']] = 0;
        }
        $payment_totals[$row['payment_method2']] += $row['payment_amount2'];
    }

    $total_income += $row['total'];
    $total_profit += $row['profit'];
}

// Fetch payment methods from the database
try {
    $stmt = $pdo->query("SELECT id, method_name FROM payment_methods ORDER BY method_name ASC");
    $payment_methods = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Error fetching payment methods: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Finance Report</title>
    
    <!-- Consolidated CSS -->
    <link rel="stylesheet" href="..\libs\bootsrtap2\css\bootstrap.min.css">
    <link rel="stylesheet" href="..\libs\fontawesome\css\all.min.css">
    
    <style>
        body {
            background-color: #f5f6fa;
            font-family: 'Mulish', sans-serif;
            padding-top: 70px; /* Add padding for sticky header */
        }

        .top-bar {
            background-color: #ffffff;
            padding: 15px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            position: fixed;
            top: 0;
            left: 0;
            right: 0;
            z-index: 1030;
        }

        .page-content {
            padding: 20px;
        }

        .filter-form {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            margin-bottom: 20px;
            transition: all 0.3s ease;
        }

        .filter-form:hover {
            box-shadow: 0 4px 15px rgba(0,0,0,0.15);
        }

        .stats-cards {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(280px, 1fr));
            gap: 20px;
            margin-bottom: 20px;
        }

        .stats-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            transition: transform 0.3s ease;
            border-left: 4px solid #3498db;
        }

        .stats-card:hover {
            transform: translateY(-5px);
        }

        .stats-card h5 {
            color: #7f8c8d;
            font-size: 0.9rem;
            text-transform: uppercase;
            letter-spacing: 0.5px;
            margin-bottom: 10px;
        }

        .stats-card h3 {
            font-size: 1.8rem;
            margin-bottom: 0;
        }

        .table-container {
            background: white;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            overflow: hidden;
            margin-top: 20px;
        }

        .table thead th {
            background: #2c3e50;
            color: white;
            font-weight: 500;
            border: none;
            padding: 12px 15px;
        }

        .table tbody td {
            padding: 12px 15px;
            vertical-align: middle;
        }

        .table tbody tr:hover {
            background-color: #f8f9fa;
        }

        .btn-primary {
            background-color: #3498db;
            border-color: #3498db;
        }

        .btn-primary:hover {
            background-color: #2980b9;
            border-color: #2980b9;
        }

        @media (max-width: 768px) {
            .stats-cards {
                grid-template-columns: 1fr;
            }
            
            .table-responsive {
                border-radius: 10px;
            }
        }

        /* Different colors for different payment method cards */
        .stats-card:nth-child(1) { border-left-color: #3498db; }
        .stats-card:nth-child(2) { border-left-color: #2ecc71; }
        .stats-card:nth-child(3) { border-left-color: #e74c3c; }
        .stats-card:nth-child(4) { border-left-color: #f1c40f; }
        .stats-card:nth-child(5) { border-left-color: #9b59b6; }
    </style>
</head>
<body>
    <!-- Sticky Top Bar -->
    <div class="top-bar">
        <div class="d-flex justify-content-between align-items-center">
            <div class="d-flex align-items-center gap-3">
                <a href="admin_dashboard.php" class="btn btn-outline-primary">
                    <i class="fas fa-arrow-left"></i> Dashboard
                </a>
                <h4 class="mb-0">Finance Report</h4>
            </div>
            <div class="dropdown">
                <button class="btn btn-outline-secondary dropdown-toggle" type="button" id="userMenu" data-bs-toggle="dropdown">
                    <i class="fas fa-user"></i> <?= htmlspecialchars($_SESSION['username']) ?>
                </button>
                <ul class="dropdown-menu dropdown-menu-end">
                    <li><a class="dropdown-item" href="settings.php"><i class="fas fa-cog me-2"></i>Settings</a></li>
                    <li><hr class="dropdown-divider"></li>
                    <li><a class="dropdown-item text-danger" href="logout.php"><i class="fas fa-sign-out-alt me-2"></i>Logout</a></li>
                </ul>
            </div>
        </div>
    </div>

    <!-- Page Content -->
    <div class="page-content">
        <!-- Filter Form -->
        <div class="filter-form">
            <form method="GET" action="" class="row g-3">
                <div class="col-md-3">
                    <label class="form-label">Start Date</label>
                    <input type="date" name="start_date" class="form-control" value="<?= $startDate ?>">
                </div>
                <div class="col-md-3">
                    <label class="form-label">End Date</label>
                    <input type="date" name="end_date" class="form-control" value="<?= $endDate ?>">
                </div>
                <div class="col-md-3">
                    <label class="form-label">Username</label>
                    <select name="username" class="form-select">
                        <option value="">All Users</option>
                        <?php foreach ($usernames as $username): ?>
                            <option value="<?= $username['username'] ?>" <?= $username_filter == $username['username'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($username['username']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-3 d-flex align-items-end">
                    <button type="submit" class="btn btn-primary me-2">Apply Filter</button>
                    <a href="finance_report.php" class="btn btn-secondary">Clear</a>
                </div>
            </form>
        </div>

        <!-- Stats Cards -->
        <div class="stats-cards">
            <div class="stats-card">
                <h5>Total Income</h5>
                <h3 class="text-primary">₦<?= number_format($total_income, 2) ?></h3>
            </div>
            <div class="stats-card">
                <h5>Total Profit</h5>
                <h3 class="text-success">₦<?= number_format($total_profit, 2) ?></h3>
            </div>
        </div>

        <!-- Payment Methods Cards -->
        <div class="stats-cards">
            <?php foreach ($payment_methods as $method): ?>
                <div class="stats-card">
                    <h5><?= htmlspecialchars($method['method_name']) ?></h5>
                    <h3 class="text-primary">₦<?= number_format($payment_totals[$method['method_name']] ?? 0, 2) ?></h3>
                </div>
            <?php endforeach; ?>
        </div>

        <!-- Sales Table -->
        <div class="table-container">
            <table class="table table-hover">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Receipt</th>
                        <th>Username</th>
                        <th>Profit</th>
                        <th>Payment 1</th>
                        <th>Amount</th>
                        <th>Payment 2</th>
                        <th>Amount</th>
                        <th>Discount</th>
                        <th>Total</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!empty($sales)): ?>
                        <?php foreach ($sales as $row): ?>
                            <tr>
                                <td><?= htmlspecialchars($row['sale_date']) ?></td>
                                <td><?= htmlspecialchars($row['receipt']) ?></td>
                                <td><?= htmlspecialchars($row['username']) ?></td>
                                <td>₦<?= number_format($row['profit'], 2) ?></td>
                                <td><?= htmlspecialchars($row['payment_method1']) ?></td>
                                <td>₦<?= number_format($row['payment_amount1'], 2) ?></td>
                                <td><?= htmlspecialchars($row['payment_method2']) ?></td>
                                <td>₦<?= number_format($row['payment_amount2'], 2) ?></td>
                                <td>₦<?= number_format($row['discount'], 2) ?></td>
                                <td>₦<?= number_format($row['total'], 2) ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="10" class="text-center">No records found</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <script src="..\libs\jquery\jquery-3.7.1.min.js"></script>
    <script src="..\libs\bootsrtap2\js\bootstrap.bundle.min.js"></script>

    <!-- Add JavaScript for sticky behavior -->
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            const topBar = document.querySelector('.top-bar');
            const pageContent = document.querySelector('.page-content');
            
            function updateTopBar() {
                if (window.scrollY > 0) {
                    topBar.style.boxShadow = '0 2px 10px rgba(0,0,0,0.1)';
                } else {
                    topBar.style.boxShadow = 'none';
                }
            }

            window.addEventListener('scroll', updateTopBar);
        });
    </script>
</body>
</html>
