<?php
// ============================================================
//  MediCore — Product History API
//  File: api/product-history.php
//
//  Powers Product List's "History" button: purchase/sales summaries,
//  the PO/invoice lists behind them, and the full stock movement
//  timeline for one product.
//
//  Split of responsibility (see includes/db.php's stock_ledger
//  comment for the full rationale): the Stock Movement Timeline is
//  generated entirely from `stock_ledger`, per the spec's explicit
//  goal of faster/simpler reporting off one central table. The
//  Purchase/Sales SUMMARY figures (which need actual currency
//  amounts) are read from purchase_items/sale_items directly, since
//  `stock_ledger`'s schema — by design, as specified — only tracks
//  quantities and running balance, not money. Using the ledger for
//  the (potentially large, ever-growing) movement timeline and the
//  source tables for the (small, bounded — one row per PO/invoice
//  this product ever appeared on) monetary summaries gets the
//  reporting-speed benefit where it actually matters.
//
//  All four pieces (summary/purchases/sales/timeline) are computed by
//  plain data-returning functions, so the 'full' action can call all
//  of them in ONE request/ONE PHP process instead of the History
//  modal firing four separate HTTP requests. That matters a lot on
//  this app's XAMPP/Apache setup, which has a documented small
//  worker-thread pool (see assets/topbar.js's SSE history) — four
//  concurrent requests each independently re-running
//  ensure_stock_ledger_opening_backfill() (since PHP doesn't share
//  its "already ran" static flag across separate requests) is what
//  made the first click on a large catalog look stuck on "Loading…".
//  One request means that backfill work only happens once per click.
// ============================================================

require_once '../includes/db.php';
require_once '../includes/auth.php';
require_login();
session_write_close();

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

ensure_stock_ledger_table();
ensure_stock_ledger_opening_backfill();

if (!can('product_history', 'view')) {
    http_response_code(403);
    echo json_encode(['success' => false, 'error' => 'You do not have permission to view Product History.']);
    exit;
}

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

try {
    match ($action) {
        'full'      => api_full($body),
        'summary'   => ok(['data' => data_summary($body)]),
        'purchases' => api_purchases($body),
        'sales'     => api_sales($body),
        'timeline'  => api_timeline($body),
        default     => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── Everything the History modal needs, in one round trip ───────
function api_full(array $b): void {
    $pid = (int) ($b['product_id'] ?? 0);
    if (!$pid) fail('Missing product_id');

    $summary = data_summary($b);
    if ($summary === null) fail('Product not found');

    ok([
        'product'  => $summary['product'],
        'purchase' => $summary['purchase'],
        'sales'    => $summary['sales'],
        'purchases_list' => data_purchases($pid, 1, (int) ($b['po_page_size'] ?? 10))['data'],
        'sales_list'      => data_sales($pid, 1, (int) ($b['sale_page_size'] ?? 10))['data'],
        'timeline'        => data_timeline($pid, 1, (int) ($b['timeline_page_size'] ?? 15)),
    ]);
}

// ── Summary: product basics + lifetime purchase/sales totals ────
function data_summary(array $b): ?array {
    $pid = (int) ($b['product_id'] ?? 0);
    if (!$pid) return null;

    $product = db_row("SELECT id, name, strength, form, barcode, stock_pcs FROM products WHERE id = ?", [$pid]);
    if (!$product) return null;

    // Only POs that actually affected stock count toward lifetime
    // totals — a still-pending or rejected PO never added stock, so
    // including it would overstate "Total Purchased".
    $purchase = db_row(
        "SELECT COALESCE(SUM(pi.qty_pcs + pi.free), 0) AS qty,
                COALESCE(SUM(pi.total_price), 0)        AS amount
           FROM purchase_items pi
           JOIN purchases p ON p.id = pi.purchase_id
          WHERE pi.product_id = ? AND p.is_deleted = 0
            AND p.approval_status IN ('approved', 'not_required')",
        [$pid]
    );

    $sales = db_row(
        "SELECT COALESCE(SUM(si.qty), 0)   AS qty,
                COALESCE(SUM(si.total), 0) AS amount
           FROM sale_items si
           JOIN sales s ON s.id = si.sale_id
          WHERE si.product_id = ? AND s.is_deleted = 0",
        [$pid]
    );

    return [
        'product'  => $product,
        'purchase' => ['qty' => (int) $purchase['qty'], 'amount' => (float) $purchase['amount']],
        'sales'    => ['qty' => (int) $sales['qty'],    'amount' => (float) $sales['amount']],
    ];
}

// ── Purchase orders this product appeared on, latest first ──────
function data_purchases(int $pid, int $page, int $pageSize): array {
    $page     = max(1, $page);
    $pageSize = max(5, min(100, $pageSize));
    $offset   = ($page - 1) * $pageSize;

    $total = (int) db_val(
        "SELECT COUNT(DISTINCT p.id)
           FROM purchases p JOIN purchase_items pi ON pi.purchase_id = p.id
          WHERE pi.product_id = ? AND p.is_deleted = 0",
        [$pid]
    );

    $rows = db_rows(
        "SELECT p.id, p.invoice, p.date, p.supplier_name, p.final_price, p.approval_status,
                SUM(pi.qty_pcs + pi.free) AS this_product_qty,
                SUM(pi.total_price)       AS this_product_amount
           FROM purchases p
           JOIN purchase_items pi ON pi.purchase_id = p.id
          WHERE pi.product_id = ? AND p.is_deleted = 0
       GROUP BY p.id
       ORDER BY p.date DESC, p.id DESC
          LIMIT $pageSize OFFSET $offset",
        [$pid]
    );

    return ['data' => $rows, 'page' => $page, 'total' => $total, 'total_pages' => (int) max(1, ceil($total / $pageSize))];
}
function api_purchases(array $b): void {
    $pid = (int) ($b['product_id'] ?? 0);
    if (!$pid) fail('Missing product_id');
    ok(data_purchases($pid, (int) ($b['page'] ?? 1), (int) ($b['page_size'] ?? 20)));
}

// ── Sale invoices this product appeared on, latest first ────────
function data_sales(int $pid, int $page, int $pageSize): array {
    $page     = max(1, $page);
    $pageSize = max(5, min(100, $pageSize));
    $offset   = ($page - 1) * $pageSize;

    $total = (int) db_val(
        "SELECT COUNT(DISTINCT s.id)
           FROM sales s JOIN sale_items si ON si.sale_id = s.id
          WHERE si.product_id = ? AND s.is_deleted = 0",
        [$pid]
    );

    $rows = db_rows(
        "SELECT s.id, s.invoice, s.date, s.customer_name, s.final_price, s.status,
                SUM(si.qty)   AS this_product_qty,
                SUM(si.total) AS this_product_amount
           FROM sales s
           JOIN sale_items si ON si.sale_id = s.id
          WHERE si.product_id = ? AND s.is_deleted = 0
       GROUP BY s.id
       ORDER BY s.date DESC, s.id DESC
          LIMIT $pageSize OFFSET $offset",
        [$pid]
    );

    return ['data' => $rows, 'page' => $page, 'total' => $total, 'total_pages' => (int) max(1, ceil($total / $pageSize))];
}
function api_sales(array $b): void {
    $pid = (int) ($b['product_id'] ?? 0);
    if (!$pid) fail('Missing product_id');
    ok(data_sales($pid, (int) ($b['page'] ?? 1), (int) ($b['page_size'] ?? 20)));
}

// ── Full stock movement timeline, from stock_ledger, latest first ──
function data_timeline(int $pid, int $page, int $pageSize): array {
    $page     = max(1, $page);
    $pageSize = max(5, min(200, $pageSize));
    $offset   = ($page - 1) * $pageSize;

    $total = (int) db_val("SELECT COUNT(*) FROM stock_ledger WHERE product_id = ?", [$pid]);

    $rows = db_rows(
        "SELECT sl.*, u.name AS user_name
           FROM stock_ledger sl
      LEFT JOIN users u ON u.id = sl.created_by
          WHERE sl.product_id = ?
       ORDER BY sl.created_at DESC, sl.id DESC
          LIMIT $pageSize OFFSET $offset",
        [$pid]
    );

    return ['data' => $rows, 'page' => $page, 'total' => $total, 'total_pages' => (int) max(1, ceil($total / $pageSize))];
}
function api_timeline(array $b): void {
    $pid = (int) ($b['product_id'] ?? 0);
    if (!$pid) fail('Missing product_id');
    ok(data_timeline($pid, (int) ($b['page'] ?? 1), (int) ($b['page_size'] ?? 25)));
}

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