<?php
include("db/cn.php");
include("common/tx.php");
ob_start();
if (isset($_SESSION['user_name']) && isset($_SESSION['user_role']) && isset($_SESSION['outlet_name']) && isset($_SESSION['outlet_address'])) {
    $userName = $_SESSION['user_name'];
    $userRole = $_SESSION['user_role'];
    $outlet_address = $_SESSION['outlet_address'];
    $outlet_name = $_SESSION['outlet_name'];
}
ob_end_flush();

$transactions = [];
$totalBalance = 0;
$supplierName = '';
$startDate = '';
$endDate = '';
$openingBalance = 0;
$openingBalanceDate = '';

if (isset($_POST['filter'])) {
    $supplierName = $_POST['supplier'];
    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];

    function isValidDate($dateString)
    {
        if (empty($dateString) || $dateString == '0000-00-00 00:00:00' || $dateString == '0000-00-00' || $dateString == '00-00-0000') {
            return false;
        }
        $timestamp = strtotime($dateString);
        if ($timestamp === false || $dateString == '1970-01-01' || $timestamp == 0) {
            return false;
        }
        return true;
    }

    function formatDateForDisplay($dateString)
    {
        if (isValidDate($dateString)) {
            return date("d-m-Y", strtotime($dateString));
        }
        return '';
    }

    // Supplier opening balance
    $query = "SELECT open_amount AS amount, date_time 
              FROM supplier_opening_amount 
              WHERE supplier_name = '$supplierName' 
              AND date_time BETWEEN '$startDate' AND '$endDate'";
    $result = mysqli_query($connection, $query);
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        $openingBalance = $row['amount'];
        $openingBalanceDate = $row['date_time'];

        if (isValidDate($openingBalanceDate)) {
            $totalBalance += $openingBalance;
            $transactions[] = [
                'date' => formatDateForDisplay($openingBalanceDate),
                'description' => 'Opening Balance',
                'product_detail' => '',
                'inv_id' => '',
                'amount' => $openingBalance,
                'type' => 'balance'
            ];
        }
    }

    // ✅ Credit purchases (with product detail + invoice)
    $query = "SELECT inv_id, GROUP_CONCAT(CONCAT(product_name, ' (Qty = ', product_quantity, ')') SEPARATOR ', ') AS product_detail, 
                     discount_amount AS amount, date_time 
              FROM purchase 
              WHERE customer_name = '$supplierName' 
              AND paid_by = 'Credit' 
              AND status = '' 
              AND r_inv_id = '' 
              AND date_time BETWEEN '$startDate' AND '$endDate'
              GROUP BY inv_id";
    $result = mysqli_query($connection, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        $date_time = $row['date_time'];
        if (isValidDate($date_time)) {
            $transactions[] = [
                'date' => formatDateForDisplay($date_time),
                'description' => 'Credit Purchase',
                'product_detail' => $row['product_detail'],
                'inv_id' => $row['inv_id'],
                'amount' => $row['amount'],
                'type' => 'debit'
            ];
            $totalBalance += $row['amount'];
        }
    }

    // Payments to supplier
    $query = "SELECT supplier_amount AS amount, date_time 
              FROM supplier_paid 
              WHERE supplier_name = '$supplierName' 
              AND date_time BETWEEN '$startDate' AND '$endDate'";
    $result = mysqli_query($connection, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        $date_time = $row['date_time'];
        if (isValidDate($date_time)) {
            $transactions[] = [
                'date' => formatDateForDisplay($date_time),
                'description' => 'Payment to Supplier',
                'product_detail' => '',
                'inv_id' => '',
                'amount' => $row['amount'],
                'type' => 'credit'
            ];
            $totalBalance -= $row['amount'];
        }
    }

    // ✅ Purchase returns (with product detail + invoice)
    $query = "SELECT r_inv_id AS inv_id, GROUP_CONCAT(CONCAT(product_name, ' (', product_quantity, ')') SEPARATOR ', ') AS product_detail, 
                     discount_amount AS amount, date_time 
              FROM purchase 
              WHERE customer_name = '$supplierName' 
              AND paid_by = 'Credit'  
              AND status = 'return' 
              AND date_time BETWEEN '$startDate' AND '$endDate'
              GROUP BY r_inv_id";
    $result = mysqli_query($connection, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        $date_time = $row['date_time'];
        if (isValidDate($date_time)) {
            $transactions[] = [
                'date' => formatDateForDisplay($date_time),
                'description' => 'Purchase Return',
                'product_detail' => $row['product_detail'],
                'inv_id' => $row['inv_id'],
                'amount' => $row['amount'],
                'type' => 'credit'
            ];
            $totalBalance -= $row['amount'];
        }
    }

    // Sort by date
    usort($transactions, function ($a, $b) {
        return strtotime($a['date']) - strtotime($b['date']);
    });
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title><?php echo $tx; ?></title>
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
    <link href="https://fonts.googleapis.com/css2?family=Quicksand:wght@200;300;400;500;600;700&display=swap" rel="stylesheet">
    <link href="../css/sb-admin-2.min.css" rel="stylesheet">
    <link href="../css/sb-admin-2.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>

<style>
#table-container-wrapper { max-height: 460px; overflow-y: auto; }
body { font-family: 'Quicksand', sans-serif; }
.bg-gradient-primary { background: #0A4657; color: #fff; }
.btn-primary { background: #0A4657; color: #fff; border: 1px #0A4657 solid; }
.btn-primary:hover { background: #fff; border: 1px #0A4657 solid; color: #0A4657; }
.fa-download:hover { color: #953E39; }
.btn, .form-control, tr { font-weight: 700; }
</style>

<body id="page-top">
<div id="wrapper">
<?php include("common/sd.php"); ?>
<div id="content-wrapper" class="d-flex flex-column">
<div id="content">
<nav class="navbar navbar-expand navbar-light bg-white topbar mb-4 static-top shadow">
    <button id="sidebarToggleTop" class="btn btn-link d-md-none rounded-circle mr-3">
        <i class="fa fa-bars"></i>
    </button>
    <?php include('common/log.php'); ?>
</nav>

<?php
$supplierQuery = "SELECT DISTINCT supplier_name FROM supplier";
$supplierResult = mysqli_query($connection, $supplierQuery);
?>
<div class="container-fluid mt-5">
<div class="row">
    <div class="col-sm-3">
        <h3 style="font-weight:700; color:#060606;">Supplier Report</h3>
    </div>
</div>
<br>
<form method="post">
    <div class="row">
        <div class="col-md-3">
            <div class="form-group">
                <label for="supplier">Supplier Name:</label>
                <select class="form-control" name="supplier" required style="text-transform:capitalize;">
                    <option value="">Select Supplier</option>
                    <?php
                    while ($supplierRow = mysqli_fetch_assoc($supplierResult)) {
                        echo '<option value="' . $supplierRow['supplier_name'] . '">' . $supplierRow['supplier_name'] . '</option>';
                    }
                    ?>
                </select>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-md-3">
            <div class="form-group">
                <label for="startDate">Start Date:</label>
                <input type="date" class="form-control" name="startDate" value="<?php echo $startDate; ?>" required>
            </div>
        </div>
        <div class="col-md-3">
            <div class="form-group">
                <label for="endDate">End Date:</label>
                <input type="date" class="form-control" name="endDate" value="<?php echo $endDate; ?>" required>
            </div>
        </div>
        <div class="col-md-4">
            <div class="form-group">
                <button type="submit" class="btn btn-dark" name="filter" style="margin-top:30px;">Filter</button>
                <a href="report_supplier.php"><button type="button" class="btn btn-primary btn-lg" style="margin-top:30px;"><i class="fas fa-sync-alt fa-1x"></i></button></a>
                <button id="downloadExcel" type='button' class="btn btn-success" style="margin-top:30px;"><i class="fas fa-file fa-1x"></i> XLXS</button>
                <button id="downloadPdf" type='button' class="btn btn-danger" style="margin-top:30px;">
                    <i class="fas fa-file fa-1x"></i>  Pdf</button>
            </div>
        </div>
    </div>

    <div id='report'>
        <?php if (!empty($transactions)) : ?>
            <h3 style="background:#0A4657; color:#fff;padding:20px; border-radius:10px;text-align:center;text-transform:capitalize;">
                Supplier Ledger Report : <?php echo $supplierName; ?>
            </h3>

            <div class="row">
                <div class="col-sm-12" id="table-container-wrapper">
                    <table id="patientTable" class="table table-striped table-hover text-center table-sm bg-white">
                        <thead>
                            <tr class="bg-dark text-white">
                                <th>Date</th>
                                <th>Description</th>
                                <th>Product Detail</th>
                                <th>Invoice#</th>
                                <th>Debit Amount (Rs)</th>
                                <th>Credit Amount (Rs)</th>
                                <th>Balance (Rs)</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            $runningBalance = $openingBalance;
                            foreach ($transactions as $transaction) :
                                $debit = '';
                                $credit = '';
                                if ($transaction['type'] == 'debit') {
                                    $runningBalance += abs($transaction['amount']);
                                    $credit = number_format(abs($transaction['amount']), 2);
                                } elseif ($transaction['type'] == 'credit') {
                                    $runningBalance -= abs($transaction['amount']);
                                    $debit = number_format(abs($transaction['amount']), 2);
                                } elseif ($transaction['type'] == 'balance') {
                                    $runningBalance = abs($transaction['amount']);
                                }
                            ?>
                                <tr style="color:#060606;">
                                    <td><?php echo $transaction['date']; ?></td>
                                    <td style="text-transform:capitalize;"><?php echo $transaction['description']; ?></td>
                                    <td><?php echo $transaction['product_detail']; ?></td>
                                    <td><?php echo $transaction['inv_id']; ?></td>
                                    <td><?php echo $debit; ?></td>
                                    <td><?php echo $credit; ?></td>
                                    <td><?php echo number_format($runningBalance, 2); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <div class="col-sm-12">
                    <table class="table bg-white" style="color:#060606;">
                        <tfoot>
                            <tr>
                                <th>Final Balance:</th>
                                <td><?php echo number_format($totalBalance, 2); ?></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        <?php else : ?>
            <?php if (isset($_POST['filter'])) : ?>
                <div class="alert alert-warning">
                    No records found for the selected supplier and date range.
                </div>
            <?php endif; ?>
        <?php endif; ?>
</form>
</div>
</div>
</div>
<br><br>
<?php include("common/main_ft.php"); ?>
</div>
</div>
<a class="scroll-to-top rounded" href="#page-top">
    <i class="fas fa-angle-up"></i>
</a>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
<script>
document.getElementById('downloadExcel').addEventListener('click', function() {
    var table = document.getElementById('patientTable');
    var clone = table.cloneNode(true);
    var rows = clone.querySelectorAll('tr');
    rows.forEach(function(row) {
        if (row.style.display === 'none' || row.innerText.trim() === '') {
            row.remove();
        }
    });
    var workbook = XLSX.utils.table_to_book(clone, { sheet: "Supplier Data" });
    XLSX.writeFile(workbook, 'Supplier_data.xlsx');
});

document.getElementById("downloadPdf").addEventListener("click", function() {
    const { jsPDF } = window.jspdf;
    const report = document.getElementById("report");
    const now = new Date();
    const formattedDateTime = now.toLocaleDateString() + " " + now.toLocaleTimeString();

    html2canvas(report, { scale: 2 }).then(canvas => {
        const imgData = canvas.toDataURL("image/png");
        const pdf = new jsPDF("p", "mm", "a4");
        const pdfWidth = pdf.internal.pageSize.getWidth();
        const pdfHeight = (canvas.height * pdfWidth) / canvas.width;
        pdf.addImage(imgData, "PNG", 0, 30, pdfWidth, pdfHeight);
        pdf.setFont("helvetica", "bold");
        pdf.setFontSize(14);
        pdf.text("Supplier Report", pdfWidth / 2, 12, { align: "center" });
        pdf.setFontSize(10);
        pdf.setFont("helvetica", "normal");
        pdf.text(`Printed: ${formattedDateTime}`, pdfWidth - 10, 20, { align: "right" });
        pdf.save("Supplier-Report.pdf");
    });
});
</script>
<script src="../vendor/jquery/jquery.min.js"></script>
<script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
<script src="../vendor/jquery-easing/jquery.easing.min.js"></script>
<script src="../js/sb-admin-2.min.js"></script>
</body>
</html>
