<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/stock_transfer/send_transfer.php
// Purpose: Send stock transfer request
// Data change: YES
// Safety:
// - transaction + row lock
// - sold/deleted/pending item block
// - from/to active location validation
// - barcode_stock location সরাসরি change করবে না
// - stock_transfers pending row create করবে
// - barcode_stock.transfer_status = in_transit করবে
// - audit_log থাকলে entry করবে

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 current_user_id()
{
    foreach (['user_id', 'id', 'auth_user_id'] as $k) {
        if (isset($_SESSION[$k]) && (int)$_SESSION[$k] > 0) {
            return (int)$_SESSION[$k];
        }
    }

    if (isset($_SESSION['user']) && is_array($_SESSION['user'])) {
        foreach (['id', 'user_id'] as $k) {
            if (isset($_SESSION['user'][$k]) && (int)$_SESSION['user'][$k] > 0) {
                return (int)$_SESSION['user'][$k];
            }
        }
    }

    return 0;
}

function client_ip()
{
    foreach (['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_CLIENT_IP', 'REMOTE_ADDR'] as $k) {
        if (!empty($_SERVER[$k])) {
            $v = trim((string)$_SERVER[$k]);
            if ($k === 'HTTP_X_FORWARDED_FOR') {
                $parts = explode(',', $v);
                return trim($parts[0]);
            }
            return $v;
        }
    }

    return '';
}

function make_transfer_code(PDO $pdo)
{
    for ($i = 0; $i < 20; $i++) {
        $code = 'TRF' . date('ymdHis') . str_pad((string)mt_rand(1, 999), 3, '0', STR_PAD_LEFT);

        $q = $pdo->prepare("SELECT COUNT(*) FROM `stock_transfers` WHERE `transfer_code` = ?");
        $q->execute([$code]);

        if ((int)$q->fetchColumn() === 0) {
            return $code;
        }

        usleep(50000);
    }

    return 'TRF' . date('ymdHis') . strtoupper(bin2hex(random_bytes(2)));
}

function json_text($value)
{
    return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function add_audit_log(PDO $pdo, $tableName, $recordId, $action, $userId, $oldValues, $newValues, $note)
{
    try {
        if (!table_exists($pdo, 'audit_log')) return;

        $cols = get_columns($pdo, 'audit_log');
        if (empty($cols)) return;

        $fields = [];
        $marks = [];
        $params = [];

        $add = function($col, $val) use (&$fields, &$marks, &$params, $cols) {
            if (isset($cols[$col])) {
                $fields[] = qi($col);
                $marks[] = '?';
                $params[] = $val;
            }
        };

        $add('table_name', $tableName);
        $add('record_id', (int)$recordId);
        $add('action', strtoupper((string)$action));

        if (isset($cols['changed_by'])) {
            $add('changed_by', (int)$userId);
        } elseif (isset($cols['user_id'])) {
            $add('user_id', (int)$userId);
        }

        if (isset($cols['old_values'])) {
            $add('old_values', json_text($oldValues));
        }

        if (isset($cols['new_values'])) {
            $add('new_values', json_text($newValues));
        }

        $add('ip_address', client_ip());
        $add('note', $note);

        if (isset($cols['changed_at'])) {
            $fields[] = qi('changed_at');
            $marks[] = 'NOW()';
        } elseif (isset($cols['created_at'])) {
            $fields[] = qi('created_at');
            $marks[] = 'NOW()';
        }

        if (empty($fields)) return;

        $sql = "INSERT INTO `audit_log` (" . implode(',', $fields) . ") VALUES (" . implode(',', $marks) . ")";
        $st = $pdo->prepare($sql);
        $st->execute($params);
    } catch (Throwable $e) {
        // Audit log fail হলেও মূল transfer fail করানো হবে না।
    }
}

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

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

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

    return in_array($s, [
        'sold', 'sell', 'sold_out', 'sale_done',
        'deleted', 'cancelled', 'canceled', 'void', 'trash', 'trashed',
        'বিক্রি', 'বিক্রিত', 'বিক্রি হয়েছে', 'বিক্রি হয়েছে', 'বাতিল'
    ], true);
}

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 get_weight_gram($row)
{
    $weight = first_float($row, [
        'gross_weight_gram',
        'gross_weight',
        'weight_gram',
        'total_weight_gram',
        'net_weight',
        'weight'
    ], 0);

    if ($weight <= 0) {
        $vori  = first_float($row, ['vori', 'bhori'], 0);
        $ana   = first_float($row, ['ana', 'aana'], 0);
        $roti  = first_float($row, ['roti', 'rati'], 0);
        $point = first_float($row, ['point', 'points'], 0);

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

    return round((float)$weight, 3);
}

function normalize_item_ids($raw)
{
    if (isset($raw['item_id'])) {
        $raw['item_ids'] = [$raw['item_id']];
    }

    $itemIds = $raw['item_ids'] ?? ($raw['ids'] ?? ($raw['barcode_stock_ids'] ?? []));

    if (is_string($itemIds)) {
        $itemIds = explode(',', $itemIds);
    }

    if (!is_array($itemIds)) {
        return [];
    }

    $clean = [];
    foreach ($itemIds as $id) {
        $id = (int)$id;
        if ($id > 0) {
            $clean[$id] = $id;
        }
    }

    return array_values($clean);
}

function validate_locations(PDO $pdo, $fromLocationId, $toLocationId)
{
    if (!table_exists($pdo, 'stock_locations')) {
        return [false, 'stock_locations table পাওয়া যায়নি'];
    }

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

    $where = "`id` IN (?, ?)";
    if (isset($cols['is_active'])) {
        $where .= " AND (`is_active` IS NULL OR `is_active` = 1)";
    } elseif (isset($cols['active'])) {
        $where .= " AND (`active` IS NULL OR `active` = 1)";
    }

    $st = $pdo->prepare("SELECT COUNT(DISTINCT `id`) FROM `stock_locations` WHERE {$where}");
    $st->execute([$fromLocationId, $toLocationId]);

    if ((int)$st->fetchColumn() !== 2) {
        return [false, 'From/To location সঠিক বা active নয়'];
    }

    return [true, ''];
}

try {
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        send_json(['success' => false, 'status' => false, 'message' => 'শুধু POST request allowed'], 405);
    }

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

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

    $rawJson = file_get_contents('php://input');
    $raw = json_decode($rawJson, true);

    if (!is_array($raw)) {
        $raw = $_POST;
    }

    if (!is_array($raw)) {
        send_json(['success' => false, 'status' => false, 'message' => 'ভুল JSON data'], 400);
    }

    $userId = current_user_id();

    if ($userId <= 0) {
        send_json(['success' => false, 'status' => false, 'message' => 'Login session পাওয়া যায়নি'], 401);
    }

    $itemIds = normalize_item_ids($raw);

    $fromLocationId = (int)($raw['from_location_id'] ?? $raw['location_id'] ?? 0);
    $toLocationId   = (int)($raw['to_location_id'] ?? 0);
    $note           = trim((string)($raw['note'] ?? ''));

    if (count($itemIds) === 0) {
        send_json(['success' => false, 'status' => false, 'message' => 'কমপক্ষে একটি আইটেম বাছাই করুন'], 400);
    }

    if (count($itemIds) > 100) {
        send_json(['success' => false, 'status' => false, 'message' => 'একবারে সর্বোচ্চ 100 টি আইটেম ট্রান্সফার করা যাবে'], 400);
    }

    if ($fromLocationId <= 0 || $toLocationId <= 0) {
        send_json(['success' => false, 'status' => false, 'message' => 'From ও To location দরকার'], 400);
    }

    if ($fromLocationId === $toLocationId) {
        send_json(['success' => false, 'status' => false, 'message' => 'একই location-এ ট্রান্সফার করা যাবে না'], 400);
    }

    if (mb_strlen($note, 'UTF-8') > 500) {
        send_json(['success' => false, 'status' => false, 'message' => 'নোট 500 অক্ষরের বেশি হতে পারবে না'], 400);
    }

    list($locOk, $locMsg) = validate_locations($pdo, $fromLocationId, $toLocationId);
    if (!$locOk) {
        send_json(['success' => false, 'status' => false, 'message' => $locMsg], 400);
    }

    $bsCols = get_columns($pdo, 'barcode_stock');
    $trCols = get_columns($pdo, 'stock_transfers');

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

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

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

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

    $pdo->beginTransaction();

    $sent = 0;
    $skipped = 0;
    $reasons = [];
    $sentItems = [];

    foreach ($itemIds as $itemId) {
        $check = $pdo->prepare("SELECT * FROM `barcode_stock` WHERE `id` = ? FOR UPDATE");
        $check->execute([$itemId]);
        $item = $check->fetch(PDO::FETCH_ASSOC);

        if (!$item) {
            $skipped++;
            $reasons[] = "Item #{$itemId}: পাওয়া যায়নি";
            continue;
        }

        $barcode = trim((string)first_value($item, ['barcode', 'barcode_no', 'barcode_code'], ''));
        $label = $barcode !== '' ? $barcode : "Item #{$itemId}";

        $itemLocation = (int)($item['location_id'] ?? 0);
        if ($itemLocation !== $fromLocationId) {
            $skipped++;
            $reasons[] = "{$label}: selected From location-এ নেই";
            continue;
        }

        $status = (string)($item['status'] ?? 'in_stock');

        if (is_sold_or_deleted_status($status) || !is_stock_status_ok($status)) {
            $skipped++;
            $reasons[] = "{$label}: stock available নয়";
            continue;
        }

        if (isset($bsCols['is_deleted']) && (int)($item['is_deleted'] ?? 0) !== 0) {
            $skipped++;
            $reasons[] = "{$label}: deleted stock";
            continue;
        }

        if (isset($bsCols['deleted_at']) && !empty($item['deleted_at'])) {
            $skipped++;
            $reasons[] = "{$label}: deleted stock";
            continue;
        }

        if (isset($bsCols['sale_id']) && (int)($item['sale_id'] ?? 0) > 0) {
            $skipped++;
            $reasons[] = "{$label}: already sold";
            continue;
        }

        if (isset($bsCols['sold_at']) && !empty($item['sold_at']) && $item['sold_at'] !== '0000-00-00 00:00:00') {
            $skipped++;
            $reasons[] = "{$label}: already sold";
            continue;
        }

        if (isset($bsCols['sold_invoice_no']) && trim((string)($item['sold_invoice_no'] ?? '')) !== '') {
            $skipped++;
            $reasons[] = "{$label}: already sold";
            continue;
        }

        $transferStatus = mb_strtolower(trim((string)($item['transfer_status'] ?? 'none')), 'UTF-8');
        if ($transferStatus === '') $transferStatus = 'none';

        if ($transferStatus !== 'none') {
            $skipped++;
            $reasons[] = "{$label}: আগে থেকেই transfer pending/in transit";
            continue;
        }

        $weightGram = get_weight_gram($item);
        if ($weightGram <= 0) {
            $skipped++;
            $reasons[] = "{$label}: 0g ওজন, আগে ওজন ঠিক করুন";
            continue;
        }

        $pendingCheck = $pdo->prepare("
            SELECT COUNT(*)
            FROM `stock_transfers`
            WHERE `barcode_stock_id` = ?
              AND LOWER(TRIM(COALESCE(`status`, ''))) = 'pending'
        ");
        $pendingCheck->execute([$itemId]);

        if ((int)$pendingCheck->fetchColumn() > 0) {
            $skipped++;
            $reasons[] = "{$label}: duplicate pending transfer আছে";
            continue;
        }

        $transferCode = make_transfer_code($pdo);

        $fields = [];
        $marks = [];
        $insParams = [];

        $addParam = function($col, $val) use (&$fields, &$marks, &$insParams, $trCols) {
            if (isset($trCols[$col])) {
                $fields[] = qi($col);
                $marks[] = '?';
                $insParams[] = $val;
            }
        };

        $addRaw = function($col, $expr) use (&$fields, &$marks, $trCols) {
            if (isset($trCols[$col])) {
                $fields[] = qi($col);
                $marks[] = $expr;
            }
        };

        $addParam('transfer_code', $transferCode);
        $addParam('barcode_stock_id', $itemId);
        $addParam('from_location_id', $fromLocationId);
        $addParam('to_location_id', $toLocationId);
        $addParam('status', 'pending');
        $addParam('note', $note);
        $addParam('sent_by', $userId);
        $addRaw('sent_at', 'NOW()');
        $addRaw('created_at', 'NOW()');
        $addRaw('updated_at', 'NOW()');

        $insSql = "INSERT INTO `stock_transfers` (" . implode(',', $fields) . ") VALUES (" . implode(',', $marks) . ")";
        $ins = $pdo->prepare($insSql);
        $ins->execute($insParams);

        $transferId = (int)$pdo->lastInsertId();

        $setParts = ["`transfer_status` = ?"];
        $updParams = ['in_transit'];

        if (isset($bsCols['updated_at'])) {
            $setParts[] = "`updated_at` = NOW()";
        }

        $updWhere = [];
        $updWhere[] = "`id` = ?";
        $updParams[] = $itemId;

        $updWhere[] = "`location_id` = ?";
        $updParams[] = $fromLocationId;

        if (isset($bsCols['is_deleted'])) {
            $updWhere[] = "(`is_deleted` IS NULL OR `is_deleted` = 0)";
        }

        if (isset($bsCols['deleted_at'])) {
            $updWhere[] = "`deleted_at` IS NULL";
        }

        if (isset($bsCols['status'])) {
            $updWhere[] = "LOWER(TRIM(COALESCE(`status`, ''))) IN ('', 'in_stock', 'stock', 'available', 'ready')";
        }

        $updWhere[] = "LOWER(TRIM(COALESCE(`transfer_status`, 'none'))) IN ('', 'none')";

        $updSql = "
            UPDATE `barcode_stock`
            SET " . implode(', ', $setParts) . "
            WHERE " . implode(' AND ', $updWhere) . "
        ";

        $upd = $pdo->prepare($updSql);
        $upd->execute($updParams);

        if ($upd->rowCount() !== 1) {
            throw new Exception('Stock update failed: ' . $label);
        }

        add_audit_log(
            $pdo,
            'stock_transfers',
            $transferId,
            'INSERT',
            $userId,
            null,
            [
                'transfer_code' => $transferCode,
                'barcode_stock_id' => $itemId,
                'from_location_id' => $fromLocationId,
                'to_location_id' => $toLocationId,
                'status' => 'pending',
                'note' => $note
            ],
            "Stock transfer request created | {$transferCode} | {$label}"
        );

        add_audit_log(
            $pdo,
            'barcode_stock',
            $itemId,
            'UPDATE',
            $userId,
            [
                'location_id' => $fromLocationId,
                'transfer_status' => 'none'
            ],
            [
                'location_id' => $fromLocationId,
                'transfer_status' => 'in_transit',
                'transfer_code' => $transferCode
            ],
            "Stock marked in_transit for transfer | {$transferCode} | {$label}"
        );

        $sent++;
        $sentItems[] = [
            'transfer_id' => $transferId,
            'transfer_code' => $transferCode,
            'barcode_stock_id' => $itemId,
            'barcode' => $barcode,
            'from_location_id' => $fromLocationId,
            'to_location_id' => $toLocationId,
            'weight_gram' => $weightGram
        ];
    }

    if ($sent === 0) {
        $pdo->rollBack();

        send_json([
            'success' => false,
            'status' => false,
            'message' => 'কোনো আইটেম ট্রান্সফার হয়নি',
            'sent' => 0,
            'skipped' => $skipped,
            'reasons' => array_slice($reasons, 0, 20)
        ], 400);
    }

    $pdo->commit();

    $msg = $sent . ' টি আইটেম ট্রান্সফার পাঠানো হয়েছে';
    if ($skipped > 0) {
        $msg .= ' — ' . $skipped . ' টি স্কিপ হয়েছে';
    }

    send_json([
        'success' => true,
        'status' => true,
        'message' => $msg,
        'sent' => $sent,
        'skipped' => $skipped,
        'items' => $sentItems,
        'reasons' => array_slice($reasons, 0, 20),
        'data' => [
            'sent' => $sent,
            'skipped' => $skipped,
            'items' => $sentItems,
            'reasons' => array_slice($reasons, 0, 20)
        ]
    ]);

} catch (Throwable $e) {
    if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
        $pdo->rollBack();
    }

    send_json([
        'success' => false,
        'status' => false,
        'message' => 'ট্রান্সফার পাঠানো সমস্যা: ' . $e->getMessage()
    ], 500);
}