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

ini_set('display_errors', 0);
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, 'message' => 'লগইন প্রয়োজন'], JSON_UNESCAPED_UNICODE);
    exit;
}

function send_json($arr, $code = 200)
{
    http_response_code($code);

    if (function_exists('jsonResponse')) {
        jsonResponse($arr);
        exit;
    }

    echo json_encode($arr, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    send_json(['success' => 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, '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 safe_num($v)
{
    return round((float)$v, 3);
}

function classify_location($id, $name, $nameBn, $type)
{
    $id = (int)$id;
    $txt = mb_strtolower(trim($name . ' ' . $nameBn . ' ' . $type), 'UTF-8');

    if ($id === 4 || mb_strpos($txt, 'locker') !== false || mb_strpos($txt, 'লকার') !== false) {
        return 'locker';
    }

    if ($id === 2 || $id === 3 || mb_strpos($txt, 'showroom') !== false || mb_strpos($txt, 'শোরুম') !== false) {
        return 'showroom';
    }

    if ($id === 1 || mb_strpos($txt, 'factory') !== false || mb_strpos($txt, 'কারখানা') !== false) {
        return 'factory';
    }

    return 'other';
}

try {
    if (!table_exists($pdo, 'stock_locations')) {
        send_json([
            'success' => true,
            'message' => 'stock_locations table পাওয়া যায়নি',
            'locations' => [],
            'pending_count' => 0,
            'summary' => [
                'total_items' => 0,
                'total_weight' => 0,
                'showroom_items' => 0,
                'showroom_weight' => 0,
                'locker_items' => 0,
                'locker_weight' => 0,
                'factory_items' => 0,
                'factory_weight' => 0,
                'zero_weight_items' => 0,
                'pending_count' => 0
            ]
        ]);
    }

    $locCols = get_columns($pdo, 'stock_locations');
    $bsCols  = get_columns($pdo, 'barcode_stock');

    $hasBarcodeStock = table_exists($pdo, 'barcode_stock') && !empty($bsCols);

    $locNameCol = pick_col($locCols, ['name', 'location_name', 'title']);
    $locNameBnCol = pick_col($locCols, ['name_bn', 'location_name_bn', 'title_bn']);
    $locTypeCol = pick_col($locCols, ['type', 'location_type']);
    $locActiveCol = pick_col($locCols, ['is_active', 'active']);
    $locSortCol = pick_col($locCols, ['sort_order', 'sort', 'position']);

    $whereLoc = " WHERE 1=1 ";
    if ($locActiveCol) {
        $whereLoc .= " AND (sl." . qi($locActiveCol) . " IS NULL OR sl." . qi($locActiveCol) . " = 1) ";
    }

    $orderLoc = $locSortCol
        ? " ORDER BY sl." . qi($locSortCol) . " ASC, sl.`id` ASC "
        : " ORDER BY sl.`id` ASC ";

    $locSelect = [
        "sl.`id` AS id",
        $locNameCol ? "sl." . qi($locNameCol) . " AS name" : "'' AS name",
        $locNameBnCol ? "sl." . qi($locNameBnCol) . " AS name_bn" : "'' AS name_bn",
        $locTypeCol ? "sl." . qi($locTypeCol) . " AS type" : "'' AS type"
    ];

    $locSql = "SELECT " . implode(", ", $locSelect) . " FROM `stock_locations` sl {$whereLoc} {$orderLoc}";
    $locRows = $pdo->query($locSql)->fetchAll(PDO::FETCH_ASSOC);

    $stockByLocation = [];
    $zeroByLocation = [];

    if ($hasBarcodeStock) {
        $bsIdCol = pick_col($bsCols, ['id', 'barcode_stock_id', 'barcode_id']);
        $bsLocCol = pick_col($bsCols, ['location_id', 'stock_location_id']);
        $bsWeightCol = pick_col($bsCols, [
            'gross_weight_gram',
            'gross_weight',
            'weight_gram',
            'total_weight_gram',
            'net_weight',
            'weight'
        ]);

        if ($bsIdCol && $bsLocCol) {
            $bsWhere = [];
            $bsWhere[] = "1=1";

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

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

            if (isset($bsCols['status'])) {
                $bsWhere[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) IN ('', 'in_stock', 'stock', 'available', 'ready')";
                $bsWhere[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) NOT IN ('sold','sell','sold_out','deleted','cancelled','canceled','void','trash','trashed')";
            }

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

            $weightExpr = $bsWeightCol ? "COALESCE(bs." . qi($bsWeightCol) . ",0)" : "0";

            $stockSql = "
                SELECT
                    bs." . qi($bsLocCol) . " AS location_id,
                    COUNT(bs." . qi($bsIdCol) . ") AS item_count,
                    COALESCE(SUM({$weightExpr}),0) AS gross_gram,
                    SUM(CASE WHEN {$weightExpr} <= 0 THEN 1 ELSE 0 END) AS zero_weight_count
                FROM `barcode_stock` bs
                WHERE " . implode(" AND ", $bsWhere) . "
                GROUP BY bs." . qi($bsLocCol) . "
            ";

            $st = $pdo->query($stockSql);
            $stockRows = $st->fetchAll(PDO::FETCH_ASSOC);

            foreach ($stockRows as $r) {
                $lid = (int)($r['location_id'] ?? 0);
                $stockByLocation[$lid] = [
                    'item_count' => (int)($r['item_count'] ?? 0),
                    'gross_gram' => safe_num($r['gross_gram'] ?? 0),
                    'zero_weight_count' => (int)($r['zero_weight_count'] ?? 0)
                ];
            }
        }
    }

    $locations = [];
    $summary = [
        'total_items' => 0,
        'total_weight' => 0,
        'showroom_items' => 0,
        'showroom_weight' => 0,
        'locker_items' => 0,
        'locker_weight' => 0,
        'factory_items' => 0,
        'factory_weight' => 0,
        'other_items' => 0,
        'other_weight' => 0,
        'zero_weight_items' => 0,
        'pending_count' => 0,
        'confirmed_count' => 0,
        'rejected_count' => 0,
        'total_transfer_count' => 0
    ];

    foreach ($locRows as $loc) {
        $id = (int)($loc['id'] ?? 0);
        $name = (string)($loc['name'] ?? '');
        $nameBn = (string)($loc['name_bn'] ?? '');
        $type = (string)($loc['type'] ?? '');

        $itemCount = isset($stockByLocation[$id]) ? (int)$stockByLocation[$id]['item_count'] : 0;
        $grossGram = isset($stockByLocation[$id]) ? (float)$stockByLocation[$id]['gross_gram'] : 0;
        $zeroCount = isset($stockByLocation[$id]) ? (int)$stockByLocation[$id]['zero_weight_count'] : 0;

        $group = classify_location($id, $name, $nameBn, $type);

        $displayName = $nameBn !== '' ? $nameBn : $name;
        if ($displayName === '') $displayName = 'Location-' . $id;

        $locations[] = [
            'id' => $id,
            'name' => $name,
            'name_bn' => $nameBn,
            'display_name' => $displayName,
            'type' => $type,
            'group' => $group,
            'item_count' => $itemCount,
            'gross_gram' => safe_num($grossGram),
            'gross_weight_gram' => safe_num($grossGram),
            'zero_weight_count' => $zeroCount
        ];

        $summary['total_items'] += $itemCount;
        $summary['total_weight'] = safe_num($summary['total_weight'] + $grossGram);
        $summary['zero_weight_items'] += $zeroCount;

        if ($group === 'showroom') {
            $summary['showroom_items'] += $itemCount;
            $summary['showroom_weight'] = safe_num($summary['showroom_weight'] + $grossGram);
        } elseif ($group === 'locker') {
            $summary['locker_items'] += $itemCount;
            $summary['locker_weight'] = safe_num($summary['locker_weight'] + $grossGram);
        } elseif ($group === 'factory') {
            $summary['factory_items'] += $itemCount;
            $summary['factory_weight'] = safe_num($summary['factory_weight'] + $grossGram);
        } else {
            $summary['other_items'] += $itemCount;
            $summary['other_weight'] = safe_num($summary['other_weight'] + $grossGram);
        }
    }

    $transferSummary = [
        'pending_count' => 0,
        'confirmed_count' => 0,
        'rejected_count' => 0,
        'total_transfer_count' => 0,
        'today_pending_count' => 0,
        'today_confirmed_count' => 0,
        'today_rejected_count' => 0
    ];

    if (table_exists($pdo, 'stock_transfers')) {
        $trCols = get_columns($pdo, 'stock_transfers');

        if (isset($trCols['status'])) {
            $trSql = "
                SELECT
                    LOWER(TRIM(COALESCE(`status`, ''))) AS st,
                    COUNT(*) AS cnt
                FROM `stock_transfers`
                GROUP BY LOWER(TRIM(COALESCE(`status`, '')))
            ";

            $rows = $pdo->query($trSql)->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rows as $r) {
                $st = (string)($r['st'] ?? '');
                $cnt = (int)($r['cnt'] ?? 0);

                $transferSummary['total_transfer_count'] += $cnt;

                if ($st === 'pending') {
                    $transferSummary['pending_count'] += $cnt;
                } elseif ($st === 'confirmed' || $st === 'complete' || $st === 'completed') {
                    $transferSummary['confirmed_count'] += $cnt;
                } elseif ($st === 'rejected' || $st === 'reject' || $st === 'cancelled' || $st === 'canceled') {
                    $transferSummary['rejected_count'] += $cnt;
                }
            }

            $dateCol = pick_col($trCols, ['sent_at', 'created_at', 'transfer_date']);
            if ($dateCol) {
                $todaySql = "
                    SELECT
                        LOWER(TRIM(COALESCE(`status`, ''))) AS st,
                        COUNT(*) AS cnt
                    FROM `stock_transfers`
                    WHERE DATE(" . qi($dateCol) . ") = CURDATE()
                    GROUP BY LOWER(TRIM(COALESCE(`status`, '')))
                ";

                $todayRows = $pdo->query($todaySql)->fetchAll(PDO::FETCH_ASSOC);

                foreach ($todayRows as $r) {
                    $st = (string)($r['st'] ?? '');
                    $cnt = (int)($r['cnt'] ?? 0);

                    if ($st === 'pending') {
                        $transferSummary['today_pending_count'] += $cnt;
                    } elseif ($st === 'confirmed' || $st === 'complete' || $st === 'completed') {
                        $transferSummary['today_confirmed_count'] += $cnt;
                    } elseif ($st === 'rejected' || $st === 'reject' || $st === 'cancelled' || $st === 'canceled') {
                        $transferSummary['today_rejected_count'] += $cnt;
                    }
                }
            }
        }
    }

    $summary['pending_count'] = $transferSummary['pending_count'];
    $summary['confirmed_count'] = $transferSummary['confirmed_count'];
    $summary['rejected_count'] = $transferSummary['rejected_count'];
    $summary['total_transfer_count'] = $transferSummary['total_transfer_count'];

    send_json([
        'success' => true,
        'status' => true,
        'message' => 'স্টক ট্রান্সফার সামারি লোড হয়েছে',
        'read_only' => true,

        'locations' => $locations,

        'pending_count' => $transferSummary['pending_count'],
        'confirmed_count' => $transferSummary['confirmed_count'],
        'rejected_count' => $transferSummary['rejected_count'],
        'total_transfer_count' => $transferSummary['total_transfer_count'],

        'transfer_summary' => $transferSummary,
        'summary' => $summary,

        'data' => [
            'locations' => $locations,
            'pending_count' => $transferSummary['pending_count'],
            'transfer_summary' => $transferSummary,
            'summary' => $summary
        ]
    ]);

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