<?php
// ============================================================
//  MediCore — Purchase Returns API
//  File: api/purchase-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 purchase_returns (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prn              VARCHAR(30)  NOT NULL UNIQUE,
    purchase_id      INT UNSIGNED,
    purchase_invoice VARCHAR(30)  DEFAULT '',
    supplier_id      INT UNSIGNED,
    supplier_name    VARCHAR(150) DEFAULT '',
    date             DATE         NOT NULL,
    total_amount     DECIMAL(12,2) DEFAULT 0.00,
    credit_type      ENUM('supplier_credit','cash_recovery','adjustment')
                     DEFAULT 'adjustment',
    credit_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_purchase (purchase_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_date     (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

db_run("CREATE TABLE IF NOT EXISTS purchase_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 purchase_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('purchase_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 Purchase Return ──────────────────────────────────────
function api_save(array $b): void {

    // ── Validate ──────────────────────────────────────────────
    $purchaseId  = (int)($b['purchase_id']  ?? 0);
    $items       = $b['items']              ?? [];
    $creditType  = trim($b['credit_type']   ?? 'adjustment');
    $creditAcct  = trim($b['credit_account']?? 'Cash');
    $reason      = trim($b['reason']        ?? '');
    $date        = trim($b['date']          ?? date('Y-m-d'));

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

    $validCredit = ['supplier_credit','cash_recovery','adjustment'];
    $validAccts  = ['Cash','Bank','MFS','Card'];
    if (!in_array($creditType, $validCredit)) fail('Invalid credit type');
    if (!in_array($creditAcct, $validAccts))  fail('Invalid credit account');

    // Load original purchase
    $po = db_row("SELECT * FROM purchases WHERE id = ?", [$purchaseId]);
    if (!$po) fail('Original purchase order not found');

    // Load original purchase items for validation
    $poItems    = db_rows(
        "SELECT * FROM purchase_items WHERE purchase_id = ?", [$purchaseId]
    );
    $poItemMap  = [];
    foreach ($poItems as $pi) {
        $pid = (int)$pi['product_id'];
        if (!isset($poItemMap[$pid])) $poItemMap[$pid] = $pi;
    }

    // Validate return quantities
    $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 PO
        $alreadyReturned = (int) db_val(
            "SELECT COALESCE(SUM(pri.qty),0)
               FROM purchase_return_items pri
               JOIN purchase_returns pr ON pr.id = pri.return_id
              WHERE pri.product_id = ? AND pr.purchase_id = ?
                AND pr.status != 'rejected'",
            [$pid, $purchaseId]
        );

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

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

        // Check sufficient stock to deduct
        $currentStock = (int) db_val(
            "SELECT stock_pcs FROM products WHERE id = ?", [$pid]
        );
        if ($qty > $currentStock)
            fail("Insufficient stock for \"{$item['product_name']}\". Current stock: {$currentStock} pcs");

        $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 PRN
    $cfg = get_settings();
    $prn = next_ref($cfg['prn_prefix'] ?? 'PRN', 'purchase_returns', 'prn');

    $suppId   = (int)$po['supplier_id'];
    $suppName = $po['supplier_name'];

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

        // 1. Insert return header
        $returnId = db_insert(
            "INSERT INTO purchase_returns
               (prn, purchase_id, purchase_invoice, supplier_id, supplier_name,
                date, total_amount, credit_type, credit_account,
                reason, status, created_by)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
            [
                $prn, $purchaseId, $po['invoice'],
                $suppId, $suppName,
                $date, $totalAmount,
                $creditType, $creditAcct,
                $reason, 'approved',
                current_user_id(),
            ]
        );

        // 2. Insert items + DEDUCT stock + log stock history
        foreach ($validItems as $item) {
            db_run(
                "INSERT INTO purchase_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'],
                ]
            );

            // DEDUCT stock (returning to supplier) — the pre-check above
            // (before the transaction) is only a friendly early rejection;
            // it can't by itself prevent two concurrent requests against
            // the same product from both reading "enough stock" a moment
            // apart. This atomic `WHERE stock_pcs >= ?` guard is what
            // actually enforces it — if two racing returns would jointly
            // exceed available stock, only one UPDATE can match/affect a
            // row, and the other is rejected/rolled back instead of
            // silently clamping to 0.
            $affected = db_run(
                "UPDATE products
                    SET stock_pcs  = stock_pcs - ?,
                        updated_at = NOW()
                  WHERE id = ? AND stock_pcs >= ?",
                [$item['qty'], $item['product_id'], $item['qty']]
            );
            if ($affected === 0) {
                db()->rollBack();
                $avail = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$item['product_id']]);
                fail("Insufficient stock for \"{$item['product_name']}\". Current stock: {$avail} pcs");
            }
            $newStock = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$item['product_id']]);
            publish_stock_event((int)$item['product_id'], 'purchase_return', -(int)$item['qty'], $newStock, 'purchase_return', $returnId);
            record_stock_ledger((int) $item['product_id'], 'purchase_return', 0, (int) $item['qty'], $newStock,
                'purchase_returns', $returnId, $prn);

            // Log stock movement (out)
            db_run(
                "INSERT INTO stock_history
                   (product_id, product_name, movement, qty,
                    ref_type, ref_id, ref_no, note, created_by)
                 VALUES (?,?,'out',?,'purchase_return',?,?,?,?)",
                [
                    $item['product_id'], $item['product_name'],
                    $item['qty'],
                    $returnId, $prn,
                    "Returned to supplier — PO {$po['invoice']}",
                    current_user_id(),
                ]
            );
        }

        // 3. Update supplier ledger based on credit type
        if ($creditType === 'supplier_credit') {
            // Reduce supplier balance (what we owe them)
            db_run(
                "UPDATE suppliers
                    SET balance = GREATEST(0, balance - ?)
                  WHERE id = ?",
                [$totalAmount, $suppId]
            );
            // Also reduce PO due if any
            $poDue = (float)$po['due'];
            if ($poDue > 0) {
                $reduceAmt = min($poDue, $totalAmount);
                db_run(
                    "UPDATE purchases SET due = GREATEST(0, due - ?)
                       WHERE id = ?",
                    [$reduceAmt, $purchaseId]
                );
            }

        } elseif ($creditType === 'cash_recovery') {
            // Cash coming back — record as cash IN
            $ledgerId = db_insert(
                "INSERT INTO cash_ledger
                   (date, description, account, type, amount, ref)
                 VALUES (?,?,?,'in',?,?)",
                [
                    $date,
                    "Purchase return recovery — $suppName ($prn)",
                    $creditAcct, $totalAmount, $prn,
                ]
            );
            enqueue_sync('cash_ledger', $ledgerId, 'INSERT',
                db_row("SELECT * FROM cash_ledger WHERE id = ?", [$ledgerId]));
            // Reduce supplier balance
            db_run(
                "UPDATE suppliers
                    SET balance = GREATEST(0, balance - ?)
                  WHERE id = ?",
                [$totalAmount, $suppId]
            );

        } elseif ($creditType === 'adjustment') {
            // Reduce PO due if pending
            $poDue = (float)$po['due'];
            if ($poDue > 0) {
                $reduceAmt = min($poDue, $totalAmount);
                db_run(
                    "UPDATE purchases
                        SET due  = GREATEST(0, due - ?),
                            paid = paid + ?
                      WHERE id = ?",
                    [$reduceAmt, $reduceAmt, $purchaseId]
                );
                // Also reduce supplier balance
                db_run(
                    "UPDATE suppliers
                        SET balance = GREATEST(0, balance - ?)
                      WHERE id = ?",
                    [$reduceAmt, $suppId]
                );
            }
        }

        // 4. Update PO status
        $newPoDue = (float) db_val(
            "SELECT due FROM purchases WHERE id = ?", [$purchaseId]
        );
        // (status field on purchases not tracked separately — due=0 means cleared)
        enqueue_sync('purchases', $purchaseId, 'UPDATE', db_row("SELECT * FROM purchases WHERE id = ?", [$purchaseId]));

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

        db()->commit();

        log_action('create', 'purchase_returns', $returnId,
            "PRN {$prn} against PO {$po['invoice']} — Amount: " .
            number_format($totalAmount, 2) . " — Credit: {$creditType}");

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

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

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

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

    if ($q) {
        $where[]  = "(pr.prn LIKE ? OR pr.purchase_invoice LIKE ?
                      OR pr.supplier_name LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }
    if ($from)   { $where[] = "pr.date >= ?";        $params[] = $from; }
    if ($to)     { $where[] = "pr.date <= ?";        $params[] = $to; }
    if ($status) { $where[] = "pr.status = ?";       $params[] = $status; }
    if ($suppId) { $where[] = "pr.supplier_id = ?";  $params[] = $suppId; }

    $wSql = implode(' AND ', $where);
    $rows = db_rows(
        "SELECT pr.*,
                COUNT(pri.id)   AS item_count,
                u.name          AS created_by_name
           FROM purchase_returns pr
      LEFT JOIN purchase_return_items pri ON pri.return_id = pr.id
      LEFT JOIN users u ON u.id = pr.created_by
          WHERE $wSql
          GROUP BY pr.id
          ORDER BY pr.date DESC, pr.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 pr.*, u.name AS created_by_name
           FROM purchase_returns pr
      LEFT JOIN users u ON u.id = pr.created_by
          WHERE pr.id = ?",
        [$id]
    );
    if (!$ret) fail('Return record not found');

    $items = db_rows(
        "SELECT pri.*, p.form, p.strength, p.rack
           FROM purchase_return_items pri
      LEFT JOIN products p ON p.id = pri.product_id
          WHERE pri.return_id = ?
          ORDER BY pri.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 `purchase_returns` WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$ret) fail('Return record not found');

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

    log_action('delete', 'purchase_returns', $id,
        'Soft-deleted return: ' . ($ret['prn'] ?? '#' . $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;
}
