<?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 the database configuration file
include('../config/db.php');

// Initialize variables for date filtering
$report_type = isset($_POST['report_type']) ? $_POST['report_type'] : 'daily';

// Prepare the SQL query for fetching sales data based on report type
switch ($report_type) {
    case 'daily':
        $sales_query = "SELECT DATE(sale_date) AS sale_date, SUM(total) AS total_sales, SUM(profit) AS total_profit, SUM(discount) AS total_discount 
                        FROM sales 
                        GROUP BY DATE(sale_date) 
                        ORDER BY DATE(sale_date) DESC"; // Latest first
        $expenses_query = "SELECT DATE(expense_date) AS expense_date, SUM(amount) AS total_expenses 
                           FROM expenses 
                           GROUP BY DATE(expense_date) 
                           ORDER BY DATE(expense_date) DESC"; // Latest first
        $owings_query = "SELECT DATE(owing_date) AS owing_date, SUM(amount) AS total_owings 
                         FROM owings 
                         GROUP BY DATE(owing_date) 
                         ORDER BY DATE(owing_date) DESC"; // Latest first
        break;
    case 'weekly':
        $sales_query = "SELECT DATE(sale_date) AS sale_date, SUM(total) AS total_sales, SUM(profit) AS total_profit, SUM(discount) AS total_discount 
                        FROM sales 
                        GROUP BY YEARWEEK(sale_date) 
                        ORDER BY YEARWEEK(sale_date) DESC"; // Latest first
        $expenses_query = "SELECT DATE(expense_date) AS expense_date, SUM(amount) AS total_expenses 
                           FROM expenses 
                           GROUP BY YEARWEEK(expense_date) 
                           ORDER BY YEARWEEK(expense_date) DESC"; // Latest first
        $owings_query = "SELECT DATE(owing_date) AS owing_date, SUM(amount) AS total_owings 
                         FROM owings 
                         GROUP BY YEARWEEK(owing_date) 
                         ORDER BY YEARWEEK(owing_date) DESC"; // Latest first
        break;
    case 'monthly':
        $sales_query = "SELECT DATE_FORMAT(sale_date, '%Y-%m') AS sale_date, SUM(total) AS total_sales, SUM(profit) AS total_profit, SUM(discount) AS total_discount 
                        FROM sales 
                        GROUP BY DATE_FORMAT(sale_date, '%Y-%m') 
                        ORDER BY DATE_FORMAT(sale_date, '%Y-%m') DESC"; // Latest first
        $expenses_query = "SELECT DATE_FORMAT(expense_date, '%Y-%m') AS expense_date, SUM(amount) AS total_expenses 
                           FROM expenses 
                           GROUP BY DATE_FORMAT(expense_date, '%Y-%m') 
                           ORDER BY DATE_FORMAT(expense_date, '%Y-%m') DESC"; // Latest first
        $owings_query = "SELECT DATE_FORMAT(owing_date, '%Y-%m') AS owing_date, SUM(amount) AS total_owings 
                         FROM owings 
                         GROUP BY DATE_FORMAT(owing_date, '%Y-%m') 
                         ORDER BY DATE_FORMAT(owing_date, '%Y-%m') DESC"; // Latest first
        break;
    case 'yearly':
        $sales_query = "SELECT YEAR(sale_date) AS sale_date, SUM(total) AS total_sales, SUM(profit) AS total_profit, SUM(discount) AS total_discount 
                        FROM sales 
                        GROUP BY YEAR(sale_date) 
                        ORDER BY YEAR(sale_date) DESC"; // Latest first
        $expenses_query = "SELECT YEAR(expense_date) AS expense_date, SUM(amount) AS total_expenses 
                           FROM expenses 
                           GROUP BY YEAR(expense_date) 
                           ORDER BY YEAR(expense_date) DESC"; // Latest first
        $owings_query = "SELECT YEAR(owing_date) AS owing_date, SUM(amount) AS total_owings 
                         FROM owings 
                         GROUP BY YEAR(owing_date) 
                         ORDER BY YEAR(owing_date) DESC"; // Latest first
        break;
}

// Fetch sales data
$sales_stmt = $pdo->query($sales_query);
$sales_data = $sales_stmt->fetchAll(PDO::FETCH_ASSOC);

// 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 expenses data
$expenses_stmt = $pdo->query($expenses_query);
$expenses_data = $expenses_stmt->fetchAll(PDO::FETCH_ASSOC);

// Modify this part of the code where you fetch total owings
$owings_query = $pdo->query("SELECT DATE(created_at) AS owing_date, SUM(amount) AS total_owings FROM owings WHERE status = 'owing' GROUP BY DATE(created_at)");
$owings_data = $owings_query->fetchAll(PDO::FETCH_ASSOC);


// Initialize totals for sales, profit, discounts, expenses, and owings
$total_sales = $total_profit = $total_discount = 0;
$total_expenses = $total_owings = 0;

foreach ($sales_data as $data) {
    $total_sales += $data['total_sales'];
    $total_profit += $data['total_profit'];
    $total_discount += $data['total_discount'];
}

foreach ($expenses_data as $data) {
    $total_expenses += $data['total_expenses'];
}

foreach ($owings_data as $data) {
    $total_owings += $data['total_owings'];
}

// Modify the most sold query
$most_sold_query = "SELECT drug_name AS product_name, SUM(quantity) AS total_quantity 
                    FROM sales 
                    GROUP BY drug_name 
                    ORDER BY total_quantity DESC 
                    LIMIT 1";
$most_sold_stmt = $pdo->query($most_sold_query);
$most_sold = $most_sold_stmt->fetch(PDO::FETCH_ASSOC);

// Function to format currency
function format_currency($amount) {
    return '₦' . number_format($amount, 2);
}

// Function to format date with day
function format_date($date) {
    return date('l, F j, Y', strtotime($date)); // Includes day of the week
}

// Include the TCPDF library
require_once ('../libs/tcpdf/tcpdf.php');

function export_to_pdf($sales_data, $expenses_data, $owings_data, $total_sales, $total_profit, $total_discount, $total_expenses, $total_owings) {
    // Create new PDF document
    $pdf = new TCPDF();
    
    // Set the font to DejaVu Sans with a smaller size
    $pdf->SetFont('dejavusans', '', 10);  // Reduce font size to 10
    $pdf->SetMargins(10, 10, 10);  // Set the page margins

    // Add a page
    $pdf->AddPage();
    

    // Title of the Report
    $pdf->SetFont('dejavusans', 'B', 14);
    $pdf->Cell(0, 10, 'Pharmacy Financial Report', 0, 1, 'C');
    $pdf->Ln(5); // Line break

    // Sales section
    $pdf->SetFont('dejavusans', 'B', 12);
    $pdf->Cell(0, 10, 'Sales Summary', 0, 1, 'L');
    $pdf->SetFont('dejavusans', '', 10);

    // Table headers without borders
    $pdf->Cell(40, 10, 'Date', 0, 0, 'C');
    $pdf->Cell(45, 10, 'Total Sales', 0, 0, 'C');
    $pdf->Cell(45, 10, 'Total Profit', 0, 0, 'C');
    $pdf->Cell(45, 10, 'Total Discount', 0, 1, 'C'); // New line after headers

    // Adding sales data without borders
    foreach ($sales_data as $data) {
        $pdf->Cell(40, 10, format_date($data['sale_date']), 0, 0, 'C');
        $pdf->Cell(45, 10, format_currency($data['total_sales']), 0, 0, 'C');
        $pdf->Cell(45, 10, format_currency($data['total_profit']), 0, 0, 'C');
        $pdf->Cell(45, 10, format_currency($data['total_discount']), 0, 1, 'C');
    }

    $pdf->Ln(10); // Line break

    // Expenses section
    $pdf->SetFont('dejavusans', 'B', 12);
    $pdf->Cell(0, 10, 'Expenses Summary', 0, 1, 'L');
    $pdf->SetFont('dejavusans', '', 10);

    // Table headers for Expenses without borders
    $pdf->Cell(40, 10, 'Date', 0, 0, 'C');
    $pdf->Cell(50, 10, 'Amount', 0, 1, 'C');

    // Adding expense data without borders
    foreach ($expenses_data as $data) {
        $pdf->Cell(40, 10, format_date($data['expense_date']), 0, 0, 'C');
        $pdf->Cell(50, 10, format_currency($data['total_expenses']), 0, 1, 'C');
    }

    $pdf->Ln(10); // Line break

    // Owings section
    $pdf->SetFont('dejavusans', 'B', 12);
    $pdf->Cell(0, 10, 'Owings Summary', 0, 1, 'L');
    $pdf->SetFont('dejavusans', '', 10);

    // Table headers for Owings without borders
    $pdf->Cell(40, 10, 'Date', 0, 0, 'C');
    $pdf->Cell(50, 10, 'Amount', 0, 1, 'C');

    // Adding owings data without borders
    foreach ($owings_data as $data) {
        $pdf->Cell(40, 10, format_date($data['owing_date']), 0, 0, 'C');
        $pdf->Cell(50, 10, format_currency($data['total_owings']), 0, 1, 'C');
    }

    $pdf->Ln(10); // Line break

    // Total summary section
    $pdf->SetFont('dejavusans', 'B', 12);
    $pdf->Cell(0, 10, 'Total Summary', 0, 1, 'L');
    $pdf->SetFont('dejavusans', '', 10);

    // Adding total summary without borders
    $pdf->Cell(40, 10, 'Total Sales:', 0, 0, 'L');
    $pdf->Cell(50, 10, format_currency($total_sales), 0, 1, 'L');
    
    $pdf->Cell(40, 10, 'Total Profit:', 0, 0, 'L');
    $pdf->Cell(50, 10, format_currency($total_profit), 0, 1, 'L');
    
    $pdf->Cell(40, 10, 'Total Discount:', 0, 0, 'L');
    $pdf->Cell(50, 10, format_currency($total_discount), 0, 1, 'L');
    
    $pdf->Cell(40, 10, 'Total Expenses:', 0, 0, 'L');
    $pdf->Cell(50, 10, format_currency($total_expenses), 0, 1, 'L');
    
    $pdf->Cell(40, 10, 'Total Owings:', 0, 0, 'L');
    $pdf->Cell(50, 10, format_currency($total_owings), 0, 1, 'L');

    // Output the PDF
    $pdf->Output('report.pdf', 'I');
}





// Check if PDF export button is clicked
if (isset($_POST['export_pdf'])) {
    export_to_pdf($sales_data, $expenses_data, $owings_data, $total_sales, $total_profit, $total_discount, $total_expenses, $total_owings);
}


?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Reports - Store Management</title>
    <link rel="stylesheet" href="..\libs\bootsrtap\css\bootstrap.min.css">
    <link rel="stylesheet" href="..\libs\fontawesome\css\all.min.css">
    
    <style>
        body {
            background-color: #f5f6fa;
            font-family: 'Mulish', sans-serif;
            overflow-x: hidden;
        }

        .content {
            margin-left: 240px;
            padding: 20px;
            transition: margin-left 0.3s ease;
            padding-top: 0; /* Remove top padding since top-bar is sticky */
        }

        .top-bar {
            background-color: #ffffff;
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            position: sticky;
            top: 0;
            z-index: 1000;
            backdrop-filter: blur(10px);
            -webkit-backdrop-filter: blur(10px);
        }

        .top-bar::after {
            content: '';
            position: absolute;
            bottom: -10px;
            left: 0;
            right: 0;
            height: 10px;
            background: linear-gradient(to bottom, rgba(0,0,0,0.05), transparent);
        }

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

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

        .summary-card {
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            color: white;
            text-align: center;
        }

        .total-sales { background: linear-gradient(135deg, #3498db, #2980b9); }
        .total-profit { background: linear-gradient(135deg, #2ecc71, #27ae60); }
        .total-discount { background: linear-gradient(135deg, #f1c40f, #f39c12); }
        .total-expenses { background: linear-gradient(135deg, #e74c3c, #c0392b); }
        .total-owings { background: linear-gradient(135deg, #9b59b6, #8e44ad); }

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

        .export-buttons {
            margin: 20px 0;
            display: flex;
            gap: 10px;
        }

        .btn-export {
            padding: 8px 20px;
            border-radius: 5px;
            font-weight: 500;
            transition: transform 0.2s;
        }

        .btn-export:hover {
            transform: translateY(-2px);
        }

        @media (max-width: 768px) {
            .content {
                margin-left: 0;
                padding: 10px;
            }
            
            .top-bar {
                margin-left: 0;
            }

            .summary-card {
                margin-bottom: 15px;
            }
        }

        /* Add space after top-bar */
        .reports-content {
            padding-top: 20px;
        }
    </style>
</head>
<body>
    <?php include '../includes/side_bar.php'; ?>

    <!-- Mobile Toggle Button -->
    <button class="mobile-toggle d-md-none" onclick="toggleSidebar()">
        <i class="fas fa-bars"></i>
    </button>

    <div class="content">
        <!-- Top Bar -->
        <div class="top-bar">
            <div class="d-flex justify-content-between align-items-center">
                <h4 class="mb-0">Financial Reports</h4>
                <div class="d-flex gap-2">
                    <form method="POST" class="d-flex align-items-center">
                        <select name="report_type" class="form-select me-2" onchange="this.form.submit()">
                            <option value="daily" <?= ($report_type == 'daily') ? 'selected' : ''; ?>>Daily</option>
                            <option value="weekly" <?= ($report_type == 'weekly') ? 'selected' : ''; ?>>Weekly</option>
                            <option value="monthly" <?= ($report_type == 'monthly') ? 'selected' : ''; ?>>Monthly</option>
                            <option value="yearly" <?= ($report_type == 'yearly') ? 'selected' : ''; ?>>Yearly</option>
                        </select>
                    </form>
                    <form method="post" class="d-flex gap-2">
                        <button type="submit" name="export_pdf" class="btn btn-danger btn-export">
                            <i class="fas fa-file-pdf"></i> PDF
                        </button>
                        <button type="submit" name="export_excel" class="btn btn-success btn-export">
                            <i class="fas fa-file-excel"></i> Excel
                        </button>
                    </form>
                </div>
            </div>
        </div>

        <div class="reports-content">
            <!-- Summary Cards -->
            <div class="row mb-4">
                <div class="col-md-4">
                    <div class="summary-card total-sales">
                        <h5>Total Sales</h5>
                        <h3><?= format_currency($total_sales) ?></h3>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="summary-card total-profit">
                        <h5>Total Profit</h5>
                        <h3><?= format_currency($total_profit) ?></h3>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="summary-card total-expenses">
                        <h5>Total Expenses</h5>
                        <h3><?= format_currency($total_expenses) ?></h3>
                    </div>
                </div>
            </div>

            <!-- Reports Tables -->
            <div class="report-card">
                <h5 class="mb-4">Sales Details</h5>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Total Sales</th>
                                <th>Total Profit</th>
                                <th>Total Discounts</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($sales_data as $data) : ?>
                                <tr>
                                    <td><?php echo format_date($data['sale_date']); ?></td>
                                    <td><?php echo format_currency($data['total_sales']); ?></td>
                                    <td><?php echo format_currency($data['total_profit']); ?></td>
                                    <td><?php echo format_currency($data['total_discount']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <div class="report-card">
                <h5 class="mb-4">Expenses Details</h5>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Total Expenses</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($expenses_data as $data) : ?>
                                <tr>
                                    <td><?php echo format_date($data['expense_date']); ?></td>
                                    <td><?php echo format_currency($data['total_expenses']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <div class="report-card">
                <h5 class="mb-4">Owings Details</h5>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Total Owings</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($owings_data as $data) : ?>
                                <tr>
                                    <td><?php echo format_date($data['owing_date']); ?></td>
                                    <td><?php echo format_currency($data['total_owings']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <script src="..\libs\jquery\jquery-3.7.1.min.js"></script>
    <script src="..\libs\bootsrtap\js\bootstrap.bundle.min.js"></script>
    <script>
        function toggleSidebar() {
            document.querySelector('.sidebar').classList.toggle('active');
            document.querySelector('.content').classList.toggle('sidebar-active');
        }
    </script>
</body>
</html>