<?php
include('session.php');
include('stock_helpers.php');
include_once('complaint_scope.php');

if (!stock_can_view()) {
    header('location: dashboard.php');
    exit;
}

$filter_material = isset($_GET['material_id']) ? (int)$_GET['material_id'] : 0;
$filter_from = isset($_GET['from_date']) ? mysqli_real_escape_string($conn, $_GET['from_date']) : '';
$filter_to = isset($_GET['to_date']) ? mysqli_real_escape_string($conn, $_GET['to_date']) : '';

function stock_report_format_qty($qty)
{
    return rtrim(rtrim(number_format((float)$qty, 2, '.', ''), '0'), '.');
}

function stock_report_division_id()
{
    return (int)($_SESSION['division_id'] ?? 0);
}

function stock_report_holder_rows($conn)
{
    $holders = array();
    $division_id = stock_report_division_id();
    $department_id = (int)($_SESSION['department_id'] ?? 0);
    $login_id = (int)($_SESSION['admin'] ?? 0);

    if (stock_is_contractor()) {
        $holders[] = array('type' => 'contractor', 'id' => $login_id, 'label' => stock_holder_label($conn, 'contractor', $login_id));
        return $holders;
    }

    if (stock_is_department()) {
        $holders[] = array('type' => 'department', 'id' => $department_id, 'label' => stock_holder_label($conn, 'department', $department_id));

        $contractors = $conn->query("
            SELECT id, name
            FROM admin
            WHERE (role='contractor' OR type='contractor')
            AND status='1'
            AND department_id='$department_id'
            ORDER BY name ASC
        ");
        while ($contractors && $row = $contractors->fetch_assoc()) {
            $holders[] = array('type' => 'contractor', 'id' => (int)$row['id'], 'label' => 'Contractor - '.$row['name']);
        }

        return $holders;
    }

    if (stock_is_bso()) {
        $holders[] = array('type' => 'bso', 'id' => $login_id, 'label' => stock_holder_label($conn, 'bso', $login_id));
    }

    $bsoWhere = "(role='bso' OR type='bso') AND status='1'";
    $deptWhere = "status='1'";
    $contractorWhere = "(role='contractor' OR type='contractor') AND status='1'";

    if (stock_is_bso()) {
        $bsoWhere .= " AND id='$login_id'";
        if ($division_id > 0) {
            $deptWhere .= " AND division_id='$division_id'";
            $contractorWhere .= " AND division_id='$division_id'";
        }
    } elseif (stock_role() === 'ge_admin' && $division_id > 0) {
        $bsoWhere .= " AND division_id='$division_id'";
        $deptWhere .= " AND division_id='$division_id'";
        $contractorWhere .= " AND division_id='$division_id'";
    }

    if (!stock_is_bso()) {
        $bsos = $conn->query("SELECT id, name FROM admin WHERE $bsoWhere ORDER BY name ASC");
        while ($bsos && $row = $bsos->fetch_assoc()) {
            $holders[] = array('type' => 'bso', 'id' => (int)$row['id'], 'label' => 'BSO - '.$row['name']);
        }
    }

    $departments = $conn->query("SELECT id, name FROM departments WHERE $deptWhere ORDER BY name ASC");
    while ($departments && $row = $departments->fetch_assoc()) {
        $holders[] = array('type' => 'department', 'id' => (int)$row['id'], 'label' => 'Department - '.$row['name']);
    }

    $contractors = $conn->query("SELECT id, name FROM admin WHERE $contractorWhere ORDER BY name ASC");
    while ($contractors && $row = $contractors->fetch_assoc()) {
        $holders[] = array('type' => 'contractor', 'id' => (int)$row['id'], 'label' => 'Contractor - '.$row['name']);
    }

    return $holders;
}

function stock_report_holder_condition($holders, $alias, $side)
{
    $parts = array();
    foreach ($holders as $holder) {
        $type = mysqli_real_escape_string($GLOBALS['conn'], $holder['type']);
        $id = (int)$holder['id'];
        $parts[] = "($alias.".$side."_holder_type='$type' AND $alias.".$side."_holder_id='$id')";
    }

    if (empty($parts)) {
        return '1=0';
    }

    return '('.implode(' OR ', $parts).')';
}

function stock_report_history_where($conn, $holders, $filter_material, $filter_from, $filter_to)
{
    $fromCondition = stock_report_holder_condition($holders, 'st', 'from');
    $toCondition = stock_report_holder_condition($holders, 'st', 'to');
    $where = "WHERE ($fromCondition OR $toCondition)";

    if ($filter_material > 0) {
        $where .= " AND st.material_id='$filter_material'";
    }
    if ($filter_from !== '') {
        $where .= " AND DATE(st.created_at) >= '$filter_from'";
    }
    if ($filter_to !== '') {
        $where .= " AND DATE(st.created_at) <= '$filter_to'";
    }

    return $where;
}

function stock_report_complaint_used_filter($conn, $filter_material, $filter_from, $filter_to)
{
    global $admin;

    $where = "WHERE cum.ucid <> 0";
    $complaintDateSql = "(CASE WHEN uc.completiondate IS NULL OR CAST(uc.completiondate AS CHAR)='' THEN STR_TO_DATE(uc.date, '%d/%m/%Y') ELSE uc.completiondate END)";

    if ($filter_material > 0) {
        $where .= " AND cum.material_id='$filter_material'";
    }

    if ($filter_from !== '') {
        $where .= " AND DATE($complaintDateSql) >= '$filter_from'";
    }

    if ($filter_to !== '') {
        $where .= " AND DATE($complaintDateSql) <= '$filter_to'";
    }

    $division_id = (int)($_SESSION['division_id'] ?? 0);

    if (stock_is_department()) {
        $scope = complaint_department_scope($admin);
        $scopeWhere = str_replace('users_complaint.', 'uc.', $scope['plain_where']);
        $where .= " AND ".$scopeWhere;
    } elseif (stock_is_contractor()) {
        $login_id = (int)$_SESSION['admin'];
        $where .= " AND (uc.engineer='$login_id' OR uc.department='$login_id')";
    } elseif ((stock_is_bso() || stock_role() === 'ge_admin') && $division_id > 0) {
        $where .= " AND uc.b_list IN (SELECT bid FROM building_list WHERE division_id='$division_id')";
    }

    return $where;
}

$holders = stock_report_holder_rows($conn);
$holderFromCondition = stock_report_holder_condition($holders, 'st', 'from');
$holderToCondition = stock_report_holder_condition($holders, 'st', 'to');
$history_where = stock_report_history_where($conn, $holders, $filter_material, $filter_from, $filter_to);
$complaint_used_where = stock_report_complaint_used_filter($conn, $filter_material, $filter_from, $filter_to);
$complaintUsedDateSql = "(CASE WHEN uc.completiondate IS NULL OR CAST(uc.completiondate AS CHAR)='' THEN STR_TO_DATE(uc.date, '%d/%m/%Y') ELSE uc.completiondate END)";
$summaryInHeading = (stock_is_department() || stock_is_contractor()) ? 'Total Stock In' : 'Total Received In BSO';
$summaryInCondition = (stock_is_department() || stock_is_contractor())
    ? $holderToCondition
    : "st.transaction_type='receive' AND st.from_holder_type='external' AND st.to_holder_type='bso' AND $holderToCondition";
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>ECIMS ADMIN | Stock Report</title>
    <?php include('includes/head.php'); ?>
    <style>
        th,td{text-align:center;vertical-align:middle;}
        .text-left{text-align:left!important;}
        @media print{.no-print{display:none;}}
    </style>
</head>
<body>
<div class="container-scroller">
    <div class="horizontal-menu"><?php include('includes/header.php'); ?></div>
    <div class="container-fluid page-body-wrapper">
        <div class="main-panel">
            <div class="content-wrapper">
                <div class="page-header">
                    <h3 class="page-title">Stock Reports</h3>
                    <button type="button" onclick="window.print()" class="btn btn-sm btn-primary no-print">Print</button>
                </div>

                <div class="card grid-margin no-print">
                    <div class="card-body">
                        <h4 class="card-title">Report Filter</h4>
                        <form method="GET">
                            <div class="row">
                                <div class="col-md-3 form-group">
                                    <label>Material</label>
                                    <select name="material_id" class="form-control">
                                        <option value="">All Materials</option>
                                        <?php
                                        $materials = $conn->query(stock_material_select_sql());
                                        while ($mat = $materials->fetch_assoc()) {
                                        ?>
                                            <option value="<?php echo $mat['id']; ?>" <?php echo $filter_material == $mat['id'] ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars(stock_material_label($mat)); ?>
                                            </option>
                                        <?php } ?>
                                    </select>
                                </div>
                                <div class="col-md-3 form-group">
                                    <label>From Date</label>
                                    <input type="date" name="from_date" class="form-control" value="<?php echo $filter_from; ?>">
                                </div>
                                <div class="col-md-3 form-group">
                                    <label>To Date</label>
                                    <input type="date" name="to_date" class="form-control" value="<?php echo $filter_to; ?>">
                                </div>
                                <div class="col-md-3 form-group pt-4">
                                    <button type="submit" class="btn btn-primary">Search</button>
                                    <a href="stockReport.php" class="btn btn-light">Reset</a>
                                </div>
                            </div>
                        </form>
                    </div>
                </div>

                <div class="card grid-margin">
                    <div class="card-body">
                        <h4 class="card-title">Material Stock Summary</h4>
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered">
                                <thead>
                                <tr>
                                    <th>Material</th>
                                    <th>Unit</th>
                                    <th><?php echo $summaryInHeading; ?></th>
                                    <th>Complaint Used</th>
                                    <th>Total Available Stock</th>
                                </tr>
                                </thead>
                                <tbody>
                                <?php
                                $summarySql = "
                                    SELECT
                                        m.m_id,
                                        m.material_name,
                                        m.serial_number AS material_code,
                                        m.s_code,
                                        m.m_unit AS unit,
                                        COALESCE(SUM(CASE WHEN $summaryInCondition THEN st.quantity ELSE 0 END), 0) AS received_qty,
                                        (
                                            SELECT COALESCE(SUM(cum.qty), 0)
                                            FROM complaint_used_material cum
                                            INNER JOIN users_complaint uc ON uc.ucid = cum.ucid
                                            $complaint_used_where
                                            AND cum.material_id = m.m_id
                                        ) AS complaint_used_qty,
                                        COALESCE(SUM(CASE WHEN $holderToCondition THEN st.quantity ELSE 0 END), 0)
                                        - COALESCE(SUM(CASE WHEN $holderFromCondition THEN st.quantity ELSE 0 END), 0) AS available_qty
                                    FROM matrial m
                                    LEFT JOIN stock_transactions st ON st.material_id=m.m_id
                                    ".($filter_material > 0 ? "WHERE m.m_id='$filter_material'" : "")."
                                    GROUP BY m.m_id, m.material_name, m.serial_number, m.s_code, m.m_unit
                                    HAVING received_qty <> 0 OR complaint_used_qty <> 0 OR available_qty <> 0
                                    ORDER BY m.material_name ASC
                                ";
                                $summary = $conn->query($summarySql);
                                if ($summary && $summary->num_rows > 0) {
                                    while ($row = $summary->fetch_assoc()) {
                                ?>
                                    <tr>
                                        <td class="text-left"><?php echo htmlspecialchars(stock_material_label($row, false)); ?></td>
                                        <td><?php echo htmlspecialchars($row['unit']); ?></td>
                                        <td><?php echo stock_report_format_qty($row['received_qty']); ?></td>
                                        <td><?php echo stock_report_format_qty($row['complaint_used_qty']); ?></td>
                                        <td><?php echo stock_report_format_qty($row['available_qty']); ?></td>
                                    </tr>
                                <?php
                                    }
                                } else {
                                ?>
                                    <tr><td colspan="5">No stock found.</td></tr>
                                <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

                <div class="card grid-margin">
                    <div class="card-body">
                        <h4 class="card-title">Holder Wise Stock Position</h4>
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered">
                                <thead>
                                <tr>
                                    <th>Holder</th>
                                    <th>Material</th>
                                    <th>Unit</th>
                                    <th>Stock In</th>
                                    <th>Transfer / Return Out</th>
                                    <th>Complaint Used</th>
                                    <th>Pending Stock</th>
                                </tr>
                                </thead>
                                <tbody>
                                <?php
                                $printedRows = 0;
                                $materials = $conn->query(stock_material_select_sql($filter_material > 0 ? "m_id='$filter_material'" : ""));
                                while ($materials && $mat = $materials->fetch_assoc()) {
                                    foreach ($holders as $holder) {
                                        $type = mysqli_real_escape_string($conn, $holder['type']);
                                        $id = (int)$holder['id'];
                                        $material_id = (int)$mat['id'];

                                        $inQ = $conn->query("
                                            SELECT COALESCE(SUM(quantity), 0) AS qty
                                            FROM stock_transactions
                                            WHERE material_id='$material_id'
                                            AND to_holder_type='$type'
                                            AND to_holder_id='$id'
                                        ");
                                        $outQ = $conn->query("
                                            SELECT COALESCE(SUM(quantity), 0) AS qty
                                            FROM stock_transactions
                                            WHERE material_id='$material_id'
                                            AND from_holder_type='$type'
                                            AND from_holder_id='$id'
                                            AND to_holder_type!='consumed'
                                        ");
                                        $usedQ = $conn->query("
                                            SELECT COALESCE(SUM(quantity), 0) AS qty
                                            FROM stock_transactions
                                            WHERE material_id='$material_id'
                                            AND from_holder_type='$type'
                                            AND from_holder_id='$id'
                                            AND to_holder_type='consumed'
                                        ");

                                        $stockIn = $inQ ? (float)$inQ->fetch_assoc()['qty'] : 0;
                                        $transferOut = $outQ ? (float)$outQ->fetch_assoc()['qty'] : 0;
                                        $complaintUsed = $usedQ ? (float)$usedQ->fetch_assoc()['qty'] : 0;
                                        $pending = $stockIn - $transferOut - $complaintUsed;

                                        if ($stockIn == 0 && $transferOut == 0 && $complaintUsed == 0 && $pending == 0) {
                                            continue;
                                        }

                                        $printedRows++;
                                ?>
                                    <tr>
                                        <td class="text-left"><?php echo htmlspecialchars($holder['label']); ?></td>
                                        <td class="text-left"><?php echo htmlspecialchars(stock_material_label($mat, false)); ?></td>
                                        <td><?php echo htmlspecialchars($mat['unit']); ?></td>
                                        <td><?php echo stock_report_format_qty($stockIn); ?></td>
                                        <td><?php echo stock_report_format_qty($transferOut); ?></td>
                                        <td><?php echo stock_report_format_qty($complaintUsed); ?></td>
                                        <td><?php echo stock_report_format_qty($pending); ?></td>
                                    </tr>
                                <?php
                                    }
                                }

                                if ($printedRows == 0) {
                                ?>
                                    <tr><td colspan="7">No holder stock found.</td></tr>
                                <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

                <div class="card grid-margin">
                    <div class="card-body">
                        <h4 class="card-title">Stock In / Transfer Details</h4>
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered">
                                <thead>
                                <tr>
                                    <th>Date</th>
                                    <th>Material</th>
                                    <th>Quantity</th>
                                    <th>Movement</th>
                                    <th>From</th>
                                    <th>To</th>
                                    <th>Reference</th>
                                    <th>Remarks</th>
                                    <th>Created By</th>
                                </tr>
                                </thead>
                                <tbody>
                                <?php
                                $movementWhere = $history_where." AND st.to_holder_type!='consumed'";
                                $history = $conn->query("
                                    SELECT st.*, sm.material_name, sm.serial_number AS material_code, sm.s_code, sm.m_unit AS unit, a.name AS created_by_name
                                    FROM stock_transactions st
                                    INNER JOIN matrial sm ON sm.m_id = st.material_id
                                    LEFT JOIN admin a ON a.id = st.created_by
                                    $movementWhere
                                    ORDER BY st.id DESC
                                    LIMIT 500
                                ");
                                if ($history && $history->num_rows > 0) {
                                    while ($row = $history->fetch_assoc()) {
                                        $movement = $row['transaction_type'] === 'receive' ? 'Received In BSO' : 'Stock Transfer / Return';
                                ?>
                                    <tr>
                                        <td><?php echo date('d/m/Y H:i', strtotime($row['created_at'])); ?></td>
                                        <td class="text-left"><?php echo htmlspecialchars(stock_material_label($row, false)); ?></td>
                                        <td><?php echo stock_report_format_qty($row['quantity']).' '.$row['unit']; ?></td>
                                        <td><?php echo $movement; ?></td>
                                        <td><?php echo stock_holder_label($conn, $row['from_holder_type'], $row['from_holder_id']); ?></td>
                                        <td><?php echo stock_holder_label($conn, $row['to_holder_type'], $row['to_holder_id']); ?></td>
                                        <td><?php echo htmlspecialchars($row['reference_no']); ?></td>
                                        <td><?php echo htmlspecialchars($row['remarks']); ?></td>
                                        <td><?php echo htmlspecialchars($row['created_by_name']); ?></td>
                                    </tr>
                                <?php }} else { ?>
                                    <tr><td colspan="9">No transfer records found.</td></tr>
                                <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

                <div class="card grid-margin">
                    <div class="card-body">
                        <h4 class="card-title">Complaint Used Material Details</h4>
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered">
                                <thead>
                                <tr>
                                    <th>Date</th>
                                    <th>Material</th>
                                    <th>Unit</th>
                                    <th>Used Qty</th>
                                    <th>Used From</th>
                                    <th>Complaint</th>
                                    <th>Reference</th>
                                    <th>Remarks</th>
                                </tr>
                                </thead>
                                <tbody>
                                <?php
                                $usedWhere = $complaint_used_where;
                                $used = $conn->query("
                                    SELECT
                                        cum.*,
                                        sm.material_name,
                                        sm.serial_number AS material_code,
                                        sm.s_code,
                                        sm.m_unit AS unit,
                                        uc.docketNumber,
                                        uc.date AS complaint_date,
                                        uc.time AS complaint_time,
                                        uc.completiondate,
                                        st.from_holder_type,
                                        st.from_holder_id,
                                        st.reference_no,
                                        st.remarks
                                    FROM complaint_used_material cum
                                    INNER JOIN users_complaint uc ON uc.ucid = cum.ucid
                                    INNER JOIN matrial sm ON sm.m_id = cum.material_id
                                    LEFT JOIN stock_transactions st ON st.reference_no = CONCAT('COMPLAINT-', cum.ucid)
                                        AND st.material_id = cum.material_id
                                        AND st.to_holder_type = 'consumed'
                                        AND st.from_holder_type IN ('bso','department','contractor')
                                    $usedWhere
                                    ORDER BY $complaintUsedDateSql DESC, cum.ucid DESC
                                    LIMIT 500
                                ");
                                if ($used && $used->num_rows > 0) {
                                    while ($row = $used->fetch_assoc()) {
                                        $usedDate = !empty($row['completiondate'])
                                            ? date('d/m/Y H:i', strtotime($row['completiondate']))
                                            : trim(($row['complaint_date'] ?? '').' '.($row['complaint_time'] ?? ''));
                                        $usedFrom = !empty($row['from_holder_type'])
                                            ? stock_holder_label($conn, $row['from_holder_type'], $row['from_holder_id'])
                                            : 'Complaint Entry';
                                        $referenceNo = !empty($row['reference_no']) ? $row['reference_no'] : 'COMPLAINT-'.$row['ucid'];
                                ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($usedDate); ?></td>
                                        <td class="text-left"><?php echo htmlspecialchars(stock_material_label($row, false)); ?></td>
                                        <td><?php echo htmlspecialchars($row['unit']); ?></td>
                                        <td><?php echo stock_report_format_qty($row['qty']); ?></td>
                                        <td><?php echo htmlspecialchars($usedFrom); ?></td>
                                        <td><?php echo 'Complaint #'.htmlspecialchars($row['docketNumber'] ?: $row['ucid']); ?></td>
                                        <td><?php echo htmlspecialchars($referenceNo); ?></td>
                                        <td><?php echo htmlspecialchars($row['remarks']); ?></td>
                                    </tr>
                                <?php }} else { ?>
                                    <tr><td colspan="8">No complaint used material found.</td></tr>
                                <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <?php include('includes/footer.php'); ?>
        </div>
    </div>
</div>
<?php include('includes/foot.php'); ?>
</body>
</html>
