<?php
// ============================================================
//  MediCore — Sales Returns API
//  File: api/sale-returns.php
// ============================================================
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_once '../includes/audit.php';
require_login();
session_write_close(); // release session lock — prevents XAMPP request queuing

header('Content-Type: application/json');

// ── Auto-create tables if missing ────────────────────────────
db_run("CREATE TABLE IF NOT EXISTS sales_returns (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    srn            VARCHAR(30)  NOT NULL UNIQUE,
    sale_id        INT UNSIGNED,
    sale_invoice   VARCHAR(30)  DEFAULT '',
    customer_id    INT UNSIGNED,
    customer_name  VARCHAR(100) DEFAULT '',
    date           DATE         NOT NULL,
    total_amount   DECIMAL(12,2) DEFAULT 0.00,
    refund_type    ENUM('cash','advance','adjustment') DEFAULT 'cash',
    refund_account ENUM('Cash','Bank','MFS','Card')   DEFAULT 'Cash',
    reason         TEXT,
    status         ENUM('pending','approved','rejected') DEFAULT 'approved',
    created_by     INT UNSIGNED,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sale     (sale_id),
    INDEX idx_customer (customer_id),
    INDEX idx_date     (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

db_run("CREATE TABLE IF NOT EXISTS sales_return_items (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    return_id    INT UNSIGNED NOT NULL,
    product_id   INT UNSIGNED,
    product_name VARCHAR(150) DEFAULT '',
    qty          INT UNSIGNED DEFAULT 1,
    rate         DECIMAL(10,2) DEFAULT 0.00,
    amount       DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (return_id) REFERENCES sales_returns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

db_run("CREATE TABLE IF NOT EXISTS stock_history (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED,
    product_name VARCHAR(150) DEFAULT '',
    movement     ENUM('in','out') NOT NULL,
    qty          INT          NOT NULL,
    ref_type     VARCHAR(30)  DEFAULT '',
    ref_id       INT UNSIGNED DEFAULT 0,
    ref_no       VARCHAR(30)  DEFAULT '',
    note         VARCHAR(255) DEFAULT '',
    created_by   INT UNSIGNED,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_ref     (ref_type, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

ensure_soft_delete('sales_returns');

// Must run BEFORE any transaction starts, never inside one: CREATE TABLE
// is DDL, and MySQL implicitly commits whatever transaction is currently
// open the moment a DDL statement executes — even one guarded by
// "IF NOT EXISTS". publish_stock_event() (called mid-transaction below)
// calls this too, but its own static-guard makes that call a no-op once
// it's already run here, so the table never gets created mid-transaction.
ensure_stock_events_table();
ensure_sync_tables();
ensure_stock_ledger_table();

$body   = json_decode(file_get_contents('php://input'), true) ?? [];
$action = trim($body['action'] ?? '');

try {
    match($action) {
        'save'   => api_save($body),
        'list'   => api_list($body),
        'detail' => api_detail($body),
        'delete' => api_delete($body),
        default  => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── Save Sales Return ─────────────────────────────────────────
function api_save(array $b): void {

    // ── Validate ──────────────────────────────────────────────
    $saleId     = (int)($b['sale_id']   ?? 0);
    $items      = $b['items']           ?? [];
    $refundType = trim($b['refund_type'] ?? 'cash');
    $refundAcct = trim($b['refund_account'] ?? 'Cash');
    $reason     = trim($b['reason']     ?? '');
    $date       = trim($b['date']       ?? date('Y-m-d'));

    if (!$saleId) fail('Sale ID is required');
    if (!$items)  fail('No items selected for return');

    $validRefund = ['cash','advance','adjustment'];
    $validAccts  = ['Cash','Bank','MFS','Card'];
    if (!in_array($refundType, $validRefund)) fail('Invalid refund type');
    if (!in_array($refundAcct, $validAccts))  fail('Invalid refund account');

    // Load original sale
    $sale = db_row("SELECT * FROM sales WHERE id = ?", [$saleId]);
    if (!$sale) fail('Original sale not found');

    // Load original sale items for validation
    $saleItems = db_rows(
        "SELECT * FROM sale_items WHERE sale_id = ?", [$saleId]
    );
    $saleItemMap = array_column($saleItems, null, 'product_id');

    // Validate return quantities against original
    $totalAmount = 0;
    $validItems  = [];
    foreach ($items as $item) {
        $pid  = (int)($item['product_id']  ?? 0);
        $qty  = (int)($item['qty']          ?? 0);
        $rate = (float)($item['rate']       ?? 0);

        if (!$pid || $qty <= 0) continue;

        // Check already returned qty for this product on this sale
        $alreadyReturned = (int) db_val(
            "SELECT COALESCE(SUM(sri.qty),0)
               FROM sales_return_items sri
               JOIN sales_returns sr ON sr.id = sri.return_id
              WHERE sri.product_id = ? AND sr.sale_id = ?
                AND sr.status != 'rejected'",
            [$pid, $saleId]
        );

        $origItem  = $saleItemMap[$pid] ?? null;
        $origQty   = $origItem ? (int)$origItem['qty'] : 0;
        $maxReturn = $origQty - $alreadyReturned;

        if ($qty > $maxReturn)
            fail("Cannot return {$qty} of \"{$item['product_name']}\". Max returnable: {$maxReturn}");

        $amount       = $qty * $rate;
        $totalAmount += $amount;
        $validItems[] = [
            'product_id'   => $pid,
            'product_name' => trim($item['product_name'] ?? ''),
            'qty'          => $qty,
            'rate'         => $rate,
            'amount'       => $amount,
        ];
    }

    if (!$validItems)  fail('No valid items to return');
    if ($totalAmount <= 0) fail('Return amount must be greater than zero');

    // Generate SRN
    $cfg = get_settings();
    $srn = next_ref($cfg['srn_prefix'] ?? 'SRN', 'sales_returns', 'srn');

    $custId   = (int)$sale['customer_id'];
    $custName = $sale['customer_name'];

    // ── Transaction ───────────────────────────────────────────
    db()->beginTransaction();
    try {

        // 1. Insert return header
        $returnId = db_insert(
            "INSERT INTO sales_returns
               (srn, sale_id, sale_invoice, customer_id, customer_name,
                date, total_amount, refund_type, refund_account,
                reason, status, created_by)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
            [
                $srn, $saleId, $sale['invoice'],
                $custId, $custName,
                $date, $totalAmount,
                $refundType, $refundAcct,
                $reason, 'approved',
                current_user_id(),
            ]
        );

        // 2. Insert items + restore stock + log stock history
        foreach ($validItems as $item) {
            db_run(
                "INSERT INTO sales_return_items
                   (return_id, product_id, product_name, qty, rate, amount)
                 VALUES (?,?,?,?,?,?)",
                [
                    $returnId, $item['product_id'],
                    $item['product_name'],
                    $item['qty'], $item['rate'], $item['amount'],
                ]
            );

            // Restore stock
            db_run(
                "UPDATE products SET stock_pcs = stock_pcs + ?,
                                     updated_at = NOW()
                   WHERE id = ?",
                [$item['qty'], $item['product_id']]
            );
            $newStock = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$item['product_id']]);
            publish_stock_event((int)$item['product_id'], 'sale_return', (int)$item['qty'], $newStock, 'sale_return', $returnId);
            record_stock_ledger((int) $item['product_id'], 'sale_return', (int) $item['qty'], 0, $newStock,
                'sales_returns', $returnId, $srn);

            // Log stock movement
            db_run(
                "INSERT INTO stock_history
                   (product_id, product_name, movement, qty,
                    ref_type, ref_id, ref_no, note, created_by)
                 VALUES (?,?,'in',?,'sale_return',?,?,?,?)",
                [
                    $item['product_id'], $item['product_name'],
                    $item['qty'],
                    $returnId, $srn,
                    "Returned from sale {$sale['invoice']}",
                    current_user_id(),
                ]
            );
        }

        // 3. Update customer ledger based on refund type
        if ($custId > 1) {
            if ($refundType === 'cash') {
                // Cash refund — record cash going out
                $ledgerId = db_insert(
                    "INSERT INTO cash_ledger
                       (date, description, account, type, amount, ref)
                     VALUES (?,?,?,'out',?,?)",
                    [
                        $date,
                        "Sales return refund — $custName ($srn)",
                        $refundAcct, $totalAmount, $srn,
                    ]
                );
                enqueue_sync('cash_ledger', $ledgerId, 'INSERT',
                    db_row("SELECT * FROM cash_ledger WHERE id = ?", [$ledgerId]));

                // If sale had due, reduce it first
                $saleDue = (float)$sale['due'];
                if ($saleDue > 0) {
                    $reduceDue = min($saleDue, $totalAmount);
                    db_run(
                        "UPDATE sales SET due  = GREATEST(0, due - ?),
                                          paid = paid + ?
                           WHERE id = ?",
                        [$reduceDue, $reduceDue, $saleId]
                    );
                    if ($custId > 1) {
                        db_run(
                            "UPDATE customers SET due = GREATEST(0, due - ?)
                               WHERE id = ?",
                            [$reduceDue, $custId]
                        );
                    }
                }

            } elseif ($refundType === 'advance') {
                // Add to customer advance balance
                db_run(
                    "UPDATE customers SET advance = advance + ? WHERE id = ?",
                    [$totalAmount, $custId]
                );
                // If sale had due, reduce it
                $saleDue = (float)$sale['due'];
                if ($saleDue > 0) {
                    $reduceDue = min($saleDue, $totalAmount);
                    db_run(
                        "UPDATE sales SET due = GREATEST(0, due - ?)
                           WHERE id = ?",
                        [$reduceDue, $saleId]
                    );
                    db_run(
                        "UPDATE customers SET due = GREATEST(0, due - ?)
                           WHERE id = ?",
                        [$reduceDue, $custId]
                    );
                }
                // Log as advance received in cash_ledger
                $ledgerId = db_insert(
                    "INSERT INTO cash_ledger
                       (date, description, account, type, amount, ref)
                     VALUES (?,?,'Cash','out',?,?)",
                    [
                        $date,
                        "Return added to advance — $custName ($srn)",
                        $totalAmount, $srn,
                    ]
                );
                enqueue_sync('cash_ledger', $ledgerId, 'INSERT',
                    db_row("SELECT * FROM cash_ledger WHERE id = ?", [$ledgerId]));

            } elseif ($refundType === 'adjustment') {
                // Adjustment against future sales — reduce due if any
                $saleDue = (float)$sale['due'];
                if ($saleDue > 0) {
                    $reduceDue = min($saleDue, $totalAmount);
                    db_run(
                        "UPDATE sales SET due  = GREATEST(0, due - ?),
                                          paid = paid + ?
                           WHERE id = ?",
                        [$reduceDue, $reduceDue, $saleId]
                    );
                    db_run(
                        "UPDATE customers SET due = GREATEST(0, due - ?)
                           WHERE id = ?",
                        [$reduceDue, $custId]
                    );
                }
            }
        }

        // 4. Update sale status
        $newSaleDue  = (float) db_val(
            "SELECT due FROM sales WHERE id = ?", [$saleId]
        );
        $newStatus = $newSaleDue <= 0.001 ? 'Paid'
                   : ((float)$sale['paid'] > 0 ? 'Partial' : 'Due');
        db_run(
            "UPDATE sales SET status = ? WHERE id = ?",
            [$newStatus, $saleId]
        );
        enqueue_sync('sales', $saleId, 'UPDATE', db_row("SELECT * FROM sales WHERE id = ?", [$saleId]));

        // Enqueue the return itself (header + line items as one snapshot).
        $retSnapshot = db_row("SELECT * FROM sales_returns WHERE id = ?", [$returnId]);
        $retSnapshot['items'] = db_rows("SELECT * FROM sales_return_items WHERE return_id = ?", [$returnId]);
        enqueue_sync('sales_returns', $returnId, 'INSERT', $retSnapshot);

        db()->commit();

        log_action('create', 'sale_returns', $returnId,
            "SRN {$srn} against invoice {$sale['invoice']} — Amount: " .
            number_format($totalAmount, 2) . " — Refund: {$refundType}");

        ok(['srn' => $srn, 'return_id' => $returnId,
            'total' => $totalAmount,
            'msg'   => "Sales return $srn created successfully"]);

    } catch (Throwable $e) {
        db()->rollBack();
        fail('Save failed: ' . $e->getMessage());
    }
}

// ── List Returns ──────────────────────────────────────────────
function api_list(array $b): void {
    $where  = ['sr.is_deleted = 0'];
    $params = [];

    $q      = trim($b['q']       ?? '');
    $from   = trim($b['from']    ?? '');
    $to     = trim($b['to']      ?? '');
    $status = trim($b['status']  ?? '');
    $custId = (int)($b['customer_id'] ?? 0);

    if ($q) {
        $where[]  = "(sr.srn LIKE ? OR sr.sale_invoice LIKE ?
                      OR sr.customer_name LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }
    if ($from)   { $where[] = "sr.date >= ?";         $params[] = $from; }
    if ($to)     { $where[] = "sr.date <= ?";         $params[] = $to; }
    if ($status) { $where[] = "sr.status = ?";        $params[] = $status; }
    if ($custId) { $where[] = "sr.customer_id = ?";   $params[] = $custId; }

    $wSql = implode(' AND ', $where);
    $rows = db_rows(
        "SELECT sr.*,
                COUNT(sri.id)        AS item_count,
                u.name               AS created_by_name
           FROM sales_returns sr
      LEFT JOIN sales_return_items sri ON sri.return_id = sr.id
      LEFT JOIN users u ON u.id = sr.created_by
          WHERE $wSql
          GROUP BY sr.id
          ORDER BY sr.date DESC, sr.id DESC",
        $params
    );
    ok(['data' => $rows]);
}

// ── Detail ────────────────────────────────────────────────────
function api_detail(array $b): void {
    $id = (int)($b['id'] ?? 0);
    if (!$id) fail('Missing id');

    $ret = db_row(
        "SELECT sr.*, u.name AS created_by_name
           FROM sales_returns sr
      LEFT JOIN users u ON u.id = sr.created_by
          WHERE sr.id = ?",
        [$id]
    );
    if (!$ret) fail('Return record not found');

    $items = db_rows(
        "SELECT sri.*, p.form, p.strength, p.rack
           FROM sales_return_items sri
      LEFT JOIN products p ON p.id = sri.product_id
          WHERE sri.return_id = ?
          ORDER BY sri.id",
        [$id]
    );
    ok(['data' => $ret, 'items' => $items]);
}

// ── Delete (admin only, reverses all changes) ─────────────────
function api_delete(array $b): void {
    require_admin();
    $id = (int)($b['id'] ?? 0);
    if (!$id) fail('Missing id');

    $ret = db_row("SELECT * FROM `sales_returns` WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$ret) fail('Return record not found');

    $uid = current_user_id();
    db_run(
        "UPDATE `sales_returns` SET is_deleted = 1, deleted_at = NOW(), deleted_by = ? WHERE id = ?",
        [$uid, $id]
    );
    enqueue_sync('sales_returns', $id, 'UPDATE', db_row("SELECT * FROM sales_returns WHERE id = ?", [$id]));

    log_action('delete', 'sale_returns', $id,
        'Soft-deleted return: ' . ($ret['srn'] ?? '#' . $id));

    ok(['msg' => 'Moved to Recycle Bin']);
}


// ── Helpers ───────────────────────────────────────────────────
function ok(array $extra = []): void {
    echo json_encode(array_merge(['success' => true], $extra));
    exit;
}
function fail(string $msg): never {
    echo json_encode(['success' => false, 'error' => $msg]);
    exit;
}
