<?php
require_once '../includes/auth.php';
require_once '../db.php';

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    die("Invalid Request");
}

$sales_invoice_id = (int)$_POST['sales_invoice_id'];
$return_date = $_POST['return_date'];
$action = $_POST['action']; // draft | final
$round_off = floatval($_POST['round_off'] ?? 0);
$return_qtys = $_POST['return_qty'] ?? []; // [product_id => qty]

// Load parent invoice
$inv = $conn->query("SELECT * FROM sales_invoices WHERE id = $sales_invoice_id AND company_id = $cid")->fetch_assoc();
if (!$inv || $inv['status'] !== 'final') {
    die("Return allowed only for Final invoices.");
}

$client_id = $inv['client_id'];

$conn->begin_transaction();

try {

    // Generate Credit Note No
    $seqRes = $conn->query("
        SELECT COUNT(*) AS cnt
        FROM sales_returns
        WHERE sales_invoice_id = $sales_invoice_id
    ")->fetch_assoc();
    $seq = $seqRes['cnt'] + 1;
    $return_no = "CN-" . $inv['invoice_no'] . "-" . $seq;

    // Insert return header
    $stmt = $conn->prepare("
        INSERT INTO sales_returns
        (return_no, sales_invoice_id, client_id, return_date, status, company_id)
        VALUES (?, ?, ?, ?, ?, ?)
    ");
    $status = ($action === 'final') ? 'final' : 'draft';
    $stmt->bind_param("siissi", $return_no, $sales_invoice_id, $client_id, $return_date, $status, $cid);
    $stmt->execute();
    $sales_return_id = $stmt->insert_id;

    $total_taxable = 0;
    $total_gst = 0;
    $total_amount = 0;

    foreach ($return_qtys as $product_id => $qty) {
        $product_id = (int)$product_id;
        $qty = floatval($qty);
        if ($qty <= 0) continue;

        // Fetch all sales_items for this product in FIFO order (oldest batch first)
        $salesItemsRes = $conn->query("
            SELECT si.id, si.sold_qty, si.selling_price_incl, si.gst_percent,
                   si.batch_no, si.purchase_item_id,
                   COALESCE(v.total_returned_qty, 0) AS already_returned,
                   (si.sold_qty - COALESCE(v.total_returned_qty, 0)) AS balance_qty
            FROM sales_items si
            LEFT JOIN v_sales_returned_qty v ON v.sales_item_id = si.id
            WHERE si.sales_invoice_id = $sales_invoice_id AND si.product_id = $product_id
            ORDER BY si.id ASC
        ");

        $remaining = $qty;

        while ($remaining > 0.001 && ($si = $salesItemsRes->fetch_assoc())) {
            $available = floatval($si['balance_qty']);
            if ($available <= 0.001) continue;

            $take = min($remaining, $available);
            $rate_incl = floatval($si['selling_price_incl']);
            $gstp = floatval($si['gst_percent']);
            $line_total = $take * $rate_incl;
            $taxable = $line_total / (1 + $gstp / 100);
            $gst_amt = $line_total - $taxable;

            $si_id = (int)$si['id'];
            $batch_no = $si['batch_no'];
            $sold_qty = floatval($si['sold_qty']);

            $stmtItem = $conn->prepare("
                INSERT INTO sales_return_items
                (sales_return_id, sales_item_id, product_id, batch_no,
                 sold_qty, return_qty, rate_incl_gst, gst_percent,
                 taxable_amount, gst_amount, total_amount)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");
            $stmtItem->bind_param(
                "iiisddddddd",
                $sales_return_id, $si_id, $product_id, $batch_no,
                $sold_qty, $take, $rate_incl, $gstp, $taxable, $gst_amt, $line_total
            );
            $stmtItem->execute();

            // Restore stock if finalizing
            if ($action === 'final') {
                $purchase_item_id = (int)$si['purchase_item_id'];
                $conn->query("
                    UPDATE purchase_items
                    SET available_qty = available_qty + $take
                    WHERE id = $purchase_item_id
                ");

                $stmtMove = $conn->prepare("
                    INSERT INTO stock_movements
                    (product_id, purchase_item_id, type, quantity, reference_id, reference_type)
                    VALUES (?, ?, 'RETURN', ?, ?, 'sales_return')
                ");
                $stmtMove->bind_param("iidi", $product_id, $purchase_item_id, $take, $sales_return_id);
                $stmtMove->execute();
            }

            $total_taxable += $taxable;
            $total_gst += $gst_amt;
            $total_amount += $line_total;
            $remaining -= $take;
        }

        if ($remaining > 0.001) {
            throw new Exception("Return qty exceeds available balance for one or more products.");
        }
    }

    // Apply round off
    $total_amount = round($total_amount + $round_off, 2);

    // Update totals in header
    $stmtUpd = $conn->prepare("
        UPDATE sales_returns
        SET total_taxable=?, total_gst=?, total_amount=?, round_off=?
        WHERE id=?
    ");
    $stmtUpd->bind_param("ddddi", $total_taxable, $total_gst, $total_amount, $round_off, $sales_return_id);
    $stmtUpd->execute();

    $conn->commit();

    if ($status === 'final') {
        header("Location: view.php?id=" . $sales_return_id);
    } else {
        header("Location: edit.php?id=" . $sales_return_id);
    }
    exit;

} catch (Exception $e) {
    $conn->rollback();
    die("Sales Return Failed: " . $e->getMessage());
}
