<?php
// api/blocks.php
declare(strict_types=1);

header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../auth_stub.php';

session_start();

/**
 * ASL BELGISI OPEN API konfiguratsiyasi
 */
const API_TOKEN             = '4e0d9f49-0eec-4a05-93ec-45eed1f3cc62';
const SERVER                = 'https://xtrace.aslbelgisi.uz';
const XTRACE_DOC_STATUS_PATH = '/public/api/v1/doc/storage/docs/';

/** Yordamchi JSON funksiyalar */
function json_error(string $message, int $code = 400, array $extra = []): void {
    http_response_code($code);
    echo json_encode(array_merge(['status' => 'error', 'message' => $message], $extra), JSON_UNESCAPED_UNICODE);
    exit;
}
function json_ok(array $data = []): void {
    echo json_encode(array_merge(['status' => 'ok'], $data), JSON_UNESCAPED_UNICODE);
    exit;
}

/**
 * Xtrace doc status API chaqiruv (DOC STORAGE)
 * GET {SERVER}/public/api/v1/doc/storage/docs/{docId}
 */
function xtrace_check_doc_status(string $docId, string $token, string $server): array
{
    if ($docId === '') {
        return [
            'ok'       => false,
            'httpCode' => 0,
            'data'     => null,
            'error'    => 'docId bo‘sh',
        ];
    }

    $url = rtrim($server, '/') . XTRACE_DOC_STATUS_PATH . rawurlencode($docId);

    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER     => [
            'Authorization: Bearer ' . $token,
            'Content-Type: application/json;charset=UTF-8',
            'Accept: application/json',
        ],
        CURLOPT_TIMEOUT        => 25,
    ]);

    $body     = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlErr  = curl_error($ch);
    curl_close($ch);

    if ($body === false) {
        return [
            'ok'       => false,
            'httpCode' => $httpCode ?: 0,
            'data'     => null,
            'error'    => 'cURL xatosi: ' . ($curlErr ?: 'noma’lum'),
        ];
    }

    $data = json_decode($body, true);
    if (!is_array($data)) {
        return [
            'ok'       => false,
            'httpCode' => $httpCode,
            'data'     => null,
            'error'    => 'OPEN API noto‘g‘ri JSON javob qaytardi',
        ];
    }

    return [
        'ok'       => true,
        'httpCode' => $httpCode,
        'data'     => $data,
        'error'    => null,
    ];
}

/**
 * HTTP kod + payload bo‘yicha Xtrace xatosini o‘zbekcha matnga aylantirish
 */
function asl_map_error_message(int $httpCode, array $payload): string
{
    $baseMsg = $payload['message'] ?? $payload['error'] ?? '';

    // 404 – hujjat topilmadi: bu ko‘pincha hali Xtrace bazasiga tushmagan bo‘lishi mumkin
    if ($httpCode === 404) {
        $t = 'Xtrace’da ushbu hujjat topilmadi (HTTP 404). '
           . 'Bu hujjat hali serverga yuborilmagan yoki ID noto‘g‘ri bo‘lishi mumkin.';
        if ($baseMsg !== '') {
            $t .= ' Server javobi: ' . $baseMsg;
        }
        return $t;
    }

    if ($httpCode === 400 || $httpCode === 422) {
        $t = 'Xtrace’ga yuborilgan ma’lumot formatida xatolik bor (HTTP ' . $httpCode . ').';
        if ($baseMsg !== '') {
            $t .= ' Server javobi: ' . $baseMsg;
        }
        return $t;
    }

    if ($httpCode === 401 || $httpCode === 403) {
        $t = 'Avtorizatsiya xatosi (HTTP ' . $httpCode . '). API TOKEN yoki STIR noto‘g‘ri bo‘lishi mumkin.';
        if ($baseMsg !== '') {
            $t .= ' Server javobi: ' . $baseMsg;
        }
        return $t;
    }

    if ($httpCode >= 500) {
        $t = 'Xtrace serverida texnik nosozlik (HTTP ' . $httpCode . ').';
        if ($baseMsg !== '') {
            $t .= ' Server javobi: ' . $baseMsg;
        }
        return $t;
    }

    if ($httpCode >= 400) {
        $t = 'Xtrace API xatosi (HTTP ' . $httpCode . ').';
        if ($baseMsg !== '') {
            $t .= ' Server javobi: ' . $baseMsg;
        }
        return $t;
    }

    // 2xx bo‘lsa – odatda asl xabar yetarli
    return $baseMsg !== '' ? $baseMsg : ('HTTP ' . $httpCode);
}

/**
 * Doc JSON ichidan qo‘shimcha xatolik matnini yig‘ish (globalErrors / unitErrors va h.k.)
 * Strukturasi PDFga qarab yozilgan, lekin himoyalangan: kalit bo‘lmasa – shunchaki bo‘sh qaytadi
 */
function asl_build_error_text(array $data): string
{
    $parts = [];

    // globalErrors
    if (!empty($data['globalErrors']) && is_array($data['globalErrors'])) {
        foreach ($data['globalErrors'] as $ge) {
            if (is_array($ge)) {
                $msg = $ge['message'] ?? '';
                $code = $ge['code'] ?? '';
                $parts[] = trim($code . ' ' . $msg);
            } elseif (is_string($ge)) {
                $parts[] = $ge;
            }
        }
    }

    // errors (asosiy massiv)
    if (!empty($data['errors']) && is_array($data['errors'])) {
        foreach ($data['errors'] as $err) {
            if (is_array($err)) {
                $msg  = $err['message'] ?? '';
                $code = $err['code'] ?? '';
                $parts[] = trim($code . ' ' . $msg);
            } elseif (is_string($err)) {
                $parts[] = $err;
            }
        }
    }

    // unitErrorList / unitErrors – agar bo‘lsa
    if (!empty($data['unitErrorList']) && is_array($data['unitErrorList'])) {
        foreach ($data['unitErrorList'] as $ue) {
            if (is_array($ue)) {
                $unit = $ue['unitSerial'] ?? $ue['unit'] ?? '';
                $msg  = $ue['message'] ?? '';
                $parts[] = trim('#' . $unit . ' ' . $msg);
            }
        }
    } elseif (!empty($data['unitErrors']) && is_array($data['unitErrors'])) {
        foreach ($data['unitErrors'] as $ue) {
            if (is_array($ue)) {
                $unit = $ue['unitSerial'] ?? $ue['unit'] ?? '';
                $msg  = $ue['message'] ?? '';
                $parts[] = trim('#' . $unit . ' ' . $msg);
            }
        }
    }

    return trim(implode('; ', array_filter($parts)));
}

/**
 * Doc status kodi → qisqa matn
 * (status kodlarini PDF bo‘yicha kengaytirsa bo‘ladi, hozircha umumiy ko‘rinishda qoldiramiz)
 */
function xtrace_build_status_text(?string $status): string
{
    if ($status === null || $status === '') {
        return 'Xtrace hujjat statusi ko‘rsatilmagan.';
    }

    $s = strtoupper($status);

    switch ($s) {
        case 'SUCCESS':
        case 'PROCESSED':
            return 'Hujjat Xtrace tomonidan muvaffaqiyatli qayta ishlangan (' . $s . ').';
        case 'PROCESSED_WITH_ERRORS':
            return 'Hujjat qayta ishlangan, lekin xatoliklar mavjud (' . $s . ').';
        case 'NEW':
        case 'CREATED':
            return 'Hujjat yaratilgan, lekin hali qayta ishlanmagan (' . $s . ').';
        case 'PENDING':
        case 'QUEUED':
        case 'IN_PROGRESS':
            return 'Hujjat navbatda yoki qayta ishlanmoqda (' . $s . ').';
        case 'REJECTED':
        case 'DECLINED':
            return 'Hujjat Xtrace tomonidan rad etilgan (' . $s . ').';
        case 'CANCELLED':
            return 'Hujjat bekor qilingan (' . $s . ').';
        default:
            return 'Xtrace statusi: ' . $s;
    }
}

/**
 * Eski check_report_status() nomini saqlab, ichida DOC STORAGE statusiga moslab ishlaymiz.
 * Natija struktura: ok, httpCode, status, reason[], error
 */
function check_report_status(string $reportId): array
{
    // DOC STORAGE orqali tekshiramiz
    $xRes = xtrace_check_doc_status($reportId, API_TOKEN, SERVER);
    if (!$xRes['ok']) {
        return [
            'ok'       => false,
            'httpCode' => $xRes['httpCode'],
            'status'   => null,
            'reason'   => [],
            'error'    => $xRes['error'],
        ];
    }

    $httpCode = (int)$xRes['httpCode'];
    $data     = $xRes['data'];

    // 2xx bo‘lmasa – xato sifatida qaytaramiz
    if ($httpCode < 200 || $httpCode >= 300) {
        $msg = asl_map_error_message($httpCode, $data);
        return [
            'ok'       => false,
            'httpCode' => $httpCode,
            'status'   => null,
            'reason'   => [],
            'error'    => $msg,
        ];
    }

    // documentInfos bo‘lgan strukturani tekshiramiz
    $docInfo = $data['documentInfos'] ?? $data;
    if (isset($docInfo[0]) && is_array($docInfo[0])) {
        $docInfo = $docInfo[0];
    }

    $status = $docInfo['status'] ?? null;

    // Sabablar (errors + globalErrors + unitErrors)
    $reason = [];

    if (!empty($data['errors']) && is_array($data['errors'])) {
        foreach ($data['errors'] as $err) {
            $msg  = $err['message'] ?? '';
            $code = $err['code'] ?? '';
            $txt  = trim($code . ' — ' . $msg);
            if ($txt !== '') {
                $reason[] = $txt;
            }
        }
    }

    $extra = asl_build_error_text($data);
    if ($extra !== '') {
        $reason[] = $extra;
    }

    return [
        'ok'       => true,
        'httpCode' => $httpCode,
        'status'   => $status,
        'reason'   => $reason,
        'error'    => null,
    ];
}

/**
 * Xtrace javoblarini O‘zbekcha matnga aylantirish
 * (severity: ok / info / warning / error)
 */
function map_report_status_uz(?string $status, array $rejectReason, ?string $error, int $httpCode, bool $ok): array
{
    $statusNorm = $status !== null ? strtoupper(trim($status)) : null;
    $severity   = 'info';
    $text       = '';

    // HTTP yoki texnik xatolar
    if (!$ok) {
        if ($httpCode === 0) {
            $text = 'Internet yoki Xtrace serveriga ulanishda xatolik yuz berdi. '
                  . 'Tarmoqni tekshirib, birozdan so‘ng qayta urinib ko‘ring.';
        } elseif ($httpCode === 404) {
            // MUHIM: 404 endi "qattiq xato" emas, balki ogohlantirish
            $text = 'Xtrace’da ushbu hujjat topilmadi (HTTP 404). '
                  . 'Bu hujjat hali serverga yuborilmagan yoki ID noto‘g‘ri bo‘lishi mumkin.';
        } elseif ($httpCode >= 500) {
            $text = 'Xtrace serverida texnik nosozlik (HTTP ' . $httpCode . '). '
                  . 'Keyinroq qayta urinib ko‘ring.';
        } elseif ($httpCode === 401 || $httpCode === 403) {
            $text = 'Avtorizatsiya xatosi (HTTP ' . $httpCode . '). API token yoki STIR noto‘g‘ri bo‘lishi mumkin.';
        } elseif ($httpCode === 400 || $httpCode === 422) {
            $text = 'Xtrace’ga yuborilgan ma’lumot formatida xatolik bor (HTTP ' . $httpCode . '). '
                  . 'Yuborilgan parametrlar va doc strukturasi to‘g‘riligini tekshiring.';
        } elseif ($httpCode >= 400) {
            $text = 'Xtrace API xatosi (HTTP ' . $httpCode . ').';
        } else {
            $text = 'Xtrace’dan kutilmagan xato javob qaytdi.';
        }

        if ($error) {
            $text .= ' Texnik izoh: ' . $error;
        }

        if (!empty($rejectReason)) {
            $text .= ' Sabab(lar):';
            foreach ($rejectReason as $r) {
                if (is_array($r) && isset($r['message'])) {
                    $text .= ' ' . $r['message'] . ';';
                } elseif (is_string($r)) {
                    $text .= ' ' . $r . ';';
                }
            }
        }

        // 404 ni sariq, qolganlarini qizil
        $severity = ($httpCode === 404) ? 'warning' : 'error';

        return [
            'status_text' => $text,
            'severity'    => $severity,
        ];
    }

    // HTTP 2xx, status bo‘yicha izoh
    switch ($statusNorm) {
        case 'PROCESSED':
        case 'SUCCESS':
            $text     = 'Hujjat Xtrace tomonidan to‘liq qayta ishlangan (' . $statusNorm . ').';
            $severity = 'ok';
            break;

        case 'PROCESSED_WITH_ERRORS':
            $text     = 'Hujjat qayta ishlangan, lekin xatoliklar mavjud (PROCESSED_WITH_ERRORS).';
            $severity = 'warning';
            break;

        case 'ACCEPTED':
        case 'PENDING':
        case 'QUEUED':
        case 'IN_PROGRESS':
            $text     = 'Hujjat qabul qilingan yoki qayta ishlanmoqda (' . $statusNorm . ').';
            $severity = 'info';
            break;

        case 'REJECTED':
        case 'DECLINED':
            $text     = 'Hujjat Xtrace tomonidan rad etilgan (' . $statusNorm . ').';
            $severity = 'error';
            if (!empty($rejectReason)) {
                $text .= ' Sabab(lar):';
                foreach ($rejectReason as $r) {
                    if (is_array($r) && isset($r['message'])) {
                        $text .= ' ' . $r['message'] . ';';
                    } elseif (is_string($r)) {
                        $text .= ' ' . $r . ';';
                    }
                }
            }
            break;

        default:
            $text     = 'Hujjat holati noma’lum yoki API javobida ko‘rsatilmagan (' . ($statusNorm ?: 'null') . ').';
            $severity = 'warning';
            break;
    }

    return [
        'status_text' => $text,
        'severity'    => $severity,
    ];
}

/**
 * Filter + paginatsiya parametrlari
 */
function build_blocks_filter_and_paging(): array
{
    $src = array_merge($_GET, $_POST);

    $page    = max(1, (int)($src['page'] ?? 1));
    $perPage = (int)($src['per_page'] ?? 20);
    if ($perPage <= 0)  $perPage = 20;
    if ($perPage > 200) $perPage = 200;
    $offset  = ($page - 1) * $perPage;

    $productId   = (int)($src['product_id'] ?? 0);
    $userId      = (int)($src['user_id'] ?? 0);
    $modeType    = $src['mode_type'] ?? ''; // manual / auto
    $dateFrom    = $src['date_from'] ?? '';
    $dateTo      = $src['date_to'] ?? '';
    $search      = trim($src['search'] ?? '');
    $categoryId  = (int)($src['category_id'] ?? 0);
    $saleFilter  = $src['sale_filter'] ?? ''; // '', 'sold', 'not_sold'

    $where  = [];
    $params = [];

    if ($productId > 0) {
        $where[] = 'b.product_id = :product_id';
        $params[':product_id'] = $productId;
    }
    if ($categoryId > 0) {
        $where[] = 'p.category_id = :category_id';
        $params[':category_id'] = $categoryId;
    }
    if ($userId > 0) {
        $where[] = 'b.user_id = :user_id';
        $params[':user_id'] = $userId;
    }
    if ($modeType !== '' && in_array($modeType, ['manual', 'auto'], true)) {
        $where[] = 'b.mode_type = :mode_type';
        $params[':mode_type'] = $modeType;
    }
    if ($dateFrom !== '') {
        $where[] = 'b.created_at >= :date_from';
        $params[':date_from'] = $dateFrom . ' 00:00:00';
    }
    if ($dateTo !== '') {
        $where[] = 'b.created_at <= :date_to';
        $params[':date_to'] = $dateTo . ' 23:59:59';
    }
    if ($search !== '') {
        $where[] = '(b.gtin2_number LIKE :q OR p.name LIKE :q OR c.name LIKE :q OR b.id = :id_q)';
        $params[':q']    = '%' . $search . '%';
        $params[':id_q'] = ctype_digit($search) ? (int)$search : 0;
    }

    // sotilgan / sotilmagan filter (blocks.is_sale)
    if ($saleFilter === 'sold') {
        $where[] = 'b.is_sale = 1';
    } elseif ($saleFilter === 'not_sold') {
        $where[] = 'b.is_sale = 0';
    }

    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    return [$whereSql, $params, $page, $perPage, $offset];
}

// ==== main ====

$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$action = $_GET['action'] ?? $_POST['action'] ?? 'list';

try {
    /** @var PDO $pdo */
    if (!isset($pdo)) {
        json_error('Ma’lumotlar bazasiga ulanishda xatolik', 500);
    }

    // ---------------- LIST ----------------
    if ($action === 'list') {
        list($whereSql, $params, $page, $perPage, $offset) = build_blocks_filter_and_paging();

        // Jami soni
        $countSql = "
          SELECT COUNT(*)
            FROM blocks b
            JOIN products p ON p.id = b.product_id
       LEFT JOIN categories c ON c.id = p.category_id
       LEFT JOIN users u      ON u.id = b.user_id
          $whereSql
        ";
        $countStmt = $pdo->prepare($countSql);
        foreach ($params as $k => $v) {
            $countStmt->bindValue($k, $v);
        }
        $countStmt->execute();
        $total = (int)$countStmt->fetchColumn();

        // Ma'lumotlar
        $listSql = "
          SELECT
            b.id,
            b.product_id,
            p.name       AS product_name,
            c.name       AS category_name,
            COALESCE(p.product_image, '') AS product_image,
            b.from_id,
            b.until_id,
            b.gtin2_number,
            b.mode_type,
            b.user_id,
            u.name       AS user_name,
            b.report_id,
            b.is_sale,
            b.created_at,
            (
                SELECT COUNT(*)
                  FROM codes cc
                 WHERE cc.id BETWEEN b.from_id AND b.until_id
            ) AS total_codes,
            (
                SELECT COUNT(*)
                  FROM codes cc
                 WHERE cc.id BETWEEN b.from_id AND b.until_id
                   AND (cc.is_sold = 1 OR cc.sale_id IS NOT NULL)
            ) AS sold_codes
          FROM blocks b
            JOIN products p ON p.id = b.product_id
       LEFT JOIN categories c ON c.id = p.category_id
       LEFT JOIN users u      ON u.id = b.user_id
          $whereSql
          ORDER BY b.id DESC
          LIMIT :limit OFFSET :offset
        ";

        $listStmt = $pdo->prepare($listSql);
        foreach ($params as $k => $v) {
            $listStmt->bindValue($k, $v);
        }
        $listStmt->bindValue(':limit',  $perPage, PDO::PARAM_INT);
        $listStmt->bindValue(':offset', $offset,  PDO::PARAM_INT);
        $listStmt->execute();

        $rows = $listStmt->fetchAll(PDO::FETCH_ASSOC);
        $pageCount = $total > 0 ? (int)ceil($total / $perPage) : 1;

        json_ok([
            'blocks' => $rows,
            'pagination' => [
                'total'      => $total,
                'page'       => $page,
                'per_page'   => $perPage,
                'page_count' => $pageCount,
            ],
        ]);
    }

    // ---------------- CHECK_REPORTS ----------------
    if ($action === 'check_reports') {
        list($whereSql, $params, $page, $perPage, $offset) = build_blocks_filter_and_paging();

        $listSql = "
          SELECT
            b.id,
            b.gtin2_number,
            b.report_id,
            b.is_sale,
            p.name         AS product_name,
            p.product_image AS product_image
          FROM blocks b
            JOIN products p ON p.id = b.product_id
       LEFT JOIN categories c ON c.id = p.category_id
       LEFT JOIN users u      ON u.id = b.user_id
          $whereSql
          ORDER BY b.id DESC
          LIMIT :limit OFFSET :offset
        ";
        $stmt = $pdo->prepare($listSql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset,  PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $results = [];
        foreach ($rows as $row) {
            $id        = (int)$row['id'];
            $reportId  = trim((string)($row['report_id'] ?? ''));
            $gtin2     = $row['gtin2_number'] ?? null;
            $prodName  = $row['product_name'] ?? null;
            $isSale    = (int)($row['is_sale'] ?? 0) === 1;

            if ($reportId === '' || $reportId === '0') {
                $msgUz = 'Bu blok bo‘yicha DOC ID (report_id) bazada yo‘q. Ehtimol, Xtrace’ga hali yuborilmagan.';
                $results[] = [
                    'id'             => $id,
                    'report_id'      => null,
                    'gtin2_number'   => $gtin2,
                    'product_name'   => $prodName,
                    'ok'             => false,
                    'httpCode'       => 404,
                    'status'         => null,
                    'rejectReason'   => [],
                    'error'          => $msgUz,
                    'status_text'    => $msgUz,
                    'severity'       => 'warning',
                    'has_error'      => false,
                    'is_sale'        => $isSale,
                ];
                continue;
            }

            $apiRes = check_report_status($reportId);
            $map    = map_report_status_uz(
                $apiRes['status'],
                $apiRes['reason'],
                $apiRes['error'],
                (int)$apiRes['httpCode'],
                (bool)$apiRes['ok']
            );

            $results[] = [
                'id'             => $id,
                'report_id'      => $reportId,
                'gtin2_number'   => $gtin2,
                'product_name'   => $prodName,
                'ok'             => $apiRes['ok'],
                'httpCode'       => $apiRes['httpCode'],
                'status'         => $apiRes['status'],
                'rejectReason'   => $apiRes['reason'],
                'error'          => $apiRes['error'],
                'status_text'    => $map['status_text'],
                'severity'       => $map['severity'],
                'has_error'      => ($map['severity'] === 'error'),
                'is_sale'        => $isSale,
            ];
        }

        json_ok([
            'page'    => $page,
            'results' => $results,
        ]);
    }

    // ---------------- DELETE ----------------
    if ($action === 'delete') {
        if ($method !== 'POST') {
            json_error('POST bo‘lishi kerak', 405);
        }
        $id = (int)($_POST['id'] ?? 0);
        if ($id <= 0) {
            json_error('Noto‘g‘ri ID');
        }

        // 1) Avval blokni topamiz (from_id / until_id kerak)
        $stmt = $pdo->prepare("
            SELECT id, from_id, until_id
              FROM blocks
             WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $id]);
        $block = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$block) {
            json_error('Blok topilmadi', 404);
        }

        $fromId  = (int)$block['from_id'];
        $untilId = (int)$block['until_id'];

        if ($fromId <= 0 || $untilId <= 0 || $fromId > $untilId) {
            json_error('Blok uchun from_id / until_id noto‘g‘ri', 400, [
                'from_id'  => $fromId,
                'until_id' => $untilId,
            ]);
        }

        // 2) Transaction: avval codes oralig'ini o'chiramiz, keyin blocks
        $pdo->beginTransaction();

        // Codes delete
        $delCodes = $pdo->prepare("
            DELETE FROM codes
             WHERE id BETWEEN :from_id AND :until_id
        ");
        $delCodes->execute([
            ':from_id'  => $fromId,
            ':until_id' => $untilId,
        ]);
        $deletedCodesCount = $delCodes->rowCount();

        // Block delete
        $delBlock = $pdo->prepare("DELETE FROM blocks WHERE id = :id");
        $delBlock->execute([':id' => $id]);

        $pdo->commit();

        json_ok([
            'message'            => 'Blok va unga tegishli kodlar o‘chirildi',
            'id'                 => $id,
            'from_id'            => $fromId,
            'until_id'           => $untilId,
            'deleted_codes_count'=> $deletedCodesCount,
        ]);
    }

    // ---------------- BLOCK_CODES ----------------
    if ($action === 'block_codes') {
        $blockId = (int)($_GET['block_id'] ?? 0);
        if ($blockId <= 0) {
            json_error('Noto‘g‘ri block_id');
        }

        // 1) Blokni topamiz
        $stmt = $pdo->prepare("
            SELECT id, product_id, from_id, until_id, report_id
              FROM blocks
             WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $blockId]);
        $block = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$block) {
            json_error('Blok topilmadi');
        }

        $fromId    = (int)$block['from_id'];
        $untilId   = (int)$block['until_id'];
        $productId = (int)$block['product_id'];
        $reportId  = $block['report_id'] ?? null;

        if ($fromId <= 0 || $untilId <= 0 || $fromId > $untilId) {
            json_error('Blok uchun from_id / until_id noto‘g‘ri');
        }

        $expectedCount = $untilId - $fromId + 1;

        // 2) Kodlar
        $stmt = $pdo->prepare("
            SELECT 
                c.id,
                c.product_id,
                c.gs1_text,
                c.created_at,
                c.is_sold,
                c.sale_id,
                s.customer_id,
                s.created_at AS sale_created_at,
                cu.name      AS customer_name,
                u.name       AS sale_user_name
            FROM codes c
       LEFT JOIN sales     s  ON s.id          = c.sale_id
       LEFT JOIN customers cu ON cu.id         = s.customer_id
       LEFT JOIN users     u  ON u.id          = s.created_by
            WHERE c.id BETWEEN :from_id AND :until_id
            ORDER BY c.id ASC
        ");
        $stmt->execute([
            ':from_id'  => $fromId,
            ':until_id' => $untilId,
        ]);
        $rowsDb = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $byId = [];
        foreach ($rowsDb as $r) {
            $cid = (int)$r['id'];
            $byId[$cid] = $r;
        }

        $codes              = [];
        $soldCount          = 0;
        $missingIds         = [];
        $productMismatchIds = [];

        for ($id = $fromId; $id <= $untilId; $id++) {
            if (!isset($byId[$id])) {
                $missingIds[] = $id;
                continue;
            }

            $r = $byId[$id];
            $belongsOther = ((int)$r['product_id'] !== $productId);
            if ($belongsOther) {
                $productMismatchIds[] = $id;
            }

            $isSold = ((int)($r['is_sold'] ?? 0) === 1) || (!empty($r['sale_id']));
            if ($isSold) {
                $soldCount++;
            }

            $issuesLocal = [];
            if ($belongsOther) {
                $issuesLocal[] = 'Kod boshqa mahsulotga tegishli (product_id = ' . (int)$r['product_id'] . ').';
            }

            $codes[] = [
                'id'                       => $id,
                'gs1_text'                 => $r['gs1_text'],
                'created_at'               => $r['created_at'],
                'is_sold'                  => $isSold,
                'sale_id'                  => $isSold ? (int)$r['sale_id'] : null,
                'sale_customer_full_name'  => $isSold ? ($r['customer_name'] ?? null) : null,
                'sale_user_name'           => $isSold ? ($r['sale_user_name'] ?? null) : null,
                'sale_created_at'          => $isSold ? ($r['sale_created_at'] ?? null) : null,
                'sale_date'                => $isSold ? ($r['sale_created_at'] ?? null) : null,
                'belongs_to_other_product' => $belongsOther,
                'issues'                   => $issuesLocal,
            ];
        }

        // 4) Integratsiya xatoliklari
        $integrityErrors   = [];
        $hasIntegrityError = false;

        if (!empty($missingIds)) {
            $hasIntegrityError = true;
            $countMissing      = count($missingIds);
            $previewMissing    = array_slice($missingIds, 0, 30);
            $msg  = 'Blok diapazonidagi (' . $fromId . ' – ' . $untilId . ') kodlar orasida '
                  . $countMissing . ' ta kod bazada topilmadi. '
                  . 'Bu kodlar ehtimol hali generatsiya qilinmagan yoki bazadan tasodifan o‘chirilgan. ';
            $msg .= 'Yo‘q kod IDlari (birinchi ' . count($previewMissing) . ' ta): '
                  . implode(', ', $previewMissing) . '.';

            $integrityErrors[] = [
                'type'      => 'missing_codes',
                'message'   => $msg,
                'code_ids'  => $missingIds,
            ];
        }

        if (!empty($productMismatchIds)) {
            $hasIntegrityError = true;
            $countMismatch     = count($productMismatchIds);
            $previewMismatch   = array_slice($productMismatchIds, 0, 30);
            $msg = 'Ushbu blok faqat product_id = ' . $productId
                 . ' (blokdagi mahsulot) uchun bo‘lishi kerak, ammo diapazondagi '
                 . $countMismatch . ' ta kod boshqa mahsulotga tegishli bo‘lib chiqdi. '
                 . 'Bu kodlar ehtimol boshqa blokka kiritilgan yoki noto‘g‘ri product_id bilan yozilgan. '
                 . 'Noto‘g‘ri mahsulotga tegishli kod IDlari (birinchi '
                 . count($previewMismatch) . ' ta): ' . implode(', ', $previewMismatch) . '.';

            $integrityErrors[] = [
                'type'      => 'product_mismatch',
                'message'   => $msg,
                'code_ids'  => $productMismatchIds,
            ];
        }

        // 5) Blok bo‘yicha Xtrace (doc storage) statusi
        $reportStatusRaw    = null;
        $reportStatusMapped = null;

        if (!empty($reportId) && (string)$reportId !== '0') {
            $apiRes = check_report_status((string)$reportId);
            $reportStatusRaw    = $apiRes;
            $reportStatusMapped = map_report_status_uz(
                $apiRes['status'],
                $apiRes['reason'],
                $apiRes['error'],
                (int)$apiRes['httpCode'],
                (bool)$apiRes['ok']
            );
        }

        json_ok([
            'block' => [
                'id'         => (int)$block['id'],
                'from_id'    => $fromId,
                'until_id'   => $untilId,
                'product_id' => $productId,
                'report_id'  => $reportId,
            ],
            'report_status' => [
                'has_report'  => !empty($reportId) && (string)$reportId !== '0',
                'raw'         => $reportStatusRaw,
                'status_text' => $reportStatusMapped['status_text'] ?? null,
                'severity'    => $reportStatusMapped['severity'] ?? null,
            ],
            'codes' => $codes,
            'summary' => [
                'block_range_from'       => $fromId,
                'block_range_until'      => $untilId,
                'expected_codes'         => $expectedCount,
                'total_found'            => count($codes),
                'total_missing'          => count($missingIds),
                'sold_codes'             => $soldCount,
                'product_mismatch_codes' => count($productMismatchIds),
                'has_integrity_error'    => $hasIntegrityError,
            ],
            'integrity_errors' => $integrityErrors,
        ]);
    }

    // Agar yuqoridagilarga tushmasa
    json_error('Noto‘g‘ri action', 400);

} catch (Throwable $e) {
    // Transaction bo‘lsa rollback qilamiz (aks holda DB lock/yarim ish qolib ketadi)
    if (isset($pdo) && $pdo instanceof PDO) {
        try {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
        } catch (Throwable $t) {
            // jim
        }
    }
    json_error('Serverda kutilmagan xato yuz berdi. Texnik izoh: ' . $e->getMessage(), 500);
}
