<?php
/*
  cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/new_stock/delete_stock.php
  Purpose: New Stock / Old Purchase Stock safe soft delete
  Data change: YES

  Safety:
  - NO ALTER / CREATE / DROP
  - Soft delete only: deleted_at / is_deleted থাকলে সেট করবে
  - Used pcs/weight থাকলে delete block
  - barcode_stock এ transfer/barcode তৈরি থাকলে delete block
  - cash_transactions থাকলে reverse entry করবে
  - audit_log থাকলে entry করবে
  - transaction + rollback
*/

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
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (function_exists('require_login')) {
    require_login();
} elseif (function_exists('is_logged_in') && !is_logged_in()) {
    nsd_out(401, false, 'লগইন প্রয়োজন');
} elseif (empty($_SESSION['user_id']) && empty($_SESSION['user']['id'])) {
    nsd_out(401, false, 'লগইন প্রয়োজন');
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    nsd_out(405, false, 'শুধু POST অনুমোদিত');
}

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($arr, $keys, $default = '') {
    foreach ($keys as $k) {
        if (array_key_exists($k, $arr) && $arr[$k] !== null && $arr[$k] !== '') {
            return $arr[$k];
        }
    }
    return $default;
}

function nsd_first_int($arr, $keys, $default = 0) {
    foreach ($keys as $k) {
        if (array_key_exists($k, $arr) && $arr[$k] !== null && $arr[$k] !== '') {
            return (int)$arr[$k];
        }
    }
    return (int)$default;
}

function nsd_first_float($arr, $keys, $default = 0) {
    foreach ($keys as $k) {
        if (array_key_exists($k, $arr) && $arr[$k] !== null && $arr[$k] !== '') {
            return (float)$arr[$k];
        }
    }
    return (float)$default;
}

function nsd_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 nsd_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 nsd_json_text($value) {
    return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function nsd_cash_account_col($cols) {
    if (isset($cols['cash_account_id'])) return 'cash_account_id';
    if (isset($cols['account_id'])) return 'account_id';
    return null;
}

function nsd_cash_direction($row) {
    foreach (['direction', 'transaction_direction', 'transaction_type'] as $c) {
        if (isset($row[$c]) && trim((string)$row[$c]) !== '') {
            $v = strtoupper(trim((string)$row[$c]));
            if ($v === 'OUT' || $v === 'CASH_OUT') return 'OUT';
            if ($v === 'IN' || $v === 'CASH_IN') return 'IN';
        }
    }

    if (isset($row['type'])) {
        $v = strtolower(trim((string)$row['type']));
        if ($v === 'cash_out' || $v === 'out') return 'OUT';
        if ($v === 'cash_in' || $v === 'in') return 'IN';
    }

    return 'OUT';
}

function nsd_update_cash_balance(PDO $pdo, $cashAccountId, $direction, $amount) {
    if ($cashAccountId <= 0 || $amount <= 0) return;
    if (!nsd_table_exists($pdo, 'cash_accounts')) return;

    $cols = nsd_get_columns($pdo, 'cash_accounts');
    $balanceCol = nsd_pick_col($cols, ['current_balance', 'balance']);

    if (!$balanceCol || !isset($cols['id'])) return;

    $sign = strtoupper($direction) === 'IN' ? 1 : -1;

    $st = $pdo->prepare("
        UPDATE `cash_accounts`
        SET " . nsd_qi($balanceCol) . " = COALESCE(" . nsd_qi($balanceCol) . ",0) + ?
        WHERE `id` = ?
    ");
    $st->execute([$sign * (float)$amount, $cashAccountId]);
}

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

        $cols = nsd_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[] = nsd_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', nsd_json_text($oldValues));
        }

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

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

        if (isset($cols['changed_at'])) {
            $fields[] = nsd_qi('changed_at');
            $marks[] = 'NOW()';
        } elseif (isset($cols['created_at'])) {
            $fields[] = nsd_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 fail হলেও main operation fail হবে না
    }
}

function nsd_find_referenced_barcode_count(PDO $pdo, $stockId) {
    if (!nsd_table_exists($pdo, 'barcode_stock')) return 0;

    $cols = nsd_get_columns($pdo, 'barcode_stock');
    if (empty($cols)) return 0;

    $refCols = [
        'new_stock_id',
        'source_stock_id',
        'source_new_stock_id',
        'purchase_stock_id',
        'source_purchase_id',
        'purchase_id',
        'stock_id'
    ];

    $refWhere = [];

    foreach ($refCols as $c) {
        if (isset($cols[$c])) {
            $refWhere[] = "`{$c}` = ?";
        }
    }

    if (empty($refWhere)) return 0;

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

    $where[] = "(" . implode(" OR ", $refWhere) . ")";

    foreach ($refWhere as $x) {
        $params[] = $stockId;
    }

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

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

    if (isset($cols['status'])) {
        $where[] = "LOWER(TRIM(COALESCE(`status`,''))) NOT IN ('deleted','cancelled','canceled','void','trash','trashed')";
    }

    $sql = "SELECT COUNT(*) FROM `barcode_stock` WHERE " . implode(" AND ", $where);
    $st = $pdo->prepare($sql);
    $st->execute($params);

    return (int)$st->fetchColumn();
}

function nsd_insert_cash_reverse(PDO $pdo, $oldCash, $stockId, $itemName, $userId) {
    if (!nsd_table_exists($pdo, 'cash_transactions')) {
        return 0;
    }

    $cols = nsd_get_columns($pdo, 'cash_transactions');
    if (empty($cols)) return 0;

    $accountCol = nsd_cash_account_col($cols);
    if (!$accountCol || !isset($cols['amount'])) return 0;

    $oldId = (int)($oldCash['id'] ?? 0);
    $cashAccountId = (int)($oldCash[$accountCol] ?? 0);
    $amount = (float)($oldCash['amount'] ?? 0);

    if ($oldId <= 0 || $cashAccountId <= 0 || $amount <= 0) return 0;

    $oldDirection = nsd_cash_direction($oldCash);
    $reverseDirection = $oldDirection === 'OUT' ? 'IN' : 'OUT';
    $reverseType = $reverseDirection === 'IN' ? 'cash_in' : 'cash_out';

    $note = 'স্টক ডিলিট - লেনদেন রিভার্স';
    $desc = 'স্টক ডিলিট: ' . $itemName;

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

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

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

    if (isset($cols['cash_account_id'])) {
        $add('cash_account_id', $cashAccountId);
    } elseif (isset($cols['account_id'])) {
        $add('account_id', $cashAccountId);
    }

    foreach (['category_id', 'transaction_type_id', 'payment_method_id'] as $copyCol) {
        if (isset($cols[$copyCol]) && array_key_exists($copyCol, $oldCash)) {
            $add($copyCol, $oldCash[$copyCol]);
        }
    }

    $add('direction', $reverseDirection);
    $add('transaction_direction', $reverseDirection);
    $add('transaction_type', $reverseDirection);
    $add('type', $reverseType);

    $add('amount', round($amount, 2));

    if (isset($cols['transaction_date'])) {
        $add('transaction_date', date('Y-m-d'));
    } elseif (isset($cols['date'])) {
        $add('date', date('Y-m-d'));
    }

    $add('note', $note);
    $add('purpose', $note);
    $add('description', $desc);
    $add('remarks', $desc);

    $add('reference_type', 'new_stock_delete');
    $add('ref_type', 'new_stock_delete');
    $add('source_type', 'new_stock_delete');

    $add('reference_id', $stockId);
    $add('ref_id', $stockId);
    $add('source_id', $stockId);

    $add('created_by', $userId);
    $add('user_id', $userId);

    if (isset($cols['is_reversal'])) {
        $add('is_reversal', 1);
    }

    if (isset($cols['reversed_transaction_id'])) {
        $add('reversed_transaction_id', $oldId);
    }

    if (isset($cols['is_deleted'])) {
        $add('is_deleted', 0);
    }

    $addRaw('created_at', 'NOW()');
    $addRaw('updated_at', 'NOW()');

    if (empty($fields)) {
        throw new RuntimeException('cash_transactions reverse insert করার মতো column পাওয়া যায়নি');
    }

    $sql = "INSERT INTO `cash_transactions` (" . implode(',', $fields) . ") VALUES (" . implode(',', $marks) . ")";
    $st = $pdo->prepare($sql);
    $st->execute($params);

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

    nsd_update_cash_balance($pdo, $cashAccountId, $reverseDirection, $amount);

    return $reverseId;
}

function nsd_reverse_cash_transactions(PDO $pdo, $stockId, $itemName, $userId) {
    if (!nsd_table_exists($pdo, 'cash_transactions')) {
        return [];
    }

    $cols = nsd_get_columns($pdo, 'cash_transactions');
    if (empty($cols) || !isset($cols['id']) || !isset($cols['amount'])) {
        return [];
    }

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

    if (isset($cols['reference_type']) && isset($cols['reference_id'])) {
        $where[] = "`reference_type` = 'new_stock'";
        $where[] = "`reference_id` = ?";
        $params[] = $stockId;
    } elseif (isset($cols['ref_type']) && isset($cols['ref_id'])) {
        $where[] = "`ref_type` = 'new_stock'";
        $where[] = "`ref_id` = ?";
        $params[] = $stockId;
    } elseif (isset($cols['source_type']) && isset($cols['source_id'])) {
        $where[] = "`source_type` = 'new_stock'";
        $where[] = "`source_id` = ?";
        $params[] = $stockId;
    } else {
        return [];
    }

    if (isset($cols['is_reversal'])) {
        $where[] = "COALESCE(`is_reversal`,0) = 0";
    }

    if (isset($cols['is_deleted'])) {
        $where[] = "COALESCE(`is_deleted`,0) = 0";
    }

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

    $sql = "
        SELECT *
        FROM `cash_transactions`
        WHERE " . implode(" AND ", $where) . "
        ORDER BY `id` DESC
        LIMIT 20
        FOR UPDATE
    ";

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

    $reversed = [];

    foreach ($rows as $oldCash) {
        $reverseId = nsd_insert_cash_reverse($pdo, $oldCash, $stockId, $itemName, $userId);

        if ($reverseId > 0) {
            $reversed[] = [
                'old_transaction_id' => (int)$oldCash['id'],
                'reverse_transaction_id' => $reverseId,
                'amount' => round((float)$oldCash['amount'], 2),
                'old_direction' => nsd_cash_direction($oldCash),
                'reverse_direction' => nsd_cash_direction([
                    'direction' => nsd_cash_direction($oldCash) === 'OUT' ? 'IN' : 'OUT'
                ])
            ];
        }
    }

    return $reversed;
}

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

    if (!is_array($raw)) {
        nsd_out(400, false, 'ভুল JSON data');
    }

    $userId = nsd_user_id();
    if ($userId <= 0) {
        nsd_out(401, false, 'Login session পাওয়া যায়নি');
    }

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

    $cols = nsd_get_columns($pdo, 'new_stock');
    $idCol = nsd_pick_col($cols, ['id', 'stock_id', 'new_stock_id']);

    if (!$idCol) {
        nsd_out(500, false, 'new_stock ID column পাওয়া যায়নি');
    }

    $id = nsd_first_int($raw, ['id', 'stock_id', 'new_stock_id'], 0);
    if ($id <= 0) {
        nsd_out(400, false, 'ID লাগবে');
    }

    $reason = trim((string)nsd_first_value($raw, ['reason', 'note', 'delete_note'], ''));
    if ($reason === '') {
        $reason = 'Stock soft deleted';
    }

    if (mb_strlen($reason, 'UTF-8') > 500) {
        nsd_out(400, false, 'Delete reason 500 অক্ষরের বেশি হতে পারবে না');
    }

    if (!isset($cols['deleted_at']) && !isset($cols['is_deleted'])) {
        nsd_out(500, false, 'Soft delete column নেই — deleted_at অথবা is_deleted দরকার');
    }

    $pdo->beginTransaction();

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

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

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

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

    $stockQ = $pdo->prepare("
        SELECT *
        FROM `new_stock`
        WHERE " . implode(" AND ", $where) . "
        LIMIT 1
        FOR UPDATE
    ");
    $stockQ->execute($params);
    $stock = $stockQ->fetch(PDO::FETCH_ASSOC);

    if (!$stock) {
        $pdo->rollBack();
        nsd_out(404, false, 'স্টক পাওয়া যায়নি অথবা আগেই delete করা');
    }

    $itemName = (string)nsd_first_value($stock, ['product_name', 'item_name', 'name', 'category'], 'স্টক');

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

    $totalWeight = nsd_first_float($stock, ['total_weight_gram', 'gross_weight_gram', 'weight_gram', 'weight'], 0);
    $availableWeight = nsd_first_float($stock, ['available_weight_gram', 'balance_weight_gram'], $totalWeight);
    $usedWeight = max(0, round($totalWeight - $availableWeight, 3));

    if ($usedPcs > 0 || $usedWeight > 0.0001) {
        $pdo->rollBack();
        nsd_out(400, false, 'ডিলিট সম্ভব নয় — এই stock থেকে কিছু অংশ ব্যবহৃত/transfer হয়েছে', [
            'id' => $id,
            'item_name' => $itemName,
            'total_pcs' => $totalPcs,
            'available_pcs' => $availablePcs,
            'used_pcs' => $usedPcs,
            'total_weight_gram' => round($totalWeight, 3),
            'available_weight_gram' => round($availableWeight, 3),
            'used_weight_gram' => round($usedWeight, 3)
        ]);
    }

    $barcodeCount = nsd_find_referenced_barcode_count($pdo, $id);
    if ($barcodeCount > 0) {
        $pdo->rollBack();
        nsd_out(400, false, 'ডিলিট সম্ভব নয় — এই stock থেকে barcode stock তৈরি হয়েছে', [
            'id' => $id,
            'item_name' => $itemName,
            'barcode_count' => $barcodeCount
        ]);
    }

    $set = [];
    $updParams = [];

    if (isset($cols['deleted_at'])) {
        $set[] = "`deleted_at` = NOW()";
    }

    if (isset($cols['is_deleted'])) {
        $set[] = "`is_deleted` = ?";
        $updParams[] = 1;
    }

    if (isset($cols['deleted_by'])) {
        $set[] = "`deleted_by` = ?";
        $updParams[] = $userId;
    }

    if (isset($cols['delete_note'])) {
        $set[] = "`delete_note` = ?";
        $updParams[] = $reason;
    }

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

    if (isset($cols['note'])) {
        $oldNote = trim((string)($stock['note'] ?? ''));
        $newNote = trim($oldNote . "\nDeleted: " . date('Y-m-d H:i:s') . " | " . $reason);
        $set[] = "`note` = ?";
        $updParams[] = $newNote;
    } elseif (isset($cols['remarks'])) {
        $oldNote = trim((string)($stock['remarks'] ?? ''));
        $newNote = trim($oldNote . "\nDeleted: " . date('Y-m-d H:i:s') . " | " . $reason);
        $set[] = "`remarks` = ?";
        $updParams[] = $newNote;
    }

    if (empty($set)) {
        throw new RuntimeException('Delete করার মতো column পাওয়া যায়নি');
    }

    $updParams[] = $id;

    $deleteSql = "
        UPDATE `new_stock`
        SET " . implode(", ", $set) . "
        WHERE " . nsd_qi($idCol) . " = ?
    ";

    $up = $pdo->prepare($deleteSql);
    $up->execute($updParams);

    if ($up->rowCount() !== 1) {
        throw new RuntimeException('Stock delete update failed');
    }

    $cashReversed = nsd_reverse_cash_transactions($pdo, $id, $itemName, $userId);

    if (!empty($cashReversed)) {
        if (function_exists('gs_recalc_cash_account')) {
            $accountIds = [];

            if (nsd_table_exists($pdo, 'cash_transactions')) {
                $ctCols = nsd_get_columns($pdo, 'cash_transactions');
                $accCol = nsd_cash_account_col($ctCols);

                if ($accCol && isset($ctCols['id'])) {
                    foreach ($cashReversed as $r) {
                        $oldId = (int)$r['old_transaction_id'];
                        $q = $pdo->prepare("SELECT " . nsd_qi($accCol) . " FROM `cash_transactions` WHERE `id` = ? LIMIT 1");
                        $q->execute([$oldId]);
                        $accId = (int)$q->fetchColumn();
                        if ($accId > 0) $accountIds[] = $accId;
                    }
                }
            }

            foreach (array_values(array_unique($accountIds)) as $accId) {
                gs_recalc_cash_account($pdo, $accId);
            }
        } elseif (function_exists('gs_recalc_cash_accounts')) {
            gs_recalc_cash_accounts($pdo, []);
        }
    }

    nsd_audit(
        $pdo,
        'new_stock',
        $id,
        'DELETE',
        $userId,
        [
            'id' => $id,
            'item_name' => $itemName,
            'total_pcs' => $totalPcs,
            'available_pcs' => $availablePcs,
            'total_weight_gram' => round($totalWeight, 3),
            'available_weight_gram' => round($availableWeight, 3),
            'total_amount' => nsd_first_float($stock, ['total_amount', 'amount'], 0)
        ],
        [
            'id' => $id,
            'deleted' => true,
            'reason' => $reason,
            'cash_reversed_count' => count($cashReversed)
        ],
        'New stock soft deleted | ' . $itemName
    );

    $pdo->commit();

    nsd_out(200, true, '✅ স্টক সফলভাবে delete/void হয়েছে', [
        'id' => $id,
        'stock_id' => $id,
        'item_name' => $itemName,
        'total_pcs' => $totalPcs,
        'total_weight_gram' => round($totalWeight, 3),
        'cash_reversed' => count($cashReversed) > 0,
        'cash_reversed_count' => count($cashReversed),
        'cash_reversed_items' => $cashReversed
    ]);

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

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