<?php
// ============================================================
//  MediCore — Requisitions API
//  File: api/requisitions.php
// ============================================================
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_login();
// Note: session intentionally stays open — the 'generate_po' action below
// writes $_SESSION['po_from_req'] so purchase/entry.php can read it after
// redirect. Closing the session early would prevent that write from
// persisting to disk.

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

// Must run before any transaction starts (DDL-inside-transaction rule
// — see the identical comment in api/sales.php etc.). Without this,
// api_save() below fails on every single call — see the comment on
// ensure_requisition_items_columns() in includes/db.php.
ensure_requisition_items_columns();

$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),
        'browse'        => api_browse($body),
        'detail'        => api_detail($body),
        'update_status' => api_update_status($body),
        'generate_po'   => api_generate_po($body),
        'delete'        => api_delete($body),
        default         => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── Save ──────────────────────────────────────────────────────
function api_save(array $b): void {
    $ref      = trim($b['ref']           ?? '');
    // supplier_id is a nullable FK (ON DELETE SET NULL) — "Unknown Supplier"
    // groups from Smart Requisition send an empty string here, which must
    // become NULL, not 0 (there is no suppliers.id = 0 row, so inserting 0
    // trips the foreign key constraint).
    $suppIdRaw = trim((string)($b['supplier_id'] ?? ''));
    $suppId    = ($suppIdRaw !== '' && (int)$suppIdRaw > 0) ? (int)$suppIdRaw : null;
    $suppName = trim($b['supplier_name'] ?? '');
    $items    = $b['items']              ?? [];

    // Auto-generate ref if empty
    if (!$ref) {
        $cfg = get_settings();
        $prefix = $cfg['req_prefix'] ?? 'REQ';
        $ref = next_ref($prefix, 'requisitions', 'ref');
    }
    if (!$items) fail('No products selected');

    if (db_val("SELECT id FROM requisitions WHERE ref = ?", [$ref]))
        $ref = $ref . '-' . date('His'); // make unique

    $date  = trim($b['date']        ?? date('Y-m-d'));
    $total = (float)($b['total_price'] ?? 0);
    $notes = trim($b['notes']       ?? '');

    db()->beginTransaction();
    try {
        $id = db_insert(
            "INSERT INTO requisitions
               (ref, supplier_id, supplier_name, date,
                status, total_price, notes)
             VALUES (?,?,?,?,'active',?,?)",
            [$ref, $suppId, $suppName, $date, $total, $notes]
        );

        foreach ($items as $item) {
            db_run(
                "INSERT INTO requisition_items
                   (requisition_id, product_id, product_name,
                    qty_box, btp_vat, total_price,
                    current_stock, alert_qty, suggested_qty)
                 VALUES (?,?,?,?,?,?,?,?,?)",
                [
                    $id,
                    (int)($item['product_id']    ?? 0),
                    trim($item['product_name']   ?? ''),
                    max(1, (int)($item['qty_box']   ?? 1)),
                    (float)($item['btp_vat']     ?? 0),
                    (float)($item['total_price'] ?? 0),
                    (int)($item['current_stock'] ?? 0),
                    (int)($item['alert_qty']     ?? 0),
                    (int)($item['suggested_qty'] ?? 0),
                ]
            );
        }

        db()->commit();
        ok(['ref' => $ref, 'id' => $id, 'msg' => 'Requisition created']);

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

// ── Next REQ reference ────────────────────────────────────────
function api_next_ref(): void {
    $cfg    = get_settings();
    $prefix = $cfg['req_prefix'] ?? 'REQ';
    ok(['ref' => next_ref($prefix, 'requisitions', 'ref')]);
}

// ── List ──────────────────────────────────────────────────────
function api_list(array $b): void {
    $where  = ['1=1'];
    $params = [];
    $q      = trim($b['q']      ?? '');
    $status = trim($b['status'] ?? '');

    if ($q) {
        $where[]  = "(r.ref LIKE ? OR r.supplier_name LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%";
    }
    if ($status) { $where[] = "r.status = ?"; $params[] = $status; }

    $wSql = implode(' AND ', $where);
    $rows = db_rows(
        "SELECT r.*, COUNT(ri.id) AS item_count
           FROM requisitions r
      LEFT JOIN requisition_items ri ON ri.requisition_id = r.id
          WHERE $wSql
          GROUP BY r.id
          ORDER BY r.date DESC, r.id DESC",
        $params
    );
    ok(['data' => $rows]);
}

// ── Browse (paginated product catalog for Manual Requisition) ──
// Same enriched query product/requisition.php used to run inline
// (last supplier, 90-day avg, pending PO qty, suggested boxes),
// but paged so the left-hand list doesn't have to load the whole
// catalog. Not restricted by low_stock_alert — that filtering is
// Smart Requisition's job; Manual Requisition browses everything.
function api_browse(array $b): void {
    ensure_soft_delete('products');

    $where  = ['p.is_active = 1', 'p.is_deleted = 0'];
    $params = [];

    $q = trim($b['q'] ?? '');
    if ($q) {
        $where[]  = "(p.name LIKE ? OR p.barcode LIKE ? OR p.strength LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }

    $wSql = implode(' AND ', $where);

    $page     = max(1, (int)($b['page'] ?? 1));
    $pageSize = (int)($b['page_size'] ?? 50);
    $pageSize = max(10, min(200, $pageSize));
    $offset   = ($page - 1) * $pageSize;

    $total = (int) db_val("SELECT COUNT(*) FROM products p WHERE $wSql", $params);

    // Photo columns dropped entirely — this browse table never renders
    // product photos, so there's no reason to ship them (image can be
    // up to ~700KB/row; see products_columns_except() in includes/db.php).
    $prodCols = products_columns_except(['image', 'image_thumb']);
    $rows = db_rows(
        "SELECT $prodCols,
                g.name  AS generic_name,
                c.name  AS company_name,
                (SELECT s.name FROM purchases po
                   JOIN purchase_items pi2 ON pi2.purchase_id = po.id
                   JOIN suppliers s ON s.id = po.supplier_id
                  WHERE pi2.product_id = p.id
                  ORDER BY po.date DESC LIMIT 1) AS last_supplier,
                (SELECT po.supplier_id FROM purchases po
                   JOIN purchase_items pi2 ON pi2.purchase_id = po.id
                  WHERE pi2.product_id = p.id
                  ORDER BY po.date DESC LIMIT 1) AS last_supplier_id,
                COALESCE((SELECT SUM(si.qty)/3 FROM sale_items si
                   JOIN sales s2 ON s2.id = si.sale_id
                  WHERE si.product_id = p.id
                    AND s2.date >= DATE_SUB(NOW(), INTERVAL 90 DAY)),0) AS avg_monthly,
                COALESCE((SELECT SUM(ri.qty_box * p.qty_in_box)
                   FROM requisition_items ri
                   JOIN requisitions r ON r.id = ri.requisition_id
                  WHERE ri.product_id = p.id
                    AND r.status IN ('active','ordered')),0) AS pending_qty
           FROM products p
      LEFT JOIN generics  g ON g.id = p.generic_id
      LEFT JOIN companies c ON c.id = p.company_id
          WHERE $wSql
       ORDER BY p.name ASC
          LIMIT $pageSize OFFSET $offset",
        $params
    );

    foreach ($rows as &$p) {
        $stock   = (int)$p['stock_pcs'];
        $alert   = (int)$p['low_stock_alert'];
        $avg     = (float)$p['avg_monthly'];
        $pending = (int)$p['pending_qty'];
        $minQty  = (int)$p['min_qty'];
        $qib     = max(1, (int)$p['qty_in_box']);

        $avgDaily = $avg > 0 ? $avg / 30 : 0;
        $p['days_remaining'] = $avgDaily > 0 ? round($stock / $avgDaily, 1) : null;

        $sugPcsRaw = max($alert * 2, (int)ceil($avg * 1.5)) - $stock - $pending;
        $sugPcs    = max(0, $sugPcsRaw);
        if ($sugPcs > 0 && $minQty > 0) $sugPcs = max($sugPcs, $minQty);
        $p['sug_boxes']     = $sugPcs > 0 ? max(1, (int)ceil($sugPcs / $qib)) : 0;
        $p['covered_by_po'] = $sugPcsRaw <= 0 && $pending > 0;
    }
    unset($p);

    ok([
        'data'        => $rows,
        'page'        => $page,
        'page_size'   => $pageSize,
        'total'       => $total,
        'total_pages' => (int) max(1, ceil($total / $pageSize)),
    ]);
}

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

    $req = db_row("SELECT * FROM requisitions WHERE id = ?", [$id]);
    if (!$req) fail('Requisition not found');

    $items = db_rows(
        "SELECT ri.*, p.stock_pcs, p.btp_vat AS current_price,
                p.rack, p.form, p.strength
           FROM requisition_items ri
      LEFT JOIN products p ON p.id = ri.product_id
          WHERE ri.requisition_id = ?
          ORDER BY ri.id",
        [$id]
    );
    ok(['data' => $req, 'items' => $items]);
}

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

    $valid = ['active','ordered','received','cancelled'];
    if (!in_array($status, $valid)) fail('Invalid status');

    db_run(
        "UPDATE requisitions SET status = ? WHERE id = ?",
        [$status, $id]
    );
    ok(['msg' => 'Status updated to ' . $status]);
}

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

    db_run("DELETE FROM requisition_items WHERE requisition_id = ?", [$id]);
    db_run("DELETE FROM requisitions WHERE id = ?",                  [$id]);
    ok(['msg' => 'Requisition deleted']);
}

// ── Generate PO from Requisition ────────────────────────────────
function api_generate_po(array $b): void {
    $reqId = (int)($b['req_id'] ?? 0);
    $mode  = trim($b['mode']    ?? 'unit_trvat');

    if (!$reqId) fail('Missing requisition id');
    if (!in_array($mode, ['unit_trvat', 'net_value'])) fail('Invalid purchase mode');

    $req = db_row("SELECT * FROM requisitions WHERE id = ?", [$reqId]);
    if (!$req) fail('Requisition not found');

    if ($req['status'] !== 'active') {
        fail('Only Active requisitions can generate a PO (this one is "' . $req['status'] . '").');
    }

    // Pull requisition items joined to LIVE product data (current prices,
    // box size, etc.) so the PO reflects up-to-date pricing rather than
    // whatever was on record when the requisition was first created.
    $items = db_rows(
        "SELECT ri.product_id, ri.product_name, ri.qty_box, ri.btp_vat AS req_btp_vat,
                p.btp, p.vat, p.btp_vat, p.sale_price, p.qty_in_box
           FROM requisition_items ri
      LEFT JOIN products p ON p.id = ri.product_id
          WHERE ri.requisition_id = ?
          ORDER BY ri.id",
        [$reqId]
    );
    if (!$items) fail('This requisition has no items to order.');

    $poItems = array_map(function (array $it): array {
        return [
            'product_id'   => (int)$it['product_id'],
            'product_name' => $it['product_name'],
            'sale_price'   => (float)($it['sale_price'] ?? 0),
            'btp'          => (float)($it['btp']        ?? 0),
            'vat'          => (float)($it['vat']         ?? 0),
            // Fall back to the requisition's own snapshot price if the
            // product record is missing (e.g. product was deactivated).
            'btp_vat'      => (float)($it['btp_vat'] ?? $it['req_btp_vat'] ?? 0),
            'qty_in_box'   => max(1, (int)($it['qty_in_box'] ?? 1)),
            'boxes'        => max(1, (int)$it['qty_box']),
        ];
    }, $items);

    // Re-open the session for writing — it's read-only by default in this
    // file for most actions, but generate_po needs to pass data forward
    // to purchase/entry.php via $_SESSION.
    if (session_status() !== PHP_SESSION_ACTIVE) {
        session_start();
    }
    $_SESSION['po_from_req'] = [
        'mode'        => $mode,
        'supplier_id' => (int)$req['supplier_id'],
        'req_ref'     => $req['ref'],
        'items'       => $poItems,
    ];

    // Mark requisition as ordered so it won't be double-converted
    db_run("UPDATE requisitions SET status = 'ordered' WHERE id = ?", [$reqId]);

    ok([
        'msg'      => 'Purchase Order prepared from requisition ' . $req['ref'],
        'redirect' => '../purchase/entry.php?from_req=1',
    ]);
}

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