<?php
include 'db.php';
include 'auth_check.php';
require __DIR__ . '/vendor/autoload.php';
use Dompdf\Dompdf;

// Determine report type
$report_type = isset($_GET['report_type']) ? $_GET['report_type'] : 'inward';
$table = ($report_type == 'outward') ? 'outward_stock' : 'purchase_stock';

// Single search input
$search = isset($_GET['search']) ? $_GET['search'] : '';

// Pagination
$limit = 10;
$page = isset($_GET['page']) ? max(1,(int)$_GET['page']) : 1;
$offset = ($page - 1) * $limit;

// Columns to search
$search_columns = ($report_type == 'inward') 
    ? ['p.item_name','p.particular_name','p.specification','p.model_number','p.invoice_number','p.courier_name','p.rate','p.fault_count']
    : ['item_name','particular_name','specification','model_number','given_to','remarks'];

$search_escaped = $conn->real_escape_string($search);

// ------------------- BUILD SQL -------------------
if ($report_type == 'inward') {
    $where_clause = '';
    if (!empty($search_escaped)) {
        $conds = [];
        foreach ($search_columns as $col) $conds[] = "$col LIKE '%$search_escaped%'";
        $where_clause = " AND (" . implode(' OR ', $conds) . ")";
    }

    $base_sql = "
        SELECT 
            p.item_name,
            p.particular_name,
            p.specification,
            p.model_number,
            SUM(p.qty) AS purchased_qty,
            COALESCE(SUM(f.fault_qty),0) AS fault_count,
            COALESCE(SUM(o.outward_qty),0) AS outward_qty,
            (SUM(p.qty) - COALESCE(SUM(f.fault_qty),0) - COALESCE(SUM(o.outward_qty),0)) AS available_stock
        FROM purchase_stock p
        LEFT JOIN (
            SELECT purchase_stock_id, SUM(qty) AS fault_qty
            FROM fault_stock
            GROUP BY purchase_stock_id
        ) f ON f.purchase_stock_id = p.id
        LEFT JOIN (
            SELECT purchase_stock_id, SUM(qty) AS outward_qty
            FROM outward_stock
            GROUP BY purchase_stock_id
        ) o ON o.purchase_stock_id = p.id
        WHERE 1=1 $where_clause
        GROUP BY p.item_name, p.particular_name, p.specification, p.model_number
    ";

    $count_sql = "
        SELECT COUNT(*) as total FROM (
            SELECT 1
            FROM purchase_stock p
            LEFT JOIN (
                SELECT purchase_stock_id, SUM(qty) AS fault_qty
                FROM fault_stock
                GROUP BY purchase_stock_id
            ) f ON f.purchase_stock_id = p.id
            LEFT JOIN (
                SELECT purchase_stock_id, SUM(qty) AS outward_qty
                FROM outward_stock
                GROUP BY purchase_stock_id
            ) o ON o.purchase_stock_id = p.id
            WHERE 1=1 $where_clause
            GROUP BY p.item_name, p.particular_name, p.specification, p.model_number
        ) t
    ";

    $sql = $base_sql . " ORDER BY p.item_name ASC LIMIT $limit OFFSET $offset";
} else {
    $sql = "SELECT * FROM outward_stock";
    $count_sql = "SELECT COUNT(*) as total FROM outward_stock";
    if (!empty($search_escaped)) {
        $conds = [];
        foreach ($search_columns as $col) $conds[] = "$col LIKE '%$search_escaped%'";
        $where = " WHERE ".implode(' OR ',$conds);
        $sql .= $where;
        $count_sql .= $where;
    }
    $sql .= " ORDER BY id DESC LIMIT $limit OFFSET $offset";
}

// Execute queries
$total_records = $conn->query($count_sql)->fetch_assoc()['total'];
$total_pages = ceil($total_records / $limit);
$result = $conn->query($sql);

// Columns to display
$columns = ($report_type=='inward') 
    ? ['item_name'=>'Item','particular_name'=>'Particular','specification'=>'Specification','model_number'=>'Model','purchased_qty'=>'Inward','outward_qty'=>'Outward','fault_count'=>'Fault','available_stock'=>'Available Stock']
    : ['outward_date'=>'Date','given_to'=>'Given To','item_name'=>'Item','particular_name'=>'Particular','specification'=>'Specification','model_number'=>'Model','qty'=>'Quantity','remarks'=>'Remarks'];

// ------------------- PDF Export -------------------
if(isset($_GET['export']) && $_GET['export']=='pdf'){
    $export_sql = preg_replace('/\s+LIMIT\s+\d+\s+OFFSET\s+\d+/i','',$sql);
    $export_result = $conn->query($export_sql);
    $html = "<h2>".ucfirst($report_type)." Stock Report</h2>
             <table border='1' width='100%' style='border-collapse: collapse; text-align:center;'><thead><tr>";
    foreach($columns as $label) $html .= "<th>$label</th>";
    $html .= "</tr></thead><tbody>";
    if($export_result->num_rows>0){
        while($row = $export_result->fetch_assoc()){
            $html .= "<tr>";
            foreach(array_keys($columns) as $col){
                if (in_array($col,['purchased_qty','outward_qty','fault_count','available_stock','qty'])) {
                    $value = (int)($row[$col] ?? 0);
                } else {
                    $value = $row[$col] ?? '';
                }
                $html .= "<td>$value</td>";
            }
            $html .= "</tr>";
        }
    } else $html .= "<tr><td colspan='".count($columns)."' align='center'>No records found</td></tr>";
    $html .= "</tbody></table>";

    $dompdf = new Dompdf();
    $dompdf->loadHtml($html);
    $dompdf->setPaper('A4','landscape');
    $dompdf->render();
    $dompdf->stream($report_type . '_report_' . date("YmdHis") . '.pdf', ['Attachment' => true]);
    exit;
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Stock Report</title>
    <link rel="icon" type="image/png" href="assets/logo/logo_icon.png">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        /* Ensure footer stays at bottom */
        body { display: flex; flex-direction: column; min-height: 100vh; }
        .flex-grow-1 { flex: 1; }
        footer { background: #f8f9fa; padding: 10px 0; }
    </style>
    <script>function autoSearch(){document.getElementById('searchForm').submit();}</script>
</head>
<body class="bg-light">
<?php if(session_status()==PHP_SESSION_NONE) session_start(); include 'navbar.php'; ?>

<div class="flex-grow-1">
    <div class="container mt-2">
        <h2 class="mb-4 text-center text-primary">Net Stock</h2>

        <form method="GET" id="searchForm" class="row g-3 mb-4 justify-content-center text-center">
            <div class="col-md-3">
                <select class="form-select" name="report_type" onchange="this.form.submit()">
                    <option value="inward" <?php if($report_type=='inward') echo 'selected'; ?>>Inward</option>
                    <option value="outward" <?php if($report_type=='outward') echo 'selected'; ?>>Outward</option>
                </select>
            </div>
            <div class="col-md-3">
                <input type="text" name="search" class="form-control" placeholder="Search" value="<?php echo htmlspecialchars($search); ?>" onkeyup="autoSearch()">
            </div>
            <div class="col-md-2 d-flex gap-2">
                <a href="report.php?report_type=<?php echo $report_type; ?>&export=pdf&search=<?php echo urlencode($search); ?>" class="btn btn-success">Export PDF</a>
            </div>
        </form>

        <div class="table-responsive shadow-sm bg-white rounded p-2">
            <table class="table table-bordered table-striped text-center">
                <thead class="table-dark"><tr><?php foreach($columns as $label) echo "<th>$label</th>"; ?></tr></thead>
                <tbody>
                <?php
                if($result->num_rows>0){
                    while($row=$result->fetch_assoc()){
                        echo "<tr>";
                        foreach(array_keys($columns) as $col){
                            if (in_array($col,['purchased_qty','outward_qty','fault_count','available_stock','qty'])) {
                                $value = (int)($row[$col] ?? 0);
                            } else {
                                $value = $row[$col] ?? '';
                            }
                            echo "<td>$value</td>";
                        }
                        echo "</tr>";
                    }
                } else echo "<tr><td colspan='".count($columns)."' class='text-center text-danger'>No records found</td></tr>";
                ?>
                </tbody>
            </table>
        </div>

        <!-- Pagination -->
        <nav aria-label="Page navigation example">
            <ul class="pagination justify-content-center mt-3">
                <?php
                $prev_page = $page-1;
                echo '<li class="page-item '.($page<=1?'disabled':'').'">
                        <a class="page-link" href="?report_type='.$report_type.'&page='.$prev_page.'&search='.urlencode($search).'">Previous</a>
                    </li>';
                for($i=1;$i<=$total_pages;$i++){
                    echo '<li class="page-item '.($i==$page?'active':'').'">
                            <a class="page-link" href="?report_type='.$report_type.'&page='.$i.'&search='.urlencode($search).'">'.$i.'</a>
                        </li>';
                }
                $next_page=$page+1;
                echo '<li class="page-item '.($page>=$total_pages?'disabled':'').'">
                        <a class="page-link" href="?report_type='.$report_type.'&page='.$next_page.'&search='.urlencode($search).'">Next</a>
                    </li>';
                ?>
            </ul>
        </nav>
    </div>
</div>

<?php
if (session_status() == PHP_SESSION_NONE) session_start();
include 'footer.php';
?>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
