<?php
include 'php/db_connection.php';
?>

<!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">
</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">
                <div class="card-header bg-primary text-white">
                    <h4 class="mb-0">🔄 Return Product</h4>
                </div>
                <div class="card-body">
                    <!-- Search Invoice -->
                    <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" 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
                    if (isset($_GET['invoice_id'])) {
                        $invoice_id = intval($_GET['invoice_id']);

                        // Check if invoice exists
                        $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 {
                            // Fetch products for this invoice
                            $invoice_items = $con->query("
                                SELECT si.id, si.product_id, p.name, si.quantity, si.price, si.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->num_rows > 0) {
                                // Show invoice info
                                $invoice = $invoice_check->fetch_assoc();
                                echo "<div class='alert alert-info'>
                                        <strong>Invoice #$invoice_id</strong> | 
                                        Customer: {$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()) {
                                    echo "<tr>
                                            <td>{$sl}</td>
                                            <td><strong>{$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[{$row['product_id']}]' 
                                                       max='{$row['quantity']}' 
                                                       min='0' 
                                                       value='0'
                                                       class='form-control text-center' 
                                                       style='max-width: 80px;'>
                                            </td>
                                            <td>
                                                <input type='text' 
                                                       name='reason[{$row['product_id']}]' 
                                                       class='form-control' 
                                                       placeholder='e.g. Damaged'>
                                            </td>
                                            <td>
                                                <input type='text' 
                                                       name='notes[{$row['product_id']}]' 
                                                       class='form-control' 
                                                       placeholder='Additional notes'>
                                            </td>
                                          </tr>
                                          <input type='hidden' name='price[{$row['product_id']}]' value='{$row['price']}'>
                                          <input type='hidden' name='purchase_price[{$row['product_id']}]' value='{$row['purchase_price']}'>
                                          <input type='hidden' name='sold_qty[{$row['product_id']}]' 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 been already returned.</small>
                                      </div>";
                            }
                        }
                    }
                    ?>

                    <?php
                    if (isset($_POST['process_return'])) {
                        $invoice_id = intval($_POST['invoice_id']);
                        $customer_id = 1; // session থেকে customer_id নিতে হবে যদি থাকে
                        $total_refund = 0;
                        $items_returned = 0;

                        // Start transaction
                        $con->begin_transaction();

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

                                if ($qty > 0) {
                                    $items_returned++;
                                    $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 cannot exceed sold quantity for product ID: $product_id");
                                    }

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

                                    // Insert into 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())
                                    ");
                                    $stmt->bind_param("iiiissd", $invoice_id, $customer_id, $product_id, $qty, $reason, $notes, $refund_amount);
                                    $stmt->execute();

                                    // Update product stock
                                    $con->query("
                                        UPDATE product 
                                        SET quantity = quantity + $qty 
                                        WHERE Product_id = '$product_id'
                                    ");

                                    // 🔧 FIXED: Correct quantity and total calculations
                                    $new_qty = $sold_qty - $qty;
                                    $new_total = $new_qty * $price;
                                    $new_total_purchase = $new_qty * $purchase_price;

                                    // Update sales_items
                                    $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'
                                    ");

                                    // Delete row if quantity is zero or less
                                    $con->query("
                                        DELETE FROM sales_items 
                                        WHERE sales_id = $invoice_id 
                                        AND product_id = '$product_id' 
                                        AND quantity <= 0
                                    ");
                                }
                            }

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

                            // Recalculate 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;
                                $new_collected = 0;
                                $discount = 0;
                            } else {
                                $new_grand_total = $new_sub_total - $discount;

                                if ($new_grand_total < 0) {
                                    $discount = $new_sub_total;
                                    $new_grand_total = 0;
                                }

                                $new_final_amount = $new_grand_total;
                                $actual_collected = $collected_one;

                                $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;
                            }

                            // Update 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 border-left-success shadow-sm'>
                                    <h4 class='alert-heading'>
                                        <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 Amount:</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_process.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 border-left-danger shadow-sm'>
                                    <h4 class='alert-heading'>
                                        <i class='fas fa-exclamation-triangle'></i> Error Processing Return
                                    </h4>
                                    <hr>
                                    <p class='mb-0'>" . htmlspecialchars($e->getMessage()) . "</p>
                                    <small class='text-muted'>Please try again or contact support if needed.</small>
                                  </div>";
                        }
                    }
                    ?>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Styles -->
<style>
    .card {
        border: none;
        margin-top: 20px;
    }

    .form-control {
        border-radius: 4px;
    }

    .table {
        background: white;
        font-size: 14px;
    }

    .table thead th {
        border-bottom: 2px solid #dee2e6;
        font-weight: 600;
    }

    .table td {
        vertical-align: middle;
    }

    .alert-success h4,
    .alert-danger h4 {
        margin-bottom: 15px;
        font-size: 20px;
    }

    .alert-success p,
    .alert-danger p {
        margin: 8px 0;
        font-size: 15px;
    }

    .border-left-success {
        border-left: 4px solid #28a745;
    }

    .border-left-danger {
        border-left: 4px solid #dc3545;
    }

    .shadow-sm {
        box-shadow: 0 .125rem .25rem rgba(0,0,0,.075);
    }

    .badge {
        font-size: 13px;
        padding: 5px 10px;
    }

    .input-group {
        margin: 0 auto;
    }

    @media print {
        .no-print {
            display: none;
        }
    }
</style>

<!-- FontAwesome -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.4/css/all.min.css">
</body>
</html>
