<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/stock_transfer/get_pending.php
// Purpose: Stock Transfer pending list API
// Safe: READ ONLY, no ALTER / INSERT / UPDATE / DELETE

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 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 coalesce_select($alias, $cols, $names, $as, $fallback = "''")
{
    $parts = [];

    foreach ($names as $n) {
        if (isset($cols[$n])) {
            $parts[] = $alias . "." . qi($n);
        }
    }

    $parts[] = $fallback;

    return "COALESCE(" . implode(", ", $parts) . ") AS " . qi($as);
}

function normalize_stock_status($status)
{
    $s = mb_strtolower(trim((string)$status), 'UTF-8');

    if (in_array($s, ['sold', 'sell', 'sold_out', 'sale_done', 'বিক্রি', 'বিক্রিত', 'বিক্রি হয়েছে', 'বিক্রি হয়েছে'], true)) {
        return 'sold';
    }

    if (in_array($s, ['deleted', 'cancelled', 'canceled', 'void', 'trash', 'trashed', 'বাতিল'], true)) {
        return 'deleted';
    }

    if (in_array($s, ['', 'in_stock', 'stock', 'available', 'ready'], true)) {
        return 'in_stock';
    }

    return $s;
}

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

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

    $trCols = get_columns($pdo, 'stock_transfers');
    $bsCols = get_columns($pdo, 'barcode_stock');
    $locCols = table_exists($pdo, 'stock_locations') ? get_columns($pdo, 'stock_locations') : [];
    $userCols = table_exists($pdo, 'users') ? get_columns($pdo, 'users') : [];

    foreach (['id', 'barcode_stock_id', 'from_location_id', 'to_location_id', 'status'] as $c) {
        if (!isset($trCols[$c])) {
            send_json([
                'success' => false,
                'status' => false,
                'message' => 'stock_transfers.' . $c . ' column পাওয়া যায়নি'
            ], 500);
        }
    }

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

    $toLocationId = (int)($_GET['to_location_id'] ?? 0);
    $fromLocationId = (int)($_GET['from_location_id'] ?? 0);
    $search = trim((string)($_GET['search'] ?? $_GET['q'] ?? ''));
    $limit = (int)($_GET['limit'] ?? 300);

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

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

    $where[] = "LOWER(TRIM(COALESCE(st.`status`, ''))) = 'pending'";

    if ($toLocationId > 0) {
        $where[] = "st.`to_location_id` = ?";
        $params[] = $toLocationId;
    }

    if ($fromLocationId > 0) {
        $where[] = "st.`from_location_id` = ?";
        $params[] = $fromLocationId;
    }

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

        if (isset($trCols['transfer_code'])) {
            $searchParts[] = "st.`transfer_code` LIKE ?";
            $params[] = '%' . $search . '%';
        }

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

        if (isset($trCols['note'])) {
            $searchParts[] = "st.`note` LIKE ?";
            $params[] = '%' . $search . '%';
        }

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

    $select = [];

    $select[] = "st.`id` AS id";
    $select[] = "st.`id` AS transfer_id";
    $select[] = isset($trCols['transfer_code']) ? "st.`transfer_code` AS transfer_code" : "CONCAT('TRF-', st.`id`) AS transfer_code";
    $select[] = "st.`barcode_stock_id` AS barcode_stock_id";
    $select[] = "st.`from_location_id` AS from_location_id";
    $select[] = "st.`to_location_id` AS to_location_id";
    $select[] = "st.`status` AS transfer_status";
    $select[] = isset($trCols['note']) ? "st.`note` AS note" : "'' AS note";
    $select[] = isset($trCols['sent_at']) ? "st.`sent_at` AS sent_at" : (isset($trCols['created_at']) ? "st.`created_at` AS sent_at" : "'' AS sent_at");
    $select[] = isset($trCols['sent_by']) ? "st.`sent_by` AS sent_by" : "0 AS sent_by";

    $select[] = coalesce_select('bs', $bsCols, ['barcode', 'barcode_no', 'barcode_code'], 'barcode', "''");
    $select[] = coalesce_select('bs', $bsCols, ['barcode_no', 'barcode', 'barcode_code'], 'barcode_no', "''");
    $select[] = coalesce_select('bs', $bsCols, ['product_name', 'item_name', 'name'], 'product_name', "'—'");
    $select[] = coalesce_select('bs', $bsCols, ['product_name', 'item_name', 'name'], 'item_name', "'—'");
    $select[] = coalesce_select('bs', $bsCols, ['karat', 'purity'], 'karat', "'22K'");

    $select[] = coalesce_select('bs', $bsCols, ['gross_weight_gram', 'gross_weight', 'weight_gram', 'total_weight_gram', 'net_weight', 'weight'], 'gross_weight_raw', "0");
    $select[] = coalesce_select('bs', $bsCols, ['pure_weight_gram', 'pure_weight', 'pure_gold_gram'], 'pure_weight_raw', "0");
    $select[] = coalesce_select('bs', $bsCols, ['purity_percent', 'purity_percentage'], 'purity_percent_raw', "0");

    $select[] = isset($bsCols['vori']) ? "bs.`vori` AS vori_raw" : (isset($bsCols['bhori']) ? "bs.`bhori` AS vori_raw" : "0 AS vori_raw");
    $select[] = isset($bsCols['ana']) ? "bs.`ana` AS ana_raw" : (isset($bsCols['aana']) ? "bs.`aana` AS ana_raw" : "0 AS ana_raw");
    $select[] = isset($bsCols['roti']) ? "bs.`roti` AS roti_raw" : (isset($bsCols['rati']) ? "bs.`rati` AS roti_raw" : "0 AS roti_raw");
    $select[] = isset($bsCols['point']) ? "bs.`point` AS point_raw" : (isset($bsCols['points']) ? "bs.`points` AS point_raw" : "0 AS point_raw");

    $select[] = isset($bsCols['status']) ? "bs.`status` AS stock_status_raw" : "'in_stock' AS stock_status_raw";
    $select[] = isset($bsCols['is_deleted']) ? "bs.`is_deleted` AS is_deleted_raw" : "0 AS is_deleted_raw";
    $select[] = isset($bsCols['deleted_at']) ? "bs.`deleted_at` AS deleted_at_raw" : "NULL AS deleted_at_raw";
    $select[] = isset($bsCols['transfer_status']) ? "bs.`transfer_status` AS barcode_transfer_status" : "'' AS barcode_transfer_status";
    $select[] = isset($bsCols['location_id']) ? "bs.`location_id` AS current_location_id" : "0 AS current_location_id";
    $select[] = isset($bsCols['created_at']) ? "bs.`created_at` AS stock_created_at" : "'' AS stock_created_at";

    if (table_exists($pdo, 'stock_locations') && !empty($locCols)) {
        $select[] = coalesce_select('fl', $locCols, ['name_bn', 'name', 'location_name_bn', 'location_name'], 'from_name', "'Location'");
        $select[] = coalesce_select('tl', $locCols, ['name_bn', 'name', 'location_name_bn', 'location_name'], 'to_name', "'Location'");
    } else {
        $select[] = "CONCAT('Location-', st.`from_location_id`) AS from_name";
        $select[] = "CONCAT('Location-', st.`to_location_id`) AS to_name";
    }

    if (table_exists($pdo, 'users') && !empty($userCols) && isset($trCols['sent_by'])) {
        $select[] = coalesce_select('u', $userCols, ['name', 'full_name', 'username', 'user_name', 'email'], 'sent_by_name', "'—'");
    } else {
        $select[] = "'—' AS sent_by_name";
    }

    $joins = [];
    $joins[] = "JOIN `barcode_stock` bs ON bs.`id` = st.`barcode_stock_id`";

    if (table_exists($pdo, 'stock_locations') && !empty($locCols)) {
        $joins[] = "LEFT JOIN `stock_locations` fl ON fl.`id` = st.`from_location_id`";
        $joins[] = "LEFT JOIN `stock_locations` tl ON tl.`id` = st.`to_location_id`";
    }

    if (table_exists($pdo, 'users') && !empty($userCols) && isset($trCols['sent_by'])) {
        $joins[] = "LEFT JOIN `users` u ON u.`id` = st.`sent_by`";
    }

    $orderCol = isset($trCols['sent_at']) ? 'sent_at' : (isset($trCols['created_at']) ? 'created_at' : 'id');

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM `stock_transfers` st
        " . implode("\n        ", $joins) . "
        WHERE " . implode(" AND ", $where) . "
        ORDER BY st." . qi($orderCol) . " DESC, st.`id` DESC
        LIMIT " . (int)$limit . "
    ";

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

    $items = [];
    $warningCount = 0;

    foreach ($rows as $r) {
        $grossGram = (float)($r['gross_weight_raw'] ?? 0);

        $vori = (float)($r['vori_raw'] ?? 0);
        $ana = (float)($r['ana_raw'] ?? 0);
        $roti = (float)($r['roti_raw'] ?? 0);
        $point = (float)($r['point_raw'] ?? 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'];
        }

        $stockStatus = normalize_stock_status($r['stock_status_raw'] ?? '');
        $barcodeTransferStatus = mb_strtolower(trim((string)($r['barcode_transfer_status'] ?? '')), 'UTF-8');
        $currentLocationId = (int)($r['current_location_id'] ?? 0);

        $warnings = [];

        if ($stockStatus !== 'in_stock') {
            $warnings[] = 'Stock status mismatch: ' . $stockStatus;
        }

        if ((int)($r['is_deleted_raw'] ?? 0) !== 0 || !empty($r['deleted_at_raw'])) {
            $warnings[] = 'Deleted stock mismatch';
        }

        if (!in_array($barcodeTransferStatus, ['in_transit', 'pending'], true)) {
            $warnings[] = 'Transfer status mismatch: ' . ($barcodeTransferStatus ?: 'empty');
        }

        if ($currentLocationId > 0 && $currentLocationId !== (int)$r['from_location_id']) {
            $warnings[] = 'Current location mismatch';
        }

        if ($grossGram <= 0) {
            $warnings[] = '0g weight';
        }

        if (!empty($warnings)) {
            $warningCount++;
        }

        $item = [
            'id' => (int)$r['id'],
            'transfer_id' => (int)$r['transfer_id'],
            'transfer_code' => (string)$r['transfer_code'],

            'barcode_stock_id' => (int)$r['barcode_stock_id'],
            'barcode' => (string)$r['barcode'],
            'barcode_no' => (string)$r['barcode_no'],

            'product_name' => (string)$r['product_name'],
            'item_name' => (string)$r['item_name'],

            'karat' => (string)$r['karat'],
            'purity' => (string)$r['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($r['pure_weight_raw'] ?? 0),
            'pure_weight' => safe_num($r['pure_weight_raw'] ?? 0),
            'purity_percent' => safe_num($r['purity_percent_raw'] ?? 0, 2),

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

            'from_location_id' => (int)$r['from_location_id'],
            'to_location_id' => (int)$r['to_location_id'],
            'from_name' => (string)$r['from_name'],
            'to_name' => (string)$r['to_name'],

            'status' => 'pending',
            'transfer_status' => 'pending',
            'barcode_transfer_status' => $barcodeTransferStatus,

            'sent_by' => (int)($r['sent_by'] ?? 0),
            'sent_by_name' => (string)$r['sent_by_name'],
            'sent_at' => (string)$r['sent_at'],

            'note' => (string)($r['note'] ?? ''),

            'current_location_id' => $currentLocationId,
            'stock_status' => $stockStatus,

            'has_warning' => !empty($warnings) ? 1 : 0,
            'warnings' => $warnings
        ];

        $items[] = $item;
    }

    send_json([
        'success' => true,
        'status' => true,
        'message' => 'Pending transfer list loaded',
        'read_only' => true,

        'data' => $items,
        'items' => $items,
        'pending' => $items,
        'count' => count($items),

        'summary' => [
            'pending_count' => count($items),
            'warning_count' => $warningCount
        ],

        'meta' => [
            'to_location_id' => $toLocationId,
            'from_location_id' => $fromLocationId,
            'search' => $search,
            'limit' => $limit
        ]
    ]);

} catch (Throwable $e) {
    send_json([
        'success' => false,
        'status' => false,
        'message' => 'Pending transfer লোড সমস্যা: ' . $e->getMessage()
    ], 500);
}