<?php
session_start();
include 'php/db_connection.php';

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}
$userId = $_SESSION['user_id'];
?>
<!DOCTYPE html>
<html>
<head>
    <title>Return Process</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.4/css/all.min.css">
</head>
<body class="p-4">
<?php include 'sidebar.php'; ?>

<div class="container-fluid">
    <div class="row">
        <div class="col-md-8 offset-md-2">
            <div class="card shadow-sm" style="border:none; margin-top:20px;">
                <div class="card-header bg-primary text-white">
                    <h4 class="mb-0">🔄 Return Product</h4>
                </div>
                <div class="card-body">

                    <!-- Invoice Search Form -->
                    <form method="get" class="form-inline mb-4">
                        <div class="input-group w-50">
                            <input type="text" name="invoice_id" class="form-control"
                                   placeholder="Enter Invoice ID"
                                   value="<?php echo isset($_GET['invoice_id']) ? intval($_GET['invoice_id']) : ''; ?>"
                                   required>
                            <div class="input-group-append">
                                <button type="submit" class="btn btn-primary">
                                    <i class="fas fa-search"></i> Search Invoice
                                </button>
                            </div>
                        </div>
                    </form>

<?php
// ======================== RETURN SUBMIT ========================
if (isset($_POST['process_return'])) {
    $invoice_id = intval($_POST['invoice_id']);
    $total_refund   = 0;
    $items_returned = 0;

    $con->begin_transaction();
    try {
        foreach ($_POST['return_qty'] as $product_id => $qty) {
            $product_id = intval($product_id);
            $qty        = intval($qty);

            if ($qty <= 0) continue;

            $reason         = mysqli_real_escape_string($con, $_POST['reason'][$product_id] ?? '');
            $notes          = mysqli_real_escape_string($con, $_POST['notes'][$product_id] ?? '');
            $price          = floatval($_POST['price'][$product_id]);
            $purchase_price = floatval($_POST['purchase_price'][$product_id]);
            $sold_qty       = intval($_POST['sold_qty'][$product_id]);

            if ($qty > $sold_qty) {
                throw new Exception("Return quantity ($qty) cannot exceed sold quantity ($sold_qty) for product ID: $product_id");
            }

            $refund_amount  = $qty * $price;
            $total_refund  += $refund_amount;
            $items_returned++;

            // return_items এ ইনসার্ট
            $stmt = $con->prepare("
                INSERT INTO return_items
                (invoice_id, customer_id, product_id, return_quantity, reason, notes, refund_amount, status, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, 'completed', NOW())
            ");
            // customer_id → sales table থেকে নাও
            $cust_res = $con->query("SELECT customer_id FROM sales WHERE id = $invoice_id");
            $cust_row = $cust_res->fetch_assoc();
            $customer_id = intval($cust_row['customer_id'] ?? 0);

            $stmt->bind_param("iiiissd", $invoice_id, $customer_id, $product_id, $qty, $reason, $notes, $refund_amount);
            $stmt->execute();
            $stmt->close();

            // Product stock ফেরত দাও
            $con->query("UPDATE product SET Quantity = Quantity + $qty WHERE Product_id = $product_id");

            // sales_items আপডেট
            $new_qty           = $sold_qty - $qty;
            $new_total         = $new_qty * $price;
            $new_total_purchase = $new_qty * $purchase_price;

            if ($new_qty <= 0) {
                // সব ফেরত গেলে row ডিলেট
                $con->query("DELETE FROM sales_items WHERE sales_id = $invoice_id AND product_id = $product_id");
            } else {
                $con->query("
                    UPDATE sales_items
                    SET quantity = $new_qty,
                        total = $new_total,
                        total_purchase = $new_total_purchase
                    WHERE sales_id = $invoice_id AND product_id = $product_id
                ");
            }
        }

        if ($items_returned == 0) {
            throw new Exception("No items selected for return. Enter at least 1 quantity.");
        }

        // Sales totals পুনরায় হিসাব করো
        $result = $con->query("SELECT COALESCE(SUM(total), 0) as new_sub_total FROM sales_items WHERE sales_id = $invoice_id");
        $row = $result->fetch_assoc();
        $new_sub_total = floatval($row['new_sub_total']);

        $sale_result = $con->query("SELECT discount, collected_one FROM sales WHERE id = $invoice_id");
        if ($sale_result->num_rows == 0) throw new Exception("Invoice not found.");
        $sale_row = $sale_result->fetch_assoc();

        $discount      = floatval($sale_row['discount'] ?? 0);
        $collected_one = floatval($sale_row['collected_one'] ?? 0);

        if ($new_sub_total == 0) {
            $new_grand_total  = 0;
            $new_final_amount = 0;
            $new_final_due    = 0;
            $new_change       = $collected_one; // টাকা ফেরত দিতে হবে
            $discount         = 0;
            $new_collected    = 0;
        } else {
            // Discount যদি sub_total এর বেশি হয় তাহলে adjust করো
            if ($discount > $new_sub_total) {
                $discount = $new_sub_total;
            }
            $new_grand_total  = $new_sub_total - $discount;
            $new_final_amount = $new_grand_total;
            $actual_collected = min($collected_one, $new_grand_total); // বেশি collected হলে adjust

            $new_final_due = $new_final_amount - $actual_collected;
            $new_change    = 0;

            if ($new_final_due < 0) {
                $new_change    = abs($new_final_due);
                $new_final_due = 0;
            }
            $new_collected = $actual_collected;
        }

        // Sales table আপডেট
        $con->query("
            UPDATE sales
            SET
                grand_total   = $new_sub_total,
                discount      = $discount,
                final_amount  = $new_final_amount,
                final_due     = $new_final_due,
                collected     = $new_collected,
                `change`      = $new_change
            WHERE id = $invoice_id
        ");

        $con->commit();

        echo "<div class='alert alert-success' style='border-left:4px solid #28a745;'>
                <h4><i class='fas fa-check-circle'></i> Return Processed Successfully!</h4>
                <hr>
                <div class='row'>
                    <div class='col-md-6'>
                        <p><strong>Invoice ID:</strong> #$invoice_id</p>
                        <p><strong>Items Returned:</strong> $items_returned product(s)</p>
                        <p><strong>Total Refund:</strong> <span class='text-danger'>৳" . number_format($total_refund, 2) . "</span></p>
                    </div>
                    <div class='col-md-6'>
                        <p><strong>New Sub Total:</strong> ৳" . number_format($new_sub_total, 2) . "</p>
                        <p><strong>Discount:</strong> ৳" . number_format($discount, 2) . "</p>
                        <p><strong>New Grand Total:</strong> ৳" . number_format($new_grand_total, 2) . "</p>
                        <p><strong>Collected:</strong> ৳" . number_format($new_collected, 2) . "</p>
                        <p><strong>New Due:</strong> <span class='text-warning'>৳" . number_format($new_final_due, 2) . "</span></p>
                        <p><strong>Change to Return:</strong> <span class='text-success'>৳" . number_format($new_change, 2) . "</span></p>
                    </div>
                </div>
                <hr>
                <div class='text-center'>
                    <a href='retru_in.php?id=$invoice_id' class='btn btn-primary' target='_blank'>
                        <i class='fas fa-print'></i> Print Updated Invoice
                    </a>
                    <a href='return_page.php' class='btn btn-secondary'>
                        <i class='fas fa-redo'></i> New Return
                    </a>
                </div>
              </div>";

    } catch (Exception $e) {
        $con->rollback();
        echo "<div class='alert alert-danger' style='border-left:4px solid #dc3545;'>
                <h4><i class='fas fa-exclamation-triangle'></i> Error Processing Return</h4>
                <hr>
                <p>" . htmlspecialchars($e->getMessage()) . "</p>
                <small class='text-muted'>Please try again or contact support.</small>
              </div>";
    }
}

// ======================== INVOICE SEARCH ========================
if (isset($_GET['invoice_id']) && !isset($_POST['process_return'])) {
    $invoice_id = intval($_GET['invoice_id']);

    $invoice_check = $con->query("SELECT * FROM sales WHERE id = $invoice_id");

    if ($invoice_check->num_rows == 0) {
        echo "<div class='alert alert-danger'>
                <i class='fas fa-exclamation-triangle'></i> Invoice #$invoice_id not found!
              </div>";
    } else {
        $invoice = $invoice_check->fetch_assoc();

        // sales_items যেগুলো এখনও quantity > 0
        $invoice_items = $con->query("
            SELECT si.id, si.product_id, p.name, si.quantity, si.price,
                   COALESCE(si.purchase_price, 0) as purchase_price
            FROM sales_items si
            JOIN product p ON si.product_id = p.Product_id
            WHERE si.sales_id = $invoice_id AND si.quantity > 0
        ");

        if ($invoice_items && $invoice_items->num_rows > 0) {
            echo "<div class='alert alert-info'>
                    <strong>Invoice #$invoice_id</strong> |
                    Customer: " . htmlspecialchars($invoice['customer_name']) . " |
                    Date: " . date("d-m-Y h:i A", strtotime($invoice['created_at'])) . "
                  </div>";

            echo "<form method='post'>";
            echo "<input type='hidden' name='invoice_id' value='$invoice_id'>";
            echo "<div class='table-responsive'>
                    <table class='table table-bordered table-hover'>
                        <thead class='thead-light'>
                            <tr>
                                <th>#</th>
                                <th>Product Name</th>
                                <th class='text-center'>Sold Qty</th>
                                <th class='text-center'>Return Qty</th>
                                <th>Reason</th>
                                <th>Notes</th>
                            </tr>
                        </thead>
                        <tbody>";

            $sl = 1;
            while ($row = $invoice_items->fetch_assoc()) {
                $pid = intval($row['product_id']);
                echo "<tr>
                        <td>{$sl}</td>
                        <td><strong>" . htmlspecialchars($row['name']) . "</strong></td>
                        <td class='text-center'><span class='badge badge-primary'>{$row['quantity']}</span></td>
                        <td class='text-center'>
                            <input type='number'
                                   name='return_qty[{$pid}]'
                                   max='{$row['quantity']}'
                                   min='0'
                                   value='0'
                                   class='form-control text-center'
                                   style='max-width:80px;'>
                        </td>
                        <td>
                            <input type='text' name='reason[{$pid}]' class='form-control' placeholder='e.g. Damaged'>
                        </td>
                        <td>
                            <input type='text' name='notes[{$pid}]' class='form-control' placeholder='Additional notes'>
                        </td>
                      </tr>";
                // Hidden fields
                echo "<input type='hidden' name='price[{$pid}]' value='{$row['price']}'>";
                echo "<input type='hidden' name='purchase_price[{$pid}]' value='{$row['purchase_price']}'>";
                echo "<input type='hidden' name='sold_qty[{$pid}]' value='{$row['quantity']}'>";
                $sl++;
            }

            echo "      </tbody>
                    </table>
                  </div>
                  <div class='text-right mt-3'>
                      <button type='submit' name='process_return' class='btn btn-success btn-lg'>
                          <i class='fas fa-check-circle'></i> Submit Return
                      </button>
                  </div>
                  </form>";
        } else {
            echo "<div class='alert alert-warning'>
                    <i class='fas fa-info-circle'></i> No products available for return in this invoice.
                    <br><small>All items may have already been returned or the invoice has no items.</small>
                  </div>";
        }
    }
}
?>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
    .card { border: none; margin-top: 20px; }
    .table { background: white; font-size: 14px; }
    .table thead th { border-bottom: 2px solid #dee2e6; font-weight: 600; }
    .table td { vertical-align: middle; }
    .badge { font-size: 13px; padding: 5px 10px; }
</style>
</body>
</html>
