<?php
/**
 * ============================================================
 * _finance.php v2.3 — Central Cash Helper (Cash Module 2.0)
 * Date: 2026-06-11
 * v2.3 Fix: gs_table_exists() — SHOW TABLES permission issue fix
 * ============================================================
 */

if (!function_exists('gs_table_exists')) {
    function gs_table_exists(PDO $pdo, string $table): bool
    {
        try {
            $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
            if ($table === '') return false;
            $pdo->query("SELECT 1 FROM `{$table}` LIMIT 1");
            return true;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('gs_column_exists')) {
    function gs_column_exists(PDO $pdo, string $table, string $column): bool
    {
        try {
            $table  = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
            $column = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            if ($table === '' || $column === '') return false;
            $stmt = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?");
            $stmt->execute([$column]);
            return $stmt->fetch() !== false;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('gs_get_client_ip')) {
    function gs_get_client_ip(): string
    {
        $keys = ['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_CLIENT_IP', 'REMOTE_ADDR'];
        foreach ($keys as $k) {
            if (!empty($_SERVER[$k])) {
                $ip = explode(',', $_SERVER[$k])[0];
                return trim($ip);
            }
        }
        return '0.0.0.0';
    }
}

if (!function_exists('gs_current_user_id')) {
    function gs_current_user_id(): ?int
    {
        if (!empty($_SESSION['user_id']))  return (int)$_SESSION['user_id'];
        if (!empty($_SESSION['uid']))      return (int)$_SESSION['uid'];
        if (!empty($_SESSION['admin_id'])) return (int)$_SESSION['admin_id'];
        return null;
    }
}

if (!function_exists('gs_current_user_name')) {
    function gs_current_user_name(): ?string
    {
        if (!empty($_SESSION['user_name'])) return (string)$_SESSION['user_name'];
        if (!empty($_SESSION['username']))  return (string)$_SESSION['username'];
        if (!empty($_SESSION['name']))      return (string)$_SESSION['name'];
        return null;
    }
}

if (!function_exists('gs_find_cash_category_id')) {
    function gs_find_cash_category_id(PDO $pdo, string $code, int $fallback = 0): int
    {
        try {
            $stmt = $pdo->prepare("SELECT id FROM cash_categories WHERE category_code = ? LIMIT 1");
            $stmt->execute([strtoupper(trim($code))]);
            $id = $stmt->fetchColumn();
            return $id ? (int)$id : $fallback;
        } catch (Throwable $e) {
            return $fallback;
        }
    }
}

if (!function_exists('gs_generate_voucher_no')) {
    function gs_generate_voucher_no(PDO $pdo, string $date): string
    {
        static $requestCounter = [];
        $ts = strtotime($date);
        if (!$ts) $ts = time();
        $ym = date('Ym', $ts);
        $prefix = 'CV-' . $ym . '-';
        $stmt = $pdo->prepare("SELECT voucher_no FROM cash_transactions WHERE voucher_no LIKE ? ORDER BY id DESC LIMIT 1");
        $stmt->execute([$prefix . '%']);
        $last = $stmt->fetchColumn();
        $dbNext = 1;
        if ($last) {
            $parts = explode('-', $last);
            $dbNext = (int)end($parts) + 1;
        }
        $reqNext = ($requestCounter[$ym] ?? 0) + 1;
        $next = max($dbNext, $reqNext);
        $requestCounter[$ym] = $next;
        return $prefix . str_pad((string)$next, 4, '0', STR_PAD_LEFT);
    }
}

if (!function_exists('gs_insert_cash_transaction')) {
    function gs_insert_cash_transaction(PDO $pdo, array $data): int
    {
        $categoryId = isset($data['category_id']) ? (int)$data['category_id'] : 0;
        if ($categoryId <= 0 && !empty($data['category_code'])) {
            $categoryId = gs_find_cash_category_id($pdo, (string)$data['category_code'], 0);
        }
        if ($categoryId <= 0) {
            throw new InvalidArgumentException('Cash category পাওয়া যায়নি');
        }
        $catStmt = $pdo->prepare("SELECT direction, category_name_bn FROM cash_categories WHERE id = ? LIMIT 1");
        $catStmt->execute([$categoryId]);
        $cat = $catStmt->fetch(PDO::FETCH_ASSOC);
        if (!$cat) throw new RuntimeException('Invalid category_id: ' . $categoryId);
        $direction = $cat['direction'];
        $cashAccountId = (int)($data['cash_account_id'] ?? 0);
        if ($cashAccountId <= 0) throw new InvalidArgumentException('cash_account_id দিন');
        $amount = round((float)($data['amount'] ?? 0), 2);
        if ($amount <= 0) throw new InvalidArgumentException('amount > 0 হতে হবে');
        $transactionDate = $data['transaction_date'] ?? date('Y-m-d');
        $voucherNo = !empty($data['voucher_no']) ? $data['voucher_no'] : gs_generate_voucher_no($pdo, $transactionDate);
        $userId = isset($data['created_by']) ? (int)$data['created_by'] : gs_current_user_id();
        $userIp = $data['created_ip'] ?? gs_get_client_ip();
        $sql = "INSERT INTO cash_transactions (
            voucher_no, transaction_date, cash_account_id, category_id, direction, amount, note,
            reference_type, reference_id, party_id, pledge_id, locker_id, transfer_id, paired_transaction_id,
            is_locked, is_reversed, reversal_of_id, created_by, created_ip
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            $voucherNo, $transactionDate, $cashAccountId, $categoryId, $direction, $amount,
            (string)($data['note'] ?? ''),
            (string)($data['reference_type'] ?? 'MANUAL'),
            !empty($data['reference_id'])          ? (int)$data['reference_id']          : null,
            !empty($data['party_id'])              ? (int)$data['party_id']              : null,
            !empty($data['pledge_id'])             ? (int)$data['pledge_id']             : null,
            !empty($data['locker_id'])             ? (int)$data['locker_id']             : null,
            !empty($data['transfer_id'])           ? (int)$data['transfer_id']           : null,
            !empty($data['paired_transaction_id']) ? (int)$data['paired_transaction_id'] : null,
            isset($data['is_locked'])   ? (int)$data['is_locked']   : 0,
            isset($data['is_reversed']) ? (int)$data['is_reversed'] : 0,
            !empty($data['reversal_of_id']) ? (int)$data['reversal_of_id'] : null,
            $userId, $userIp
        ]);
        $txnId = (int)$pdo->lastInsertId();
        gs_audit_log($pdo, [
            'transaction_id' => $txnId,
            'voucher_no'     => $voucherNo,
            'action'         => 'CREATE',
            'change_summary' => sprintf('%s ৳%s | %s | %s',
                $direction === 'IN' ? 'IN' : 'OUT',
                number_format($amount, 2),
                $cat['category_name_bn'] ?? '',
                (string)($data['note'] ?? ''))
        ]);
        return $txnId;
    }
}

if (!function_exists('gs_recalc_cash_account')) {
    function gs_recalc_cash_account(PDO $pdo, int $cashAccountId): void
    {
        if ($cashAccountId <= 0) return;
        $stmt = $pdo->prepare("UPDATE cash_accounts SET current_balance = opening_balance + COALESCE((SELECT SUM(CASE WHEN direction = 'IN' THEN amount ELSE -amount END) FROM cash_transactions WHERE cash_account_id = ? AND is_deleted = 0 AND is_reversed = 0), 0) WHERE id = ?");
        $stmt->execute([$cashAccountId, $cashAccountId]);
    }
}

if (!function_exists('gs_recalc_cash_accounts')) {
    function gs_recalc_cash_accounts(PDO $pdo, array $ids): void
    {
        $ids = array_values(array_unique(array_map('intval', $ids)));
        foreach ($ids as $id) {
            if ($id > 0) gs_recalc_cash_account($pdo, $id);
        }
    }
}

if (!function_exists('gs_audit_log')) {
    function gs_audit_log(PDO $pdo, array $data): void
    {
        try {
            if (!gs_table_exists($pdo, 'cash_audit_log')) return;
            $stmt = $pdo->prepare("INSERT INTO cash_audit_log (transaction_id, voucher_no, action, field_name, old_value, new_value, change_summary, user_id, user_name, ip_address, user_agent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([
                !empty($data['transaction_id']) ? (int)$data['transaction_id'] : null,
                $data['voucher_no']     ?? null,
                $data['action']         ?? 'CREATE',
                $data['field_name']     ?? null,
                $data['old_value']      ?? null,
                $data['new_value']      ?? null,
                $data['change_summary'] ?? null,
                gs_current_user_id(),
                gs_current_user_name(),
                gs_get_client_ip(),
                $_SERVER['HTTP_USER_AGENT'] ?? null
            ]);
        } catch (Throwable $e) {}
    }
}

if (!function_exists('gs_update_locker_investor_summary')) {
    function gs_update_locker_investor_summary(PDO $pdo, int $partyId): void
    {
        if ($partyId <= 0 || !gs_table_exists($pdo, 'locker_investors')) return;
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(CASE WHEN cc.category_code = 'LOCKER_INVESTMENT_RECEIVE' THEN ct.amount ELSE 0 END), 0) AS total_invested, COALESCE(SUM(CASE WHEN cc.category_code = 'LOCKER_LOAN_RETURN' THEN ct.amount ELSE 0 END), 0) AS total_returned, COALESCE(SUM(CASE WHEN cc.category_code = 'LOCKER_PROFIT_SHARE' THEN ct.amount ELSE 0 END), 0) AS total_profit_paid, MAX(ct.transaction_date) AS last_date FROM cash_transactions ct INNER JOIN cash_categories cc ON cc.id = ct.category_id WHERE ct.party_id = ? AND ct.is_deleted = 0 AND ct.is_reversed = 0 AND cc.category_group = 'LOCKER'");
        $stmt->execute([$partyId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $invested    = (float)($row['total_invested']    ?? 0);
        $returned    = (float)($row['total_returned']    ?? 0);
        $profit      = (float)($row['total_profit_paid'] ?? 0);
        $lastDate    = $row['last_date'] ?? null;
        $outstanding = $invested - $returned;
        $activeCount = 0;
        if (gs_table_exists($pdo, 'pawn_pledges') && gs_column_exists($pdo, 'pawn_pledges', 'locker_id')) {
            $cs = $pdo->prepare("SELECT COUNT(*) FROM pawn_pledges WHERE locker_id = ? AND status = 'OPEN'");
            $cs->execute([$partyId]);
            $activeCount = (int)$cs->fetchColumn();
        }
        $exists = $pdo->prepare("SELECT id FROM locker_investors WHERE party_id = ? LIMIT 1");
        $exists->execute([$partyId]);
        $existingId = $exists->fetchColumn();
        if ($existingId) {
            $pdo->prepare("UPDATE locker_investors SET total_invested=?, total_returned=?, total_profit_paid=?, current_outstanding=?, active_pledges_count=?, last_transaction_date=? WHERE party_id=?")->execute([$invested, $returned, $profit, $outstanding, $activeCount, $lastDate, $partyId]);
        } else {
            $pdo->prepare("INSERT INTO locker_investors (party_id, total_invested, total_returned, total_profit_paid, current_outstanding, active_pledges_count, last_transaction_date) VALUES (?, ?, ?, ?, ?, ?, ?)")->execute([$partyId, $invested, $returned, $profit, $outstanding, $activeCount, $lastDate]);
        }
    }
}

if (!function_exists('gs_sync_sale_payment_summary')) {
    function gs_sync_sale_payment_summary(PDO $pdo, int $saleId): void {}
}