<?php
// ============================================================
//  MediCore — Purchases API
//  File: api/purchases.php
// ============================================================
// ── CSV Export (GET ?action=export_csv) ──────────────────────
if (($_GET['action'] ?? '') === 'export_csv') {
    require_once '../includes/db.php';
    require_once '../includes/auth.php';
    require_login();
session_write_close(); // release session lock — prevents XAMPP request queuing
    ensure_soft_delete('purchases');
    $rows = db_rows(
        "SELECT p.invoice, p.supplier_name, p.date, p.final_price,
                p.paid, p.due, p.payment_type, p.mode, p.notes, u.name AS submitted_by_name
           FROM purchases p
      LEFT JOIN users u ON u.id = p.submitted_by
          WHERE p.is_deleted = 0 ORDER BY p.date DESC, p.id DESC"
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="purchases_export_'.date('Y-m-d').'.csv"');
    $out = fopen('php://output','w');
    fputcsv($out,['Invoice','Supplier','Date','Total','Paid','Due',
                  'Payment','Mode','Staff','Notes']);
    foreach ($rows as $r) {
        fputcsv($out,[$r['invoice'],$r['supplier_name'],$r['date'],
            $r['final_price'],$r['paid'],$r['due'],
            $r['payment_type'],$r['mode'],$r['submitted_by_name'],$r['notes']]);
    }
    fclose($out); exit;
}

require_once '../includes/db.php';
require_once '../includes/auth.php';
require_once '../includes/audit.php';
require_once '../includes/notify.php';
require_once '../includes/po_approval.php';
require_login();

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

// ── Defensive migration: guarantee UNIQUE INDEX on purchases.invoice ──
try {
    $hasUnique = db_val(
        "SELECT COUNT(*) FROM information_schema.STATISTICS
          WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME   = 'purchases'
            AND COLUMN_NAME  = 'invoice'
            AND NON_UNIQUE   = 0"
    );
    if (!$hasUnique) {
        db_run("ALTER TABLE purchases ADD UNIQUE INDEX uq_purchases_invoice (invoice)");
    }
} catch (Throwable $e) { /* best-effort */ }

ensure_soft_delete('purchases');
ensure_po_approval_columns();

// 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),
        'next_ref' => api_next_ref(),
        'list'     => api_list($body),
        'detail'   => api_detail($body),
        'delete'   => api_delete($body),
        'approve'  => api_approve($body),
        'reject'   => api_reject($body),
        default    => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── Save Purchase Order ───────────────────────────────────────
function api_save(array $b): void {

    // ── Validate inputs ──────────────────────────────────────
    $suppId  = (int)($b['supplier_id'] ?? 0);
    $items   = $b['items'] ?? [];

    if (!$suppId)       fail('Supplier is required');
    if (!$items)        fail('No items in purchase order');

    $supplier = db_row("SELECT * FROM suppliers WHERE id = ?", [$suppId]);
    if (!$supplier) fail('Supplier not found');

    $cfg    = get_settings();
    $prefix = $cfg['po_prefix'] ?? 'PO';
    $year   = date('Y');

    $date       = $b['date']         ?? date('Y-m-d');
    $mode       = $b['mode']         ?? 'unit_trvat';
    $discPct    = (float)($b['discount_pct'] ?? 0);
    $totalBtp   = (float)($b['total_btp']    ?? 0);
    $totalVat   = (float)($b['total_vat']    ?? 0);
    $finalPrice = (float)($b['final_price']  ?? 0);
    $paid       = (float)($b['paid']         ?? 0);
    $due        = (float)($b['due']          ?? 0);
    $account    = trim($b['account']  ?? 'Cash');
    $notes      = trim($b['notes']    ?? '');
    $invNo      = trim($b['inv_no']   ?? '');

    // ── Approval gate ─────────────────────────────────────────
    // Off by default (Settings > Invoice tab). When on, POs at/above
    // the configured threshold skip the immediate stock/ledger effects
    // below and instead wait in the Approvals queue (purchase/approvals.php)
    // until an admin/manager signs off — see api_approve()/api_reject().
    $needsApproval  = po_needs_approval($finalPrice);
    $approvalStatus = $needsApproval ? 'pending' : (po_approval_enabled() ? 'approved' : 'not_required');

    // ── Atomic PO number generation ─────────────────────────────
    // Same race-free approach as sales.php: never SELECT MAX()/COUNT().
    // Insert with a temporary unique placeholder, read back the
    // database-guaranteed-unique AUTO_INCREMENT id, derive the real
    // PO number from that id, then assign it. Retries automatically
    // on the rare duplicate-key violation instead of failing the user.
    $maxAttempts = 5;
    $poId    = null;
    $invoice = null;
    $lastErr = null;

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        db()->beginTransaction();
        try {

            // 1. Insert purchase header with a temporary placeholder
            $tempInvoice = 'TMP-' . bin2hex(random_bytes(8));

            $poId = db_insert(
                "INSERT INTO purchases
                   (invoice, supplier_id, supplier_name, inv_no, date,
                    mode, discount_pct, total_btp, total_vat,
                    final_price, paid, due, notes, account,
                    approval_status, submitted_by)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                [
                    $tempInvoice,
                    $suppId,
                    $supplier['name'],
                    $invNo,
                    $date,
                    $mode,
                    $discPct,
                    $totalBtp,
                    $totalVat,
                    $finalPrice,
                    $paid,
                    $due,
                    $notes,
                    $account,
                    $approvalStatus,
                    current_user_id(),
                ]
            );

            // 2 & 3. Derive the real PO number from the atomic id
            $invoice = sprintf('%s-%s-%06d', $prefix, $year, $poId);

            // 4. Assign the final PO number (and, if this was auto-approved
            // because the workflow is on but under threshold, stamp it now)
            if ($approvalStatus === 'approved') {
                db_run(
                    "UPDATE purchases SET invoice = ?, approved_at = NOW() WHERE id = ?",
                    [$invoice, $poId]
                );
            } else {
                db_run("UPDATE purchases SET invoice = ? WHERE id = ?", [$invoice, $poId]);
            }

            // 5. Insert items (always — the PO record exists either way).
            // Stock is only touched immediately when NOT pending approval;
            // a pending PO's stock effect happens later in api_approve().
            foreach ($items as $item) {
                $productId  = (int)($item['product_id']  ?? 0);
                $boxes      = (int)($item['boxes']        ?? 0);
                $qtyInBox   = (int)($item['qty_in_box']   ?? 1);
                $qtyPcs     = (int)($item['qty_pcs']      ?? ($boxes * $qtyInBox));
                $free       = (int)($item['free']         ?? 0);
                $btp        = (float)($item['btp']        ?? 0);
                $vat        = (float)($item['vat']        ?? 0);
                $btpVat     = (float)($item['btp_vat']    ?? 0);
                $salePrice  = (float)($item['sale_price'] ?? 0);
                $exp        = trim($item['exp']           ?? '');
                $totalPrice = (float)($item['total_price']?? ($boxes * $btpVat));
                $prodName   = trim($item['product_name']  ?? '');

                db_run(
                    "INSERT INTO purchase_items
                       (purchase_id, product_id, product_name,
                        boxes, qty_in_box, qty_pcs,
                        btp, vat, btp_vat, sale_price,
                        free, exp, total_price)
                     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    [
                        $poId, $productId, $prodName,
                        $boxes, $qtyInBox, $qtyPcs,
                        $btp, $vat, $btpVat, $salePrice,
                        $free, $exp, $totalPrice,
                    ]
                );

                if (!$needsApproval) {
                    $addStock = $qtyPcs + $free;
                    db_run(
                        "UPDATE products
                            SET stock_pcs  = stock_pcs + ?,
                                btp        = ?,
                                vat        = ?,
                                btp_vat    = ?,
                                sale_price = IF(? > 0, ?, sale_price),
                                expiry     = ?,
                                updated_at = NOW()
                          WHERE id = ?",
                        [
                            $addStock,
                            $btp, $vat, $btpVat,
                            $salePrice, $salePrice,
                            $exp,
                            $productId,
                        ]
                    );
                    $newStock = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$productId]);
                    publish_stock_event($productId, 'purchase', $addStock, $newStock, 'purchase', $poId);
                    record_stock_ledger($productId, 'purchase', $addStock, 0, $newStock, 'purchases', $poId, $invoice);
                }
            }

            if (!$needsApproval) {
                // 6. Update supplier balance (add due amount)
                if ($due > 0) {
                    db_run(
                        "UPDATE suppliers
                            SET balance = balance + ?
                          WHERE id = ?",
                        [$due, $suppId]
                    );
                }

                // 7. Add cash_ledger entry if paid > 0 (payment)
                if ($paid > 0) {
                    $ledgerId = db_insert(
                        "INSERT INTO cash_ledger
                           (date, description, account, type, amount, ref)
                         VALUES (?, ?, ?, 'out', ?, ?)",
                        [
                            $date,
                            "Purchase payment — {$supplier['name']} ({$invoice})",
                            $account,
                            $paid,
                            $invoice,
                        ]
                    );
                    enqueue_sync('cash_ledger', $ledgerId, 'INSERT',
                        db_row("SELECT * FROM cash_ledger WHERE id = ?", [$ledgerId]));
                }
            }

            // Enqueue the PO itself (header + line items as one snapshot,
            // same denormalized-for-reporting approach used for sales).
            $poSnapshot = db_row("SELECT * FROM purchases WHERE id = ?", [$poId]);
            $poSnapshot['items'] = db_rows("SELECT * FROM purchase_items WHERE purchase_id = ?", [$poId]);
            enqueue_sync('purchases', $poId, 'INSERT', $poSnapshot);

            db()->commit();

            if ($needsApproval) {
                log_action('create', 'purchases', $poId,
                    "PO {$invoice} — Supplier: {$supplier['name']} — Total: " .
                    number_format((float)$finalPrice, 2) . ' (submitted for approval)');

                create_notification('admins', 'po_pending_approval', '🧾',
                    'Purchase Order Needs Approval',
                    "PO {$invoice} from {$supplier['name']} — " .
                    ($cfg['currency'] ?? '') . number_format((float)$finalPrice, 2) .
                    ' is awaiting your approval.',
                    'purchase/approvals.php'
                );

                ok(['invoice' => $invoice, 'po_id' => $poId,
                    'approval_status' => 'pending',
                    'msg' => 'Purchase order submitted for approval. Stock will update once approved.']);
                return;
            }

            log_action('create', 'purchases', $poId,
                "PO {$invoice} — Supplier: {$supplier['name']} — Total: " .
                number_format((float)$finalPrice, 2));

            create_notification('warehouse_staff', 'purchase_received', '📥',
                'New Purchase Received',
                "PO {$invoice} from {$supplier['name']} — " .
                ($cfg['currency'] ?? '') . number_format((float)$finalPrice, 2),
                'purchase/list.php'
            );

            ok(['invoice' => $invoice, 'po_id' => $poId,
                'approval_status' => $approvalStatus,
                'msg' => 'Purchase order saved successfully']);
            return; // success — stop retry loop

        } catch (Throwable $e) {
            db()->rollBack();
            $lastErr = $e;

            $isDuplicate = ($e instanceof PDOException && $e->getCode() === '23000')
                || str_contains(strtolower($e->getMessage()), 'duplicate');

            if ($isDuplicate && $attempt < $maxAttempts) {
                usleep(30000 * $attempt);
                continue;
            }

            fail('Save failed: ' . $e->getMessage());
        }
    }

    fail('Save failed after ' . $maxAttempts . ' attempts: ' .
         ($lastErr ? $lastErr->getMessage() : 'Unknown error'));
}

// ── Approve a pending PO (admin/manager) ────────────────────────
// Applies the stock/supplier-balance/cash-ledger effects that were
// deferred at save time, then marks the PO approved.
function api_approve(array $b): void {
    if (!can_approve_po()) fail('Only admins/managers can approve purchase orders.');

    $id = (int)($b['id'] ?? 0);
    if (!$id) fail('Missing id');

    $po = db_row("SELECT * FROM purchases WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$po) fail('Purchase order not found');
    if ($po['approval_status'] !== 'pending') {
        fail('This purchase order is not awaiting approval (status: ' . $po['approval_status'] . ').');
    }

    db()->beginTransaction();
    try {
        $items = db_rows("SELECT * FROM purchase_items WHERE purchase_id = ?", [$id]);
        foreach ($items as $item) {
            $addStock = (int)$item['qty_pcs'] + (int)$item['free'];
            db_run(
                "UPDATE products
                    SET stock_pcs  = stock_pcs + ?,
                        btp        = ?,
                        vat        = ?,
                        btp_vat    = ?,
                        sale_price = IF(? > 0, ?, sale_price),
                        expiry     = ?,
                        updated_at = NOW()
                  WHERE id = ?",
                [
                    $addStock,
                    $item['btp'], $item['vat'], $item['btp_vat'],
                    $item['sale_price'], $item['sale_price'],
                    $item['exp'],
                    $item['product_id'],
                ]
            );
            $newStock = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$item['product_id']]);
            publish_stock_event((int)$item['product_id'], 'purchase', $addStock, $newStock, 'purchase', $id);
            record_stock_ledger((int) $item['product_id'], 'purchase', $addStock, 0, $newStock,
                'purchases', $id, $po['invoice']);
        }

        if ((float)$po['due'] > 0 && (int)$po['supplier_id'] > 0) {
            db_run(
                "UPDATE suppliers SET balance = balance + ? WHERE id = ?",
                [$po['due'], $po['supplier_id']]
            );
        }

        if ((float)$po['paid'] > 0) {
            $ledgerId = db_insert(
                "INSERT INTO cash_ledger
                   (date, description, account, type, amount, ref)
                 VALUES (?, ?, ?, 'out', ?, ?)",
                [
                    $po['date'],
                    "Purchase payment — {$po['supplier_name']} ({$po['invoice']})",
                    $po['account'] ?: 'Cash',
                    $po['paid'],
                    $po['invoice'],
                ]
            );
            enqueue_sync('cash_ledger', $ledgerId, 'INSERT',
                db_row("SELECT * FROM cash_ledger WHERE id = ?", [$ledgerId]));
        }

        db_run(
            "UPDATE purchases SET approval_status = 'approved', approved_by = ?, approved_at = NOW()
              WHERE id = ?",
            [current_user_id(), $id]
        );
        $poSnapshot = db_row("SELECT * FROM purchases WHERE id = ?", [$id]);
        $poSnapshot['items'] = db_rows("SELECT * FROM purchase_items WHERE purchase_id = ?", [$id]);
        enqueue_sync('purchases', $id, 'UPDATE', $poSnapshot);

        db()->commit();

        log_action('approve', 'purchases', $id, "Approved PO {$po['invoice']}");

        create_notification(
            $po['submitted_by'] ? [(int)$po['submitted_by']] : 'admins',
            'po_approved', '✅', 'Purchase Order Approved',
            "PO {$po['invoice']} has been approved — stock and ledger have been updated.",
            'purchase/list.php'
        );

        ok(['msg' => 'Purchase order approved. Stock and ledger have been updated.']);
    } catch (Throwable $e) {
        db()->rollBack();
        fail('Approval failed: ' . $e->getMessage());
    }
}

// ── Reject a pending PO (admin/manager) ─────────────────────────
// No stock/ledger effects to undo — a pending PO never applied them.
function api_reject(array $b): void {
    if (!can_approve_po()) fail('Only admins/managers can reject purchase orders.');

    $id     = (int)($b['id'] ?? 0);
    $reason = trim($b['reason'] ?? '');
    if (!$id) fail('Missing id');

    $po = db_row("SELECT * FROM purchases WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$po) fail('Purchase order not found');
    if ($po['approval_status'] !== 'pending') {
        fail('This purchase order is not awaiting approval (status: ' . $po['approval_status'] . ').');
    }

    db_run(
        "UPDATE purchases
            SET approval_status = 'rejected', approved_by = ?, approved_at = NOW(),
                rejection_reason = ?
          WHERE id = ?",
        [current_user_id(), $reason, $id]
    );
    enqueue_sync('purchases', $id, 'UPDATE', db_row("SELECT * FROM purchases WHERE id = ?", [$id]));

    log_action('update', 'purchases', $id,
        "Rejected PO {$po['invoice']}" . ($reason ? " — {$reason}" : ''));

    create_notification(
        $po['submitted_by'] ? [(int)$po['submitted_by']] : 'admins',
        'po_rejected', '⛔', 'Purchase Order Rejected',
        "PO {$po['invoice']} was rejected." . ($reason ? " Reason: {$reason}" : ''),
        'purchase/list.php'
    );

    ok(['msg' => 'Purchase order rejected.']);
}

// ── Next PO reference number ──────────────────────────────────
function api_next_ref(): void {
    // DISPLAY PREVIEW ONLY — see comment in api/sales.php's api_next_ref().
    // The real PO number is always derived atomically from the
    // AUTO_INCREMENT id inside api_save(), so this preview being
    // slightly stale can never cause a collision.
    $cfg    = get_settings();
    $prefix = $cfg['po_prefix'] ?? 'PO';
    $year   = date('Y');
    $nextId = (int) db_val(
        "SELECT AUTO_INCREMENT FROM information_schema.TABLES
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'purchases'"
    );
    if (!$nextId) $nextId = 1;
    ok(['ref' => sprintf('%s-%s-%06d', $prefix, $year, $nextId)]);
}

// ── List all purchases ────────────────────────────────────────
function api_list(array $b): void {
    $q           = trim($b['q']    ?? '');
    $from        = trim($b['from'] ?? '');
    $to          = trim($b['to']   ?? '');
    $appr        = trim($b['approval_status'] ?? '');
    $submittedBy = (int)($b['submitted_by'] ?? 0);
    $where       = ['1=1'];
    $params      = [];

    if ($q) {
        $where[]  = "(p.invoice LIKE ? OR p.supplier_name LIKE ? OR p.inv_no LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }
    if ($from)        { $where[] = "p.date >= ?"; $params[] = $from; }
    if ($to)          { $where[] = "p.date <= ?"; $params[] = $to;   }
    if ($appr)        { $where[] = "p.approval_status = ?"; $params[] = $appr; }
    if ($submittedBy) { $where[] = "p.submitted_by = ?"; $params[] = $submittedBy; }

    $wSql = implode(' AND ', $where);
    $rows = db_rows(
        "SELECT p.*,
                COUNT(pi.id)  AS item_count,
                su.name       AS submitted_by_name,
                ap.name       AS approved_by_name
           FROM purchases p
      LEFT JOIN purchase_items pi ON pi.purchase_id = p.id
      LEFT JOIN users su ON su.id = p.submitted_by
      LEFT JOIN users ap ON ap.id = p.approved_by
          WHERE p.is_deleted = 0 AND $wSql
          GROUP BY p.id
          ORDER BY p.date DESC, p.id DESC",
        $params
    );
    ok(['data' => $rows]);
}

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

    $po = db_row(
        "SELECT p.*, su.name AS submitted_by_name, ap.name AS approved_by_name
           FROM purchases p
      LEFT JOIN users su ON su.id = p.submitted_by
      LEFT JOIN users ap ON ap.id = p.approved_by
          WHERE p.id = ? AND p.is_deleted = 0",
        [$id]
    );
    if (!$po) fail('Purchase order not found');

    $items = db_rows(
        "SELECT pi.*,
                p.barcode, p.rack, p.form
           FROM purchase_items pi
      LEFT JOIN products p ON p.id = pi.product_id
          WHERE pi.purchase_id = ?
          ORDER BY pi.id",
        [$id]
    );

    ok(['data' => $po, 'items' => $items]);
}

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

    $po = db_row("SELECT * FROM purchases WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$po) fail('Purchase order not found');

    // Pending/rejected POs never touched stock or the supplier balance
    // (that only happens on approval), so there's nothing to reverse —
    // reversing here would incorrectly subtract stock that was never added.
    $wasApplied = in_array($po['approval_status'] ?? 'not_required', ['approved', 'not_required']);

    db()->beginTransaction();
    try {
        if ($wasApplied) {
            // Reverse stock (undo the addition when PO was received)
            $items = db_rows("SELECT * FROM purchase_items WHERE purchase_id = ?", [$id]);
            foreach ($items as $item) {
                $removeStock = ($item['qty_pcs'] ?? 0) + ($item['free'] ?? 0);
                db_run(
                    "UPDATE products SET stock_pcs = GREATEST(0, stock_pcs - ?) WHERE id = ?",
                    [$removeStock, $item['product_id']]
                );
                $newStock = (int) db_val("SELECT stock_pcs FROM products WHERE id = ?", [$item['product_id']]);
                publish_stock_event((int)$item['product_id'], 'purchase_reversal', -$removeStock, $newStock, 'purchase', $id);
                // Same "no dedicated reversal type" reasoning as sales.php's
                // delete path — recorded as 'purchase' with a qty_out
                // movement (the accurate direction) plus an explanatory remark.
                record_stock_ledger((int) $item['product_id'], 'purchase', 0, (int) $removeStock, $newStock,
                    'purchases', $id, $po['invoice'], 'PO deleted — stock reversed');
            }

            // Reverse supplier due balance
            if ((float)$po['due'] > 0 && (int)$po['supplier_id'] > 0) {
                db_run(
                    "UPDATE suppliers SET balance = GREATEST(0, balance - ?) WHERE id = ?",
                    [$po['due'], $po['supplier_id']]
                );
            }
        }

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

        db()->commit();

        log_action('delete', 'purchases', $id,
            'Soft-deleted PO: ' . ($po['invoice'] ?? '#' . $id));

        ok(['msg' => 'Purchase moved to Recycle Bin']);

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


// ── 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;
}
