<?php
// /public_html/erp.jewellersbd.com/api/barcode/get_customer_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[] = "(
            COALESCE(bs.customer_name, 'Cash Customer') LIKE :search_name
            OR bs.barcode LIKE :search_barcode
            OR bs.item_name LIKE :search_item
        )";
        $params[':search_name'] = '%' . $search . '%';
        $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,
            COUNT(DISTINCT COALESCE(bs.customer_id, 0), COALESCE(bs.customer_name, 'Cash Customer')) AS total_customers
        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);

    $groupSql = "
        SELECT
            COALESCE(bs.customer_id, 0) AS customer_id,
            COALESCE(NULLIF(bs.customer_name, ''), 'Cash Customer') AS customer_name,
            COUNT(bs.id) AS total_items,
            COALESCE(SUM(bs.weight), 0) AS total_weight,
            COALESCE(SUM(bs.sale_price), 0) AS total_sale_value,
            MAX(bs.sold_at) AS last_sold_at
        FROM barcode_stock bs
        $whereSql
        GROUP BY
            COALESCE(bs.customer_id, 0),
            COALESCE(NULLIF(bs.customer_name, ''), 'Cash Customer')
        ORDER BY last_sold_at DESC, total_sale_value DESC
        LIMIT :limit OFFSET :offset
    ";

    $stmt = $pdo->prepare($groupSql);
    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();

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

    $report = [];

    foreach ($groups as $group) {
        $custId = (int)($group['customer_id'] ?? 0);
        $custName = $group['customer_name'] ?? 'Cash Customer';

        $itemWhereParts = $whereParts;
        if ($custId > 0) {
            $itemWhereParts[] = "bs.customer_id = :group_customer_id";
        } else {
            $itemWhereParts[] = "(COALESCE(bs.customer_id, 0) = 0)";
            $itemWhereParts[] = "COALESCE(NULLIF(bs.customer_name, ''), 'Cash Customer') = :group_customer_name";
        }

        $itemWhereSql = 'WHERE ' . implode(' AND ', $itemWhereParts);

        $itemSql = "
            SELECT
                bs.id,
                bs.purchase_stock_id,
                bs.barcode,
                bs.item_name,
                bs.karat,
                bs.weight,
                bs.location_id,
                bs.location_id,
                bs.cost_price,
                bs.sale_price,
                bs.status,
                bs.sold_at,
                bs.note,
                bs.created_at,
                bs.customer_id,
                bs.customer_name,
                COALESCE(sl.name, '') AS location_name
            FROM barcode_stock bs
            LEFT JOIN stock_locations sl
                ON bs.location_id = sl.id
            $itemWhereSql
            ORDER BY bs.sold_at DESC, bs.id DESC
        ";

        $itemStmt = $pdo->prepare($itemSql);

        foreach ($params as $key => $value) {
            $itemStmt->bindValue($key, $value, PDO::PARAM_STR);
        }

        if ($custId > 0) {
            $itemStmt->bindValue(':group_customer_id', $custId, PDO::PARAM_INT);
        } else {
            $itemStmt->bindValue(':group_customer_name', $custName, PDO::PARAM_STR);
        }

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

        $items = [];
        foreach ($rows as $row) {
            $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' => round((float)($row['cost_price'] ?? 0), 2),
                'sale_price' => round((float)($row['sale_price'] ?? 0), 2),
                'status' => $row['status'] ?? 'sold',
                'sold_at' => $row['sold_at'] ?? null,
                'note' => $row['note'] ?? '',
                'created_at' => $row['created_at'] ?? '',
                'customer_id' => isset($row['customer_id']) ? (int)$row['customer_id'] : null,
                'customer_name' => $row['customer_name'] ?? $custName
            ];
        }

        $report[] = [
            'customer_id' => $custId > 0 ? $custId : null,
            'customer_name' => $custName,
            'total_items' => (int)($group['total_items'] ?? 0),
            'total_weight' => round((float)($group['total_weight'] ?? 0), 3),
            'total_sale_value' => round((float)($group['total_sale_value'] ?? 0), 2),
            'last_sold_at' => $group['last_sold_at'] ?? null,
            'items' => $items
        ];
    }

    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_customers' => (int)($summary['total_customers'] ?? 0)
        ],
        'customers' => $report,
        'limit' => $limit,
        'offset' => $offset
    ]);

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