<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/stock_transfer/search_items.php
// Purpose: Stock Transfer item search API
// Safe: READ ONLY, no ALTER / INSERT / UPDATE / DELETE
// Fix: SQLSTATE[HY093] Invalid parameter number solved by positional placeholders

ini_set('display_errors', 0);
ini_set('precision', '14');
ini_set('serialize_precision', '-1');
error_reporting(E_ALL);
header('Content-Type: application/json; charset=utf-8');

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

if (file_exists(__DIR__ . '/../../config/response.php')) {
    require_once __DIR__ . '/../../config/response.php';
}

if (function_exists('require_login')) {
    require_login();
} elseif (function_exists('is_logged_in') && !is_logged_in()) {
    http_response_code(401);
    echo json_encode(['success' => false, 'status' => false, 'message' => 'লগইন প্রয়োজন'], JSON_UNESCAPED_UNICODE);
    exit;
}

function send_json($arr, $code = 200)
{
    http_response_code($code);
    echo json_encode($arr, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    send_json(['success' => false, 'status' => false, 'message' => 'PDO DB connection পাওয়া যায়নি'], 500);
}

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("SET NAMES utf8mb4");
} catch (Throwable $e) {
    send_json(['success' => false, 'status' => false, 'message' => 'ডাটাবেজ কানেকশন সমস্যা'], 500);
}

function clean_name($name)
{
    return preg_replace('/[^a-zA-Z0-9_]/', '', (string)$name);
}

function qi($name)
{
    return '`' . str_replace('`', '', clean_name($name)) . '`';
}

function table_exists(PDO $pdo, $table)
{
    $table = clean_name($table);
    if ($table === '') return false;

    $st = $pdo->query("SHOW TABLES LIKE " . $pdo->quote($table));
    return (bool)$st->fetchColumn();
}

function get_columns(PDO $pdo, $table)
{
    $table = clean_name($table);
    if ($table === '' || !table_exists($pdo, $table)) return [];

    $rows = $pdo->query("SHOW COLUMNS FROM " . qi($table))->fetchAll(PDO::FETCH_ASSOC);
    $cols = [];

    foreach ($rows as $r) {
        if (isset($r['Field'])) {
            $cols[$r['Field']] = true;
        }
    }

    return $cols;
}

function pick_col($cols, $names)
{
    foreach ($names as $n) {
        if (isset($cols[$n])) return $n;
    }
    return null;
}

function first_value($row, $keys, $default = '')
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return $row[$k];
        }
    }
    return $default;
}

function first_float($row, $keys, $default = 0)
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return (float)$row[$k];
        }
    }
    return (float)$default;
}

function first_int($row, $keys, $default = 0)
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return (int)$row[$k];
        }
    }
    return (int)$default;
}

function safe_num($v, $dec = 3)
{
    return round((float)$v, $dec);
}

function gram_from_parts($vori, $ana, $roti, $point)
{
    $VORI = 11.664;

    return round(
        ((float)$vori * $VORI) +
        ((float)$ana * ($VORI / 16)) +
        ((float)$roti * ($VORI / 96)) +
        ((float)$point * ($VORI / 960)),
        3
    );
}

function gram_to_parts($gram)
{
    $VORI = 11.664;
    $totalPoint = (int)round(((float)$gram / $VORI) * 960);

    $vori = intdiv($totalPoint, 960);
    $totalPoint %= 960;

    $ana = intdiv($totalPoint, 60);
    $totalPoint %= 60;

    $roti = intdiv($totalPoint, 10);
    $point = $totalPoint % 10;

    return [
        'vori' => $vori,
        'ana' => $ana,
        'roti' => $roti,
        'rati' => $roti,
        'point' => $point
    ];
}

function load_location_map(PDO $pdo)
{
    $map = [
        1 => ['name' => 'Factory', 'name_bn' => 'ফ্যাক্টরি', 'display_name' => 'ফ্যাক্টরি'],
        2 => ['name' => 'Showroom-1', 'name_bn' => 'শোরুম-১', 'display_name' => 'শোরুম-১'],
        3 => ['name' => 'Showroom-2', 'name_bn' => 'শোরুম-২', 'display_name' => 'শোরুম-২'],
        4 => ['name' => 'Locker', 'name_bn' => 'লকার', 'display_name' => 'লকার'],
    ];

    if (!table_exists($pdo, 'stock_locations')) return $map;

    $cols = get_columns($pdo, 'stock_locations');

    $nameCol = pick_col($cols, ['name', 'location_name', 'title']);
    $nameBnCol = pick_col($cols, ['name_bn', 'location_name_bn', 'title_bn']);
    $activeCol = pick_col($cols, ['is_active', 'active']);

    $select = ["`id`"];
    $select[] = $nameCol ? qi($nameCol) . " AS name" : "'' AS name";
    $select[] = $nameBnCol ? qi($nameBnCol) . " AS name_bn" : "'' AS name_bn";

    $where = " WHERE 1=1 ";
    if ($activeCol) {
        $where .= " AND (" . qi($activeCol) . " IS NULL OR " . qi($activeCol) . " = 1) ";
    }

    $sql = "SELECT " . implode(", ", $select) . " FROM `stock_locations` {$where} ORDER BY `id` ASC";
    $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $r) {
        $id = (int)($r['id'] ?? 0);
        if ($id <= 0) continue;

        $name = trim((string)($r['name'] ?? ''));
        $nameBn = trim((string)($r['name_bn'] ?? ''));
        $display = $nameBn !== '' ? $nameBn : $name;
        if ($display === '') $display = 'Location-' . $id;

        $map[$id] = [
            'name' => $name,
            'name_bn' => $nameBn,
            'display_name' => $display
        ];
    }

    return $map;
}

try {
    $locationId = (int)($_GET['location_id'] ?? $_GET['from_location_id'] ?? 0);
    $search = trim((string)($_GET['search'] ?? $_GET['q'] ?? ''));
    $includeZero = (int)($_GET['include_zero'] ?? 0);
    $limit = (int)($_GET['limit'] ?? 100);

    if ($limit <= 0) $limit = 100;
    if ($limit > 300) $limit = 300;

    if ($locationId <= 0) {
        send_json([
            'success' => false,
            'status' => false,
            'message' => 'location_id দরকার'
        ], 400);
    }

    if (!table_exists($pdo, 'barcode_stock')) {
        send_json([
            'success' => true,
            'status' => true,
            'message' => 'barcode_stock table পাওয়া যায়নি',
            'items' => [],
            'data' => ['items' => []]
        ]);
    }

    $cols = get_columns($pdo, 'barcode_stock');

    $idCol = pick_col($cols, ['id', 'barcode_stock_id', 'barcode_id']);
    $locCol = pick_col($cols, ['location_id', 'stock_location_id']);

    if (!$idCol) {
        send_json(['success' => false, 'status' => false, 'message' => 'barcode_stock ID column পাওয়া যায়নি'], 500);
    }

    if (!$locCol) {
        send_json(['success' => false, 'status' => false, 'message' => 'barcode_stock location column পাওয়া যায়নি'], 500);
    }

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

    $where[] = "bs." . qi($locCol) . " = ?";
    $params[] = $locationId;

    if (isset($cols['is_deleted'])) {
        $where[] = "(bs.`is_deleted` IS NULL OR bs.`is_deleted` = 0)";
    }

    if (isset($cols['deleted_at'])) {
        $where[] = "bs.`deleted_at` IS NULL";
    }

    if (isset($cols['status'])) {
        $where[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) IN ('', 'in_stock', 'stock', 'available', 'ready')";
        $where[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) NOT IN ('sold','sell','sold_out','sale_done','deleted','cancelled','canceled','void','trash','trashed','বিক্রি','বিক্রিত','বিক্রি হয়েছে','বিক্রি হয়েছে','বাতিল')";
    }

    if (isset($cols['sale_id'])) {
        $where[] = "(bs.`sale_id` IS NULL OR bs.`sale_id` = 0)";
    }

    if (isset($cols['sold_at'])) {
        $where[] = "(bs.`sold_at` IS NULL OR bs.`sold_at` = '' OR bs.`sold_at` = '0000-00-00 00:00:00')";
    }

    if (isset($cols['sold_invoice_no'])) {
        $where[] = "(bs.`sold_invoice_no` IS NULL OR bs.`sold_invoice_no` = '')";
    }

    if (isset($cols['transfer_status'])) {
        $where[] = "LOWER(TRIM(COALESCE(bs.`transfer_status`, 'none'))) IN ('', 'none')";
    }

    if ($search !== '') {
        $searchParts = [];

        foreach (['barcode', 'barcode_no', 'barcode_code', 'product_name', 'item_name', 'name', 'karat', 'purity', 'note', 'remarks'] as $c) {
            if (isset($cols[$c])) {
                $searchParts[] = "bs." . qi($c) . " LIKE ?";
                $params[] = '%' . $search . '%';
            }
        }

        if (!empty($searchParts)) {
            $where[] = "(" . implode(" OR ", $searchParts) . ")";
        }
    }

    $orderCol = pick_col($cols, ['created_at', 'id']);
    $karatCol = pick_col($cols, ['karat', 'purity']);

    $orderSql = " ORDER BY ";
    if ($karatCol) {
        $orderSql .= "bs." . qi($karatCol) . " ASC, ";
    }

    if ($orderCol) {
        $orderSql .= "bs." . qi($orderCol) . " DESC ";
    } else {
        $orderSql .= "bs." . qi($idCol) . " DESC ";
    }

    $sql = "
        SELECT bs.*
        FROM `barcode_stock` bs
        WHERE " . implode(" AND ", $where) . "
        {$orderSql}
        LIMIT " . (int)$limit . "
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $locations = load_location_map($pdo);

    $items = [];
    $skippedZero = 0;

    foreach ($rows as $r) {
        $id = (int)$r[$idCol];

        $barcode = trim((string)first_value($r, ['barcode', 'barcode_no', 'barcode_code'], ''));
        $productName = trim((string)first_value($r, ['product_name', 'item_name', 'name'], '—'));
        $karat = trim((string)first_value($r, ['karat', 'purity'], '22K'));

        $vori = first_float($r, ['vori', 'bhori'], 0);
        $ana = first_float($r, ['ana', 'aana'], 0);
        $roti = first_float($r, ['roti', 'rati'], 0);
        $point = first_float($r, ['point', 'points'], 0);

        $grossGram = first_float($r, [
            'gross_weight_gram',
            'gross_weight',
            'weight_gram',
            'total_weight_gram',
            'net_weight',
            'weight'
        ], 0);

        if ($grossGram <= 0 && ($vori > 0 || $ana > 0 || $roti > 0 || $point > 0)) {
            $grossGram = gram_from_parts($vori, $ana, $roti, $point);
        }

        if ($grossGram > 0 && $vori <= 0 && $ana <= 0 && $roti <= 0 && $point <= 0) {
            $parts = gram_to_parts($grossGram);
            $vori = $parts['vori'];
            $ana = $parts['ana'];
            $roti = $parts['roti'];
            $point = $parts['point'];
        }

        $isZero = $grossGram <= 0;

        if ($isZero && $includeZero !== 1) {
            $skippedZero++;
            continue;
        }

        $pureGram = first_float($r, ['pure_weight_gram', 'pure_weight', 'pure_gold_gram'], 0);
        $purityPercent = first_float($r, ['purity_percent', 'purity_percentage'], 0);

        $locId = (int)first_int($r, ['location_id', 'stock_location_id'], $locationId);
        $locName = isset($locations[$locId]) ? $locations[$locId]['display_name'] : ('Location-' . $locId);

        $transferStatus = trim((string)first_value($r, ['transfer_status'], 'none'));
        if ($transferStatus === '') $transferStatus = 'none';

        $createdAt = trim((string)first_value($r, ['created_at', 'date', 'purchase_date'], ''));
        $note = trim((string)first_value($r, ['note', 'remarks'], ''));

        $items[] = [
            'id' => $id,
            'barcode_stock_id' => $id,
            'barcode_id' => $id,

            'barcode' => $barcode,
            'barcode_no' => $barcode,
            'barcode_code' => $barcode,

            'product_name' => $productName,
            'item_name' => $productName,
            'name' => $productName,

            'karat' => $karat,
            'purity' => $karat,

            'gross_weight_gram' => safe_num($grossGram),
            'gross_weight' => safe_num($grossGram),
            'weight' => safe_num($grossGram),
            'weight_gram' => safe_num($grossGram),

            'pure_weight_gram' => safe_num($pureGram),
            'pure_weight' => safe_num($pureGram),
            'purity_percent' => safe_num($purityPercent, 2),

            'vori' => (int)$vori,
            'ana' => (int)$ana,
            'roti' => (int)$roti,
            'rati' => (int)$roti,
            'point' => (int)$point,

            'location_id' => $locId,
            'stock_location_id' => $locId,
            'location_name' => $locName,
            'location' => $locName,

            'status' => 'in_stock',
            'transfer_status' => $transferStatus,

            'note' => $note,
            'created_at' => $createdAt,

            'is_zero_weight' => $isZero ? 1 : 0,
            'is_transferable' => (!$isZero && strtolower($transferStatus) === 'none') ? 1 : 0
        ];
    }

    send_json([
        'success' => true,
        'status' => true,
        'message' => 'ট্রান্সফারের জন্য স্টক আইটেম লোড হয়েছে',
        'read_only' => true,
        'items' => $items,
        'data' => ['items' => $items],
        'meta' => [
            'location_id' => $locationId,
            'search' => $search,
            'limit' => $limit,
            'returned' => count($items),
            'skipped_zero_weight' => $skippedZero,
            'include_zero' => $includeZero
        ]
    ]);

} catch (Throwable $e) {
    send_json([
        'success' => false,
        'status' => false,
        'message' => 'স্টক আইটেম সার্চ সমস্যা: ' . $e->getMessage()
    ], 500);
}