<?php
/*
  cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/new_stock/get_stock_list.php
  Purpose: New Stock / Old Purchase Stock 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 (session_status() === PHP_SESSION_NONE) {
    session_start();
}

function ns_out($code, $ok, $msg, $payload = []) {
    http_response_code($code);

    $summary = $payload['summary'] ?? [];
    $stocks  = $payload['stocks'] ?? [];
    $meta    = $payload['meta'] ?? [];

    echo json_encode([
        'success' => (bool)$ok,
        'status'  => (bool)$ok,
        'message' => $msg,

        'summary' => $summary,
        'stocks'  => $stocks,
        'items'   => $stocks,
        'data'    => [
            'summary' => $summary,
            'stocks'  => $stocks,
            'items'   => $stocks,
            'meta'    => $meta
        ],
        'meta' => $meta
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

    exit;
}

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

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

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

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

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

function ns_qi($name) {
    return '`' . str_replace('`', '', ns_clean($name)) . '`';
}

function ns_table_exists(PDO $pdo, $table) {
    $table = ns_clean($table);
    if ($table === '') return false;

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

function ns_get_columns(PDO $pdo, $table) {
    $table = ns_clean($table);
    if ($table === '' || !ns_table_exists($pdo, $table)) return [];

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

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

    return $cols;
}

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

function ns_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 ns_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 ns_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 ns_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 ns_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 ns_vori_text($gram) {
    $p = ns_gram_to_parts($gram);

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

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

function ns_valid_date($d) {
    return is_string($d) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $d);
}

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

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

    $parts[] = $fallback;

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

try {
    if (!ns_table_exists($pdo, 'new_stock')) {
        ns_out(200, true, 'new_stock table পাওয়া যায়নি', [
            'summary' => [
                'total_entries' => 0,
                'total_pcs' => 0,
                'total_weight_gram' => 0,
                'total_weight_vori' => ns_vori_text(0),
                'total_available_pcs' => 0,
                'total_available_weight_gram' => 0,
                'total_available_weight_vori' => ns_vori_text(0),
                'total_used_pcs' => 0,
                'total_used_weight_gram' => 0,
                'zero_weight_entries' => 0
            ],
            'stocks' => [],
            'meta' => []
        ]);
    }

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

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

    $dateCol = ns_pick_col($nsCols, ['stock_date', 'purchase_date', 'date', 'created_at']);
    $deletedCol = ns_pick_col($nsCols, ['deleted_at']);
    $isDeletedCol = ns_pick_col($nsCols, ['is_deleted']);

    $from = trim((string)($_GET['from_date'] ?? $_GET['date_from'] ?? ''));
    $to   = trim((string)($_GET['to_date'] ?? $_GET['date_to'] ?? ''));

    if ($from !== '' && !ns_valid_date($from)) $from = '';
    if ($to !== '' && !ns_valid_date($to)) $to = '';

    $search = trim((string)($_GET['search'] ?? $_GET['q'] ?? ''));
    $category = trim((string)($_GET['category'] ?? ''));
    $karat = trim((string)($_GET['karat'] ?? ''));
    $supplierId = (int)($_GET['supplier_id'] ?? 0);
    $locationId = (int)($_GET['location_id'] ?? 0);
    $stockStatus = trim((string)($_GET['status'] ?? ''));

    $limit = (int)($_GET['limit'] ?? 50);
    $offset = (int)($_GET['offset'] ?? 0);

    if ($limit <= 0) $limit = 50;
    if ($limit > 500) $limit = 500;
    if ($offset < 0) $offset = 0;

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

    $where[] = "1=1";

    if ($deletedCol) {
        $where[] = "ns." . ns_qi($deletedCol) . " IS NULL";
    }

    if ($isDeletedCol) {
        $where[] = "(ns." . ns_qi($isDeletedCol) . " IS NULL OR ns." . ns_qi($isDeletedCol) . " = 0)";
    }

    if ($dateCol && $from !== '') {
        $where[] = "DATE(ns." . ns_qi($dateCol) . ") >= ?";
        $params[] = $from;
    }

    if ($dateCol && $to !== '') {
        $where[] = "DATE(ns." . ns_qi($dateCol) . ") <= ?";
        $params[] = $to;
    }

    if ($category !== '' && isset($nsCols['category'])) {
        $where[] = "ns.`category` = ?";
        $params[] = $category;
    }

    if ($karat !== '') {
        $karatCol = ns_pick_col($nsCols, ['karat', 'purity']);
        if ($karatCol) {
            $where[] = "ns." . ns_qi($karatCol) . " = ?";
            $params[] = $karat;
        }
    }

    if ($supplierId > 0 && isset($nsCols['supplier_id'])) {
        $where[] = "ns.`supplier_id` = ?";
        $params[] = $supplierId;
    }

    if ($locationId > 0) {
        $locCol = ns_pick_col($nsCols, ['location_id', 'stock_location_id']);
        if ($locCol) {
            $where[] = "ns." . ns_qi($locCol) . " = ?";
            $params[] = $locationId;
        }
    }

    if ($stockStatus !== '' && $stockStatus !== 'all') {
        if ($stockStatus === 'available') {
            $availablePcsCol = ns_pick_col($nsCols, ['available_pcs', 'balance_pcs']);
            $availableWeightCol = ns_pick_col($nsCols, ['available_weight_gram', 'balance_weight_gram']);
            if ($availablePcsCol) {
                $where[] = "COALESCE(ns." . ns_qi($availablePcsCol) . ",0) > 0";
            } elseif ($availableWeightCol) {
                $where[] = "COALESCE(ns." . ns_qi($availableWeightCol) . ",0) > 0";
            }
        } elseif ($stockStatus === 'finished' || $stockStatus === 'used') {
            $availablePcsCol = ns_pick_col($nsCols, ['available_pcs', 'balance_pcs']);
            $availableWeightCol = ns_pick_col($nsCols, ['available_weight_gram', 'balance_weight_gram']);
            if ($availablePcsCol) {
                $where[] = "COALESCE(ns." . ns_qi($availablePcsCol) . ",0) <= 0";
            } elseif ($availableWeightCol) {
                $where[] = "COALESCE(ns." . ns_qi($availableWeightCol) . ",0) <= 0";
            }
        } elseif (isset($nsCols['status'])) {
            $where[] = "LOWER(TRIM(COALESCE(ns.`status`,''))) = ?";
            $params[] = mb_strtolower($stockStatus, 'UTF-8');
        }
    }

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

        foreach ([
            'invoice_no',
            'category',
            'product_name',
            'item_name',
            'name',
            'karat',
            'purity',
            'note',
            'remarks',
            'stock_code',
            'batch_no'
        ] as $c) {
            if (isset($nsCols[$c])) {
                $searchParts[] = "ns." . ns_qi($c) . " LIKE ?";
                $params[] = '%' . $search . '%';
            }
        }

        if (ns_table_exists($pdo, 'parties')) {
            $partyCols = ns_get_columns($pdo, 'parties');
            foreach (['name', 'party_name', 'phone', 'mobile'] as $c) {
                if (isset($partyCols[$c])) {
                    $searchParts[] = "p." . ns_qi($c) . " LIKE ?";
                    $params[] = '%' . $search . '%';
                }
            }
        }

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

    $whereSql = "WHERE " . implode(" AND ", $where);

    $totalPcsCol = ns_pick_col($nsCols, ['total_pcs', 'pcs', 'quantity', 'qty']);
    $totalWeightCol = ns_pick_col($nsCols, ['total_weight_gram', 'gross_weight_gram', 'weight_gram', 'weight']);
    $availablePcsCol = ns_pick_col($nsCols, ['available_pcs', 'balance_pcs']);
    $availableWeightCol = ns_pick_col($nsCols, ['available_weight_gram', 'balance_weight_gram']);

    $totalPcsExpr = $totalPcsCol ? "COALESCE(ns." . ns_qi($totalPcsCol) . ",0)" : "0";
    $totalWeightExpr = $totalWeightCol ? "COALESCE(ns." . ns_qi($totalWeightCol) . ",0)" : "0";
    $availablePcsExpr = $availablePcsCol ? "COALESCE(ns." . ns_qi($availablePcsCol) . ",0)" : $totalPcsExpr;
    $availableWeightExpr = $availableWeightCol ? "COALESCE(ns." . ns_qi($availableWeightCol) . ",0)" : $totalWeightExpr;

    $joins = [];

    $partyCols = [];
    if (ns_table_exists($pdo, 'parties') && isset($nsCols['supplier_id'])) {
        $partyCols = ns_get_columns($pdo, 'parties');
        if (isset($partyCols['id'])) {
            $joins[] = "LEFT JOIN `parties` p ON p.`id` = ns.`supplier_id`";
        }
    }

    $locCols = [];
    $locCol = ns_pick_col($nsCols, ['location_id', 'stock_location_id']);
    if (ns_table_exists($pdo, 'stock_locations') && $locCol) {
        $locCols = ns_get_columns($pdo, 'stock_locations');
        if (isset($locCols['id'])) {
            $joins[] = "LEFT JOIN `stock_locations` sl ON sl.`id` = ns." . ns_qi($locCol);
        }
    }

    $joinSql = implode("\n", $joins);

    $sumSql = "
        SELECT
            COUNT(*) AS total_entries,
            COALESCE(SUM({$totalPcsExpr}),0) AS total_pcs,
            COALESCE(SUM({$totalWeightExpr}),0) AS total_weight_gram,
            COALESCE(SUM({$availablePcsExpr}),0) AS total_available_pcs,
            COALESCE(SUM({$availableWeightExpr}),0) AS total_available_weight_gram,
            SUM(CASE WHEN {$totalWeightExpr} <= 0 THEN 1 ELSE 0 END) AS zero_weight_entries
        FROM `new_stock` ns
        {$joinSql}
        {$whereSql}
    ";

    $sum = $pdo->prepare($sumSql);
    $sum->execute($params);
    $summaryRow = $sum->fetch(PDO::FETCH_ASSOC) ?: [];

    $select = [];

    $select[] = "ns." . ns_qi($idCol) . " AS id";
    $select[] = $dateCol ? "ns." . ns_qi($dateCol) . " AS stock_date" : "'' AS stock_date";
    $select[] = isset($nsCols['supplier_id']) ? "ns.`supplier_id` AS supplier_id" : "NULL AS supplier_id";
    $select[] = ns_coalesce_select('ns', $nsCols, ['invoice_no', 'invoice', 'voucher_no'], 'invoice_no', "''");
    $select[] = ns_coalesce_select('ns', $nsCols, ['category', 'stock_type'], 'category', "''");
    $select[] = ns_coalesce_select('ns', $nsCols, ['product_name', 'item_name', 'name'], 'product_name', "'—'");
    $select[] = ns_coalesce_select('ns', $nsCols, ['karat', 'purity'], 'karat', "'22K'");
    $select[] = "{$totalPcsExpr} AS total_pcs";
    $select[] = "{$totalWeightExpr} AS total_weight_gram";
    $select[] = "{$availablePcsExpr} AS available_pcs";
    $select[] = "{$availableWeightExpr} AS available_weight_gram";
    $select[] = $locCol ? "ns." . ns_qi($locCol) . " AS location_id" : "0 AS location_id";
    $select[] = ns_coalesce_select('ns', $nsCols, ['note', 'remarks'], 'note', "''");
    $select[] = isset($nsCols['created_at']) ? "ns.`created_at` AS created_at" : "'' AS created_at";
    $select[] = isset($nsCols['updated_at']) ? "ns.`updated_at` AS updated_at" : "'' AS updated_at";

    if (!empty($partyCols) && isset($partyCols['id'])) {
        $select[] = ns_coalesce_select('p', $partyCols, ['name', 'party_name', 'customer_name'], 'supplier_name', "''");
        $select[] = ns_coalesce_select('p', $partyCols, ['phone', 'mobile'], 'supplier_phone', "''");
    } else {
        $select[] = "'' AS supplier_name";
        $select[] = "'' AS supplier_phone";
    }

    if (!empty($locCols) && isset($locCols['id'])) {
        $select[] = ns_coalesce_select('sl', $locCols, ['name_bn', 'name', 'location_name_bn', 'location_name'], 'location_name', "''");
        $select[] = ns_coalesce_select('sl', $locCols, ['type', 'location_type'], 'location_type', "''");
    } else {
        $select[] = "'' AS location_name";
        $select[] = "'' AS location_type";
    }

    $orderCol = $dateCol ?: $idCol;

    $listSql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM `new_stock` ns
        {$joinSql}
        {$whereSql}
        ORDER BY ns." . ns_qi($orderCol) . " DESC, ns." . ns_qi($idCol) . " DESC
        LIMIT " . (int)$limit . " OFFSET " . (int)$offset . "
    ";

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

    $stocks = [];

    foreach ($rows as $r) {
        $totalWeight = (float)($r['total_weight_gram'] ?? 0);
        $availableWeight = (float)($r['available_weight_gram'] ?? 0);
        $totalPcs = (int)($r['total_pcs'] ?? 0);
        $availablePcs = (int)($r['available_pcs'] ?? 0);

        $usedPcs = max(0, $totalPcs - $availablePcs);
        $usedWeight = max(0, $totalWeight - $availableWeight);

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

        $stocks[] = [
            'id' => (int)($r['id'] ?? 0),
            'stock_id' => (int)($r['id'] ?? 0),

            'stock_date' => (string)($r['stock_date'] ?? ''),
            'supplier_id' => ($r['supplier_id'] !== null ? (int)$r['supplier_id'] : null),
            'supplier_name' => (string)($r['supplier_name'] ?? ''),
            'supplier_phone' => (string)($r['supplier_phone'] ?? ''),

            'invoice_no' => (string)($r['invoice_no'] ?? ''),
            'category' => (string)($r['category'] ?? ''),
            'product_name' => (string)($r['product_name'] ?? ''),
            'item_name' => (string)($r['product_name'] ?? ''),
            'karat' => (string)($r['karat'] ?? ''),

            'total_pcs' => $totalPcs,
            'total_weight_gram' => ns_safe_num($totalWeight),
            'total_weight_vori' => ns_vori_text($totalWeight),

            'available_pcs' => $availablePcs,
            'available_weight_gram' => ns_safe_num($availableWeight),
            'available_weight_vori' => ns_vori_text($availableWeight),

            'used_pcs' => $usedPcs,
            'used_weight_gram' => ns_safe_num($usedWeight),
            'used_weight_vori' => ns_vori_text($usedWeight),

            'location_id' => (int)($r['location_id'] ?? 0),
            'location_name' => (string)($r['location_name'] ?? ''),
            'location_type' => (string)($r['location_type'] ?? ''),

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

            'note' => (string)($r['note'] ?? ''),
            'created_at' => (string)($r['created_at'] ?? ''),
            'updated_at' => (string)($r['updated_at'] ?? '')
        ];
    }

    $totalWeight = (float)($summaryRow['total_weight_gram'] ?? 0);
    $availableWeight = (float)($summaryRow['total_available_weight_gram'] ?? 0);
    $totalPcs = (int)($summaryRow['total_pcs'] ?? 0);
    $availablePcs = (int)($summaryRow['total_available_pcs'] ?? 0);

    $summary = [
        'total_entries' => (int)($summaryRow['total_entries'] ?? 0),

        'total_pcs' => $totalPcs,
        'total_weight_gram' => ns_safe_num($totalWeight),
        'total_weight_vori' => ns_vori_text($totalWeight),

        'total_available_pcs' => $availablePcs,
        'total_available_weight_gram' => ns_safe_num($availableWeight),
        'total_available_weight_vori' => ns_vori_text($availableWeight),

        'total_used_pcs' => max(0, $totalPcs - $availablePcs),
        'total_used_weight_gram' => ns_safe_num(max(0, $totalWeight - $availableWeight)),
        'total_used_weight_vori' => ns_vori_text(max(0, $totalWeight - $availableWeight)),

        'zero_weight_entries' => (int)($summaryRow['zero_weight_entries'] ?? 0)
    ];

    ns_out(200, true, 'নতুন স্টক তালিকা লোড হয়েছে', [
        'summary' => $summary,
        'stocks' => $stocks,
        'meta' => [
            'from_date' => $from,
            'to_date' => $to,
            'search' => $search,
            'category' => $category,
            'karat' => $karat,
            'supplier_id' => $supplierId,
            'location_id' => $locationId,
            'status' => $stockStatus,
            'limit' => $limit,
            'offset' => $offset,
            'returned' => count($stocks)
        ]
    ]);

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