Xiao-Shan Gao

#!/usr/bin/env python3
"""
Standalone IOC Extractor for REDB Analysis Pipeline

This is a single-file IOC extraction script that can be called from the
analysis backend after Binary Ninja processing completes.

Dependencies:
    - clickhouse-connect (pip install clickhouse-connect)
    - python-dotenv (pip install python-dotenv)
    - Python 3.9+

Usage:
    # Extract IOCs for a single sample (uses .env for ClickHouse connection)
    python standalone_ioc_extractor.py --sha256 <hash>

    # Specify custom .env file
    python standalone_ioc_extractor.py --sha256 <hash> --env-file /path/to/.env

    # Override connection settings
    python standalone_ioc_extractor.py --sha256 <hash> --host ch.example.com --port 8123

    # Force re-extraction (delete existing IOCs first)
    python standalone_ioc_extractor.py --sha256 <hash> --force

    # Programmatic usage:
    from standalone_ioc_extractor import IOCExtractor
    extractor = IOCExtractor()  # Uses .env by default
    ioc_count = extractor.extract_for_sample("sha256_hash_here")

Required files:
    - iana_tlds.txt: Place in same directory as this script, or specify with --tld-file
    - .env file with CLICKHOUSE_* variables (or pass connection params directly)

Environment variables (from .env):
    CLICKHOUSE_HOST (default: localhost)
    CLICKHOUSE_PORT (default: 8123)
    CLICKHOUSE_USER (default: default)
    CLICKHOUSE_PASSWORD (default: empty)
    CLICKHOUSE_DATABASE (default: redb)

Based on IOC scraper patterns from Synapse (https://github.com/vertexproject/synapse)
"""

import os
import re
import argparse
import ipaddress
import logging
from pathlib import Path
from datetime import datetime
from dataclasses import dataclass
from enum import Enum
from typing import List, Set, Tuple, Generator, Optional

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S'
)
logger = logging.getLogger(__name__)


# =============================================================================
# Enums and Data Classes
# =============================================================================

class IOCType(str, Enum):
    """IOC types matching ClickHouse Enum8 values"""
    IPV4 = 'ipv4'
    IPV6 = 'ipv6'
    FQDN = 'fqdn'
    URL = 'url'
    EMAIL = 'email'
    SERVER = 'server'
    HASH_MD5 = 'hash_md5'
    HASH_SHA1 = 'hash_sha1'
    HASH_SHA256 = 'hash_sha256'
    CVE = 'cve'
    CWE = 'cwe'
    CPE = 'cpe'
    CRYPTO_BTC = 'crypto_btc'
    CRYPTO_ETH = 'crypto_eth'
    CRYPTO_XRP = 'crypto_xrp'
    CRYPTO_BCH = 'crypto_bch'
    CRYPTO_ADA = 'crypto_ada'
    CRYPTO_SUBSTRATE = 'crypto_substrate'
    PATH_LINUX = 'path_linux'
    PATH_WINDOWS = 'path_windows'
    REGISTRY_KEY = 'registry_key'
    ONION = 'onion'


class SourceType(str, Enum):
    """Source types matching ClickHouse Enum8 values"""
    DECOMPILED_FUNCTION = 'decompiled_function'
    DISASSEMBLED_FUNCTION = 'disassembled_function'
    STRING = 'string'
    # Universal text-based artefact surfaces (JS today; PowerShell, Python,
    # email, extracted PDF/Office text in the future). The frontend reads
    # `redb_basic_properties.filetype_magika` to render the right per-format
    # view, the same translation pattern APK uses for `decompiled_function`.
    TEXT_RAW = 'text_raw'
    TEXT_NORMALIZED = 'text_normalized'


@dataclass
class ExtractedIOC:
    """Represents an extracted IOC"""
    ioc_type: IOCType
    ioc_value: str
    source_type: SourceType
    source_identifier: str


# =============================================================================
# TLD Loading
# =============================================================================

def load_tld_list(tld_file: Optional[Path] = None) -> str:
    """Load TLD list and build regex alternation pattern."""
    if tld_file is None:
        tld_file = Path(__file__).parent / 'iana_tlds.txt'

    tlds = []
    try:
        with open(tld_file, 'r') as f:
            for line in f:
                line = line.strip().lower()
                if not line or line.startswith('#'):
                    continue
                tlds.append(line)
    except FileNotFoundError:
        logger.warning(f"TLD file not found at {tld_file}, using fallback list")
        tlds = ['com', 'net', 'org', 'edu', 'gov', 'mil', 'io', 'co', 'info',
                'biz', 'me', 'tv', 'cc', 'uk', 'de', 'fr', 'ru', 'cn', 'jp',
                'br', 'au', 'in', 'link', 'taxi']

    # Add special TLDs used in malware
    tlds.extend(['onion', 'bit', 'bazar'])
    tlds.sort(key=len, reverse=True)

    return '(?:' + '|'.join(re.escape(tld) for tld in tlds) + ')'


# =============================================================================
# Regex Patterns
# =============================================================================

# IPv4
IPV4_OCTET = r'(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)'
IPV4_PATTERN = re.compile(
    rf'(?<![0-9.])({IPV4_OCTET}\.{IPV4_OCTET}\.{IPV4_OCTET}\.{IPV4_OCTET})(?![0-9.])',
    re.ASCII
)

# IPv6
IPV6_PATTERN = re.compile(
    r'(?<![0-9a-fA-F:])('
    r'(?:[0-9a-fA-F]{1,4}:){7}[0-9a-fA-F]{1,4}|'
    r'(?:[0-9a-fA-F]{1,4}:){1,7}:|'
    r'(?:[0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|'
    r'(?:[0-9a-fA-F]{1,4}:){1,5}(?::[0-9a-fA-F]{1,4}){1,2}|'
    r'(?:[0-9a-fA-F]{1,4}:){1,4}(?::[0-9a-fA-F]{1,4}){1,3}|'
    r'(?:[0-9a-fA-F]{1,4}:){1,3}(?::[0-9a-fA-F]{1,4}){1,4}|'
    r'(?:[0-9a-fA-F]{1,4}:){1,2}(?::[0-9a-fA-F]{1,4}){1,5}|'
    r'[0-9a-fA-F]{1,4}:(?::[0-9a-fA-F]{1,4}){1,6}|'
    r':(?::[0-9a-fA-F]{1,4}){1,7}|'
    r'::(?:[fF]{4}:)?(?:' + IPV4_OCTET + r'\.){3}' + IPV4_OCTET + r'|'
    r'(?:[0-9a-fA-F]{1,4}:){1,4}:(?:' + IPV4_OCTET + r'\.){3}' + IPV4_OCTET +
    r')(?![0-9a-fA-F:])',
    re.ASCII
)

# URL
URL_SCHEMES = r'(?:https?|ftp|ftps|sftp|file|smb|ssh|telnet|ldap|ldaps)'
URL_PATTERN = re.compile(
    rf'({URL_SCHEMES}://[^\s<>\"\'\)\]\}},;]+)',
    re.IGNORECASE
)

# Defanging patterns
DEFANG_PATTERNS = [
    (re.compile(r'hxxps?://', re.IGNORECASE), lambda m: m.group().lower().replace('xx', 'tt')),
    (re.compile(r'\[\.?\]'), '.'),
    (re.compile(r'\[\s*dot\s*\]', re.IGNORECASE), '.'),
    (re.compile(r'\[\s*at\s*\]', re.IGNORECASE), '@'),
    (re.compile(r'\(\.\)'), '.'),
    (re.compile(r'\[:\]'), ':'),
]

# Email
EMAIL_PATTERN = re.compile(
    r'(?<![a-zA-Z0-9._%+-])([a-zA-Z0-9._%+-]{1,64}@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})(?![a-zA-Z0-9_])',
    re.ASCII
)

# Server (IP:port)
SERVER_PATTERN = re.compile(
    rf'({IPV4_OCTET}\.{IPV4_OCTET}\.{IPV4_OCTET}\.{IPV4_OCTET}):(\d{{1,5}})',
    re.ASCII
)

# .onion
ONION_PATTERN = re.compile(
    r'(?<![a-zA-Z0-9.-])([a-z2-7]{16}(?:[a-z2-7]{40})?\.onion)(?![a-zA-Z0-9.-])',
    re.IGNORECASE
)

# Hashes
MD5_PATTERN = re.compile(r'(?<![A-Za-z0-9])([a-fA-F0-9]{32})(?![A-Za-z0-9])', re.ASCII)
SHA1_PATTERN = re.compile(r'(?<![A-Za-z0-9])([a-fA-F0-9]{40})(?![A-Za-z0-9])', re.ASCII)
SHA256_PATTERN = re.compile(r'(?<![A-Za-z0-9])([a-fA-F0-9]{64})(?![A-Za-z0-9])', re.ASCII)

# CVE/CWE/CPE
CVE_PATTERN = re.compile(
    r'(?i)(CVE[-\u2010\u2011\u2012\u2013\u2014\u2015](?:19|20)\d{2}[-\u2010\u2011\u2012\u2013\u2014\u2015]\d{4,7})',
    re.UNICODE
)
CWE_PATTERN = re.compile(r'(?i)(CWE-\d{1,8})')
CPE_PATTERN = re.compile(
    r'(cpe:2\.3:[aho\*\-](?::[a-zA-Z0-9\*\-._~%!$&\'()+,;=]*){10})',
    re.IGNORECASE
)

# Cryptocurrency
BASE58_CHARSET = r'a-zA-HJ-NP-Z0-9'
BECH32_CHARSET = r'qpzry9x8gf2tvdw0s3jn54khce6mua7l'

BTC_P2PKH_PATTERN = re.compile(rf'(?<![A-Za-z0-9])([1][{BASE58_CHARSET}]{{25,39}})(?![A-Za-z0-9])')
BTC_P2SH_PATTERN = re.compile(rf'(?<![A-Za-z0-9])(3[{BASE58_CHARSET}]{{33}})(?![A-Za-z0-9])')
BTC_BECH32_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9])((bc|bcrt|tb)1[{BECH32_CHARSET}]{{3,71}})(?![A-Za-z0-9])',
    re.IGNORECASE
)
ETH_PATTERN = re.compile(r'(?<![A-Za-z0-9])(0x[A-Fa-f0-9]{40})(?![A-Fa-f0-9])')
XRP_PATTERN = re.compile(rf'(?<![A-Za-z0-9])([xr][{BASE58_CHARSET}]{{25,46}})(?![A-Za-z0-9])')
BCH_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9])((bitcoincash|bchtest):[{BECH32_CHARSET}]{{42}})(?![A-Za-z0-9])',
    re.IGNORECASE
)
ADA_SHELLEY_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9])(addr1[{BECH32_CHARSET}]{{53,}})(?![A-Za-z0-9])',
    re.IGNORECASE
)
ADA_BYRON_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9])((DdzFF|Ae2td)[{BASE58_CHARSET}]{{54,99}})(?![A-Za-z0-9])'
)
SUBSTRATE_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9])([1a-z][{BASE58_CHARSET}]{{46,47}})(?![A-Za-z0-9])'
)

# File paths
LINUX_PATH_PATTERN = re.compile(
    r'(?<![a-zA-Z0-9/])(/(?:bin|boot|dev|etc|home|lib|lib64|media|mnt|opt|proc|root|run|sbin|srv|sys|tmp|usr|var)/[^\s<>\"\'\)\]\}},;]{1,500})(?![a-zA-Z0-9/])'
)

# Windows path / registry key — both share separator and component grammar.
# Separator accepts 1 or 2 backslashes so paths embedded in source-code string
# literals (where `\` is escaped to `\\` — JS, JSON, PowerShell, etc.) match
# the same as runtime-form paths. `*` is permitted in components to capture
# wildcard patterns common in malware (e.g. C:\Users\*\AppData\Local\Temp).
#
# Two-tier component grammar:
#   _WIN_FIRST — strict (no whitespace, quote, separator, reserved-char) —
#     used as the first char of every segment so segments can't start with
#     a space or a quote that would let a path drift into prose
#   _WIN_INNER — permissive (allows internal whitespace) — used in the body
#     of *delimited* segments only (those followed by `\` or `\\`), so
#     `Program Files` matches between separators
# The final segment uses _WIN_FIRST throughout, so a path that runs into
# free text stops at the first whitespace instead of slurping the rest of
# the line. (e.g. "c:\users\admin\desktop and stuff" → "c:\users\admin\desktop".)
_WIN_SEP = r'\\{1,2}'
_WIN_FIRST = r"[^\\<>\"'`\?\|\s]"
# `_WIN_INNER` also excludes `:` to prevent two adjacent paths in free text
# from collapsing into one match — e.g. "From C:\one to D:\two" must not
# slurp ` to D:` into the body of segment 1. Colon-in-segment is rare in
# practice (only NTFS alternate data streams use it: `C:\file.txt:stream`).
_WIN_INNER = r"[^\\<>\"'`\?\|:]"
WINDOWS_PATH_PATTERN = re.compile(
    rf'(?<![a-zA-Z0-9\\])'
    rf'([a-zA-Z]:{_WIN_SEP}'
    rf'(?:{_WIN_FIRST}{_WIN_INNER}*{_WIN_SEP})*'
    rf'(?:{_WIN_FIRST}+)?'
    rf')(?![a-zA-Z0-9\\])',
    re.IGNORECASE
)
_REG_HIVE = (
    r'(?:HK(?:LM|CU|CR|U|CC|PD)'
    r'|HKEY_(?:LOCAL_MACHINE|CURRENT_USER|CLASSES_ROOT|USERS|CURRENT_CONFIG|PERFORMANCE_DATA))'
)
REGISTRY_KEY_PATTERN = re.compile(
    rf'(?<![A-Za-z0-9\\])'
    rf'({_REG_HIVE}{_WIN_SEP}'
    rf'(?:{_WIN_FIRST}{_WIN_INNER}*{_WIN_SEP})*'
    rf'{_WIN_FIRST}+'
    rf')(?![A-Za-z0-9\\])',
    re.IGNORECASE
)

# Exclusions
EXCLUDED_IPS = {
    '0.0.0.0', '127.0.0.1', '255.255.255.255',
    '1.0.0.0', '1.0.0.1', '1.1.1.1',
    '8.8.8.8', '8.8.4.4',
}
EXCLUDED_DOMAINS = {
    'example.com', 'example.org', 'example.net',
    'localhost', 'localhost.localdomain',
    'test.com', 'test.local',
}

# JS-context FQDN false-positive blocklists.
#
# When the scraper is constructed with `js_context=True`, FQDN candidates whose
# leftmost segment is in JS_FP_SLDS or whose TLD is in JS_FP_TLDS are rejected.
# Both lists target patterns that arise from JS object/property access syntax
# (`this.foo.bar`, `process.id`, `lib.so`, `Function.name`, ...) where the
# segment chain accidentally shape-matches a hostname. Without this filter
# typical samples produce ~80% FQDN false positives because new gTLDs include
# common JS property-name suffixes (`.name`, `.id`, `.so`, `.post`,
# `.services`, ...).
#
# Curated specifically for JS — APK suppresses FQDN entirely (smali class
# names produce orders-of-magnitude more dotted identifiers than JS source).
# Protocol prefixes (ftp, smtp, imap, pop3, http, https) are *not* in the SLD
# list so legitimate C2 hostnames like `ftp.evil.example.com` survive.
JS_FP_TLDS = frozenset({
    'name', 'id', 'so', 'post', 'services', 'tools', 'support',
    'today', 'email', 'page', 'site', 'click', 'link', 'tech',
    'systems', 'network',
})
JS_FP_SLDS = frozenset({
    # JS keywords / pseudo-globals
    'this', 'self', 'super', 'arguments', 'globalthis',
    # Common host objects
    'window', 'document', 'console', 'navigator', 'location', 'history',
    'screen', 'event',
    # Node / runtime globals
    'process', 'proc', 'module', 'exports', 'require', 'global', 'buffer',
    # Built-in constructors / prototypes that get used as property roots
    'function', 'func', 'object', 'array', 'string', 'number', 'boolean',
    'symbol', 'promise', 'map', 'set', 'date', 'regexp', 'error', 'json',
    'math', 'reflect', 'proxy', 'class', 'interface',
    # Framework / popular library roots
    'vue', 'react', 'angular', 'firebase', 'jquery', 'lodash', 'axios',
    'socket', 'express', 'fastify', 'koa', 'next', 'nuxt', 'svelte',
    'redux', 'mobx',
    # Browser-extension surfaces
    'chrome', 'browser', 'firefox', 'safari',
    # Generic code-shape identifiers that show up in malware-analysis text
    'system', 'lib', 'api', 'app', 'component', 'controller', 'service',
    'handler', 'manager', 'factory', 'builder', 'view', 'model', 'config',
    'option', 'param', 'arg', 'data', 'result', 'value', 'key', 'index',
    'count', 'size', 'length', 'type', 'kind', 'target', 'payload',
    'exploit', 'malware', 'sandbox', 'vm', 'thread', 'task', 'job',
    'worker', 'session', 'request', 'response', 'callback', 'listener',
    'observer', 'subscriber',
})


# =============================================================================
# IOC Scraper
# =============================================================================

class IOCScraper:
    """Scrapes IOCs from text content."""

    def __init__(
        self,
        tld_file: Optional[Path] = None,
        suppress_types: Optional[Set[IOCType]] = None,
        js_context: bool = False,
    ):
        self.tld_pattern = load_tld_list(tld_file)
        self.suppress_types: Set[IOCType] = suppress_types or set()
        # When True, FQDN validation additionally rejects candidates whose
        # leftmost segment is in JS_FP_SLDS or whose TLD is in JS_FP_TLDS, to
        # filter false positives from JS object-access syntax. Defaults to
        # False so non-JS callers behave identically to before.
        self.js_context: bool = js_context
        self.fqdn_pattern = re.compile(
            rf'(?<![a-zA-Z0-9_./:@\\-])([a-zA-Z0-9](?:[a-zA-Z0-9-]{{0,61}}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{{0,61}}[a-zA-Z0-9])?)*\.{self.tld_pattern})(?![a-zA-Z0-9_-])',
            re.IGNORECASE
        )

    def _defang_text(self, text: str) -> str:
        for pattern, replacement in DEFANG_PATTERNS:
            if callable(replacement):
                text = pattern.sub(replacement, text)
            else:
                text = pattern.sub(replacement, text)
        return text

    def _validate_ipv4(self, ip: str) -> bool:
        try:
            addr = ipaddress.IPv4Address(ip)
            if ip in EXCLUDED_IPS:
                return False
            if addr.is_private or addr.is_loopback or addr.is_reserved:
                return False
            return True
        except (ipaddress.AddressValueError, ValueError):
            return False

    def _validate_ipv6(self, ip: str) -> bool:
        try:
            addr = ipaddress.IPv6Address(ip)
            if addr.is_private or addr.is_loopback or addr.is_reserved:
                return False
            return True
        except (ipaddress.AddressValueError, ValueError):
            return False

    def _validate_hash(self, hash_value: str) -> bool:
        if len(set(hash_value.lower())) <= 2:
            return False
        if hash_value.lower() in {'0' * len(hash_value), 'f' * len(hash_value)}:
            return False
        return True

    def _validate_fqdn(self, fqdn: str) -> bool:
        fqdn_lower = fqdn.lower()
        if fqdn_lower in EXCLUDED_DOMAINS:
            return False
        parts = fqdn_lower.split('.')
        if all(part.isdigit() for part in parts[:-1]):
            return False
        # Reject FQDNs where the second-level domain is less than 3 characters
        # e.g. a.com, ab.com are rejected; abc.com and x.abc.com are accepted
        if len(parts) >= 2 and len(parts[-2]) < 3:
            return False
        # JS-context filter: reject candidates that are almost certainly JS
        # object-access syntax (`this.foo.bar`, `process.id`, `lib.so`,
        # `Component.name`) rather than real hostnames. The TLD check fires on
        # gTLDs that double as common JS property suffixes; the leftmost-
        # segment check fires on JS keywords / framework roots. Either one
        # alone is sufficient to reject. See JS_FP_TLDS / JS_FP_SLDS for the
        # rationale and the curated lists.
        if self.js_context:
            if parts[-1] in JS_FP_TLDS:
                return False
            if len(parts) >= 2 and parts[0] in JS_FP_SLDS:
                return False
        return True

    def _normalize_cve(self, cve: str) -> str:
        cve = re.sub(r'[\u2010\u2011\u2012\u2013\u2014\u2015]', '-', cve)
        return cve.upper()

    def scrape(
        self,
        text: str,
        source_type: SourceType,
        source_identifier: str
    ) -> Generator[ExtractedIOC, None, None]:
        """Scrape IOCs from text."""
        if not text:
            return

        text = self._defang_text(text)
        seen: Set[Tuple[IOCType, str]] = set()

        def emit(ioc_type: IOCType, value: str) -> Generator[ExtractedIOC, None, None]:
            key = (ioc_type, value.lower())
            if key not in seen:
                seen.add(key)
                yield ExtractedIOC(
                    ioc_type=ioc_type,
                    ioc_value=value,
                    source_type=source_type,
                    source_identifier=source_identifier
                )

        # URLs
        for match in URL_PATTERN.finditer(text):
            url = match.group(1).rstrip('.,;:')
            yield from emit(IOCType.URL, url)

        # Servers (IP:port)
        for match in SERVER_PATTERN.finditer(text):
            ip = match.group(1)
            port = int(match.group(2))
            if self._validate_ipv4(ip) and 1 <= port <= 65535:
                yield from emit(IOCType.SERVER, f"{ip}:{port}")

        # IPv4
        for match in IPV4_PATTERN.finditer(text):
            ip = match.group(1)
            if self._validate_ipv4(ip):
                yield from emit(IOCType.IPV4, ip)

        # IPv6
        for match in IPV6_PATTERN.finditer(text):
            ip = match.group(1)
            if self._validate_ipv6(ip):
                yield from emit(IOCType.IPV6, ip)

        # .onion
        for match in ONION_PATTERN.finditer(text):
            onion = match.group(1).lower()
            yield from emit(IOCType.ONION, onion)

        # FQDNs
        if IOCType.FQDN not in self.suppress_types:
            for match in self.fqdn_pattern.finditer(text):
                fqdn = match.group(1).lower()
                if self._validate_fqdn(fqdn) and not fqdn.endswith('.onion'):
                    yield from emit(IOCType.FQDN, fqdn)

        # Emails
        for match in EMAIL_PATTERN.finditer(text):
            email = match.group(1).lower()
            yield from emit(IOCType.EMAIL, email)

        # Hashes (SHA256 first)
        for match in SHA256_PATTERN.finditer(text):
            hash_val = match.group(1).lower()
            if self._validate_hash(hash_val):
                yield from emit(IOCType.HASH_SHA256, hash_val)

        for match in SHA1_PATTERN.finditer(text):
            hash_val = match.group(1).lower()
            if self._validate_hash(hash_val) and (IOCType.HASH_SHA256, hash_val) not in seen:
                yield from emit(IOCType.HASH_SHA1, hash_val)

        for match in MD5_PATTERN.finditer(text):
            hash_val = match.group(1).lower()
            if self._validate_hash(hash_val):
                if not any((IOCType.HASH_SHA1, h) in seen or (IOCType.HASH_SHA256, h) in seen
                           for h in [hash_val]):
                    yield from emit(IOCType.HASH_MD5, hash_val)

        # CVE/CWE/CPE
        for match in CVE_PATTERN.finditer(text):
            cve = self._normalize_cve(match.group(1))
            yield from emit(IOCType.CVE, cve)

        for match in CWE_PATTERN.finditer(text):
            cwe = match.group(1).upper()
            yield from emit(IOCType.CWE, cwe)

        for match in CPE_PATTERN.finditer(text):
            cpe = match.group(1).lower()
            yield from emit(IOCType.CPE, cpe)

        # Cryptocurrency
        for match in BTC_BECH32_PATTERN.finditer(text):
            addr = match.group(1).lower()
            yield from emit(IOCType.CRYPTO_BTC, addr)

        for match in BTC_P2PKH_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_BTC, match.group(1))

        for match in BTC_P2SH_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_BTC, match.group(1))

        for match in ETH_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_ETH, match.group(1).lower())

        for match in XRP_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_XRP, match.group(1))

        for match in BCH_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_BCH, match.group(1).lower())

        for match in ADA_SHELLEY_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_ADA, match.group(1).lower())

        for match in ADA_BYRON_PATTERN.finditer(text):
            yield from emit(IOCType.CRYPTO_ADA, match.group(1))

        for match in SUBSTRATE_PATTERN.finditer(text):
            addr = match.group(1)
            if any(c.isdigit() for c in addr) and len(addr) in (47, 48):
                yield from emit(IOCType.CRYPTO_SUBSTRATE, addr)

        # File paths
        for match in LINUX_PATH_PATTERN.finditer(text):
            path = match.group(1).rstrip('.,;:')
            yield from emit(IOCType.PATH_LINUX, path)

        for match in WINDOWS_PATH_PATTERN.finditer(text):
            # Normalise doubled backslashes (source-escaped form) to single so
            # `C:\\Users\\Public` and `C:\Users\Public` collapse to one IOC.
            path = match.group(1).rstrip('.,;:').replace('\\\\', '\\')
            yield from emit(IOCType.PATH_WINDOWS, path)

        # Windows registry keys — same normalisation as paths
        for match in REGISTRY_KEY_PATTERN.finditer(text):
            key = match.group(1).rstrip('.,;:').replace('\\\\', '\\')
            yield from emit(IOCType.REGISTRY_KEY, key)


# =============================================================================
# IOC Extractor (ClickHouse integration)
# =============================================================================

def load_env(env_file: Optional[Path] = None):
    """Load environment variables from .env file."""
    try:
        from dotenv import load_dotenv
    except ImportError:
        logger.warning("python-dotenv not installed, using environment variables only")
        return

    if env_file:
        load_dotenv(env_file)
    else:
        # Try common locations
        for path in [Path(".env"), Path(__file__).parent.parent.parent.parent / ".env"]:
            if path.exists():
                load_dotenv(path)
                logger.debug(f"Loaded environment from {path}")
                break


class IOCExtractor:
    """Extracts IOCs from ClickHouse data and stores results."""

    def __init__(
        self,
        host: Optional[str] = None,
        port: Optional[int] = None,
        user: Optional[str] = None,
        password: Optional[str] = None,
        database: Optional[str] = None,
        batch_size: int = 1000,
        tld_file: Optional[Path] = None,
        env_file: Optional[Path] = None,
        client=None
    ):
        """
        Initialize IOC Extractor.

        Connection parameters can be passed directly or loaded from environment:
            CLICKHOUSE_HOST, CLICKHOUSE_PORT, CLICKHOUSE_USER,
            CLICKHOUSE_PASSWORD, CLICKHOUSE_DATABASE

        Args:
            host: ClickHouse host (default: from env or localhost)
            port: ClickHouse port (default: from env or 8123)
            user: ClickHouse user (default: from env or default)
            password: ClickHouse password (default: from env or empty)
            database: ClickHouse database (default: from env or redb)
            batch_size: Batch size for processing
            tld_file: Path to TLD list file
            env_file: Path to .env file (default: auto-detect)
            client: Existing ClickHouse client (if provided, connection params are ignored)
        """
        # If existing client provided, use it directly
        if client is not None:
            self.client = client
            self.database = database or os.environ.get("CLICKHOUSE_DATABASE", "redb")
            logger.info(f"Using existing ClickHouse client, database={self.database}")
        else:
            # Create new connection
            try:
                import clickhouse_connect
            except ImportError:
                raise ImportError("clickhouse-connect is required. Install with: pip install clickhouse-connect")

            # Load .env file
            load_env(env_file)

            # Use passed values or fall back to environment variables
            host = host or os.environ.get("CLICKHOUSE_HOST", "localhost")
            port = port or int(os.environ.get("CLICKHOUSE_PORT", "8123"))
            user = user or os.environ.get("CLICKHOUSE_USER", "default")
            password = password if password is not None else os.environ.get("CLICKHOUSE_PASSWORD", "")
            database = database or os.environ.get("CLICKHOUSE_DATABASE", "redb")

            logger.info(f"Connecting to ClickHouse at {host}:{port}, database={database}")

            self.client = clickhouse_connect.get_client(
                host=host,
                port=port,
                username=user,
                password=password,
                database=database
            )
            self.database = database

        self.batch_size = batch_size
        self.scraper = IOCScraper(tld_file)
        self._insert_buffer: List[dict] = []

    def has_existing_iocs(self, sha256: str) -> bool:
        """Check if sample already has IOCs extracted."""
        result = self.client.query(
            f"SELECT 1 FROM {self.database}.redb_iocs WHERE sha256 = %(sha256)s LIMIT 1",
            parameters={"sha256": sha256}
        )
        return len(result.result_rows) > 0

    def delete_iocs_for_sample(self, sha256: str):
        """Delete existing IOCs for a sample."""
        self.client.command(
            f"ALTER TABLE {self.database}.redb_iocs DELETE WHERE sha256 = %(sha256)s",
            parameters={"sha256": sha256}
        )
        logger.info(f"Deleted existing IOCs for {sha256[:16]}...")

    def extract_for_sample(self, sha256: str, force: bool = False) -> int:
        """
        Extract IOCs for a single sample.

        Args:
            sha256: Sample SHA256 hash
            force: Delete existing IOCs and re-extract

        Returns:
            Number of IOCs extracted
        """
        if force:
            self.delete_iocs_for_sample(sha256)
        elif self.has_existing_iocs(sha256):
            logger.info(f"Skipping {sha256[:16]}... (already has IOCs)")
            return 0

        total_iocs = 0

        # Extract from strings
        strings_count = self._extract_from_strings(sha256)
        total_iocs += strings_count

        # Extract from decompiled functions
        functions_count = self._extract_from_decompiled(sha256)
        total_iocs += functions_count

        # Flush remaining buffer
        self._flush_buffer()

        logger.info(f"Extracted {total_iocs} IOCs for {sha256[:16]}... (strings: {strings_count}, functions: {functions_count})")
        return total_iocs

    def _extract_from_strings(self, sha256: str) -> int:
        """Extract IOCs from sample's strings."""
        count = 0
        offset = 0

        while True:
            result = self.client.query(
                f"""
                SELECT string, string_offset
                FROM {self.database}.code_binja_strings_by_binary
                WHERE sha256 = %(sha256)s
                ORDER BY string_offset
                LIMIT %(limit)s OFFSET %(offset)s
                """,
                parameters={"sha256": sha256, "limit": self.batch_size, "offset": offset}
            )

            if not result.result_rows:
                break

            for row in result.result_rows:
                string_value, string_offset = row
                if isinstance(string_value, bytes):
                    string_value = string_value.decode('utf-8', errors='replace')

                for ioc in self.scraper.scrape(string_value, SourceType.STRING, str(string_offset)):
                    self._buffer_insert(sha256, ioc)
                    count += 1

            offset += len(result.result_rows)

        return count

    def _extract_from_decompiled(self, sha256: str) -> int:
        """Extract IOCs from sample's decompiled functions."""
        count = 0

        # Get function hashes for this sample
        refs_result = self.client.query(
            f"""
            SELECT decompiled_function_hash
            FROM {self.database}.code_binja_decompiled_functions_references
            WHERE sha256 = %(sha256)s
            """,
            parameters={"sha256": sha256}
        )

        if not refs_result.result_rows:
            return 0

        function_hashes = [row[0] for row in refs_result.result_rows]
        if isinstance(function_hashes[0], bytes):
            function_hashes = [h.decode('utf-8') for h in function_hashes]

        # Process in batches
        for i in range(0, len(function_hashes), self.batch_size):
            batch_hashes = function_hashes[i:i + self.batch_size]

            # Get function content, excluding LIBRARY and THUNK types
            content_result = self.client.query(
                f"""
                SELECT decompiled_function_hash, decompiled_function
                FROM {self.database}.code_binja_decompiled_functions_content
                WHERE decompiled_function_hash IN %(hashes)s
                  AND function_type NOT IN ('LIBRARY', 'THUNK')
                """,
                parameters={"hashes": batch_hashes}
            )

            for row in content_result.result_rows:
                func_hash, func_content = row
                if isinstance(func_hash, bytes):
                    func_hash = func_hash.decode('utf-8')
                if isinstance(func_content, bytes):
                    func_content = func_content.decode('utf-8', errors='replace')

                for ioc in self.scraper.scrape(func_content, SourceType.DECOMPILED_FUNCTION, func_hash):
                    self._buffer_insert(sha256, ioc)
                    count += 1

        return count

    def _buffer_insert(self, sha256: str, ioc: ExtractedIOC):
        """Buffer an IOC for batch insert."""
        self._insert_buffer.append({
            "sha256": sha256,
            "ioc_type": ioc.ioc_type.value,
            "ioc_value": ioc.ioc_value,
            "source_type": ioc.source_type.value,
            "source_identifier": ioc.source_identifier,
            "extracted_at": datetime.utcnow()
        })

        if len(self._insert_buffer) >= self.batch_size:
            self._flush_buffer()

    def _flush_buffer(self):
        """Flush buffered IOCs to ClickHouse."""
        if not self._insert_buffer:
            return

        columns = ["sha256", "ioc_type", "ioc_value", "source_type", "source_identifier", "extracted_at"]
        data = [[row[col] for col in columns] for row in self._insert_buffer]

        self.client.insert(
            f"{self.database}.redb_iocs",
            data,
            column_names=columns
        )

        self._insert_buffer = []


# =============================================================================
# CLI
# =============================================================================

def main():
    parser = argparse.ArgumentParser(
        description="Extract IOCs from ClickHouse sample data",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
    # Extract IOCs for a sample (uses .env for connection)
    python standalone_ioc_extractor.py --sha256 abc123...

    # Force re-extraction
    python standalone_ioc_extractor.py --sha256 abc123... --force

    # Override connection settings from .env
    python standalone_ioc_extractor.py --sha256 abc123... --host ch.example.com

    # Use custom .env file
    python standalone_ioc_extractor.py --sha256 abc123... --env-file /path/to/.env
        """
    )
    parser.add_argument("--sha256", required=True, help="Sample SHA256 hash")
    parser.add_argument("--env-file", type=Path, help="Path to .env file (default: auto-detect)")
    parser.add_argument("--host", help="ClickHouse host (default: from .env or localhost)")
    parser.add_argument("--port", type=int, help="ClickHouse HTTP port (default: from .env or 8123)")
    parser.add_argument("--user", help="ClickHouse user (default: from .env or default)")
    parser.add_argument("--password", help="ClickHouse password (default: from .env or empty)")
    parser.add_argument("--database", help="ClickHouse database (default: from .env or redb)")
    parser.add_argument("--force", action="store_true", help="Force re-extraction (delete existing IOCs)")
    parser.add_argument("--tld-file", type=Path, help="Path to TLD list file (default: iana_tlds.txt in script directory)")
    parser.add_argument("--batch-size", type=int, default=1000, help="Batch size for processing (default: 1000)")

    args = parser.parse_args()

    extractor = IOCExtractor(
        host=args.host,
        port=args.port,
        user=args.user,
        password=args.password,
        database=args.database,
        batch_size=args.batch_size,
        tld_file=args.tld_file,
        env_file=args.env_file
    )

    ioc_count = extractor.extract_for_sample(args.sha256, force=args.force)
    print(f"Extracted {ioc_count} IOCs for {args.sha256[:16]}...")


if __name__ == "__main__":
    main()