<?php
session_start();
require_once 'db.php';
require_once '../vendor/autoload.php';
require_once 'report_functions.php';

if (!isset($_SESSION['user_id']) || ($_SESSION['role'] !== 'Admin' && $_SESSION['role'] !== 'Accounts')) {
    die("Unauthorized access.");
}

$report = $_GET['report'] ?? 'sales';
$format = $_GET['format'] ?? 'pdf';
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-d');

$settings = get_report_settings($pdo);
$currency = $settings['company_currency_code'] ?? 'USD';

$meta = [
    'Period' => "$start_date to $end_date",
    'Generated By' => $_SESSION['username'],
    'Generated On' => date('Y-m-d H:i:s')
];

ob_start();

if ($report === 'sales') {
    $user_id = $_GET['user_id'] ?? '';
    $customer_id = $_GET['customer_id'] ?? '';
    
    $sql = "SELECT s.*, u.username, c.name as customer_name
            FROM sales s
            JOIN users u ON s.user_id = u.id
            LEFT JOIN customers c ON s.customer_id = c.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?";
    $params = [$start_date, $end_date];
    
    if ($user_id) { $sql .= " AND s.user_id = ?"; $params[] = $user_id; }
    if ($customer_id) { $sql .= " AND s.customer_id = ?"; $params[] = $customer_id; }
    
    $sql .= " ORDER BY s.sale_date DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll();

    echo render_report_header("Sales Intelligence Report", $settings, $format, $meta);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Date / Time</th>
                <th>Receipt #</th>
                <th>User</th>
                <th>Customer</th>
                <th>Method</th>
                <th class="text-end">Total</th>
            </tr>
        </thead>
        <tbody>
            <?php $grand_total = 0; foreach($data as $row): $grand_total += $row['total_amount']; ?>
                <tr>
                    <td><?php echo $row['sale_date']; ?></td>
                    <td class="fw-bold">#<?php echo $row['id']; ?></td>
                    <td><?php echo $row['username']; ?></td>
                    <td><?php echo $row['customer_name'] ?: 'Guest'; ?></td>
                    <td><?php echo $row['payment_method']; ?></td>
                    <td class="text-end fw-bold"><?php echo format_amount($row['total_amount']); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="5" class="text-end">GRAND TOTAL:</th>
                <th class="text-end text-primary"><?php echo $currency . ' ' . format_amount($grand_total); ?></th>
            </tr>
        </tfoot>
    </table>
    <?php

} elseif ($report === 'purchases') {
    $supplier_id = $_GET['supplier_id'] ?? '';
    $sql = "SELECT p.*, s.name as supplier_name, u.username as creator
            FROM purchases p
            LEFT JOIN suppliers s ON p.supplier_id = s.id
            JOIN users u ON p.user_id = u.id
            WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
    $params = [$start_date, $end_date];
    if ($supplier_id) { $sql .= " AND p.supplier_id = ?"; $params[] = $supplier_id; }
    
    $sql .= " ORDER BY p.purchase_date DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll();

    echo render_report_header("Purchase Analytics Report", $settings, $format, $meta);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>PO Date</th>
                <th>PO #</th>
                <th>Supplier</th>
                <th>Created By</th>
                <th>Status</th>
                <th class="text-end">Total Amount</th>
            </tr>
        </thead>
        <tbody>
            <?php $grand_total = 0; foreach($data as $row): 
                if ($row['status'] !== 'Cancelled') $grand_total += $row['total_amount']; 
                $badge_cls = ($row['status'] === 'Received') ? 'bg-success' : (($row['status'] === 'Pending') ? 'bg-warning' : 'bg-danger');
            ?>
                <tr>
                    <td><?php echo date('Y-m-d', strtotime($row['purchase_date'])); ?></td>
                    <td class="fw-bold">PO-<?php echo $row['id']; ?></td>
                    <td><?php echo $row['supplier_name']; ?></td>
                    <td><?php echo $row['creator']; ?></td>
                    <td><span class="badge <?php echo $badge_cls; ?>"><?php echo $row['status']; ?></span></td>
                    <td class="text-end fw-bold"><?php echo number_format($row['total_amount'], 2); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="5" class="text-end">TOTAL SPEND (Excl. Cancelled):</th>
                <th class="text-end text-primary"><?php echo $currency . ' ' . number_format($grand_total, 2); ?></th>
            </tr>
        </tfoot>
    </table>
    <?php

} elseif ($report === 'profit-loss') {
    // Re-use logic from reports.php
    $stmt = $pdo->prepare("SELECT SUM(total_amount) as total_sales FROM sales WHERE DATE(sale_date) BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $sales = $stmt->fetch()['total_sales'] ?? 0;
    
    $stmt = $pdo->prepare("SELECT SUM(si.quantity * p.cost_price) as cogs 
                           FROM sale_items si 
                           JOIN sales s ON si.sale_id = s.id 
                           JOIN products p ON si.product_id = p.id 
                           WHERE DATE(s.sale_date) BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $cogs = $stmt->fetch()['cogs'] ?? 0;
    
    $stmt = $pdo->prepare("SELECT SUM(total_amount) as total_purchases FROM purchases 
                           WHERE status != 'Cancelled' AND DATE(purchase_date) BETWEEN ? AND ?");
    $stmt->execute([$start_date, $end_date]);
    $purchases = $stmt->fetch()['total_purchases'] ?? 0;
    
    $gross_profit = $sales - $cogs;
    $net_profit = $sales - $purchases;

    echo render_report_header("Profit & Loss Statement", $settings, $format, $meta);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th style="font-size: 14px;">Financial Category</th>
                <th class="text-end" style="font-size: 14px;">Amount (<?php echo $currency; ?>)</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td class="fw-bold">TOTAL REVENUE (Sales)</td>
                <td class="text-end fw-bold text-primary"><?php echo format_amount($sales); ?></td>
            </tr>
            <tr>
                <td style="padding-left: 30px;">Cost of Goods Sold (COGS)</td>
                <td class="text-end text-danger">(<?php echo format_amount($cogs); ?>)</td>
            </tr>
            <tr style="background: #f0f4ff;">
                <td class="fw-bold">GROSS PROFIT</td>
                <td class="text-end fw-bold"><?php echo format_amount($gross_profit); ?></td>
            </tr>
            <tr><td colspan="2" style="height: 20px; border:none;"></td></tr>
            <tr>
                <td class="fw-bold">OPERATING EXPENSES (Inventory Procurement)</td>
                <td class="text-end text-danger">(<?php echo format_amount($purchases); ?>)</td>
            </tr>
            <tr style="background: <?php echo ($net_profit >= 0) ? '#e7f7f5' : '#fde8ea'; ?>;">
                <td class="fw-bold" style="font-size: 14px;">NET CASH FLOW POSITION</td>
                <td class="text-end fw-bold" style="font-size: 16px;"><?php echo format_amount($net_profit); ?></td>
            </tr>
        </tbody>
    </table>
    <?php

} elseif ($report === 'sales-daily') {
    $stmt = $pdo->query("SELECT DATE(sale_date) as date, COUNT(id) as total_sales, SUM(total_amount) as revenue FROM sales GROUP BY DATE(sale_date) ORDER BY date DESC LIMIT 30");
    $data = $stmt->fetchAll();

    echo render_report_header("Daily Sales Summary", $settings, $format, ['Scope' => 'Last 30 Active Days']);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Date</th>
                <th class="text-center">Total Transactions</th>
                <th class="text-end">Revenue Generated</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $row): ?>
                <tr>
                    <td><?php echo date('Y-m-d', strtotime($row['date'])); ?></td>
                    <td class="text-center"><?php echo $row['total_sales']; ?></td>
                    <td class="text-end fw-bold"><?php echo format_amount($row['revenue']); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php

} elseif ($report === 'shifts-all') {
    $stmt = $pdo->query("SELECT sh.*, u.username, 
                        (SELECT SUM(amount_paid - change_amount) FROM sales sa WHERE sa.shift_id = sh.id AND payment_method='CASH') as expected_cash
                        FROM shifts sh JOIN users u ON sh.user_id = u.id ORDER BY sh.opening_time DESC LIMIT 50");
    $data = $stmt->fetchAll();

    echo render_report_header("Terminal Shift Audit Report", $settings, $format, ['Scope' => 'Last 50 Shifts Recorded']);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Shift ID</th>
                <th>Operator</th>
                <th>Opened At</th>
                <th>Closed At</th>
                <th class="text-end">Expected (Cash)</th>
                <th class="text-end">Reported (Cash)</th>
                <th class="text-end">Discrepancy</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $s): 
                $expected = $s['opening_balance'] + ($s['expected_cash'] ?? 0);
                $actual = $s['closing_balance'];
                $diff = ($actual !== null) ? ($actual - $expected) : 0;
            ?>
                <tr>
                    <td>#<?php echo $s['id']; ?></td>
                    <td><?php echo $s['username']; ?></td>
                    <td><?php echo date('Y-m-d H:i', strtotime($s['opening_time'])); ?></td>
                    <td><?php echo $s['closing_time'] ? date('Y-m-d H:i', strtotime($s['closing_time'])) : 'STILL OPEN'; ?></td>
                    <td class="text-end"><?php echo format_amount($expected); ?></td>
                    <td class="text-end"><?php echo ($actual !== null) ? format_amount($actual) : '-'; ?></td>
                    <td class="text-end fw-bold <?php echo ($diff < 0) ? 'text-danger' : (($diff > 0) ? 'text-warning' : 'text-success'); ?>">
                        <?php echo ($actual !== null) ? format_amount($diff) : '-'; ?>
                    </td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php

} elseif ($report === 'inventory') {
    $stmt = $pdo->query("SELECT p.*, c.name as category_name, u.name as unit_name 
                          FROM products p 
                          LEFT JOIN categories c ON p.category_id = c.id 
                          LEFT JOIN units u ON p.unit_id = u.id 
                          ORDER BY p.stock_quantity DESC");
    $data = $stmt->fetchAll();

    $total_cost = 0;
    foreach($data as $p) $total_cost += ($p['stock_quantity'] * $p['cost_price']);

    echo render_report_header("Inventory Asset Valuation", $settings, $format, array_merge($meta, ['Total SKUs' => count($data)]));
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Product Description</th>
                <th>SKU / Barcode</th>
                <th>Category</th>
                <th class="text-end">Cost Price</th>
                <th class="text-center">Stock</th>
                <th class="text-end">Asset Value</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $p): $val = $p['stock_quantity'] * $p['cost_price']; ?>
                <tr>
                    <td><strong><?php echo $p['name']; ?></strong><br><small><?php echo $p['unit_name']; ?></small></td>
                    <td><?php echo $p['sku'] ?: ($p['barcode'] ?: '-'); ?></td>
                    <td><?php echo $p['category_name']; ?></td>
                    <td class="text-end"><?php echo format_amount($p['cost_price']); ?></td>
                    <td class="text-center"><?php echo $p['stock_quantity']; ?></td>
                    <td class="text-end fw-bold"><?php echo format_amount($val); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="5" class="text-end">TOTAL INVENTORY ASSET VALUE:</th>
                <th class="text-end text-primary"><?php echo $currency . ' ' . format_amount($total_cost); ?></th>
            </tr>
        </tfoot>
    </table>
    <?php

} elseif ($report === 'inventory-movements') {
    $product_id = $_GET['product_id'] ?? '';
    
    $sql = "(SELECT r.received_date as date, 'Reception' as type, CONCAT('GRN-', r.id, ' (PO-', p.id, ') - ', s.name) as identifier, ri.quantity_received as qty, pr.name as product_name, u.username
             FROM receptions r
             JOIN purchases p ON r.purchase_id = p.id
             JOIN suppliers s ON p.supplier_id = s.id
             JOIN reception_items ri ON r.id = ri.reception_id
             JOIN products pr ON ri.product_id = pr.id
             JOIN users u ON r.received_by = u.id)
            UNION ALL
            (SELECT sale_date as date, 'Sale' as type, CONCAT('Receipt #', s.id) as identifier, -si.quantity as qty, pr.name as product_name, u.username
             FROM sales s
             JOIN sale_items si ON s.id = si.sale_id
             JOIN products pr ON si.product_id = pr.id
             JOIN users u ON s.user_id = u.id)
            UNION ALL
            (SELECT adjustment_date as date, adjustment_type as type, reason as identifier, 
                CASE WHEN adjustment_type IN ('Out', 'Damage', 'Loss') THEN -quantity ELSE quantity END as qty, 
                pr.name as product_name, u.username
             FROM stock_adjustments sa
             JOIN products pr ON sa.product_id = pr.id
             JOIN users u ON sa.user_id = u.id)
            ORDER BY date DESC LIMIT 500";
            
    $stmt = $pdo->query($sql);
    $data = $stmt->fetchAll();

    echo render_report_header("Stock Movement Audit Trail", $settings, $format, array_merge($meta, ['Scope' => 'Last 500 Movements']));
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Date/Time</th>
                <th>Type</th>
                <th>Product</th>
                <th>Reference</th>
                <th class="text-center">Qty</th>
                <th>User</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $m): ?>
                <tr>
                    <td><?php echo date('Y-m-d H:i', strtotime($m['date'])); ?></td>
                    <td><?php echo $m['type']; ?></td>
                    <td><strong><?php echo $m['product_name']; ?></strong></td>
                    <td><?php echo $m['identifier']; ?></td>
                    <td class="text-center fw-bold <?php echo ($m['qty'] < 0) ? 'text-danger' : 'text-success'; ?>">
                        <?php echo ($m['qty'] > 0) ? '+' : ''; ?><?php echo $m['qty']; ?>
                    </td>
                    <td><?php echo $m['username']; ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php

} elseif ($report === 'shift-sales') {
    $shift_id = $_GET['shift_id'] ?? '';
    if (!$shift_id) die("Shift ID required");

    $stmt = $pdo->prepare("SELECT s.*, u.username as opener_name FROM shifts s JOIN users u ON s.user_id = u.id WHERE s.id = ?");
    $stmt->execute([$shift_id]);
    $shift = $stmt->fetch();

    $stmt = $pdo->prepare("SELECT s.*, u.username, c.name as customer_name FROM sales s JOIN users u ON s.user_id = u.id LEFT JOIN customers c ON s.customer_id = c.id WHERE s.shift_id = ? ORDER BY s.id DESC");
    $stmt->execute([$shift_id]);
    $sales = $stmt->fetchAll();

    $meta = [
        'Shift ID' => '#' . $shift_id,
        'Opened By' => $shift['opener_name'],
        'Period' => $shift['opening_time'] . ' to ' . ($shift['closing_time'] ?: 'Active')
    ];

    echo render_report_header("Shift Transaction Audit", $settings, $format, $meta);
    ?>
    <table class="table" <?php if($format === 'excel') echo 'border="1"'; ?>>
        <thead>
            <tr>
                <th>Receipt #</th>
                <th>Time</th>
                <th>Method</th>
                <th>Customer</th>
                <th class="text-end">Total</th>
            </tr>
        </thead>
        <tbody>
            <?php $total = 0; foreach($sales as $s): $total += $s['total_amount']; ?>
                <tr>
                    <td>#<?php echo $s['id']; ?></td>
                    <td><?php echo date('H:i', strtotime($s['sale_date'])); ?></td>
                    <td><?php echo $s['payment_method']; ?></td>
                    <td><?php echo $s['customer_name'] ?: 'Guest'; ?></td>
                    <td class="text-end fw-bold"><?php echo format_amount($s['total_amount']); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="4" class="text-end">TOTAL REVENUE:</th>
                <th class="text-end text-primary"><?php echo $currency . ' ' . format_amount($total); ?></th>
            </tr>
        </tfoot>
    </table>
    <?php
}

echo render_report_footer($settings);
$html = ob_get_clean();

$fileName = "Report_" . ucfirst($report) . "_" . date('Ymd');
if ($format === 'pdf') {
    export_to_pdf($html, $fileName . ".pdf");
} else {
    export_to_excel($html, $fileName . ".xls", "Financial Report");
}
