<?php
// /public_html/erp.jewellersbd.com/api/barcode/get_barcode_sales_report.php

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';
require_once __DIR__ . '/../../config/response.php';

if (!is_logged_in()) {
    send_response(401, false, 'লগইন প্রয়োজন', null);
}

$from_date = trim($_GET['from_date'] ?? '');
$to_date   = trim($_GET['to_date'] ?? '');
$search    = trim($_GET['search'] ?? '');
$limit     = (int)($_GET['limit'] ?? 100);
$offset    = (int)($_GET['offset'] ?? 0);

if ($limit <= 0) $limit = 100;
if ($limit > 500) $limit = 500;
if ($offset < 0) $offset = 0;

try {
    $whereParts = [
        "bs.deleted_at IS NULL",
        "LOWER(bs.status) = 'sold'"
    ];
    $params = [];

    if ($from_date !== '') {
        $whereParts[] = "DATE(bs.sold_at) >= :from_date";
        $params[':from_date'] = $from_date;
    }

    if ($to_date !== '') {
        $whereParts[] = "DATE(bs.sold_at) <= :to_date";
        $params[':to_date'] = $to_date;
    }

    if ($search !== '') {
        $whereParts[] = "(bs.barcode LIKE :search_barcode OR bs.item_name LIKE :search_item)";
        $params[':search_barcode'] = '%' . $search . '%';
        $params[':search_item'] = '%' . $search . '%';
    }

    $whereSql = 'WHERE ' . implode(' AND ', $whereParts);

    $sumSql = "
        SELECT
            COUNT(*) AS total_items,
            COALESCE(SUM(bs.weight), 0) AS total_weight,
            COALESCE(SUM(bs.sale_price), 0) AS total_sale_value,
            COALESCE(SUM(bs.cost_price), 0) AS total_cost_value,
            COALESCE(SUM(bs.sale_price - bs.cost_price), 0) AS total_profit_loss
        FROM barcode_stock bs
        $whereSql
    ";

    $sumStmt = $pdo->prepare($sumSql);
    foreach ($params as $key => $value) {
        $sumStmt->bindValue($key, $value, PDO::PARAM_STR);
    }
    $sumStmt->execute();
    $summary = $sumStmt->fetch(PDO::FETCH_ASSOC);

    $listSql = "
        SELECT
            bs.id,
            bs.purchase_stock_id,
            bs.barcode,
            bs.item_name,
            bs.karat,
            bs.weight,
            bs.location_id,
            bs.cost_price,
            bs.sale_price,
            bs.status,
            bs.sold_at,
            bs.note,
            bs.created_at,
            COALESCE(sl.name, '') AS location_name
        FROM barcode_stock bs
        LEFT JOIN stock_locations sl
            ON bs.location_id = sl.id
        $whereSql
        ORDER BY bs.sold_at DESC, bs.id DESC
        LIMIT :limit OFFSET :offset
    ";

    $stmt = $pdo->prepare($listSql);
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value, PDO::PARAM_STR);
    }
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $items = [];
    foreach ($rows as $row) {
        $costPrice = round((float)($row['cost_price'] ?? 0), 2);
        $salePrice = round((float)($row['sale_price'] ?? 0), 2);

        $items[] = [
            'id' => (int)($row['id'] ?? 0),
            'purchase_stock_id' => isset($row['purchase_stock_id']) ? (int)$row['purchase_stock_id'] : null,
            'barcode' => $row['barcode'] ?? '',
            'item_name' => $row['item_name'] ?? '',
            'karat' => $row['karat'] ?? '',
            'weight' => round((float)($row['weight'] ?? 0), 3),
            'location_id' => (int)($row['location_id'] ?? 0),
            'location_name' => $row['location_name'] ?? '',
            'cost_price' => $costPrice,
            'sale_price' => $salePrice,
            'profit_loss' => round($salePrice - $costPrice, 2),
            'status' => $row['status'] ?? 'sold',
            'sold_at' => $row['sold_at'] ?? null,
            'note' => $row['note'] ?? '',
            'created_at' => $row['created_at'] ?? ''
        ];
    }

    send_response(200, true, 'সফল', [
        'summary' => [
            'total_items' => (int)($summary['total_items'] ?? 0),
            'total_weight' => round((float)($summary['total_weight'] ?? 0), 3),
            'total_sale_value' => round((float)($summary['total_sale_value'] ?? 0), 2),
            'total_cost_value' => round((float)($summary['total_cost_value'] ?? 0), 2),
            'total_profit_loss' => round((float)($summary['total_profit_loss'] ?? 0), 2)
        ],
        'items' => $items,
        'limit' => $limit,
        'offset' => $offset
    ]);

} catch (Throwable $e) {
    send_response(500, false, 'সার্ভার সমস্যা: ' . $e->getMessage(), [
        'error' => $e->getMessage()
    ]);
}