<?php
// ============================================================
//  MediCore — Products API
//  File: api/products.php
//  Accepts JSON POST, returns JSON
// ============================================================
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_login();
session_write_close(); // release session lock — prevents XAMPP request queuing

// ── Auto-migration: guarantee low_stock_alert column exists ───
// (older databases created before this feature was added won't have it)
try {
    $hasAlertCol = db_val(
        "SELECT COUNT(*) FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME   = 'products'
            AND COLUMN_NAME  = 'low_stock_alert'"
    );
    if (!$hasAlertCol) {
        db_run("ALTER TABLE products ADD COLUMN low_stock_alert INT UNSIGNED DEFAULT 0 AFTER min_qty");
    }
} catch (Throwable $e) { /* best-effort — ignore if insufficient privileges */ }

// ── Auto-migration: guarantee max_retail_disc column exists ───
try {
    $hasMrdCol = db_val(
        "SELECT COUNT(*) FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME   = 'products'
            AND COLUMN_NAME  = 'max_retail_disc'"
    );
    if (!$hasMrdCol) {
        db_run("ALTER TABLE products ADD COLUMN max_retail_disc DECIMAL(5,2) DEFAULT 0.00 AFTER low_stock_alert");
    }
} catch (Throwable $e) { /* best-effort */ }

ensure_products_supplier_column();
ensure_products_image_column();
ensure_products_thumb_column();
ensure_product_thumbnails_migrated();

ensure_soft_delete('products');
ensure_categories_schema();

// ── CSV export (GET, opens in new tab) ────────────────────────
if (($_GET['action'] ?? '') === 'export_alert_csv') {
    $rows = db_rows(
        "SELECT p.id, p.name, p.low_stock_alert, p.stock_pcs,
                c.name AS company_name
           FROM products p
      LEFT JOIN companies c ON c.id = p.company_id
          WHERE p.is_active = 1
              AND p.is_deleted = 0
       ORDER BY p.name"
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="low_stock_alert_export.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['product_id','name','alert_qty','current_stock','company']);
    foreach ($rows as $r) {
        fputcsv($out, [
            $r['id'], $r['name'], $r['low_stock_alert'] ?: 0,
            $r['stock_pcs'], $r['company_name'] ?? '',
        ]);
    }
    fclose($out);
    exit;
}

// ── Stock Report CSV ──────────────────────────────────────────
if (($_GET['action'] ?? '') === 'export_stock_csv') {
    $rows = db_rows(
        "SELECT p.name, g.name AS generic, c.name AS company,
                p.strength, p.form, p.barcode,
                p.stock_pcs, p.low_stock_alert, p.sale_price,
                (p.stock_pcs * p.sale_price) AS stock_value
           FROM products p
      LEFT JOIN generics  g ON g.id = p.generic_id
      LEFT JOIN companies c ON c.id = p.company_id
          WHERE p.is_active = 1
              AND p.is_deleted = 0
       ORDER BY p.name"
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="stock_report_'.date('Y-m-d').'.csv"');
    $out = fopen('php://output','w');
    fputcsv($out,['Product','Generic','Company','Strength','Form','Barcode',
                  'Stock (pcs)','Alert Qty','Sale Price','Stock Value','Status']);
    foreach ($rows as $r) {
        $st = (int)$r['stock_pcs'];
        $al = (int)($r['low_stock_alert']??0);
        $status = $st<=0?'Out of Stock':($al>0&&$st<$al?'Low Stock':'Sufficient');
        fputcsv($out,[$r['name'],$r['generic']??'',$r['company']??'',
            $r['strength']??'',$r['form']??'',$r['barcode']??'',
            $r['stock_pcs'],$r['low_stock_alert']?:0,
            $r['sale_price'],number_format((float)$r['stock_value'],2),$status]);
    }
    fclose($out); exit;
}

// ── Expiry Report CSV ─────────────────────────────────────────
if (($_GET['action'] ?? '') === 'export_expiry_csv') {
    $in2m = date('Y-m', strtotime('+2 months'));
    $rows = db_rows(
        "SELECT p.name AS product, p.strength, pi.exp, pi.qty_pcs,
                pi.free, po.invoice AS po_no, po.date AS po_date, s.name AS supplier
           FROM purchase_items pi
           JOIN products  p  ON p.id  = pi.product_id
           JOIN purchases po ON po.id = pi.purchase_id
      LEFT JOIN suppliers s  ON s.id  = po.supplier_id
          WHERE pi.exp != '' AND pi.exp <= ?
          ORDER BY pi.exp ASC, p.name ASC", [$in2m]
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="expiry_report_'.date('Y-m-d').'.csv"');
    $out = fopen('php://output','w');
    fputcsv($out,['Product','Strength','Expiry','Qty (pcs)','Free','PO Invoice','PO Date','Supplier']);
    foreach ($rows as $r) {
        fputcsv($out,[$r['product'],$r['strength']??'',$r['exp'],
            $r['qty_pcs'],$r['free']??0,$r['po_no'],$r['po_date'],$r['supplier']??'']);
    }
    fclose($out); exit;
}

// ── Company-wise Stock CSV ────────────────────────────────────
if (($_GET['action'] ?? '') === 'export_company_csv') {
    $rows = db_rows(
        "SELECT COALESCE(c.name,'(No Company)') AS company,
                COUNT(p.id)                     AS products,
                SUM(p.stock_pcs)                AS total_stock,
                SUM(p.stock_pcs * p.sale_price) AS stock_value,
                SUM(CASE WHEN p.stock_pcs<=0 THEN 1 ELSE 0 END) AS out_count,
                SUM(CASE WHEN p.stock_pcs>0 AND p.low_stock_alert>0
                          AND p.stock_pcs<p.low_stock_alert THEN 1 ELSE 0 END) AS low_count
           FROM products p
      LEFT JOIN companies c ON c.id = p.company_id
          WHERE p.is_active = 1
              AND p.is_deleted = 0
          GROUP BY c.id, c.name ORDER BY stock_value DESC"
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="company_stock_'.date('Y-m-d').'.csv"');
    $out = fopen('php://output','w');
    fputcsv($out,['Company','Products','Total Stock (pcs)','Stock Value','Out of Stock','Low Stock']);
    foreach ($rows as $r) {
        fputcsv($out,[$r['company'],$r['products'],$r['total_stock'],
            number_format((float)$r['stock_value'],2),$r['out_count'],$r['low_count']]);
    }
    fclose($out); exit;
}

// ── Category-wise Stock CSV ────────────────────────────────────
if (($_GET['action'] ?? '') === 'export_category_csv') {
    ensure_categories_schema();
    $rows = db_rows(
        "SELECT COALESCE(cat.name,'(Uncategorized)') AS category,
                COUNT(p.id)                     AS products,
                SUM(p.stock_pcs)                AS total_stock,
                SUM(p.stock_pcs * p.sale_price) AS stock_value,
                SUM(CASE WHEN p.stock_pcs<=0 THEN 1 ELSE 0 END) AS out_count,
                SUM(CASE WHEN p.stock_pcs>0 AND p.low_stock_alert>0
                          AND p.stock_pcs<p.low_stock_alert THEN 1 ELSE 0 END) AS low_count
           FROM products p
      LEFT JOIN categories cat ON cat.id = p.category_id
          WHERE p.is_active = 1
              AND p.is_deleted = 0
          GROUP BY cat.id, cat.name ORDER BY stock_value DESC"
    );
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="category_stock_'.date('Y-m-d').'.csv"');
    $out = fopen('php://output','w');
    fputcsv($out,['Category','Products','Total Stock (pcs)','Stock Value','Out of Stock','Low Stock']);
    foreach ($rows as $r) {
        fputcsv($out,[$r['category'],$r['products'],$r['total_stock'],
            number_format((float)$r['stock_value'],2),$r['out_count'],$r['low_count']]);
    }
    fclose($out); exit;
}

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

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

// Auto-create price_history table
db_run("CREATE TABLE IF NOT EXISTS price_history (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    product_name VARCHAR(150) DEFAULT '',
    price_type   ENUM('retail','wholesale') NOT NULL,
    old_price    DECIMAL(10,2) DEFAULT 0.00,
    new_price    DECIMAL(10,2) DEFAULT 0.00,
    changed_by   INT UNSIGNED,
    changed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_changed (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

function log_price_change(int $pid, string $name, string $type,
                           float $old, float $new): void {
    if (abs($old - $new) < 0.001) return; // no change
    db_run(
        "INSERT INTO price_history
           (product_id,product_name,price_type,old_price,new_price,changed_by)
         VALUES (?,?,?,?,?,?)",
        [$pid, $name, $type, $old, $new, current_user_id()]
    );
}
$action = trim($body['action'] ?? '');

// ── Route ─────────────────────────────────────────────────────
try {
    match($action) {
        'list'             => api_list($body),
        'detail'           => api_detail($body),
        'add'              => api_add($body),
        'edit'             => api_edit($body),
        'delete'           => api_delete($body),
        'price_history'    => api_price_history($body),
        'save_ws_price'    => api_save_ws_price($body),
        'set_alert'        => api_set_alert($body),
        'bulk_set_alert'   => api_bulk_set_alert($body),
        'bulk_set_supplier'=> api_bulk_set_supplier($body),
        'auto_match_suppliers' => api_auto_match_suppliers($body),
        'import_alert_csv' => api_import_alert_csv($body),
        'get_alternatives' => api_get_alternatives($body),
        default            => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── List / Search ─────────────────────────────────────────────
// Search is optimized to match across every field staff actually
// think of a medicine by — not just its own name/barcode/strength,
// but also its generic (active ingredient), company (manufacturer),
// form, and category. Exact-match filter dropdowns (category/generic/
// company/form/stock status) are ANDed on top of the free-text search.
// generic_id/company_id/form all have indexes now (ensure_categories_
// schema() in includes/db.php) so these joins/filters stay fast even
// on large catalogs.
//
// Two response shapes share this one function:
//  - Plain list (Product List page): full pagination metadata, needs
//    an accurate COUNT(*) since it drives page numbers.
//  - `suggest: true` (Sale Entry / Purchase Entry search-as-you-type):
//    fired on every keystroke, so it skips the COUNT(*) query entirely
//    and hard-caps the result to a handful of rows — the UI only ever
//    shows a short dropdown, there's no reason to count or fetch more.
function api_list(array $b): void {
    $where  = ['p.is_active = 1', 'p.is_deleted = 0'];
    $params = [];

    $q = trim($b['q'] ?? '');
    if ($q) {
        $like = "%$q%";
        $where[] = "(p.name LIKE ? OR p.barcode LIKE ? OR p.strength LIKE ?
                     OR g.name LIKE ? OR c.name LIKE ? OR p.form LIKE ? OR cat.name LIKE ?)";
        array_push($params, $like, $like, $like, $like, $like, $like, $like);
    }
    $companyId = (int)($b['company_id'] ?? 0);
    if ($companyId) { $where[] = "p.company_id = ?"; $params[] = $companyId; }

    $genericId = (int)($b['generic_id'] ?? 0);
    if ($genericId) { $where[] = "p.generic_id = ?"; $params[] = $genericId; }

    $categoryId = (int)($b['category_id'] ?? 0);
    if ($categoryId) { $where[] = "p.category_id = ?"; $params[] = $categoryId; }

    $form = trim($b['form'] ?? '');
    if ($form) { $where[] = "p.form = ?"; $params[] = $form; }

    $stockStatus = trim($b['stock_status'] ?? '');
    if ($stockStatus === 'out') {
        $where[] = "p.stock_pcs <= 0";
    } elseif ($stockStatus === 'low') {
        $where[] = "p.stock_pcs > 0 AND p.low_stock_alert > 0 AND p.stock_pcs < p.low_stock_alert";
    } elseif ($stockStatus === 'in') {
        $where[] = "p.stock_pcs > 0 AND (p.low_stock_alert = 0 OR p.stock_pcs >= p.low_stock_alert)";
    }

    $wSql = implode(' AND ', $where);
    $joins = "LEFT JOIN generics   g   ON g.id   = p.generic_id
              LEFT JOIN companies  c   ON c.id   = p.company_id
              LEFT JOIN categories cat ON cat.id = p.category_id";

    $suggest = !empty($b['suggest']);

    // ── Pagination ──────────────────────────────────────────────
    // The catalog can run into the tens of thousands of rows, so an
    // unpaginated list previously tried to return every row in one
    // JSON response and render every <tr> in one pass — that stalls
    // indefinitely on large catalogs. Page size is capped both floor
    // and ceiling so a bad client value can't force a huge query.
    $page     = max(1, (int)($b['page'] ?? 1));
    $pageSize = (int)($b['page_size'] ?? 50);
    $pageSize = $suggest
        ? max(1, min(20, $pageSize ?: 12))   // tight cap — it's a dropdown, not a page
        : max(10, min(200, $pageSize));
    $offset   = $suggest ? 0 : ($page - 1) * $pageSize;

    // COUNT(*) uses only the WHERE-clause params, bound as-is — it must
    // run BEFORE the relevance-ordering param (below) is appended, since
    // that extra placeholder only exists in the SELECT's ORDER BY, not here.
    $total = $suggest ? null : (int) db_val("SELECT COUNT(*) FROM products p $joins WHERE $wSql", $params);

    // ── Relevance ordering ──────────────────────────────────────
    // When searching by name, a product whose name *starts with* what
    // was typed is almost always what staff are looking for — put those
    // first instead of leaving it to whatever order a substring match
    // happens to come back in. Falls back to a plain alphabetical sort
    // when there's no search text (browsing/filtering only). Appended
    // (not prepended) to $params — its `?` is the LAST one in the SQL
    // text below, coming after every WHERE-clause placeholder.
    $orderBy = 'p.name';
    if ($q !== '') {
        $orderBy = 'CASE WHEN p.name LIKE ? THEN 0 ELSE 1 END, p.name';
        $params[] = "$q%";
    }

    // Full-size photos (up to ~700KB) are never sent for multi-row
    // results — image_thumb (a few KB, see make_product_thumbnail())
    // is aliased back to `image` so existing renderers (product/list.php,
    // Sale Entry's suggestion dropdown) need no changes to pick it up.
    $cols = products_columns_except(['image']);
    $rows = db_rows(
        "SELECT $cols,
                g.name   AS generic_name,
                c.name   AS company_name,
                cat.name AS category_name
           FROM products p
           $joins
          WHERE $wSql
       ORDER BY $orderBy
          LIMIT $pageSize OFFSET $offset",
        $params
    );
    foreach ($rows as &$r) {
        $r['image'] = $r['image_thumb'] ?: null;
        unset($r['image_thumb']);
    }
    unset($r);

    if ($suggest) {
        ok(['data' => $rows]);
        return;
    }

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

// ── Get Alternative Medicines ──────────────────────────────────
// Returns all active products sharing the same generic_id, strength,
// and form as the reference product, excluding that product itself.
// Sorted: in-stock first, then by profit margin (sale_price - btp_vat)
// descending — highest-margin alternatives come first.
function api_get_alternatives(array $b): void {
    $id = (int)($b['product_id'] ?? 0);
    if (!$id) fail('Missing product_id');

    // Fetch the reference product's identifiers
    $ref = db_row(
        "SELECT generic_id, strength, form FROM products WHERE id = ? AND is_active = 1",
        [$id]
    );
    if (!$ref || !$ref['generic_id']) {
        ok(['data' => [], 'ref_generic' => '']);
        return;
    }

    $rows = db_rows(
        "SELECT p.id,
                p.name,
                p.form,
                p.strength,
                p.qty_in_box,
                p.btp,
                p.vat,
                p.btp_vat,
                p.sale_price,
                p.stock_pcs,
                p.low_stock_alert,
                p.max_retail_disc,
                p.expiry,
                p.image_thumb AS image,
                g.name AS generic_name,
                c.name AS company_name,
                ROUND(
                    CASE WHEN p.btp_vat > 0
                         THEN ((p.sale_price - (p.btp_vat / GREATEST(p.qty_in_box,1))) / NULLIF(p.sale_price,0)) * 100
                         ELSE 0 END,
                    1
                ) AS profit_pct
           FROM products p
      LEFT JOIN generics  g ON g.id = p.generic_id
      LEFT JOIN companies c ON c.id = p.company_id
          WHERE p.is_active = 1
              AND p.is_deleted = 0
            AND p.generic_id = ?
            AND p.strength   = ?
            AND p.form       = ?
            AND p.id        != ?
       ORDER BY (p.stock_pcs > 0) DESC,
                profit_pct          DESC,
                p.sale_price        ASC",
        [$ref['generic_id'], $ref['strength'], $ref['form'], $id]
    );

    // Fetch the generic name separately for the panel header
    $genericName = db_val("SELECT name FROM generics WHERE id = ?", [$ref['generic_id']]) ?? '';

    ok(['data' => $rows, 'ref_generic' => $genericName]);
}

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

    $row = db_row(
        "SELECT p.*,
                g.name   AS generic_name,
                c.name   AS company_name,
                cat.name AS category_name
           FROM products p
      LEFT JOIN generics   g   ON g.id   = p.generic_id
      LEFT JOIN companies  c   ON c.id   = p.company_id
      LEFT JOIN categories cat ON cat.id = p.category_id
          WHERE p.id = ?",
        [$id]
    );
    if (!$row) fail('Product not found');
    ok(['data' => $row]);
}

// ── Add ───────────────────────────────────────────────────────
// Validate an incoming product photo (base64 data-URI from the browser's
// <canvas>-downscaled FileReader output — see product/list.php). Shared by
// api_add()/api_edit() so both apply the exact same rules. The client
// already resizes to ~360px before upload (typically 5-15KB), so this
// server-side cap is a generous backstop against a bypassed/huge upload,
// not the primary size control.
function validate_product_image(string $raw): ?string {
    $image = trim($raw);
    if ($image === '') return null;
    if (!str_starts_with($image, 'data:image/')) {
        fail('Invalid product image format.');
    }
    if (strlen($image) > 700_000) {
        fail('Product image is too large — please choose a smaller photo.');
    }
    return $image;
}

// ── Search-optimization: server-side thumbnail generation ─────────
// Search/list/browse results show this tiny square JPEG instead of the
// full-size photo — see products_columns_except() in includes/db.php.
// Best-effort: if GD isn't available, or the source data can't be
// decoded, this just returns null (caller falls back to no thumbnail
// for that row) rather than failing the whole add/edit request.
//
// (Local vars, not top-level `const` — this function runs from the
// request dispatcher near the top of the file, before a plain
// top-level `const` declared further down would actually have executed.)
function make_product_thumbnail(?string $dataUri): ?string {
    // 160px keeps this a genuinely small JPEG (a few KB) while still
    // looking reasonably sharp in the hover-zoom preview (see
    // includes/footer.php), which displays it at up to ~200px.
    $maxDim  = 160;
    $quality = 65;

    if (!$dataUri) return null;
    if (!function_exists('imagecreatefromstring')) return null;

    $comma = strpos($dataUri, ',');
    if ($comma === false) return null;
    $raw = base64_decode(substr($dataUri, $comma + 1), true);
    if ($raw === false || $raw === '') return null;

    $src = @imagecreatefromstring($raw);
    if (!$src) return null;

    $w = imagesx($src);
    $h = imagesy($src);
    if ($w <= 0 || $h <= 0) { imagedestroy($src); return null; }

    // Square center-crop so thumbnails line up evenly in search rows/cards
    $side = min($w, $h);
    $srcX = (int) (($w - $side) / 2);
    $srcY = (int) (($h - $side) / 2);

    $dst = imagecreatetruecolor($maxDim, $maxDim);
    imagecopyresampled($dst, $src, 0, 0, $srcX, $srcY, $maxDim, $maxDim, $side, $side);
    imagedestroy($src);

    ob_start();
    $ok = imagejpeg($dst, null, $quality);
    $jpegData = ob_get_clean();
    imagedestroy($dst);

    if (!$ok || $jpegData === false || $jpegData === '') return null;
    return 'data:image/jpeg;base64,' . base64_encode($jpegData);
}

// ── One-time backfill: generate thumbnails for products saved before
// this feature existed. Processes a small batch per request (cheap,
// indexed WHERE) until nothing's left, then flips a settings flag so
// every request after that is a single no-op lookup — same shape as
// ensure_profit_view_migrated() in includes/db.php.
function ensure_product_thumbnails_migrated(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $flag = db_val(
            "SELECT setting_value FROM settings WHERE setting_key = 'product_thumbs_migrated'"
        );
        if ($flag === '1') return;

        $pending = db_rows(
            "SELECT id, image FROM products
              WHERE image IS NOT NULL AND image != ''
                AND (image_thumb IS NULL OR image_thumb = '')
              LIMIT 25"
        );
        foreach ($pending as $row) {
            $thumb = make_product_thumbnail($row['image']);
            if ($thumb !== null) {
                db_run("UPDATE products SET image_thumb = ? WHERE id = ?", [$thumb, $row['id']]);
            } else {
                // Undecodable/corrupt image — mark with empty string (not NULL)
                // so this row isn't retried forever every single request.
                db_run("UPDATE products SET image_thumb = '' WHERE id = ?", [$row['id']]);
            }
        }

        if (count($pending) < 25) {
            $remaining = (int) db_val(
                "SELECT COUNT(*) FROM products
                  WHERE image IS NOT NULL AND image != ''
                    AND (image_thumb IS NULL OR image_thumb = '')"
            );
            if ($remaining === 0) {
                save_setting('product_thumbs_migrated', '1');
            }
        }
    } catch (Throwable $e) {
        error_log('ensure_product_thumbnails_migrated failed: ' . $e->getMessage());
    }
}

function api_add(array $b): void {
    $name = trim($b['name'] ?? '');
    if (!$name) fail('Product name is required');

    // Check duplicate name — a soft-deleted product (is_deleted=1) must
    // NOT block re-using its name/barcode; once deleted it should behave
    // as if it no longer exists.
    $exists = db_val(
        "SELECT id FROM products WHERE name = ? AND is_active = 1 AND is_deleted = 0",
        [$name]
    );
    if ($exists) fail("A product named \"$name\" already exists");

    // Check barcode uniqueness if provided
    $barcode = trim($b['barcode'] ?? '');
    if ($barcode) {
        $bexists = db_val(
            "SELECT id FROM products WHERE barcode = ? AND is_deleted = 0",
            [$barcode]
        );
        if ($bexists) fail("Barcode \"$barcode\" is already assigned to another product");
    }

    $btp    = (float)($b['btp']       ?? 0);
    $vat    = (float)($b['vat']       ?? 0);
    $btpvat = (float)($b['btp_vat']   ?? ($btp + $vat));

    $wsPrice   = max(0, (float)($b['wholesale_price']    ?? 0));
    $wsDisc    = max(0, min(100, (float)($b['wholesale_disc_pct'] ?? 0)));

    $alertQty  = max(0, (int)($b['low_stock_alert'] ?? 0));
    $minQty    = max(0, (int)($b['min_qty']         ?? 0));
    $maxRetDisc= max(0, min(100, (float)($b['max_retail_disc'] ?? 0)));

    ensure_products_image_column();
    ensure_products_thumb_column();
    $image = validate_product_image((string)($b['image'] ?? ''));
    $thumb = make_product_thumbnail($image);

    $id = db_insert(
        "INSERT INTO products
           (name, form, strength, generic_id, company_id, supplier_id, category_id,
            barcode, image, image_thumb, rack, qty_in_box, sale_price,
            wholesale_price, wholesale_disc_pct,
            btp, vat, btp_vat, stock_pcs,
            low_stock_alert, min_qty, max_retail_disc, is_active)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1)",
        [
            $name,
            trim($b['form']     ?? ''),
            trim($b['strength'] ?? ''),
            $b['generic_id']  ?: null,
            $b['company_id']  ?: null,
            $b['supplier_id'] ?: null,
            $b['category_id'] ?: null,
            $barcode ?: null,
            $image,
            $thumb,
            trim($b['rack']     ?? ''),
            max(1, (int)($b['qty_in_box'] ?? 1)),
            max(0, (float)($b['sale_price'] ?? 0)),
            $wsPrice, $wsDisc,
            $btp, $vat, $btpvat,
            max(0, (int)($b['stock_pcs'] ?? 0)),
            $alertQty, $minQty, $maxRetDisc,
        ]
    );
    ok(['id' => $id, 'msg' => 'Product added successfully']);
}

// ── Edit ──────────────────────────────────────────────────────
function api_edit(array $b): void {
    $id   = (int)($b['id'] ?? 0);
    $name = trim($b['name'] ?? '');
    if (!$id)   fail('Missing product id');
    if (!$name) fail('Product name is required');

    // Check duplicate name (excluding self) — soft-deleted products must
    // not block reusing their name/barcode.
    $exists = db_val(
        "SELECT id FROM products WHERE name = ? AND id != ? AND is_active = 1 AND is_deleted = 0",
        [$name, $id]
    );
    if ($exists) fail("Another product named \"$name\" already exists");

    // Barcode uniqueness (excluding self)
    $barcode = trim($b['barcode'] ?? '');
    if ($barcode) {
        $bexists = db_val(
            "SELECT id FROM products WHERE barcode = ? AND id != ? AND is_deleted = 0",
            [$barcode, $id]
        );
        if ($bexists) fail("Barcode \"$barcode\" is already used by another product");
    }

    $btp    = (float)($b['btp']     ?? 0);
    $vat    = (float)($b['vat']     ?? 0);
    $btpvat = (float)($b['btp_vat'] ?? ($btp + $vat));

    $wsPrice = max(0, (float)($b['wholesale_price']    ?? 0));
    $wsDisc  = max(0, min(100, (float)($b['wholesale_disc_pct'] ?? 0)));

    // Log price changes before update
    $existing = db_row("SELECT sale_price,wholesale_price,name FROM products WHERE id=?",[$id]);
    if ($existing) {
        $newRetail = max(0,(float)($b['sale_price']??0));
        $newWhole  = $wsPrice;
        log_price_change($id,$existing['name'],'retail',
            (float)$existing['sale_price'],$newRetail);
        log_price_change($id,$existing['name'],'wholesale',
            (float)$existing['wholesale_price'],$newWhole);
    }

    // Image is only touched when the client explicitly sends the key —
    // product/list.php mirrors the "unchanged / removed / new" pattern
    // used by profile.php (pendingPic), so a plain price/stock edit
    // doesn't re-upload (or accidentally wipe) an existing photo.
    ensure_products_image_column();
    ensure_products_thumb_column();
    $imageProvided = array_key_exists('image', $b);
    $image = $imageProvided ? validate_product_image((string)$b['image']) : null;
    $thumb = $imageProvided ? make_product_thumbnail($image) : null;

    $sql    = "UPDATE products SET
           name               = ?,
           form               = ?,
           strength           = ?,
           generic_id         = ?,
           company_id         = ?,
           supplier_id        = ?,
           category_id        = ?,
           barcode            = ?,
           rack               = ?,
           qty_in_box         = ?,
           sale_price         = ?,
           wholesale_price    = ?,
           wholesale_disc_pct = ?,
           btp                = ?,
           vat                = ?,
           btp_vat            = ?,
           low_stock_alert    = ?,
           min_qty            = ?,
           max_retail_disc    = ?";
    $params = [
            $name,
            trim($b['form']     ?? ''),
            trim($b['strength'] ?? ''),
            $b['generic_id']  ?: null,
            $b['company_id']  ?: null,
            $b['supplier_id'] ?: null,
            $b['category_id'] ?: null,
            $barcode ?: null,
            trim($b['rack']     ?? ''),
            max(1, (int)($b['qty_in_box']  ?? 1)),
            max(0, (float)($b['sale_price'] ?? 0)),
            $wsPrice, $wsDisc,
            $btp, $vat, $btpvat,
            max(0, (int)($b['low_stock_alert'] ?? 0)),
            max(0, (int)($b['min_qty']         ?? 0)),
            max(0, min(100, (float)($b['max_retail_disc'] ?? 0))),
    ];
    if ($imageProvided) {
        $sql     .= ", image = ?, image_thumb = ?";
        $params[] = $image;
        $params[] = $thumb;
    }
    $sql     .= ", updated_at = NOW() WHERE id = ?";
    $params[] = $id;

    db_run($sql, $params);
    ok(['msg' => 'Product updated successfully']);
}

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

    $rec = db_row("SELECT * FROM `products` WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$rec) fail('Record not found');

    $uid = current_user_id();
    db_run(
        "UPDATE `products`
            SET is_deleted = 1,
                deleted_at = NOW(),
                deleted_by = ?
          WHERE id = ?",
        [$uid, $id]
    );

    log_action('delete', 'products', $id,
        'Soft-deleted: ' . ($rec['name'] ?? '#' . $id));

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


// ── JSON response helpers ─────────────────────────────────────
// ── Price history ─────────────────────────────────────────────
function api_price_history(array $b): void {
    $pid   = (int)($b['product_id'] ?? 0);
    $where = $pid ? "WHERE product_id = ?" : "1=1";
    $params= $pid ? [$pid] : [];
    $rows  = db_rows(
        "SELECT ph.*, u.name AS changed_by_name
           FROM price_history ph
      LEFT JOIN users u ON u.id = ph.changed_by
          WHERE $where
          ORDER BY ph.changed_at DESC LIMIT 100",
        $params
    );
    ok(['data' => $rows]);
}

// ── Save wholesale price as new default ───────────────────────
function api_save_ws_price(array $b): void {
    $pid   = (int)($b['product_id']   ?? 0);
    $price = (float)($b['wholesale_price'] ?? 0);
    if (!$pid) fail('Missing product id');

    $existing = db_row("SELECT name,wholesale_price FROM products WHERE id=?",[$pid]);
    if (!$existing) fail('Product not found');

    log_price_change($pid, $existing['name'], 'wholesale',
        (float)$existing['wholesale_price'], $price);

    db_run(
        "UPDATE products SET wholesale_price = ?, updated_at = NOW() WHERE id = ?",
        [$price, $pid]
    );
    ok(['msg' => 'Wholesale price updated as new default']);
}

// ── Set single product's low stock alert ──────────────────────
function api_set_alert(array $b): void {
    $id    = (int)($b['id'] ?? 0);
    $alert = max(0, (int)($b['low_stock_alert'] ?? 0));
    if (!$id) fail('Missing product id');

    $exists = db_val("SELECT id FROM products WHERE id=?", [$id]);
    if (!$exists) fail('Product not found');

    db_run("UPDATE products SET low_stock_alert=?, updated_at=NOW() WHERE id=?",
        [$alert, $id]);
    ok(['msg' => 'Alert quantity updated']);
}

// ── Bulk set low stock alert for multiple products ────────────
function api_bulk_set_alert(array $b): void {
    $ids   = $b['ids'] ?? [];
    $alert = max(0, (int)($b['low_stock_alert'] ?? 0));
    if (!is_array($ids) || !$ids) fail('No products selected');

    $ids = array_map('intval', $ids);
    $placeholders = implode(',', array_fill(0, count($ids), '?'));

    db_run(
        "UPDATE products SET low_stock_alert=?, updated_at=NOW()
          WHERE id IN ($placeholders)",
        array_merge([$alert], $ids)
    );
    ok(['msg' => 'Bulk update successful', 'count' => count($ids)]);
}

// ── Bulk-assign a default supplier to selected products ────────
// Used by Smart Requisition's "🔗 Set Supplier" action — lets a user
// select several products that keep landing under "Unknown Supplier"
// (because they've never been through a purchase order) and assign
// them all to a real supplier in one click, instead of editing each
// product individually.
function api_bulk_set_supplier(array $b): void {
    ensure_products_supplier_column();
    $ids       = $b['ids'] ?? [];
    $supplierId= (int)($b['supplier_id'] ?? 0);
    if (!is_array($ids) || !$ids) fail('No products selected');
    if (!$supplierId) fail('Select a supplier');

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

    $ids = array_map('intval', $ids);
    $placeholders = implode(',', array_fill(0, count($ids), '?'));

    db_run(
        "UPDATE products SET supplier_id=?, updated_at=NOW()
          WHERE id IN ($placeholders)",
        array_merge([$supplierId], $ids)
    );
    ok(['msg' => 'Supplier assigned', 'count' => count($ids)]);
}

// ── Auto-match products with no supplier to a supplier of the same
// name as their company ─────────────────────────────────────────
// Many small pharmacy operations buy directly from the manufacturer,
// so "Company" (companies table) and "Supplier" (suppliers table) are
// often the exact same entity under two different lists. This does a
// one-time, case-insensitive exact-name match for every product that
// has a company but no supplier_id yet, and reports how many it could
// and couldn't resolve — the rest still need manual assignment.
function api_auto_match_suppliers(array $b): void {
    ensure_products_supplier_column();

    $candidates = db_rows(
        "SELECT p.id, c.name AS company_name
           FROM products p
           JOIN companies c ON c.id = p.company_id
          WHERE p.supplier_id IS NULL
            AND p.is_active = 1 AND p.is_deleted = 0"
    );

    $matched = 0;
    foreach ($candidates as $row) {
        $supId = db_val(
            "SELECT id FROM suppliers WHERE LOWER(name) = LOWER(?) LIMIT 1",
            [$row['company_name']]
        );
        if ($supId) {
            db_run("UPDATE products SET supplier_id=?, updated_at=NOW() WHERE id=?", [$supId, $row['id']]);
            $matched++;
        }
    }

    ok([
        'msg'      => "Matched {$matched} of " . count($candidates) . " unassigned product(s) by company name.",
        'matched'  => $matched,
        'checked'  => count($candidates),
    ]);
}

// ── Import alert quantities from CSV ───────────────────────────
function api_import_alert_csv(array $b): void {
    $updates = $b['updates'] ?? [];
    if (!is_array($updates) || !$updates) fail('No data to import');

    $count = 0;
    db()->beginTransaction();
    try {
        foreach ($updates as $u) {
            $id    = (int)($u['id'] ?? 0);
            $alert = max(0, (int)($u['low_stock_alert'] ?? 0));
            if (!$id) continue;

            $exists = db_val("SELECT id FROM products WHERE id=?", [$id]);
            if (!$exists) continue;

            db_run("UPDATE products SET low_stock_alert=?, updated_at=NOW() WHERE id=?",
                [$alert, $id]);
            $count++;
        }
        db()->commit();
        ok(['msg' => 'Import successful', 'count' => $count]);
    } catch (Throwable $e) {
        db()->rollBack();
        fail('Import failed: ' . $e->getMessage());
    }
}


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