<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
require("includes/config.php");
require("includes/function.php");

$formname = 'Product Stock Report';
$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

// ── Ensure transacn exists ─────────────────────────────────────────────────
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS `transacn` (
    `srl` INT(11) NOT NULL AUTO_INCREMENT,
    `vouchdt` DATE NULL,
    `source` VARCHAR(10) NOT NULL DEFAULT '',
    `vouchno` INT(11) NOT NULL DEFAULT 0,
    `godown` INT(11) NOT NULL DEFAULT 0,
    `product` INT(11) NOT NULL DEFAULT 0,
    `qty` DOUBLE NOT NULL DEFAULT 0,
    `cost` DOUBLE NOT NULL DEFAULT 0,
    `adduser` VARCHAR(50) NOT NULL DEFAULT '',
    `adddate` DATE NULL,
    `addtime` TIME NULL,
    PRIMARY KEY (`srl`),
    KEY `vouchno` (`vouchno`),
    KEY `product` (`product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
// ── Migrate any missing columns on transacn ──────────────────────────────
// First: neutralise any pre-existing column with an invalid strict-mode default
// (e.g. a DATE/DATETIME NOT NULL with DEFAULT '0000-00-00' from an older schema)
$_fixRes = mysqli_query($conn, "SHOW COLUMNS FROM `transacn`");
if ($_fixRes) {
    while ($_fc = mysqli_fetch_assoc($_fixRes)) {
        $t = strtolower($_fc['Type'] ?? '');
        $d = $_fc['Default'] ?? '';
        $n = $_fc['Null'] ?? 'YES';
        // DATE/DATETIME NOT NULL with zero-date default → change to NULL DEFAULT NULL
        if (in_array($t, ['date', 'datetime']) && $n === 'NO' && ($d === '0000-00-00' || $d === '0000-00-00 00:00:00')) {
            @mysqli_query($conn, "ALTER TABLE `transacn` MODIFY COLUMN `{$_fc['Field']}` $t NULL DEFAULT NULL");
        }
    }
}
foreach ([
    'godown' => "INT(11) NOT NULL DEFAULT 0",
    'product' => "INT(11) NOT NULL DEFAULT 0",
    'cost' => "DOUBLE NOT NULL DEFAULT 0",
    'adduser' => "VARCHAR(50) NOT NULL DEFAULT ''",
    'adddate' => "DATE NULL",
    'addtime' => "TIME NULL",
] as $_col => $_def) {
    $chk = mysqli_query($conn, "SHOW COLUMNS FROM `transacn` LIKE '$_col'");
    if ($chk && mysqli_num_rows($chk) == 0) {
        try {
            mysqli_query($conn, "ALTER TABLE `transacn` ADD COLUMN `$_col` $_def");
        } catch (Exception $e) { /* column may already exist or strict-mode conflict — skip */
        }
    }
}

$godown = isset($_POST['godown']) ? (int) $_POST['godown'] : 0;
$search = isset($_POST['search']) ? (int) $_POST['search'] : 0;
$source = isset($_POST['source']) ? trim($_POST['source']) : '';
$dtype = isset($_POST['dtype']) ? trim($_POST['dtype']) : '1';
$fdate = isset($_POST['fdate']) ? trim($_POST['fdate']) : '';
$tdate = isset($_POST['tdate']) ? trim($_POST['tdate']) : '';
$showvalue = (isset($_POST['showvalue']) && $_POST['showvalue'] === 'Y') ? true : false;
$valueby = (isset($_POST['valueby']) && $_POST['valueby'] === 'rate') ? 'rate' : 'cost';
$valueCol = ($valueby === 'rate') ? 'p.amount' : 'p.cost'; // product master: cost=Cost, amount=Rate
$priceLabel = ($valueby === 'rate') ? 'Rate' : 'Cost'; // column header for the unit price itself

if ($fdate == '') {
    echo "<script>alert('From Date cannot be empty');window.history.back();</script>";
    exit;
}
if ($tdate == '') {
    echo "<script>alert('To Date cannot be empty');window.history.back();</script>";
    exit;
}

$godowncond = ($godown > 0) ? " AND t.godown='$godown' " : '';
$sourcecond = ($source != '') ? " AND t.source='" . mysqli_real_escape_string($conn, $source) . "' " : '';
$searchcond = ($search > 0) ? " AND t.product='$search' " : '';

function pf($v)
{
    $v = (float) $v;
    return $v == 0 ? '' : number_format($v, 2);
}

$sourceLabel = ['PUR' => 'Purchase', 'IMP' => 'Purchase Import', 'DSP' => 'Dispatch', 'SLTX' => 'Sale GST', 'SL' => 'Sale', 'SR' => 'Sale Return', 'INW' => 'Inward', 'OTW' => 'Outward', 'DMG' => 'Damage'];

include("includes/linkshead.php");
?>
<style>
    body {
        background: #f0f2f5;
        margin: 0;
        padding: 12px 16px;
        font-family: inherit;
    }

    .rpt-wrap {
        background: #fff;
        border-radius: 10px;
        box-shadow: 0 1px 3px rgba(0, 0, 0, .08);
        overflow: hidden;
        max-width: 1100px;
        margin: 0 auto;
    }

    .rpt-header {
        background: #1e293b;
        color: #fff;
        padding: 14px 20px;
        display: flex;
        align-items: center;
        justify-content: space-between;
    }

    .rpt-header h2 {
        font-size: 15px;
        font-weight: 700;
        margin: 0;
    }

    .rpt-params {
        font-size: 11px;
        color: #94a3b8;
        margin-top: 4px;
    }

    .rpt-tbl {
        width: 100%;
        border-collapse: collapse;
        font-size: 12.5px;
    }

    .rpt-tbl thead th {
        background: #f8fafc;
        color: #475569;
        font-weight: 700;
        font-size: 11px;
        text-transform: uppercase;
        letter-spacing: .04em;
        padding: 9px 12px;
        border-bottom: 2px solid #e2e8f0;
        text-align: left;
        white-space: nowrap;
    }

    .rpt-tbl thead th.num {
        text-align: right;
    }

    .rpt-tbl tbody td {
        padding: 9px 12px;
        border-bottom: 1px solid #f1f5f9;
        color: #334155;
        vertical-align: top;
    }

    .rpt-tbl tbody td.num {
        text-align: right;
        font-variant-numeric: tabular-nums;
    }

    .rpt-tbl tbody tr:hover {
        background: #f8fafc;
    }

    .rpt-tbl tfoot td {
        font-weight: 700;
        background: #f1f5f9;
        border-top: 2px solid #e2e8f0;
        padding: 9px 12px;
        text-align: right;
    }

    .src-badge {
        display: inline-block;
        padding: 2px 8px;
        border-radius: 12px;
        font-size: 10.5px;
        font-weight: 700;
    }

    .src-PUR {
        background: #dcfce7;
        color: #166534;
    }

    /* Green */
    .src-IMP {
        background: #d9f99d;
        color: #3f6212;
    }

    /* Light Olive Green */

    .src-DSP {
        background: #fef3c7;
        color: #92400e;
    }

    /* Yellow */

    .src-SLTX {
        background: #dbeafe;
        color: #1d4ed8;
    }

    /* Light Blue */
    .src-SL {
        background: #e0f2fe;
        color: #0369a1;
    }

    /* Sky Blue */
    .src-SR {
        background: #eff6ff;
        color: #1e3a8a;
    }

    /* Very Light Blue */

    .src-INW {
        background: #dbeafe;
        color: #1e40af;
    }

    /* Existing */
    .src-OTW {
        background: #fee2e2;
        color: #b91c1c;
    }

    /* Existing */
    .src-DMG {
        background: #ffedd5;
        color: #9a3412;
    }

    /* Existing */
    s .neg {
        color: #dc2626;
    }

    .pos {
        color: #16a34a;
    }

    .no-data {
        text-align: center;
        padding: 60px 20px;
        color: #94a3b8;
    }

    @media print {
        body {
            background: #fff;
        }

        .rpt-header {
            background: #1e293b !important;
            -webkit-print-color-adjust: exact;
        }
    }
</style>
</head>

<body>
    <div class="rpt-wrap">
        <div class="rpt-header">
            <div>
                <h2><i class="fa fa-boxes" style="margin-right:8px;"></i><?php echo $formname; ?></h2>
                <div class="rpt-params">
                    From: <?php echo date('d-m-Y', strtotime($fdate)); ?>
                    &nbsp;|&nbsp; To: <?php echo date('d-m-Y', strtotime($tdate)); ?>
                    <?php if ($godown > 0):
                        $gn = mysqli_fetch_assoc(mysqli_query($conn, "SELECT name FROM godown WHERE code='$godown'")); ?>
                        &nbsp;|&nbsp; Godown: <?php echo htmlspecialchars($gn['name'] ?? ''); ?>
                    <?php endif; ?>
                    <?php if ($source != ''): ?>&nbsp;|&nbsp; Source:
                        <?php echo htmlspecialchars($sourceLabel[$source] ?? $source); ?><?php endif; ?>
                    <?php if ($showvalue): ?>&nbsp;|&nbsp; Value By:
                        <?php echo $valueby === 'rate' ? 'Rate' : 'Cost'; ?><?php endif; ?>
                </div>
            </div>
            <div style="display:flex;gap:8px;">
                <button onclick="window.print()"
                    style="background:#2563eb;color:#fff;border:none;border-radius:6px;padding:7px 16px;font-size:12px;cursor:pointer;"><i
                        class="fa fa-print"></i> Print</button>
                <button
                    onclick="if(window.parent&&window.parent!==window){window.parent.document.getElementById('rptframe').src='about:blank';}else{window.close();}"
                    style="background:#f1f5f9;color:#374151;border:none;border-radius:6px;padding:7px 16px;font-size:12px;font-weight:600;cursor:pointer;"><i
                        class="fa fa-arrow-left"></i> Go Back</button>
            </div>
        </div>

        <?php
        if ($dtype == '1') {
            // ── SUMMARY: one row per product+godown, opening/in/out/closing ──────────
            $sql = "SELECT t.product, t.godown, p.name AS prodname, g.name AS godownname, $valueCol AS unitprice,
                   SUM(CASE WHEN t.vouchdt < '$fdate' THEN t.qty ELSE 0 END) AS opening,
                   SUM(CASE WHEN t.vouchdt >= '$fdate' AND t.vouchdt <= '$tdate' AND t.qty > 0 THEN t.qty ELSE 0 END) AS totin,
                   SUM(CASE WHEN t.vouchdt >= '$fdate' AND t.vouchdt <= '$tdate' AND t.qty < 0 THEN -t.qty ELSE 0 END) AS totout,
                   SUM(CASE WHEN t.vouchdt <= '$tdate' THEN t.qty ELSE 0 END) AS closing
            FROM `transacn` t
            LEFT JOIN `product` p ON p.id = t.product
            LEFT JOIN `godown` g ON g.code = t.godown
            WHERE 1=1 $godowncond $sourcecond $searchcond
            GROUP BY t.product, t.godown
            HAVING (totin>0 OR totout>0 OR ABS(opening)>0.001 OR ABS(closing)>0.001)
            ORDER BY p.name ASC, g.name ASC";
            $res = mysqli_query($conn, $sql) or die(mysqli_error($conn));
            $rows = [];
            while ($r = mysqli_fetch_assoc($res))
                $rows[] = $r;

            if (!$rows) {
                echo '<div class="no-data"><i class="fa fa-box-open" style="font-size:34px;display:block;margin-bottom:10px;color:#cbd5e1;"></i>No stock movements found for the selected period.</div>';
            } else {
                $totOpening = $totIn = $totOut = $totClosing = $totValue = 0;
                echo '<table class="rpt-tbl"><thead><tr>
            <th>#</th>
            <th>Product</th>
            <th>Godown</th>
            <th class="num">Opening</th>
            <th class="num">In (Purchases)</th>
            <th class="num">Out (Dispatch/Sale)</th>
            <th class="num">Closing</th>'
                    . ($showvalue ? '<th class="num">' . htmlspecialchars($priceLabel) . '</th><th class="num">Value</th>' : '') . '
        </tr></thead><tbody>';
                $i = 0;
                foreach ($rows as $r) {
                    $i++;
                    $cl = (float) $r['closing'];
                    $val = $cl * (float) $r['unitprice'];
                    echo '<tr>
                <td style="color:#94a3b8;font-size:11px;">' . $i . '</td>
                <td><b>' . htmlspecialchars($r['prodname'] ?: ('Product #' . $r['product'])) . '</b></td>
                <td>' . htmlspecialchars($r['godownname'] ?: '—') . '</td>
                <td class="num">' . pf($r['opening']) . '</td>
                <td class="num pos">' . pf($r['totin']) . '</td>
                <td class="num neg">' . pf($r['totout']) . '</td>
                <td class="num ' . ($cl < 0 ? 'neg' : '') . '" style="font-weight:700;">' . pf($cl) . '</td>'
                        . ($showvalue ? '<td class="num">' . pf($r['unitprice']) . '</td><td class="num">' . pf($val) . '</td>' : '') . '
            </tr>';
                    $totOpening += (float) $r['opening'];
                    $totIn += (float) $r['totin'];
                    $totOut += (float) $r['totout'];
                    $totClosing += $cl;
                    $totValue += $val;
                }
                echo '</tbody><tfoot><tr>
            <td colspan="3" style="text-align:right;font-weight:700;background:#f1f5f9;padding:9px 12px;border-top:2px solid #e2e8f0;">Total</td>
            <td>' . pf($totOpening) . '</td>
            <td class="pos">' . pf($totIn) . '</td>
            <td class="neg">' . pf($totOut) . '</td>
            <td style="font-weight:700;' . ($totClosing < 0 ? 'color:#dc2626;' : '') . '">' . pf($totClosing) . '</td>'
                    . ($showvalue ? '<td></td><td style="font-weight:700;">' . pf($totValue) . '</td>' : '') . '
        </tr></tfoot></table>';
            }

        } else {
            // ── DETAIL: every row, with a running Balance per Product+Godown ──────────
            // Godown added as a secondary sort key (alongside Product) so that when a
            // product moves through more than one godown, each godown's rows stay
            // grouped together instead of interleaving by date — otherwise a running
            // balance across them would be meaningless.
            $sql = "SELECT t.*, p.name AS prodname, g.name AS godownname, $valueCol AS unitprice
            FROM `transacn` t
            LEFT JOIN `product` p ON p.id = t.product
            LEFT JOIN `godown` g ON g.code = t.godown
            WHERE t.vouchdt >= '$fdate' AND t.vouchdt <= '$tdate' $godowncond $sourcecond $searchcond
            ORDER BY p.name ASC, g.name ASC, t.vouchdt ASC, t.srl ASC";
            $res = mysqli_query($conn, $sql) or die(mysqli_error($conn));
            $rows = [];
            while ($r = mysqli_fetch_assoc($res))
                $rows[] = $r;

            if (!$rows) {
                echo '<div class="no-data"><i class="fa fa-box-open" style="font-size:34px;display:block;margin-bottom:10px;color:#cbd5e1;"></i>No stock movements found for the selected period.</div>';
            } else {
                // Opening balance per Product+Godown key present in this result set —
                // true historical balance (not restricted by the Source filter), same
                // convention as the Summary view's Opening column, so Balance here
                // reconciles with it.

                $openingBal = [];
                foreach ($rows as $r) {
                    $key = $r['product'] . '|' . $r['godown'];
                    if (!isset($openingBal[$key])) {
                        $opRes = mysqli_query($conn, "SELECT COALESCE(SUM(`qty`),0) AS s FROM `transacn`
                                               WHERE `product`='{$r['product']}' AND `godown`='{$r['godown']}' AND `vouchdt` < '$fdate'");
                        $opRow = $opRes ? mysqli_fetch_assoc($opRes) : null;
                        $openingBal[$key] = $opRow ? (float) $opRow['s'] : 0;
                    }
                }

                echo '<table class="rpt-tbl"><thead><tr>
                        <th>#</th>
                        <th>Date</th>
                        <th>Source</th>
                        <th>Voucher #</th>
                        <th>Product</th>
                        <th>Godown</th>
                        <th class="num">Qty In</th>
                        <th class="num">Qty Out</th>
                        <th class="num">Balance</th>'
                        . ($showvalue ? '<th class="num">Unit ' . htmlspecialchars($priceLabel) . '</th><th class="num">Value</th>' : '') . '
                      </tr></thead><tbody>';

                $i = 0;
                $running = 0;
     
                $grpIn = 0;
                $grpOut = 0;
                $grpBalance = 0;
                $grprowValue = 0;

                $lastKey = null;
                foreach ($rows as $r) {
                    $i++;
                    $key = $r['product'] . '|' . $r['godown'];
                    $newGroup = ($key !== $lastKey);

                    if ($newGroup) {
                        $running = $openingBal[$key];
                        $lastKey = $key;
                    }

                    if ($newGroup && $lastKey !== null) {

                        echo '<tr style="background:#f8fafc;font-weight:700;border-top:2px solid #cbd5e1;">
                        <td colspan="6" style="text-align:right;">Group Total</td>
                        <td class="num pos">' . pf($grpIn) . '</td>
                        <td class="num neg">' . pf($grpOut) . '</td>
                        <td class="num">' . pf($grpBalance) . '</td>'
                            . ($showvalue ? '<td></td><td>' . pf($grprowValue) . '</td>' : '') . '
                        </tr>';

                        $grpIn = 0;
                        $grpOut = 0;
                        $grprowValue = 0;
                    }


                    $qty = (float) $r['qty'];

                    $qIn = $qty > 0 ? $qty : 0;
                    $qOut = $qty < 0 ? abs($qty) : 0;
                    $running += $qty;
                    $rowValue = $running * (float) $r['unitprice'];
                    $src = $r['source'];
                    $rowTop = $newGroup ? ' style="border-top:2px solid #e2e8f0;"' : '';

                    $grpIn += $qIn;
                    $grpOut += $qOut;
                    $grpBalance = $running;
                    $grprowValue += $rowValue;


                    echo '<tr' . $rowTop . '>
                            <td style="color:#94a3b8;font-size:11px;">' . $i . '</td>
                            <td>' . ($r['vouchdt'] && $r['vouchdt'] != '0000-00-00' ? date('d-m-Y', strtotime($r['vouchdt'])) : '') . '</td>
                            <td><span class="src-badge src-' . htmlspecialchars($src) . '">' . htmlspecialchars($sourceLabel[$src] ?? $src) . '</span></td>
                            <td style="color:#475569;font-weight:600;">#' . (int) $r['vouchno'] . '</td>
                            <td>' . htmlspecialchars($r['prodname'] ?? ('Product #' . $r['product'])) . '</td>
                            <td>' . htmlspecialchars($r['godownname'] ?? '—') . '</td>
                            <td class="num pos">' . ($qIn ? pf($qIn) : '') . '</td>
                            <td class="num neg">' . ($qOut ? pf($qOut) : '') . '</td>
                            <td class="num" style="font-weight:700;' . ($running < 0 ? 'color:#dc2626;' : '') . '">' . pf($running) . '</td>'
                        . ($showvalue ? '<td class="num">' . pf($r['unitprice']) . '</td><td class="num">' . pf($rowValue) . '</td>' : '') . '
                         </tr>';
                }

                echo '<tr style="background:#f8fafc;font-weight:700;border-top:2px solid #cbd5e1;">
                      <td colspan="6" style="text-align:right;">Group Total</td>
                      <td class="num pos">' . pf($grpIn) . '</td>
                      <td class="num neg">' . pf($grpOut) . '</td>
                      <td class="num">' . pf($grpBalance) . '</td>'
                    . ($showvalue ? '<td></td><td class="num">' . pf($grprowValue) . '</td>' : '') . '
                </tr>';
                echo '</tbody></table>';
            }
        }
        ?>

    </div>

    <?php include("includes/linksfoot.php"); ?>
</body>

</html>