<?php
/*
  cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/new_stock/get_stock_details.php
  Purpose: New Stock / Old Purchase Stock details API
  Safe: READ ONLY, no ALTER / INSERT / UPDATE / DELETE

  Input:
  - GET  ?id=123
  - POST JSON {"id":123}
*/

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 (session_status() === PHP_SESSION_NONE) {
    session_start();
}

function nsd_out($code, $ok, $msg, $data = null) {
    http_response_code($code);

    echo json_encode([
        'success' => (bool)$ok,
        'status'  => (bool)$ok,
        'message' => $msg,
        'data'    => $data,
        'stock'   => $data
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

    exit;
}

if (function_exists('require_login')) {
    require_login();
} else {
    $logged = false;

    if (!empty($_SESSION['user_id'])) {
        $logged = true;
    } elseif (!empty($_SESSION['user']['id'])) {
        $logged = true;
    } elseif (function_exists('is_logged_in') && is_logged_in()) {
        $logged = true;
    }

    if (!$logged) {
        nsd_out(401, false, 'লগইন প্রয়োজন');
    }
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    nsd_out(500, false, 'PDO DB connection পাওয়া যায়নি');
}

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

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

function nsd_qi($name) {
    return '`' . str_replace('`', '', nsd_clean($name)) . '`';
}

function nsd_table_exists(PDO $pdo, $table) {
    $table = nsd_clean($table);
    if ($table === '') return false;

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

function nsd_get_columns(PDO $pdo, $table) {
    $table = nsd_clean($table);
    if ($table === '' || !nsd_table_exists($pdo, $table)) return [];

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

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

    return $cols;
}

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

    return null;
}

function nsd_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 nsd_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 nsd_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 nsd_safe_num($v, $dec = 3) {
    return round((float)$v, $dec);
}

function nsd_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 nsd_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 nsd_vori_text($gram) {
    $p = nsd_gram_to_parts($gram);

    return $p['vori'] . ' ভরি ' .
           $p['ana'] . ' আনা ' .
           $p['roti'] . ' রতি ' .
           $p['point'] . ' পয়েন্ট';
}

function nsd_purity_percent($karat, $fallback = 91.667) {
    $k = strtoupper(trim((string)$karat));

    $map = [
        '24K' => 100,
        '22K' => 91.667,
        '21K' => 87.5,
        '18K' => 75
    ];

    return $map[$k] ?? $fallback;
}

function nsd_coalesce_select($alias, $cols, $names, $as, $fallback = "''") {
    $parts = [];

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

    $parts[] = $fallback;

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

try {
    $raw = json_decode(file_get_contents('php://input'), true);
    if (!is_array($raw)) {
        $raw = $_POST;
    }

    $id = 0;

    if (isset($_GET['id'])) {
        $id = (int)$_GET['id'];
    } elseif (is_array($raw) && isset($raw['id'])) {
        $id = (int)$raw['id'];
    } elseif (is_array($raw) && isset($raw['stock_id'])) {
        $id = (int)$raw['stock_id'];
    }

    if ($id <= 0) {
        nsd_out(400, false, 'ID লাগবে');
    }

    if (!nsd_table_exists($pdo, 'new_stock')) {
        nsd_out(404, false, 'new_stock table পাওয়া যায়নি');
    }

    $nsCols = nsd_get_columns($pdo, 'new_stock');

    $idCol = nsd_pick_col($nsCols, ['id', 'stock_id', 'new_stock_id']);
    if (!$idCol) {
        nsd_out(500, false, 'new_stock ID column পাওয়া যায়নি');
    }

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

    $where[] = "s." . nsd_qi($idCol) . " = ?";
    $params[] = $id;

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

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

    $select = [];
    $joins = [];

    $select[] = "s.*";
    $select[] = "s." . nsd_qi($idCol) . " AS main_stock_id";

    $supplierCol = nsd_pick_col($nsCols, ['supplier_id', 'party_id']);
    $partyCols = [];

    if ($supplierCol && nsd_table_exists($pdo, 'parties')) {
        $partyCols = nsd_get_columns($pdo, 'parties');

        if (isset($partyCols['id'])) {
            $joins[] = "LEFT JOIN `parties` p ON p.`id` = s." . nsd_qi($supplierCol);
            $select[] = nsd_coalesce_select('p', $partyCols, ['name', 'party_name', 'customer_name'], 'supplier_name', "''");
            $select[] = nsd_coalesce_select('p', $partyCols, ['phone', 'mobile'], 'supplier_phone', "''");
            $select[] = nsd_coalesce_select('p', $partyCols, ['address', 'address_line'], 'supplier_address', "''");
        } else {
            $select[] = "'' AS supplier_name";
            $select[] = "'' AS supplier_phone";
            $select[] = "'' AS supplier_address";
        }
    } else {
        $select[] = "'' AS supplier_name";
        $select[] = "'' AS supplier_phone";
        $select[] = "'' AS supplier_address";
    }

    $locationCol = nsd_pick_col($nsCols, ['location_id', 'stock_location_id']);
    $locCols = [];

    if ($locationCol && nsd_table_exists($pdo, 'stock_locations')) {
        $locCols = nsd_get_columns($pdo, 'stock_locations');

        if (isset($locCols['id'])) {
            $joins[] = "LEFT JOIN `stock_locations` sl ON sl.`id` = s." . nsd_qi($locationCol);
            $select[] = nsd_coalesce_select('sl', $locCols, ['name_bn', 'name', 'location_name_bn', 'location_name'], 'location_name', "''");
            $select[] = nsd_coalesce_select('sl', $locCols, ['type', 'location_type'], 'location_type', "''");
        } else {
            $select[] = "'' AS location_name";
            $select[] = "'' AS location_type";
        }
    } else {
        $select[] = "'' AS location_name";
        $select[] = "'' AS location_type";
    }

    $cashCol = nsd_pick_col($nsCols, ['cash_account_id', 'account_id']);
    $cashCols = [];

    if ($cashCol && nsd_table_exists($pdo, 'cash_accounts')) {
        $cashCols = nsd_get_columns($pdo, 'cash_accounts');

        if (isset($cashCols['id'])) {
            $joins[] = "LEFT JOIN `cash_accounts` ca ON ca.`id` = s." . nsd_qi($cashCol);
            $select[] = nsd_coalesce_select('ca', $cashCols, ['account_name_bn', 'account_name', 'name'], 'cash_account_name', "''");
            $select[] = nsd_coalesce_select('ca', $cashCols, ['current_balance', 'balance'], 'cash_account_balance', "0");
        } else {
            $select[] = "'' AS cash_account_name";
            $select[] = "0 AS cash_account_balance";
        }
    } else {
        $select[] = "'' AS cash_account_name";
        $select[] = "0 AS cash_account_balance";
    }

    $paymentCol = nsd_pick_col($nsCols, ['payment_method_id', 'payment_id']);
    $paymentCols = [];

    if ($paymentCol && nsd_table_exists($pdo, 'payment_methods')) {
        $paymentCols = nsd_get_columns($pdo, 'payment_methods');

        if (isset($paymentCols['id'])) {
            $joins[] = "LEFT JOIN `payment_methods` pm ON pm.`id` = s." . nsd_qi($paymentCol);
            $select[] = nsd_coalesce_select('pm', $paymentCols, ['name_bn', 'name', 'method_name_bn', 'method_name'], 'payment_method_name_db', "''");
        } else {
            $select[] = "'' AS payment_method_name_db";
        }
    } else {
        $select[] = "'' AS payment_method_name_db";
    }

    $createdByCol = nsd_pick_col($nsCols, ['created_by', 'user_id']);
    $userCols = [];

    if ($createdByCol && nsd_table_exists($pdo, 'users')) {
        $userCols = nsd_get_columns($pdo, 'users');

        if (isset($userCols['id'])) {
            $joins[] = "LEFT JOIN `users` u ON u.`id` = s." . nsd_qi($createdByCol);
            $select[] = nsd_coalesce_select('u', $userCols, ['name', 'full_name', 'username', 'user_name', 'email'], 'created_by_name', "''");
        } else {
            $select[] = "'' AS created_by_name";
        }
    } else {
        $select[] = "'' AS created_by_name";
    }

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM `new_stock` s
        " . implode("\n        ", $joins) . "
        WHERE " . implode(" AND ", $where) . "
        LIMIT 1
    ";

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

    if (!$row) {
        nsd_out(404, false, 'স্টক পাওয়া যায়নি');
    }

    $stockDate = (string)nsd_first_value($row, ['stock_date', 'purchase_date', 'date', 'created_at'], '');
    $category = (string)nsd_first_value($row, ['category', 'stock_type'], '');
    $productName = (string)nsd_first_value($row, ['product_name', 'item_name', 'name'], $category);
    if ($productName === '') $productName = '—';

    $karat = (string)nsd_first_value($row, ['karat', 'purity'], '22K');

    $totalPcs = nsd_first_int($row, ['total_pcs', 'pcs', 'quantity', 'qty'], 0);
    $availablePcs = nsd_first_int($row, ['available_pcs', 'balance_pcs'], $totalPcs);
    $usedPcs = max(0, $totalPcs - $availablePcs);

    $totalWeight = nsd_first_float($row, ['total_weight_gram', 'gross_weight_gram', 'weight_gram', 'weight'], 0);
    if ($totalWeight <= 0) {
        $totalWeight = nsd_gram_from_parts(
            nsd_first_float($row, ['vori', 'bhori'], 0),
            nsd_first_float($row, ['ana', 'aana'], 0),
            nsd_first_float($row, ['roti', 'rati'], 0),
            nsd_first_float($row, ['point', 'points'], 0)
        );
    }

    $availableWeight = nsd_first_float($row, ['available_weight_gram', 'balance_weight_gram'], $totalWeight);
    $usedWeight = max(0, $totalWeight - $availableWeight);

    $purityPercent = nsd_first_float($row, ['purity_percent', 'actual_purity', 'system_purity'], nsd_purity_percent($karat));
    if ($purityPercent <= 0) {
        $purityPercent = nsd_purity_percent($karat);
    }

    $pureWeight = nsd_first_float($row, ['pure_weight_gram', 'pure_weight', 'pure_gold_gram'], 0);
    if ($pureWeight <= 0 && $totalWeight > 0) {
        $pureWeight = round($totalWeight * ($purityPercent / 100), 3);
    }

    $avgWeight = $totalPcs > 0 ? round($totalWeight / $totalPcs, 3) : 0;

    $ratePerVori = null;
    if (array_key_exists('rate_per_vori', $row) && $row['rate_per_vori'] !== null && $row['rate_per_vori'] !== '') {
        $ratePerVori = (float)$row['rate_per_vori'];
    }

    $totalAmount = null;
    if (array_key_exists('total_amount', $row) && $row['total_amount'] !== null && $row['total_amount'] !== '') {
        $totalAmount = (float)$row['total_amount'];
    }

    $locationId = nsd_first_int($row, ['location_id', 'stock_location_id'], 0);
    $locationName = trim((string)($row['location_name'] ?? ''));

    if ($locationName === '') {
        $fallbackLocations = [
            1 => 'ফ্যাক্টরি',
            2 => 'শোরুম-১',
            3 => 'শোরুম-২',
            4 => 'লকার'
        ];

        $locationName = $fallbackLocations[$locationId] ?? 'অজানা';
    }

    $paymentMethodId = nsd_first_int($row, ['payment_method_id', 'payment_id'], 0);
    $paymentMethodName = trim((string)($row['payment_method_name_db'] ?? ''));

    if ($paymentMethodName === '') {
        $paymentMap = [
            1 => 'নগদ (CASH)',
            2 => 'ব্যাংক',
            3 => 'বিকাশ/নগদ',
            4 => 'কার্ড',
            5 => 'চেক'
        ];

        $paymentMethodName = $paymentMethodId > 0 ? ($paymentMap[$paymentMethodId] ?? '—') : '—';
    }

    $statusText = 'available';
    if ($availablePcs <= 0 && $availableWeight <= 0) {
        $statusText = 'finished';
    } elseif ($availablePcs < $totalPcs || $availableWeight < $totalWeight) {
        $statusText = 'partial';
    }

    $data = [
        'id' => (int)$row['main_stock_id'],
        'stock_id' => (int)$row['main_stock_id'],

        'stock_date' => $stockDate,
        'invoice_no' => (string)nsd_first_value($row, ['invoice_no', 'invoice', 'voucher_no'], ''),

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

        'karat' => $karat,
        'purity' => $karat,
        'purity_percent' => nsd_safe_num($purityPercent, 3),

        'total_pcs' => $totalPcs,
        'available_pcs' => $availablePcs,
        'used_pcs' => $usedPcs,

        'total_weight_gram' => nsd_safe_num($totalWeight),
        'total_weight_vori' => nsd_vori_text($totalWeight),

        'pure_weight_gram' => nsd_safe_num($pureWeight),
        'pure_weight' => nsd_safe_num($pureWeight),

        'available_weight_gram' => nsd_safe_num($availableWeight),
        'available_weight_vori' => nsd_vori_text($availableWeight),

        'used_weight_gram' => nsd_safe_num($usedWeight),
        'used_weight_vori' => nsd_vori_text($usedWeight),

        'avg_weight_per_piece' => $avgWeight,

        'rate_per_vori' => $ratePerVori,
        'total_amount' => $totalAmount,

        'location_id' => $locationId,
        'stock_location_id' => $locationId,
        'location_name' => $locationName,
        'location_type' => (string)($row['location_type'] ?? ''),

        'supplier_id' => nsd_first_int($row, ['supplier_id', 'party_id'], 0) ?: null,
        'supplier_name' => (string)($row['supplier_name'] ?? ''),
        'supplier_phone' => (string)($row['supplier_phone'] ?? ''),
        'supplier_address' => (string)($row['supplier_address'] ?? ''),

        'payment_method_id' => $paymentMethodId ?: null,
        'payment_method_name' => $paymentMethodName,

        'cash_account_id' => nsd_first_int($row, ['cash_account_id', 'account_id'], 0) ?: null,
        'cash_account_name' => (string)($row['cash_account_name'] ?? ''),
        'cash_account_balance' => isset($row['cash_account_balance']) ? (float)$row['cash_account_balance'] : null,

        'stock_status' => $statusText,
        'is_zero_weight' => $totalWeight <= 0 ? 1 : 0,

        'note' => (string)nsd_first_value($row, ['note', 'remarks'], ''),

        'created_by' => nsd_first_int($row, ['created_by', 'user_id'], 0) ?: null,
        'created_by_name' => (string)($row['created_by_name'] ?? ''),
        'created_at' => (string)nsd_first_value($row, ['created_at'], ''),
        'updated_at' => (string)nsd_first_value($row, ['updated_at'], '')
    ];

    nsd_out(200, true, 'স্টক বিস্তারিত লোড হয়েছে', $data);

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