<?php
session_start();
include 'layouts/dbconn.php';
include 'layouts/session.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'CHIEF_PHARMACIST', 'PHARMACIST'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: dashboard.php');
    exit();
}

// Get date range
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-01');
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');

// Sales Summary
$sales_sql = "SELECT 
                COUNT(*) as total_dispensing,
                SUM(total_amount) as total_sales,
                SUM(amount_paid) as total_collected,
                SUM(balance) as total_outstanding,
                AVG(total_amount) as average_sale
              FROM pharmacy_dispensing
              WHERE DATE(dispensing_date) BETWEEN ? AND ?";
$stmt = $conn->prepare($sales_sql);
$stmt->bind_param('ss', $date_from, $date_to);
$stmt->execute();
$sales_summary = $stmt->get_result()->fetch_assoc();

// Sales by Payment Method
$payment_method_sql = "SELECT 
                        payment_method,
                        COUNT(*) as transaction_count,
                        SUM(total_amount) as total_amount
                       FROM pharmacy_dispensing
                       WHERE DATE(dispensing_date) BETWEEN ? AND ?
                       GROUP BY payment_method
                       ORDER BY total_amount DESC";
$stmt = $conn->prepare($payment_method_sql);
$stmt->bind_param('ss', $date_from, $date_to);
$stmt->execute();
$payment_methods = $stmt->get_result();

// Sales by Patient Type
$patient_type_sql = "SELECT 
                        patient_type,
                        COUNT(*) as transaction_count,
                        SUM(total_amount) as total_amount
                     FROM pharmacy_dispensing
                     WHERE DATE(dispensing_date) BETWEEN ? AND ?
                     GROUP BY patient_type
                     ORDER BY total_amount DESC";
$stmt = $conn->prepare($patient_type_sql);
$stmt->bind_param('ss', $date_from, $date_to);
$stmt->execute();
$patient_types = $stmt->get_result();

// Top Selling Items
$top_items_sql = "SELECT 
                    pi.item_name, pi.item_code,
                    SUM(pdi.quantity) as total_quantity,
                    SUM(pdi.total_price) as total_sales
                  FROM pharmacy_dispensing_items pdi
                  INNER JOIN pharmacy_inventory pi ON pdi.inventory_id = pi.inventory_id
                  INNER JOIN pharmacy_dispensing pd ON pdi.dispensing_id = pd.dispensing_id
                  WHERE DATE(pd.dispensing_date) BETWEEN ? AND ?
                  GROUP BY pdi.inventory_id
                  ORDER BY total_quantity DESC
                  LIMIT 10";
$stmt = $conn->prepare($top_items_sql);
$stmt->bind_param('ss', $date_from, $date_to);
$stmt->execute();
$top_items = $stmt->get_result();

// Stock Valuation
$stock_valuation_sql = "SELECT 
                         SUM(current_stock * unit_cost) as stock_value_cost,
                         SUM(current_stock * selling_price) as stock_value_selling,
                         COUNT(*) as total_items,
                         SUM(current_stock) as total_units
                        FROM pharmacy_inventory
                        WHERE is_active = 1";
$stock_valuation = $conn->query($stock_valuation_sql)->fetch_assoc();

// Daily Sales Trend
$daily_sales_sql = "SELECT 
                     DATE(dispensing_date) as sale_date,
                     COUNT(*) as transaction_count,
                     SUM(total_amount) as daily_sales
                    FROM pharmacy_dispensing
                    WHERE DATE(dispensing_date) BETWEEN ? AND ?
                    GROUP BY DATE(dispensing_date)
                    ORDER BY sale_date ASC";
$stmt = $conn->prepare($daily_sales_sql);
$stmt->bind_param('ss', $date_from, $date_to);
$stmt->execute();
$daily_sales = $stmt->get_result();
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Pharmacy Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        .report-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            margin-bottom: 1.5rem;
        }
        .stat-box {
            background: #f9fafb;
            border-radius: 8px;
            padding: 1rem;
            text-align: center;
            border-left: 4px solid #3b82f6;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Pharmacy Reports & Analytics</h4>
                                <div class="page-title-right">
                                    <button class="btn btn-success" onclick="exportReports()">
                                        <i class="ri-file-excel-line me-1"></i>Export All
                                    </button>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Date Filter -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <form method="GET" action="" class="row g-3">
                                        <div class="col-md-4">
                                            <label class="form-label">From Date</label>
                                            <input type="date" class="form-control" name="date_from" 
                                                   value="<?php echo $date_from; ?>" required>
                                        </div>
                                        <div class="col-md-4">
                                            <label class="form-label">To Date</label>
                                            <input type="date" class="form-control" name="date_to" 
                                                   value="<?php echo $date_to; ?>" required>
                                        </div>
                                        <div class="col-md-4 d-flex align-items-end">
                                            <button type="submit" class="btn btn-primary w-100">
                                                <i class="ri-filter-line me-1"></i>Generate Reports
                                            </button>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Sales Summary -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <h5 class="mb-3">Sales Summary</h5>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #3b82f6;">
                                <p class="text-muted mb-1">Total Dispensing</p>
                                <h4 class="mb-0"><?php echo number_format($sales_summary['total_dispensing']); ?></h4>
                            </div>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #10b981;">
                                <p class="text-muted mb-1">Total Sales</p>
                                <h5 class="mb-0">KES <?php echo number_format($sales_summary['total_sales'], 0); ?></h5>
                            </div>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #06b6d4;">
                                <p class="text-muted mb-1">Collected</p>
                                <h5 class="mb-0">KES <?php echo number_format($sales_summary['total_collected'], 0); ?></h5>
                            </div>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #f59e0b;">
                                <p class="text-muted mb-1">Outstanding</p>
                                <h5 class="mb-0">KES <?php echo number_format($sales_summary['total_outstanding'], 0); ?></h5>
                            </div>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #8b5cf6;">
                                <p class="text-muted mb-1">Average Sale</p>
                                <h5 class="mb-0">KES <?php echo number_format($sales_summary['average_sale'], 0); ?></h5>
                            </div>
                        </div>
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="stat-box" style="border-left-color: #ec4899;">
                                <p class="text-muted mb-1">Stock Value</p>
                                <h5 class="mb-0">KES <?php echo number_format($stock_valuation['stock_value_cost'], 0); ?></h5>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <!-- Sales by Payment Method -->
                        <div class="col-xl-6">
                            <div class="report-card">
                                <h5 class="mb-3">Sales by Payment Method</h5>
                                <canvas id="paymentMethodChart" height="300"></canvas>
                                <div class="table-responsive mt-3">
                                    <table class="table table-sm">
                                        <thead>
                                            <tr>
                                                <th>Payment Method</th>
                                                <th>Transactions</th>
                                                <th>Amount</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php
                                            $payment_methods->data_seek(0);
                                            while ($pm = $payment_methods->fetch_assoc()): 
                                            ?>
                                                <tr>
                                                    <td><?php echo str_replace('_', ' ', $pm['payment_method']); ?></td>
                                                    <td><?php echo $pm['transaction_count']; ?></td>
                                                    <td>KES <?php echo number_format($pm['total_amount'], 2); ?></td>
                                                </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>

                        <!-- Sales by Patient Type -->
                        <div class="col-xl-6">
                            <div class="report-card">
                                <h5 class="mb-3">Sales by Patient Type</h5>
                                <canvas id="patientTypeChart" height="300"></canvas>
                                <div class="table-responsive mt-3">
                                    <table class="table table-sm">
                                        <thead>
                                            <tr>
                                                <th>Patient Type</th>
                                                <th>Transactions</th>
                                                <th>Amount</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php
                                            $patient_types->data_seek(0);
                                            while ($pt = $patient_types->fetch_assoc()): 
                                            ?>
                                                <tr>
                                                    <td><?php echo str_replace('_', ' ', $pt['patient_type']); ?></td>
                                                    <td><?php echo $pt['transaction_count']; ?></td>
                                                    <td>KES <?php echo number_format($pt['total_amount'], 2); ?></td>
                                                </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Top Selling Items -->
                    <div class="row">
                        <div class="col-12">
                            <div class="report-card">
                                <h5 class="mb-3">Top 10 Selling Items</h5>
                                <div class="table-responsive">
                                    <table class="table table-hover">
                                        <thead>
                                            <tr>
                                                <th>#</th>
                                                <th>Item Code</th>
                                                <th>Item Name</th>
                                                <th>Quantity Sold</th>
                                                <th>Total Sales</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php 
                                            $rank = 1;
                                            while ($item = $top_items->fetch_assoc()): 
                                            ?>
                                                <tr>
                                                    <td><?php echo $rank++; ?></td>
                                                    <td><?php echo htmlspecialchars($item['item_code']); ?></td>
                                                    <td><?php echo htmlspecialchars($item['item_name']); ?></td>
                                                    <td><?php echo number_format($item['total_quantity']); ?></td>
                                                    <td>KES <?php echo number_format($item['total_sales'], 2); ?></td>
                                                </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Daily Sales Trend -->
                    <div class="row">
                        <div class="col-12">
                            <div class="report-card">
                                <h5 class="mb-3">Daily Sales Trend</h5>
                                <canvas id="dailySalesChart" height="100"></canvas>
                            </div>
                        </div>
                    </div>

                    <!-- Stock Valuation -->
                    <div class="row">
                        <div class="col-12">
                            <div class="report-card">
                                <h5 class="mb-3">Inventory Valuation Summary</h5>
                                <div class="row">
                                    <div class="col-md-3">
                                        <div class="text-center">
                                            <p class="text-muted mb-1">Total Items</p>
                                            <h4><?php echo number_format($stock_valuation['total_items']); ?></h4>
                                        </div>
                                    </div>
                                    <div class="col-md-3">
                                        <div class="text-center">
                                            <p class="text-muted mb-1">Total Units</p>
                                            <h4><?php echo number_format($stock_valuation['total_units']); ?></h4>
                                        </div>
                                    </div>
                                    <div class="col-md-3">
                                        <div class="text-center">
                                            <p class="text-muted mb-1">Cost Value</p>
                                            <h4>KES <?php echo number_format($stock_valuation['stock_value_cost'], 0); ?></h4>
                                        </div>
                                    </div>
                                    <div class="col-md-3">
                                        <div class="text-center">
                                            <p class="text-muted mb-1">Selling Value</p>
                                            <h4>KES <?php echo number_format($stock_valuation['stock_value_selling'], 0); ?></h4>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>

            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="assets/js/app.js"></script>
    
    <script>
    // Payment Method Chart
    const paymentMethodData = {
        labels: [
            <?php 
            $payment_methods->data_seek(0);
            $labels = [];
            while ($pm = $payment_methods->fetch_assoc()) {
                $labels[] = "'" . str_replace('_', ' ', $pm['payment_method']) . "'";
            }
            echo implode(',', $labels);
            ?>
        ],
        datasets: [{
            label: 'Sales Amount',
            data: [
                <?php 
                $payment_methods->data_seek(0);
                $values = [];
                while ($pm = $payment_methods->fetch_assoc()) {
                    $values[] = $pm['total_amount'];
                }
                echo implode(',', $values);
                ?>
            ],
            backgroundColor: [
                'rgba(59, 130, 246, 0.8)',
                'rgba(16, 185, 129, 0.8)',
                'rgba(245, 158, 11, 0.8)',
                'rgba(139, 92, 246, 0.8)',
                'rgba(236, 72, 153, 0.8)'
            ]
        }]
    };

    const paymentMethodChart = new Chart(
        document.getElementById('paymentMethodChart'),
        {
            type: 'doughnut',
            data: paymentMethodData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    }
                }
            }
        }
    );

    // Patient Type Chart
    const patientTypeData = {
        labels: [
            <?php 
            $patient_types->data_seek(0);
            $labels = [];
            while ($pt = $patient_types->fetch_assoc()) {
                $labels[] = "'" . str_replace('_', ' ', $pt['patient_type']) . "'";
            }
            echo implode(',', $labels);
            ?>
        ],
        datasets: [{
            label: 'Sales Amount',
            data: [
                <?php 
                $patient_types->data_seek(0);
                $values = [];
                while ($pt = $patient_types->fetch_assoc()) {
                    $values[] = $pt['total_amount'];
                }
                echo implode(',', $values);
                ?>
            ],
            backgroundColor: [
                'rgba(59, 130, 246, 0.8)',
                'rgba(16, 185, 129, 0.8)',
                'rgba(245, 158, 11, 0.8)'
            ]
        }]
    };

    const patientTypeChart = new Chart(
        document.getElementById('patientTypeChart'),
        {
            type: 'pie',
            data: patientTypeData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    }
                }
            }
        }
    );

    // Daily Sales Trend Chart
    const dailySalesData = {
        labels: [
            <?php 
            $daily_sales->data_seek(0);
            $labels = [];
            while ($ds = $daily_sales->fetch_assoc()) {
                $labels[] = "'" . date('M d', strtotime($ds['sale_date'])) . "'";
            }
            echo implode(',', $labels);
            ?>
        ],
        datasets: [{
            label: 'Daily Sales (KES)',
            data: [
                <?php 
                $daily_sales->data_seek(0);
                $values = [];
                while ($ds = $daily_sales->fetch_assoc()) {
                    $values[] = $ds['daily_sales'];
                }
                echo implode(',', $values);
                ?>
            ],
            borderColor: 'rgb(59, 130, 246)',
            backgroundColor: 'rgba(59, 130, 246, 0.1)',
            tension: 0.4,
            fill: true
        }]
    };

    const dailySalesChart = new Chart(
        document.getElementById('dailySalesChart'),
        {
            type: 'line',
            data: dailySalesData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'KES ' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        }
    );

    function exportReports() {
        const params = new URLSearchParams(window.location.search);
        window.location.href = 'pharmacy_reports_export.php?' + params.toString();
    }
    </script>
</body>
</html>
                                            