<?php
include("db/cn.php");
include("common/tx.php");

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

ob_start();
if (!empty($_SESSION['user_name']) && !empty($_SESSION['user_role']) && !empty($_SESSION['outlet_name']) && !empty($_SESSION['outlet_address'])) {
    $userName = $_SESSION['user_name'];
    $userRole = $_SESSION['user_role'];
    $outlet_address = $_SESSION['outlet_address'];
    $outlet_name = $_SESSION['outlet_name'];
}
ob_end_flush();


if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['filter'])) {

    $creditor = mysqli_real_escape_string($connection, $_POST['supplier']);
    $startDate = date('Y-m-d', strtotime($_POST['startDate']));
    $endDate = date('Y-m-d', strtotime($_POST['endDate']));

    // ------------------------------
    // GET OPENING BALANCE
    // ------------------------------
    $openingQuery = "
        SELECT open_amount AS amount
        FROM creditor_opening_amount
        WHERE creditor_name = ?
        AND date_time BETWEEN ? AND ?
        ORDER BY date_time LIMIT 1";

    $stmt = $connection->prepare($openingQuery);
    $stmt->bind_param('sss', $creditor, $startDate, $endDate);
    $stmt->execute();
    $result = $stmt->get_result();
    $openingBalance = ($row = $result->fetch_assoc()) ? (float)$row['amount'] : 0.0;
    $stmt->close();


    // -------------------------------------------------------------------
    // UNIVERSAL TRANSACTION FETCHER (Negative Qty + PKR Support)
    // -------------------------------------------------------------------
    function fetchTransactions($connection, $query, $params, $description, $multiplier = 1)
    {
        $stmt = $connection->prepare($query);
        $stmt->bind_param(...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        $transactions = [];

        while ($row = $result->fetch_assoc()) {

            $productArray = [];

            if (!empty($row['product_detail']) && $row['product_detail'] !== '-') {

                $rawParts = preg_split('/,<br>\s*/', $row['product_detail']);

                foreach ($rawParts as $p) {

                    preg_match('/^(.*?) \(Qty/', $p, $nameMatch);
                    $name = $nameMatch[1] ?? '-';

                    preg_match('/Qty\s*=\s*(-?[0-9]+)/', $p, $qtyMatch);
                    $qty = $qtyMatch[1] ?? '0';

                    preg_match('/PKR\s*=\s*([0-9.]+)/', $p, $priceMatch);
                    $price = $priceMatch[1] ?? '0';

                    $productArray[] = [
                        'name'  => $name,
                        'qty'   => $qty,
                        'price' => $price
                    ];
                }
            }

            $transactions[] = [
                'date' => $row['date'],
                'description' => $description,
                'amount' => $multiplier * (float)$row['amount'],
                'paid_by' => $row['paid_by'],
                'invoice' => $row['order_id'] ?? '-',
                'product_detail' => $productArray
            ];
        }

        $stmt->close();
        return $transactions;
    }


    // ------------------------------
    // ALL QUERIES UPDATED WITH PKR
    // ------------------------------
    $queries = [
        [
            'query' => "
                SELECT 
                    date_time AS date,
                    discount_amount AS amount,
                    paid_by,
                    order_id,
                    GROUP_CONCAT(
                        CONCAT(
                            product_name, 
                            ' (Qty = ',
                            CASE 
                                WHEN product_quantity IS NULL OR product_quantity='' THEN 0
                                ELSE product_quantity 
                            END,
                            ', PKR = ',
                            product_price,
                            ')'
                        ) 
                        SEPARATOR ',<br> '
                    ) AS product_detail
                FROM log_user_sales
                WHERE customer_name = ?
                AND paid_by = 'credit'
                AND status = ''
                AND date_time BETWEEN ? AND ?
                GROUP BY order_id
            ",
            'description' => 'Credit Sale',
            'multiplier' => 1
        ],
        [
            'query' => "
                SELECT 
                    date_time AS date,
                    discount_amount AS amount,
                    paid_by,
                    r_order_id AS order_id,
                    GROUP_CONCAT(
                        CONCAT(
                            product_name, 
                            ' (Qty = ',
                            CASE 
                                WHEN product_quantity IS NULL OR product_quantity='' THEN 0
                                ELSE product_quantity 
                            END,
                            ', PKR = ',
                            product_price,
                            ')'
                        ) 
                        SEPARATOR ',<br> '
                    ) AS product_detail
                FROM log_user_sales
                WHERE customer_name = ?
                AND status = 'Refund'
                AND paid_by = 'credit'
                AND date_time BETWEEN ? AND ?
                GROUP BY r_order_id
            ",
            'description' => 'Credit Sale Return',
            'multiplier' => -1
        ],
        [
            'query' => "
                SELECT 
                    date_time AS date,
                    amount,
                    paid_by,
                    '-' AS order_id,
                    '-' AS product_detail
                FROM credit_paid
                WHERE cr_name = ?
                AND date_time BETWEEN ? AND ?
            ",
            'description' => 'Credit Received',
            'multiplier' => -1
        ]
    ];


    // ------------------------------
    // MERGE ALL TRANSACTIONS
    // ------------------------------
    $transactions = [];

    foreach ($queries as $queryConfig) {
        $params = ['sss', $creditor, $startDate, $endDate];
        $transactions = array_merge(
            $transactions,
            fetchTransactions(
                $connection,
                $queryConfig['query'],
                $params,
                $queryConfig['description'],
                $queryConfig['multiplier']
            )
        );
    }

    // Sort by date
    usort($transactions, fn($a, $b) => strtotime($a['date']) - strtotime($b['date']));
    $balance = (float)$openingBalance;
}

$creditorQuery = "SELECT DISTINCT cr_name FROM credit_note";
$creditorResult = mysqli_query($connection, $creditorQuery);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title><?php echo $tx; ?></title>
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Quicksand:wght@300;500;700&display=swap" rel="stylesheet">
    <link href="../css/sb-admin-2.min.css" rel="stylesheet">
    <style>
        body { font-family: 'Quicksand', sans-serif; }
        .bg-gradient-primary { background:#0A4657; color:#fff; }
        .btn-primary { background:#0A4657; border:1px solid #0A4657; }
        .btn-primary:hover { background:#fff; color:#0A4657; }
        .btn, .form-control, tr { font-weight:700; }
        .table-container { max-height:560px; overflow-y:auto; }
        label, option{ font-weight:700; color:#000; text-transform:capitalize; }
    </style>
</head>
<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 shadow">
                <?php include('common/log.php'); ?>
            </nav>
            <div class="container-fluid mt-5">
                <h3 class="text-dark font-weight-bold">Customer Ledger Report</h3>
                <form method="post" class="mt-3">
                  <div class="row">
                      <div class="col-md-3">
                            <label>Customer Name:</label>
                            <select class="form-control" name="supplier" required>
                                <option value="">Select Customer</option>
                                <?php while ($row = mysqli_fetch_assoc($creditorResult)): ?>
                                    <option value="<?= $row['cr_name'] ?>"><?= $row['cr_name'] ?></option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                  </div>
                  <br>
                    <div class="row">
                        <div class="col-md-3">
                            <label>Start Date:</label>
                            <input type="date" name="startDate" class="form-control" required>
                        </div>
                        <div class="col-md-3">
                            <label>End Date:</label>
                            <input type="date" name="endDate" class="form-control" required>
                        </div>
                        <div class="col-md-4">
                            <button type="submit" name="filter" class="btn btn-dark mt-4">Filter</button>
                            <a href="report_recieve.php" class="btn btn-primary btn-lg mt-4"><i class="fas fa-sync-alt"></i></a>
                            <button id="downloadExcel" type="button" class="btn btn-success mt-4"><i class="fas fa-file"></i> XLSX</button>
                            <button id="downloadPdf" type="button" class="btn btn-danger mt-4"><i class="fas fa-file"></i> PDF</button>
                        </div>
                    </div>
                </form>

                <?php if (isset($transactions)): ?>
                    <div id="report">
                        <h5 class="text-center bg-gradient-primary p-3 rounded mt-4">
                            Customer Ledger Report: <strong><?= htmlspecialchars($creditor) ?></strong>
                        </h5>

                        <div class="table-container">
                            <table id="patientTable" class="table table-bordered bg-white text-center">
                                <thead class="bg-dark text-white">
                                <tr>
                                    <th>Date</th>
                                    <th>Description</th>
                                    <th>Product Detail</th>
                                    <th>Invoice #</th>
                                    <th>Paid By</th>
                                    <th>Debit (Rs)</th>
                                    <th>Credit (Rs)</th>
                                    <th>Balance (Rs)</th>
                                </tr>
                                </thead>
                                <tbody>
                                <tr style="font-weight:bold; background:#fff;">
                                    <td><?= htmlspecialchars($startDate) ?></td>
                                    <td>Opening Balance</td>
                                    <td>-</td>
                                    <td>-</td>
                                    <td></td>
                                    <td><?= number_format((float)$openingBalance, 2) ?></td>
                                    <td></td>
                                    <td><?= number_format((float)$openingBalance, 2) ?></td>
                                </tr>

                                <?php foreach ($transactions as $t): ?>
                                    <?php
                                    $positiveAmount = 0.0;
                                    $negativeAmount = 0.0;

                                    if ($t['description'] === 'Credit Sale') {
                                        $balance += (float)$t['amount'];
                                        $positiveAmount = (float)$t['amount'];
                                    } elseif ($t['description'] === 'Credit Sale Return') {
                                        $balance -= abs((float)$t['amount']);
                                        $negativeAmount = abs((float)$t['amount']);
                                    } elseif ($t['description'] === 'Credit Received') {
                                        $balance -= abs((float)$t['amount']);
                                        $negativeAmount = abs((float)$t['amount']);
                                    }
                                    ?>
                                    <tr>
                                        <td><?= htmlspecialchars($t['date']) ?></td>
                                        <td><?= htmlspecialchars($t['description']) ?></td>
                                     <td>
    <?php if (!empty($t['product_detail'])): ?>
        <table class="table table-bordered m-0 p-0" style="font-size:12px; font-weight:700; color:#000;">
            <thead>
                <tr style="background:#0A4657; color:#fff;">
                    <th>Product</th>
                    <th>Qty</th>
                    <th>UNIT Rs.</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($t['product_detail'] as $pd): ?>
                    <tr>
                        <td><?= htmlspecialchars($pd['name']) ?></td>
                        <td><?= htmlspecialchars($pd['qty']) ?></td>
                        <td><?= htmlspecialchars($pd['price']) ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php else: ?>
        -
    <?php endif; ?>
</td>

                                        <td><?= htmlspecialchars($t['invoice']) ?></td>
                                        <td><?= ucfirst(htmlspecialchars($t['paid_by'])) ?></td>
                                        <td><?= $positiveAmount > 0 ? number_format($positiveAmount, 2) : '' ?></td>
                                        <td><?= $negativeAmount > 0 ? number_format($negativeAmount, 2) : '' ?></td>
                                        <td><?= number_format($balance, 2) ?></td>
                                    </tr>
                                <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>

                        <table class="table mt-3">
                            <tr>
                                <th>Final Balance:</th>
                                <td><?= number_format($balance, 2) ?></td>
                            </tr>
                        </table>
                    </div>
                <?php endif; ?>
            </div>
        </div>
        <?php include("common/main_ft.php"); ?>
    </div>
</div>

<!-- Export Scripts -->
<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 wb = XLSX.utils.table_to_book(table, { sheet: "Ledger" });
    XLSX.writeFile(wb, 'Customer_Ledger.xlsx');
});

document.getElementById("downloadPdf").addEventListener("click", function () {
    const { jsPDF } = window.jspdf;
    const report = document.getElementById("report");
    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("Customer Ledger Report", pdfWidth / 2, 12, { align: "center" });
        pdf.save("Customer_Ledger_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>
