<?php
require_once '../includes/auth.php';
requirePermission('inventory', 'export');
require_once '../db.php';

require_once '../vendors/dompdf/autoload.inc.php';
use Dompdf\Dompdf;

// ------------------- INPUT -------------------
$search = trim($_GET['search'] ?? '');
$cols   = $_GET['cols'] ?? '';
$batch  = ($_GET['batch'] ?? '0') == '1' ? true : false;

// Column index map (same as your table order)
$colMap = [
    0  => ['key' => 'product_name',   'label' => 'Product'],
    1  => ['key' => 'brand',          'label' => 'Brand'],
    2  => ['key' => 'category',       'label' => 'Category'],
    3  => ['key' => 'batch_no',       'label' => 'Batch No'],
    4  => ['key' => 'vendor_name',    'label' => 'Vendor'],
    5  => ['key' => 'purchase_date',  'label' => 'Purchase Date'],
    6  => ['key' => 'mrp',            'label' => 'MRP'],
    7  => ['key' => 'purchase_price', 'label' => 'Purchase Price'],
    8  => ['key' => 'selling_price',  'label' => 'Selling Price'],
    9  => ['key' => 'quantity',       'label' => 'Total Qty'],
    10 => ['key' => 'available_qty',  'label' => 'Available Qty'],
    11 => ['key' => 'gst_percent',    'label' => 'GST %'],
    12 => ['key' => 'expiry_date',    'label' => 'Expiry'],
];

// Selected columns
$selectedIndexes = array_filter(array_map('intval', explode(',', $cols)));

if (empty($selectedIndexes)) {
    // Default columns
    $selectedIndexes = [0,1,2,4,6,7,8,9,10];
}

// ------------------- QUERY -------------------
$where = " WHERE p.status = 1 AND po.company_id = $cid AND p.company_id = $cid ";
$params = [];
$types  = "";

// Search
if ($search !== '') {
    $where .= " AND (
        p.name LIKE ? OR
        p.brand LIKE ? OR
        p.category LIKE ? OR
        pi.batch_no LIKE ?
    )";
    $like = "%$search%";
    $params = [$like,$like,$like,$like];
    $types  = "ssss";
}

if ($batch) {

    $sql = "
        SELECT 
            p.name AS product_name,
            p.brand,
            p.category,

            pi.batch_no,
            v.name AS vendor_name,
            po.purchase_date,

            pi.mrp,
            pi.purchase_price,
            pi.selling_price,

            pi.quantity,
            pi.available_qty,

            pi.gst_percent,
            pi.expiry_date

        FROM purchase_items pi
        JOIN products p ON p.id = pi.product_id
        JOIN purchase_orders po ON po.id = pi.purchase_order_id
        LEFT JOIN vendors v ON v.id = po.vendor_id

        $where

        ORDER BY p.name ASC, pi.batch_no ASC
    ";

} else {

    $sql = "
    SELECT 
        p.name AS product_name,
        p.brand,
        p.category,

        '' AS batch_no,

        (
            SELECT v2.name
            FROM purchase_items pi2
            JOIN purchase_orders po2 ON po2.id = pi2.purchase_order_id
            LEFT JOIN vendors v2 ON v2.id = po2.vendor_id
            WHERE pi2.product_id = p.id
            ORDER BY po2.purchase_date DESC, pi2.id DESC
            LIMIT 1
        ) AS vendor_name,

        (
            SELECT po2.purchase_date
            FROM purchase_items pi2
            JOIN purchase_orders po2 ON po2.id = pi2.purchase_order_id
            WHERE pi2.product_id = p.id
            ORDER BY po2.purchase_date DESC, pi2.id DESC
            LIMIT 1
        ) AS purchase_date,

        MAX(pi.mrp) AS mrp,
        MAX(pi.purchase_price) AS purchase_price,
        MAX(pi.selling_price) AS selling_price,

        SUM(pi.quantity) AS quantity,
        SUM(pi.available_qty) AS available_qty,

        MAX(pi.gst_percent) AS gst_percent,
        MAX(pi.expiry_date) AS expiry_date

    FROM purchase_items pi
    JOIN products p ON p.id = pi.product_id
    JOIN purchase_orders po ON po.id = pi.purchase_order_id

    $where

    GROUP BY p.id
    ORDER BY p.name ASC
";
}

$stmt = $conn->prepare($sql);
if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

// ------------------- HTML BUILD -------------------
$dateStr = date("d-m-Y h:i A");

$html = "
<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: DejaVu Sans, Arial, sans-serif; font-size: 10px; }
        h2 { text-align:center; margin:0; padding:0; }
        .meta { text-align:center; font-size:9px; margin-bottom:10px; }
        table { width:100%; border-collapse:collapse; }
        th, td { border:1px solid #000; padding:4px; }
        th { background:#f2f2f2; text-align:center; }
        .r { text-align:right; }
        .c { text-align:center; }
    </style>
</head>
<body>

<h2>Stocks Inventory Report</h2>
<div class='meta'>
    Generated: {$dateStr} <br>
    Mode: ".($batch ? "Batch Wise" : "Product Wise")." <br>
    Search: ".htmlspecialchars($search ?: "All")."
</div>

<table>
<thead>
<tr>
";

// Headers
foreach ($selectedIndexes as $idx) {
    if (isset($colMap[$idx])) {
        $html .= "<th>{$colMap[$idx]['label']}</th>";
    }
}

$html .= "</tr></thead><tbody>";

// Rows
while ($row = $result->fetch_assoc()) {

    $html .= "<tr>";

    foreach ($selectedIndexes as $idx) {

        if (!isset($colMap[$idx])) continue;

        $key = $colMap[$idx]['key'];
        $val = $row[$key] ?? '';

        if (in_array($key, ['mrp','purchase_price','selling_price'])) {
            $html .= "<td class='r'>₹ " . number_format((float)$val, 2) . "</td>";
        }
        elseif (in_array($key, ['quantity','available_qty'])) {
            $html .= "<td class='r'>" . number_format((float)$val, 2) . "</td>";
        }
        elseif ($key == 'gst_percent') {
            $html .= "<td class='c'>" . ($val !== '' ? $val.'%' : '') . "</td>";
        }
        elseif ($key == 'expiry_date' && $val) {
            $html .= "<td class='c'>" . date("d-m-Y", strtotime($val)) . "</td>";
        }
        elseif ($key == 'purchase_date' && $val) {
            $html .= "<td class='c'>" . date("d-m-Y", strtotime($val)) . "</td>";
        }
        else {
            $html .= "<td>" . htmlspecialchars($val) . "</td>";
        }
    }

    $html .= "</tr>";
}

$html .= "</tbody></table>";

$html .= "</body></html>";

// ------------------- PDF OUTPUT -------------------
$dompdf = new Dompdf();
$dompdf->loadHtml($html);
$dompdf->setPaper('A4', 'landscape');
$dompdf->render();

// Direct download
$dompdf->stream("stocks_inventory.pdf", ["Attachment" => true]);
exit;
