<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/reports/stock_report.php

require_once __DIR__ . '/../../api/_core.php';
require_once __DIR__ . '/../../config/db.php';

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

try {
    $karat_filter = $_GET['karat'] ?? '';
    $search       = $_GET['search'] ?? '';

    $where  = "bs.status = 'in_stock' AND bs.is_deleted = 0 AND bs.deleted_at IS NULL";
    $params = [];

    if ($karat_filter) {
        $where .= " AND bs.karat = :karat";
        $params[':karat'] = $karat_filter;
    }
    if ($search) {
        $where .= " AND (bs.product_name LIKE :s OR bs.item_name LIKE :s2 OR bs.barcode LIKE :s3)";
        $params[':s']  = '%' . $search . '%';
        $params[':s2'] = '%' . $search . '%';
        $params[':s3'] = '%' . $search . '%';
    }

    // ── ১. Summary ────────────────────────────────────────────────────
    $sumQ = $pdo->prepare(
        "SELECT
            COUNT(*)                        AS total_items,
            SUM(bs.gross_weight_gram)       AS total_gross_gram,
            SUM(bs.pure_weight_gram)        AS total_pure_gram,
            SUM(bs.cost_price)              AS total_cost
         FROM barcode_stock bs
         WHERE $where"
    );
    $sumQ->execute($params);
    $summary = $sumQ->fetch(PDO::FETCH_ASSOC);

    // ── ২. Karat breakdown ────────────────────────────────────────────
    $katQ = $pdo->prepare(
        "SELECT
            bs.karat,
            COUNT(*)                  AS cnt,
            SUM(bs.gross_weight_gram) AS gross_gram,
            SUM(bs.pure_weight_gram)  AS pure_gram,
            SUM(bs.cost_price)        AS cost_total
         FROM barcode_stock bs
         WHERE $where
         GROUP BY bs.karat
         ORDER BY bs.karat"
    );
    $katQ->execute($params);
    $karat_breakdown = $katQ->fetchAll(PDO::FETCH_ASSOC);

    // ── ৩. Item list ──────────────────────────────────────────────────
    $itemQ = $pdo->prepare(
        "SELECT
            bs.id,
            bs.barcode,
            COALESCE(bs.product_name, bs.item_name, '—') AS product_name,
            bs.karat,
            bs.gross_weight_gram,
            bs.pure_weight_gram,
            bs.purity_percent,
            bs.cost_price,
            bs.ana,
            bs.roti,
            bs.point,
            bs.created_at
         FROM barcode_stock bs
         WHERE $where
         ORDER BY bs.karat, bs.created_at DESC"
    );
    $itemQ->execute($params);
    $items = $itemQ->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode([
        'success'         => true,
        'summary'         => [
            'total_items'      => (int)($summary['total_items'] ?? 0),
            'total_gross_gram' => (float)($summary['total_gross_gram'] ?? 0),
            'total_pure_gram'  => (float)($summary['total_pure_gram']  ?? 0),
            'total_cost'       => (float)($summary['total_cost']       ?? 0),
        ],
        'karat_breakdown' => $karat_breakdown,
        'items'           => $items,
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}