<?php 
    require("../connection.php");
    include("../user.php");
    $user_id = $_SESSION['user'];
    $username = $_SESSION['user_name'];
    $branch_id = $_SESSION['branch_id'];
    $ho_access = $_SESSION['ha'];
    $branch = $_SESSION['division_name'];

    $from_date = isset($_POST['from_date']) ? $_POST['from_date'] : date('Y-m-01');
    $to_date = isset($_POST['to_date']) ? $_POST['to_date'] : date('Y-m-d');

    $daysInMonth = [];
    $start = new DateTime($from_date);
    $end = new DateTime($to_date);
    while ($start <= $end) {
        $date = $start->format('Y-m-d');
        $display = $start->format('d-m-Y');
        $daysInMonth[] = [$date => $display];
        $start->modify('+1 day');
    }

    // Build inward array for day-wise display
    $inward_array = [];
    $inward_sql = "SELECT po_date, material_name, SUM(quantity) as qty 
                   FROM item_master 
                   WHERE po_date BETWEEN '$from_date' AND '$to_date' 
                   GROUP BY material_name, po_date";
    $inward_data = mysqli_query($con, $inward_sql);
    while($inward = mysqli_fetch_array($inward_data)) {
        $inward_array[$inward['material_name']][$inward['po_date']] = $inward['qty'];
    }
?>

<table id="example" class="table table-striped table-bordered">
    <thead>
        <tr>
            <th>S.No</th>
            <th>MATERIAL NAME</th>
            <th>CARRY FORWARD STOCK</th>
            <th>MONTHLY INWARD STOCK</th>
            <th>TOTAL STOCK</th>
            <th>MEASUREMENT TYPE</th>
            <?php foreach($daysInMonth as $value){ 
                foreach($value as $day_key => $date){ ?>
                    <th><?php echo $date ?></th>
            <?php }} ?>
            <th>TOTAL GIVEN</th>
            <th>CLOSING BALANCE</th>
        </tr>
    </thead>
    <tbody>
    <?php 
    if($ho_access == "yes") {
        $sql = "
            SELECT
                cm.id,
                cm.category_type,
                ms.name AS measurement_type,
                
                COALESCE(SUM(CASE WHEN im.po_date < '$from_date' THEN im.quantity ELSE 0 END), 0) AS carry_stock,
                COALESCE(SUM(CASE WHEN im.po_date BETWEEN '$from_date' AND '$to_date' THEN im.quantity ELSE 0 END), 0) AS inward_stock,
                
                COALESCE(outward.old_outward, 0) AS old_outward,
                COALESCE(outward.current_outward, 0) AS current_outward

            FROM category_master cm
            LEFT JOIN item_master im ON im.material_name = cm.id
            LEFT JOIN measurement_master ms ON cm.measurement = ms.id

            LEFT JOIN (
                SELECT
                    material_name,
                    SUM(CASE WHEN so_date < '$from_date' THEN qty ELSE 0 END) AS old_outward,
                    SUM(CASE WHEN so_date BETWEEN '$from_date' AND '$to_date' THEN qty ELSE 0 END) AS current_outward
                FROM outward_master
                GROUP BY material_name
            ) outward ON outward.material_name = cm.id

            GROUP BY cm.id
        ";

        $result = mysqli_query($con, $sql);
        $s = 1;
        while($row = mysqli_fetch_array($result)) {
            $carry_stock = $row['carry_stock'];
            if($carry_stock > 0 && ($row['old_outward'] < $carry_stock)) {
                $carry_stock = $carry_stock - $row['old_outward'];
            }
            $total_stock = $carry_stock + $row['inward_stock'];
            ?>
            <tr>
                <td><?php echo $s++; ?></td>
                <td><?php echo $row['category_type']; ?></td>
                <td><?php echo $carry_stock; ?></td>
                <td><?php echo $row['inward_stock']; ?></td>
                <td><?php echo $total_stock; ?></td>
                <td><?php echo $row['measurement_type']; ?></td>
                <?php 
                foreach($daysInMonth as $value){
                    foreach($value as $day_key => $date){
                        $qty = 0;
                        if(array_key_exists($row['id'], $inward_array)) {
                            if(array_key_exists($day_key, $inward_array[$row['id']])) {
                                $qty = $inward_array[$row['id']][$day_key];
                            }
                        }
                        if ($qty > 0) {
                            echo "<td style='background-color: palegreen; font-weight: bold;'>$qty</td>";
                        } else {
                            echo "<td>0</td>";
                        }
                    }
                }
                ?>
                <td><?php echo $row['current_outward']; ?></td>
                <td><?php echo $total_stock - $row['current_outward']; ?></td>
            </tr>
        <?php }

    } else {
        // For branch users
        $move = mysqli_query($con,
            "SELECT st.transaction_id, st.date, sd.category, sd.category_description,
                    lm.location as from_location, st.flag, st.status,
                    st.no_of_item as quantity,
                    CASE WHEN st.STATUS=0 THEN 'In Transit' ELSE 'Delivered' END as sta,
                    st.to_division, st.from_division
             FROM stock_transfer st
             JOIN stock_item_detail sd ON st.transaction_id=sd.transaction_id
             JOIN location_master lm ON st.from_location=lm.id
             WHERE st.branch_id='$branch_id'
             AND st.date BETWEEN '$from_date' AND '$to_date'");
        $s = 1;
        while($type = mysqli_fetch_array($move)) { ?>
            <tr>
                <td><?php echo $s++; ?></td>
                <td><?php echo $type['transaction_id']; ?></td>
                <td><?php echo $type['category'].' - '.$type['category_description']; ?></td>
                <td><?php echo date("d-m-Y", strtotime($type['date'])); ?></td>
                <td><?php echo $type['from_location']; ?></td>
                <td><?php echo $type['from_division']; ?></td>
                <td><?php echo $type['to_division']; ?></td>
                <td style="color:<?php echo ($type['status'] == 0) ? 'red' : 'green'; ?>;">
                    <?php echo ($type['status'] == 0) ? 'IN TRANSIT' : 'RECEIVED'; ?>
                </td>
            </tr>
        <?php }
    }
    ?>
    </tbody>
</table>
