pure_openapi_ingest.py

#!/usr/bin/env python3
"""
Pure API → SQLite Ingestion Tool

A schema-driven tool that automatically ingests data from Elsevier Pure API
into a queryable SQLite database with full support for nested data structures,
discriminated unions, and localized content.

Features:
- Schema-driven design - everything driven by OpenAPI specification
- Deep nested array support with recursive unpacking to arbitrary depth
- Localization-aware - automatically detects and unpacks localized strings
- Discriminated union support - fully resolves OpenAPI oneOf/anyOf/allOf schemas
- Idempotent updates - content hash tracking for change detection
- Automatic retries with exponential backoff
- Rate limiting and timeout configuration
- Comprehensive logging with progress tracking

CLI:
    python pure_openapi_ingest.py \
        --openapi ./openapi.yaml \
        --db ./pure.sqlite \
        --discover                  # ingest all detected list endpoints
        --max-depth 5               # maximum nesting depth (default: 5)
        --limit 100                 # test with limited items

    # or pick specific paths
        --paths /research-outputs /activities

.env (create a file next to your script):
    PURE_BASE_URL=https://your-institution.pure.elsevier.com/ws/api
    PURE_API_KEY=your_api_key_here

    # Optional settings (defaults shown)
    PAGE_SIZE=100                # Number of items per API request
    RATE_LIMIT_RPS=3            # Requests per second (adjust based on your API limits)
    TIMEOUT_SECONDS=60          # HTTP request timeout
    RETRY_MAX=5                 # Maximum retries on 5xx/429 errors


Dependencies:
    pip install pyyaml requests
"""

import argparse
import hashlib
import json
import logging
import os
import re
import sqlite3
import sys
import time
from collections import defaultdict
from dataclasses import dataclass, field
from typing import Any, Dict, List, Optional, Set, Tuple, Union

try:
    import yaml  # pyyaml
except ImportError as e:
    print("Missing dependency: pyyaml. Install with: pip install pyyaml", file=sys.stderr)
    raise

try:
    import requests
except ImportError:
    print("Missing dependency: requests. Install with: pip install requests", file=sys.stderr)
    raise

# -------------------------
# Configuration
# -------------------------

@dataclass
class Config:
    """Configuration for the ingestion process"""
    max_depth: int = 5
    prefix_separator: str = "_"  # or "." for dot notation
    array_table_separator: str = "__"
    skip_empty_arrays: bool = True
    detect_date_fields: bool = True
    preserve_case: bool = False  # if True, don't convert to snake_case
    create_indexes: bool = True
    verbose: bool = False

# -------------------------
# Small .env loader
# -------------------------

def load_env_file(path: str) -> Dict[str, str]:
    env = {}
    if not os.path.isfile(path):
        return env
    with open(path, "r", encoding="utf-8") as f:
        for line in f:
            line = line.strip()
            if not line or line.startswith("#"):
                continue
            if "=" in line:
                key, val = line.split("=", 1)
                env[key.strip()] = val.strip()
    return env

def getenv(key: str, default: Optional[str]=None) -> Optional[str]:
    return os.environ.get(key, default)

# -------------------------
# Utility Functions
# -------------------------

def snake(name: str, preserve_case: bool = False) -> str:
    """Convert name to snake_case"""
    if preserve_case:
        return re.sub(r'[^0-9a-zA-Z]+', '_', name).strip('_') or "field"
    name = re.sub(r'[^0-9a-zA-Z]+', '_', name)
    name = re.sub(r'([a-z0-9])([A-Z])', r'\1_\2', name)
    s = name.strip('_').lower()
    return s or "field"

def safe_ident(name: str) -> str:
    """SQLite identifier quoting"""
    return '"' + name.replace('"', '""') + '"'

def sha256_json(obj: Any) -> str:
    """Generate SHA256 hash of JSON object"""
    return hashlib.sha256(
        json.dumps(obj, sort_keys=True, separators=(",", ":")).encode("utf-8")
    ).hexdigest()

def now_iso() -> str:
    """Current time in ISO format"""
    return time.strftime("%Y-%m-%dT%H:%M:%SZ", time.gmtime())

def is_date_field(name: str, schema: Dict[str, Any]) -> bool:
    """Detect if a field is likely a date/datetime"""
    fmt = schema.get("format", "")
    if fmt in ("date", "date-time", "datetime"):
        return True
    name_lower = name.lower()
    return any(indicator in name_lower for indicator in 
               ["date", "time", "created", "updated", "modified", "published"])

def is_scalar(schema: Dict[str, Any]) -> bool:
    """Check if schema represents a scalar type"""
    t = schema.get("type")
    return t in ("string", "number", "integer", "boolean", "null")

def sql_type_for(schema: Dict[str, Any], field_name: str = "", config: Config = None) -> str:
    """Determine SQL type for a schema"""
    t = schema.get("type")
    fmt = schema.get("format")
    
    # Check for date/time types
    if config and config.detect_date_fields and is_date_field(field_name, schema):
        return "TEXT"  # Store as ISO strings
    
    if t == "integer":
        return "INTEGER"
    elif t == "number":
        return "REAL"
    elif t == "boolean":
        return "INTEGER"  # SQLite convention: 0/1
    elif t == "null":
        return "TEXT"
    
    # Default to TEXT for strings, unknown types, objects
    return "TEXT"

# -------------------------
# Schema Resolution
# -------------------------

class SchemaResolver:
    """Handles OpenAPI schema resolution including $ref, allOf, oneOf, anyOf"""

    def __init__(self, components: Dict[str, Any]):
        self.components = components
        self._ref_cache: Dict[str, Dict[str, Any]] = {}
        self._resolution_stack: List[str] = []  # Track resolution path to detect cycles
        self._max_depth = 50  # Maximum recursion depth
    
    def resolve(self, schema: Dict[str, Any]) -> Dict[str, Any]:
        """Recursively resolve schema with $ref, allOf, oneOf, anyOf"""
        if not schema:
            return {}

        # Check recursion depth
        if len(self._resolution_stack) > self._max_depth:
            return {}  # Return empty schema to prevent infinite recursion

        # Handle $ref
        if "$ref" in schema:
            ref = schema["$ref"]

            # Check if already in resolution stack (circular reference)
            if ref in self._resolution_stack:
                return {}  # Break the cycle

            if ref in self._ref_cache:
                return self._ref_cache[ref]

            # Add to stack before resolving
            self._resolution_stack.append(ref)
            try:
                resolved = self._resolve_ref(ref)
                self._ref_cache[ref] = resolved
                return resolved
            finally:
                # Remove from stack after resolving
                self._resolution_stack.pop()

        # Handle discriminator with mapping (OpenAPI 3.0 style)
        # This expands discriminator.mapping into oneOf-style resolution
        # BUT: Don't expand if we're already inside a discriminator variant to avoid cycles
        if "discriminator" in schema and "mapping" in schema["discriminator"]:
            # Check if any variant is already in the resolution stack (would create cycle)
            mapping = schema["discriminator"]["mapping"]
            variant_refs = [ref for ref in mapping.values() if isinstance(ref, str)]

            # If we're already resolving one of the variants, don't expand the discriminator
            # Just return the base schema to break the cycle
            if any(ref in self._resolution_stack for ref in variant_refs):
                # Return base schema without expanding variants
                result = dict(schema)
                # Still recursively resolve nested properties
                if "properties" in result:
                    result["properties"] = {
                        k: self.resolve(v) for k, v in result["properties"].items()
                    }
                return result

            # Safe to expand - convert mapping to list of schema refs
            variant_schemas = []
            for variant_ref in mapping.values():
                if isinstance(variant_ref, str):
                    variant_schemas.append({"$ref": variant_ref})
                else:
                    variant_schemas.append(variant_ref)
            # Merge all variants plus the base schema's own properties
            merged = self._merge_schemas(variant_schemas)
            # Also merge any properties defined directly on the base
            if "properties" in schema:
                base_props = schema["properties"]
                if "properties" not in merged:
                    merged["properties"] = {}
                merged["properties"].update(base_props)
            merged["x-discriminator"] = schema["discriminator"]
            return merged

        # Handle allOf (merge all schemas)
        if "allOf" in schema:
            return self._merge_schemas(schema["allOf"])

        # Handle oneOf/anyOf (merge all schemas to capture all possible fields)
        # This ensures we create tables for fields that exist in ANY variant
        # of a discriminated union (e.g., both Internal and External contributor types)
        if "oneOf" in schema:
            merged = self._merge_schemas(schema["oneOf"])
            # Preserve discriminator info if available
            if "discriminator" in schema:
                merged["x-discriminator"] = schema["discriminator"]
            return merged

        if "anyOf" in schema:
            return self._merge_schemas(schema["anyOf"])

        # Recursively resolve nested schemas
        result = dict(schema)
        if "properties" in result:
            result["properties"] = {
                k: self.resolve(v) for k, v in result["properties"].items()
            }
        if "items" in result:
            result["items"] = self.resolve(result["items"])

        return result
    
    def _resolve_ref(self, ref: str) -> Dict[str, Any]:
        """Resolve a $ref pointer"""
        if not ref.startswith("#/"):
            raise ValueError(f"External $ref not supported: {ref}")
        
        parts = ref.lstrip("#/").split("/")
        node = {"components": self.components}
        
        for part in parts:
            if part == "components":
                node = node["components"]
            else:
                node = node.get(part, {})
        
        # Recursively resolve in case the referenced schema has refs
        return self.resolve(node)
    
    def _merge_schemas(self, schemas: List[Dict[str, Any]]) -> Dict[str, Any]:
        """Merge multiple schemas (for allOf, anyOf)"""
        result: Dict[str, Any] = {}
        all_properties: Dict[str, Dict] = {}
        
        for s in schemas:
            resolved = self.resolve(s)
            
            # Merge properties
            if "properties" in resolved:
                all_properties.update(resolved["properties"])
            
            # Merge other fields (last one wins)
            for k, v in resolved.items():
                if k != "properties":
                    result[k] = v
        
        if all_properties:
            result["properties"] = all_properties
        
        return result

# -------------------------
# Field Path Builder
# -------------------------

@dataclass
class FieldPath:
    """Represents a path to a field in nested structure"""
    parts: List[str]
    schema: Dict[str, Any]
    is_array: bool = False
    array_depth: int = 0
    
    def to_column_name(self, separator: str = "_") -> str:
        """Convert path to column name"""
        return separator.join(self.parts)
    
    def __str__(self) -> str:
        return ".".join(self.parts)

class SchemaFlattener:
    """Flattens nested schemas into table structures"""
    
    def __init__(self, resolver: SchemaResolver, config: Config):
        self.resolver = resolver
        self.config = config
    
    def _is_locale_dict_schema(self, schema: Dict[str, Any]) -> bool:
        """Check if schema represents a locale dictionary (FormattedLocalizedString, etc.)"""
        # Check for additionalProperties pattern used by FormattedLocalizedString
        if schema.get("type") == "object" and "additionalProperties" in schema:
            # Check if description mentions "localized" or it's a string dict
            desc = schema.get("description", "").lower()
            if "localized" in desc or "locale" in desc:
                return True
            # Check if additionalProperties is string type
            add_props = schema.get("additionalProperties", {})
            if isinstance(add_props, dict) and add_props.get("type") == "string":
                return True
        return False

    def _is_formatted_string_schema(self, schema: Dict[str, Any]) -> bool:
        """Check if schema represents FormattedString {value: string}"""
        if schema.get("type") != "object":
            return False
        props = schema.get("properties", {})
        # FormattedString has a single 'value' property of type string
        if "value" in props:
            value_schema = props["value"]
            if isinstance(value_schema, dict) and value_schema.get("type") == "string":
                return True
        return False

    def flatten_schema(self, schema: Dict[str, Any], prefix: str = "",
                       depth: int = 0) -> Dict[str, FieldPath]:
        """
        Recursively flatten schema into a dict of column_name -> FieldPath

        Args:
            schema: OpenAPI schema to flatten
            prefix: Current path prefix
            depth: Current recursion depth

        Returns:
            Dict mapping column names to field paths
        """
        if depth > self.config.max_depth:
            return {}

        schema = self.resolver.resolve(schema)
        fields: Dict[str, FieldPath] = {}

        schema_type = schema.get("type")
        properties = schema.get("properties", {})
        
        if schema_type == "object" and properties:
            for prop_name, prop_schema in properties.items():
                prop_schema = self.resolver.resolve(prop_schema)
                field_prefix = f"{prefix}{prop_name}" if prefix else prop_name

                prop_type = prop_schema.get("type")

                if is_scalar(prop_schema):
                    # Scalar field - add directly
                    path_parts = field_prefix.split(self.config.prefix_separator)
                    fields[field_prefix] = FieldPath(
                        parts=path_parts,
                        schema=prop_schema,
                        is_array=False
                    )

                elif self._is_locale_dict_schema(prop_schema):
                    # Locale dictionary - create columns for common locales
                    # These will be populated at runtime by DataUnpacker
                    for locale in ["en_GB", "da_DK"]:
                        locale_field = f"{field_prefix}_{locale.lower()}"
                        path_parts = locale_field.split(self.config.prefix_separator)
                        fields[locale_field] = FieldPath(
                            parts=path_parts,
                            schema={"type": "string"},  # locale values are strings
                            is_array=False
                        )

                elif self._is_formatted_string_schema(prop_schema):
                    # FormattedString {value: "text"} - create a single column
                    # The DataUnpacker will extract the value field at runtime
                    path_parts = field_prefix.split(self.config.prefix_separator)
                    fields[field_prefix] = FieldPath(
                        parts=path_parts,
                        schema={"type": "string"},  # value is a string
                        is_array=False
                    )

                elif prop_type == "object":
                    # Nested object - recurse
                    nested_fields = self.flatten_schema(
                        prop_schema,
                        prefix=field_prefix + self.config.prefix_separator,
                        depth=depth + 1
                    )
                    fields.update(nested_fields)

                elif prop_type == "array":
                    # Array - will be handled as child table
                    # Store info about the array itself
                    items_schema = self.resolver.resolve(prop_schema.get("items", {}))
                    path_parts = field_prefix.split(self.config.prefix_separator)
                    fields[field_prefix] = FieldPath(
                        parts=path_parts,
                        schema=items_schema,
                        is_array=True,
                        array_depth=1
                    )
        
        return fields

# -------------------------
# Enhanced SQLite Schema Builder
# -------------------------

@dataclass
class TableSchema:
    """Represents a table schema"""
    name: str
    columns: Dict[str, str]  # column_name -> SQL type
    primary_key: List[str]
    foreign_keys: List[Tuple[str, str, str]]  # (column, ref_table, ref_column)
    indexes: List[List[str]]  # columns to index
    parent_table: Optional[str] = None

class EnhancedSQLiteBuilder:
    """Builds SQLite schema with deep unpacking support"""

    def __init__(self, conn: sqlite3.Connection, resolver: SchemaResolver, config: Config):
        self.conn = conn
        self.resolver = resolver
        self.config = config
        self.flattener = SchemaFlattener(resolver, config)
        self.created_tables: Set[str] = set()
        self.table_schemas: Dict[str, TableSchema] = {}

    def _get_child_table_name(self, parent_table: str, field_name: str) -> str:
        """Generate child table name from parent and field name"""
        field_snake = snake(field_name, self.config.preserve_case)
        return f"{parent_table}{self.config.array_table_separator}{field_snake}"
    
    def create_parent_table(self, base_name: str, item_schema: Dict[str, Any]) -> TableSchema:
        """Create main table for an endpoint with deep unpacking"""
        table_name = snake(base_name, self.config.preserve_case)
        
        if table_name in self.created_tables:
            return self.table_schemas[table_name]
        
        # Flatten the schema
        flat_fields = self.flattener.flatten_schema(item_schema)
        
        # Determine primary key
        pk_candidates = ["uuid", "id", "pureId"]
        pk_field = None
        for candidate in pk_candidates:
            if candidate in flat_fields or snake(candidate) in flat_fields:
                pk_field = snake(candidate, self.config.preserve_case)
                break
        
        if not pk_field:
            pk_field = "uuid"  # fallback
        
        # Build columns
        columns = {
            pk_field: "TEXT",
            "raw_json": "TEXT",
            "hash": "TEXT",
            "first_seen": "TEXT",
            "last_seen": "TEXT"
        }
        
        # Add flattened scalar columns
        for field_name, field_path in flat_fields.items():
            if not field_path.is_array:  # Skip arrays - they become child tables
                col_name = snake(field_name, self.config.preserve_case)
                if col_name not in columns:
                    columns[col_name] = sql_type_for(
                        field_path.schema, 
                        field_name, 
                        self.config
                    )
        
        # Create table
        col_defs = []
        for col_name, col_type in columns.items():
            if col_name == pk_field:
                col_defs.append(f"{safe_ident(col_name)} {col_type} PRIMARY KEY")
            else:
                col_defs.append(f"{safe_ident(col_name)} {col_type}")
        
        ddl = f"CREATE TABLE IF NOT EXISTS {safe_ident(table_name)} (\n  "
        ddl += ",\n  ".join(col_defs)
        ddl += "\n);"
        
        if self.config.verbose:
            print(f"Creating table {table_name} with {len(columns)} columns")
        
        self.conn.execute(ddl)
        self.conn.commit()
        
        # Create indexes
        if self.config.create_indexes:
            # Index on hash for deduplication
            self._create_index(table_name, ["hash"])
            # Index on timestamp fields
            self._create_index(table_name, ["last_seen"])
        
        schema = TableSchema(
            name=table_name,
            columns=columns,
            primary_key=[pk_field],
            foreign_keys=[],
            indexes=[["hash"], ["last_seen"]]
        )
        
        self.created_tables.add(table_name)
        self.table_schemas[table_name] = schema
        
        return schema
    
    def create_child_table(self, parent_table: str, field_name: str,
                          items_schema: Dict[str, Any], depth: int = 1) -> TableSchema:
        """Create child table for array field with deep unpacking"""
        items_schema = self.resolver.resolve(items_schema)

        # Generate table name using helper method
        table_name = self._get_child_table_name(parent_table, field_name)

        if table_name in self.created_tables:
            return self.table_schemas[table_name]

        # Parent FK columns - need to match parent's primary key
        parent_schema = self.table_schemas[parent_table]
        parent_pk_cols = parent_schema.primary_key

        columns = {}

        # Add FK columns for each parent PK column
        fk_col_names = []
        for parent_pk_col in parent_pk_cols:
            fk_col_name = f"{parent_table}_{parent_pk_col}"
            columns[fk_col_name] = "TEXT"
            fk_col_names.append(fk_col_name)

        # Create composite foreign key constraint
        # Format: [(fk_columns_list, ref_table, ref_columns_list)]
        foreign_keys = [(fk_col_names, parent_table, parent_pk_cols)]

        # Add ordinal column for array ordering
        columns["ord"] = "INTEGER"

        # Primary key is all parent FK columns + ord
        primary_key = fk_col_names + ["ord"]
        
        if is_scalar(items_schema):
            # Array of scalars
            columns["value"] = sql_type_for(items_schema, field_name, self.config)
        else:
            # Array of objects - flatten recursively
            flat_fields = self.flattener.flatten_schema(items_schema, depth=depth)
            
            for field_path_name, field_path in flat_fields.items():
                if not field_path.is_array:
                    col_name = snake(field_path_name, self.config.preserve_case)
                    if col_name not in columns:
                        columns[col_name] = sql_type_for(
                            field_path.schema,
                            field_path_name,
                            self.config
                        )
            
            # Always store raw JSON for complex objects
            columns["raw_json"] = "TEXT"
        
        # Create table
        col_defs = []
        for col_name, col_type in columns.items():
            col_defs.append(f"{safe_ident(col_name)} {col_type}")
        
        # Add primary key constraint
        pk_cols = ", ".join(safe_ident(c) for c in primary_key)
        col_defs.append(f"PRIMARY KEY ({pk_cols})")

        # Add foreign key constraints (now supporting composite FKs)
        for fk_entry in foreign_keys:
            if isinstance(fk_entry[0], list):
                # Composite foreign key: (list of fk_cols, ref_table, list of ref_cols)
                fk_cols, ref_table, ref_cols = fk_entry
                fk_cols_str = ", ".join(safe_ident(c) for c in fk_cols)
                ref_cols_str = ", ".join(safe_ident(c) for c in ref_cols)
                col_defs.append(
                    f"FOREIGN KEY ({fk_cols_str}) "
                    f"REFERENCES {safe_ident(ref_table)}({ref_cols_str}) "
                    f"ON DELETE CASCADE"
                )
            else:
                # Single foreign key (backward compatibility): (fk_col, ref_table, ref_col)
                fk_col, ref_table, ref_col = fk_entry
                col_defs.append(
                    f"FOREIGN KEY ({safe_ident(fk_col)}) "
                    f"REFERENCES {safe_ident(ref_table)}({safe_ident(ref_col)}) "
                    f"ON DELETE CASCADE"
                )
        
        ddl = f"CREATE TABLE IF NOT EXISTS {safe_ident(table_name)} (\n  "
        ddl += ",\n  ".join(col_defs)
        ddl += "\n);"
        
        if self.config.verbose:
            print(f"Creating child table {table_name} with {len(columns)} columns")
        
        self.conn.execute(ddl)
        self.conn.commit()
        
        # Create indexes
        # Index on the first parent FK column (typically the main parent UUID)
        if self.config.create_indexes and fk_col_names:
            self._create_index(table_name, [fk_col_names[0]])

        schema = TableSchema(
            name=table_name,
            columns=columns,
            primary_key=primary_key,
            foreign_keys=foreign_keys,
            indexes=[fk_col_names[:1]] if fk_col_names else [],
            parent_table=parent_table
        )

        self.created_tables.add(table_name)
        self.table_schemas[table_name] = schema

        # Recursively create child tables for nested arrays in the items schema
        if not is_scalar(items_schema) and depth < self.config.max_depth:
            props = items_schema.get("properties", {})
            for nested_field_name, nested_field_schema in props.items():
                nested_field_schema = self.resolver.resolve(nested_field_schema)
                if nested_field_schema.get("type") == "array":
                    nested_items_schema = self.resolver.resolve(
                        nested_field_schema.get("items", {})
                    )
                    # Create nested child table
                    self.create_child_table(
                        table_name,
                        nested_field_name,
                        nested_items_schema,
                        depth=depth + 1
                    )
                    if self.config.verbose:
                        nested_table = self._get_child_table_name(table_name, nested_field_name)
                        print(f"  Created nested child table: {nested_table}")

        return schema
    
    def _create_index(self, table: str, columns: List[str]) -> None:
        """Create index on specified columns"""
        idx_name = f"idx_{table}_{'_'.join(columns)}"
        col_list = ", ".join(safe_ident(c) for c in columns)
        try:
            self.conn.execute(
                f"CREATE INDEX IF NOT EXISTS {safe_ident(idx_name)} "
                f"ON {safe_ident(table)} ({col_list})"
            )
        except sqlite3.OperationalError:
            pass  # Index might already exist

# -------------------------
# Enhanced Data Unpacker
# -------------------------

class DataUnpacker:
    """Unpacks nested data structures recursively with Pure-specific patterns"""
    
    def __init__(self, config: Config):
        self.config = config
    
    def _is_locale_dict(self, value: Any) -> bool:
        """Check if value is a dict with locale keys (en_GB, da_DK, etc.)"""
        if not isinstance(value, dict) or not value:
            return False
        # Check if keys look like locales
        for key in value.keys():
            if "_" in key and len(key) == 5:  # e.g., en_GB, da_DK
                return True
        return False
    
    def _is_localized_array(self, value: Any) -> bool:
        """Check if value is an array with locale/value dicts"""
        if not isinstance(value, list) or not value:
            return False
        first = value[0]
        return isinstance(first, dict) and "locale" in first and "value" in first
    
    def _unpack_locale_dict(self, value_dict: Dict, field_key: str) -> Dict[str, Any]:
        """Unpack locale dictionary {en_GB: 'text', da_DK: 'tekst'}"""
        result = {}
        for locale, text in value_dict.items():
            clean_locale = locale.replace("-", "_")
            col_name = f"{field_key}_{clean_locale}"
            result[col_name] = text
        return result
    
    def _unpack_localized_array(self, values: List[Dict], field_key: str) -> Dict[str, Any]:
        """Unpack array [{locale: 'en', value: 'text'}, ...]"""
        result = {}
        for item in values:
            if not isinstance(item, dict):
                continue
            locale = item.get("locale", "").replace("-", "_")
            text = item.get("value", "")
            if locale and text:
                col_name = f"{field_key}_{locale}"
                result[col_name] = text
        return result
    
    def _is_person_reference(self, value: Any) -> bool:
        """Check if this is a person reference object {systemName: 'Person', uuid: '...'}"""
        if not isinstance(value, dict):
            return False
        return value.get("systemName") == "Person" and "uuid" in value
    
    def _is_org_reference(self, value: Any) -> bool:
        """Check if this is an org reference object {systemName: 'Organization', uuid: '...'}"""
        if not isinstance(value, dict):
            return False
        return value.get("systemName") == "Organization" and "uuid" in value
    
    def _extract_person_name(self, name_obj: Any) -> str:
        """Extract full name from Pure name object"""
        if not isinstance(name_obj, dict):
            return ""
        first = name_obj.get("firstName", "")
        last = name_obj.get("lastName", "")
        return f"{first} {last}".strip()
    
    def unpack_object(self, data: Dict[str, Any], schema: Dict[str, Any], 
                     prefix: str = "", depth: int = 0) -> Dict[str, Any]:
        """
        Recursively unpack nested object with Pure-specific patterns
        
        Handles:
        - Locale dicts: {en_GB: 'text', da_DK: 'tekst'}
        - Locale arrays: [{locale: 'en', value: 'text'}]
        - Nested locale values: {value: {en_GB: 'text'}}
        - Nested locale terms: {term: {en_GB: 'text'}}
        - Person references: {systemName: 'Person', uuid: '...'}
        - Person with name: {name: {firstName, lastName}, uuid: '...'}
        """
        if depth > self.config.max_depth:
            return {}
        
        result = {}
        properties = schema.get("properties", {})
        
        for key, value in data.items():
            if value is None:
                continue
            
            prop_schema = properties.get(key, {})
            field_key = f"{prefix}{key}" if prefix else key
            prop_type = prop_schema.get("type")
            
            # Pattern 1: Localized array [{locale, value}]
            if self._is_localized_array(value):
                localized = self._unpack_localized_array(value, field_key)
                result.update(localized)
                continue
            
            # Pattern 2: Person reference {systemName: 'Person', uuid: '...'}
            if self._is_person_reference(value):
                result[f"{field_key}_uuid"] = value.get("uuid")
                continue
            
            # Pattern 3: Organization reference
            if self._is_org_reference(value):
                result[f"{field_key}_uuid"] = value.get("uuid")
                continue
            
            # Pattern 4: Object with 'value' field containing locales
            if isinstance(value, dict) and "value" in value:
                nested_value = value["value"]
                if self._is_locale_dict(nested_value):
                    # description: {value: {en_GB: 'text', da_DK: 'tekst'}, type: {...}}
                    localized = self._unpack_locale_dict(nested_value, field_key)
                    result.update(localized)
                    # Also unpack other fields in the object (like 'type')
                    for sub_key, sub_value in value.items():
                        if sub_key != "value" and sub_value is not None:
                            sub_field_key = f"{field_key}_{sub_key}"
                            if isinstance(sub_value, dict):
                                sub_unpacked = self.unpack_object(
                                    sub_value, {}, 
                                    prefix=sub_field_key + self.config.prefix_separator,
                                    depth=depth + 1
                                )
                                result.update(sub_unpacked)
                            else:
                                result[sub_field_key] = sub_value
                    continue
                elif isinstance(nested_value, (str, int, float, bool)):
                    # Simple value field
                    result[field_key] = nested_value
                    continue
            
            # Pattern 5: Object with 'term' field containing locales
            if isinstance(value, dict) and "term" in value:
                nested_term = value["term"]
                if self._is_locale_dict(nested_term):
                    # role: {term: {en_GB: 'Recipient', da_DK: 'Modtager'}, uri: '...'}
                    localized = self._unpack_locale_dict(nested_term, f"{field_key}_term")
                    result.update(localized)
                    # Unpack other fields like 'uri'
                    for sub_key, sub_value in value.items():
                        if sub_key != "term" and sub_value is not None:
                            result[f"{field_key}_{sub_key}"] = sub_value
                    continue
            
            # Pattern 6: Person association with name object
            if isinstance(value, dict) and "name" in value:
                name_obj = value.get("name")
                if isinstance(name_obj, dict) and ("firstName" in name_obj or "lastName" in name_obj):
                    # Extract person name
                    full_name = self._extract_person_name(name_obj)
                    if full_name:
                        result[f"{field_key}_name"] = full_name
                    
                    # Also extract other person fields
                    for sub_key, sub_value in value.items():
                        if sub_key == "name":
                            continue
                        elif sub_key == "person" and self._is_person_reference(sub_value):
                            result[f"{field_key}_person_uuid"] = sub_value.get("uuid")
                        elif isinstance(sub_value, dict):
                            sub_unpacked = self.unpack_object(
                                sub_value, {},
                                prefix=f"{field_key}_{sub_key}{self.config.prefix_separator}",
                                depth=depth + 1
                            )
                            result.update(sub_unpacked)
                        else:
                            result[f"{field_key}_{sub_key}"] = sub_value
                    continue
            
            # Pattern 7: Locale dict (direct)
            if self._is_locale_dict(value):
                localized = self._unpack_locale_dict(value, field_key)
                result.update(localized)
                continue
            
            # Pattern 8: Scalar values
            if isinstance(value, (str, int, float, bool)):
                result[field_key] = value
                continue
            
            # Pattern 9: Nested object (recurse)
            if isinstance(value, dict):
                nested = self.unpack_object(
                    value,
                    prop_schema,
                    prefix=field_key + self.config.prefix_separator,
                    depth=depth + 1
                )
                result.update(nested)
                continue
            
            # Pattern 10: Arrays (will be handled as child tables elsewhere)
            if isinstance(value, list):
                pass  # Skip - arrays become child tables
                continue
            
            # Fallback: Store as JSON
            result[field_key] = json.dumps(value)
        
        return result

# -------------------------
# Enhanced Upsert Logic
# -------------------------

def upsert_parent(cur: sqlite3.Cursor, table_schema: TableSchema, 
                 pk_value: str, raw_json: Dict[str, Any], 
                 flat_data: Dict[str, Any], config: Config) -> None:
    """Upsert parent table record with unpacked data"""
    pk_field = table_schema.primary_key[0]
    h = sha256_json(raw_json)
    first_seen = now_iso()
    last_seen = first_seen
    
    # Check existing hash
    cur.execute(
        f'SELECT {safe_ident("hash")}, {safe_ident("first_seen")} '
        f'FROM {safe_ident(table_schema.name)} '
        f'WHERE {safe_ident(pk_field)}=?',
        (pk_value,)
    )
    row = cur.fetchone()
    
    # Prepare column data
    col_data = {
        pk_field: pk_value,
        "raw_json": json.dumps(raw_json),
        "hash": h,
        "last_seen": last_seen
    }
    
    # Add flattened data (only columns that exist in table)
    for key, value in flat_data.items():
        col_name = snake(key, config.preserve_case)
        if col_name in table_schema.columns:
            col_data[col_name] = value
    
    if row is None:
        # Insert new record
        col_data["first_seen"] = first_seen
        
        columns = list(col_data.keys())
        placeholders = ",".join(["?"] * len(columns))
        col_names = ",".join(safe_ident(c) for c in columns)
        
        cur.execute(
            f'INSERT INTO {safe_ident(table_schema.name)} ({col_names}) '
            f'VALUES ({placeholders})',
            [col_data[c] for c in columns]
        )
    else:
        existing_hash, existing_first_seen = row
        col_data["first_seen"] = existing_first_seen
        
        if existing_hash != h:
            # Data changed - update all fields
            set_parts = [f"{safe_ident(k)}=?" for k in col_data.keys() if k != pk_field]
            set_clause = ", ".join(set_parts)
            
            cur.execute(
                f'UPDATE {safe_ident(table_schema.name)} '
                f'SET {set_clause} '
                f'WHERE {safe_ident(pk_field)}=?',
                [col_data[k] for k in col_data.keys() if k != pk_field] + [pk_value]
            )
        else:
            # Just update last_seen
            cur.execute(
                f'UPDATE {safe_ident(table_schema.name)} '
                f'SET {safe_ident("last_seen")}=? '
                f'WHERE {safe_ident(pk_field)}=?',
                (last_seen, pk_value)
            )

def insert_child_array(cur: sqlite3.Cursor, child_schema: TableSchema,
                      parent_pk_values: Union[str, Dict[str, Any]], items_schema: Dict[str, Any],
                      array_data: List[Any], unpacker: DataUnpacker,
                      builder: 'EnhancedSQLiteBuilder' = None) -> None:
    """Insert array data into child table with deep unpacking

    Args:
        parent_pk_values: Either a string (for single-column PK) or dict mapping parent's PK column names to values
    """
    if not array_data and unpacker.config.skip_empty_arrays:
        return

    # Get the FK columns from the child schema's foreign key metadata
    # foreign_keys format: [([fk_cols], ref_table, [ref_cols])] for composite FK
    # We need to map parent PK values to child FK columns
    if not child_schema.foreign_keys:
        raise ValueError(f"No foreign keys defined for child table {child_schema.name}")

    fk_entry = child_schema.foreign_keys[0]  # First (and typically only) FK
    if isinstance(fk_entry[0], list):
        # Composite FK
        fk_cols, ref_table, ref_pk_cols = fk_entry
    else:
        # Single FK (backward compatibility)
        fk_col, ref_table, ref_pk_col = fk_entry
        fk_cols = [fk_col]
        ref_pk_cols = [ref_pk_col]

    # Normalize parent_pk_values to dict mapping ref_pk_col → value
    if isinstance(parent_pk_values, str):
        # Single value provided - assume single PK column
        if len(ref_pk_cols) != 1:
            raise ValueError(f"Single value provided but parent has {len(ref_pk_cols)} PK columns")
        ref_pk_dict = {ref_pk_cols[0]: parent_pk_values}
    else:
        # Dict provided - use as-is
        ref_pk_dict = parent_pk_values

    # Map parent PK values to child FK columns
    parent_pk_dict = {}
    for fk_col, ref_pk_col in zip(fk_cols, ref_pk_cols):
        if ref_pk_col in ref_pk_dict:
            parent_pk_dict[fk_col] = ref_pk_dict[ref_pk_col]
        else:
            raise ValueError(f"Parent PK column {ref_pk_col} not found in provided values: {ref_pk_dict}")

    # Build WHERE clause for deletion
    where_conditions = " AND ".join([f"{safe_ident(k)}=?" for k in parent_pk_dict.keys()])
    where_values = list(parent_pk_dict.values())

    cur.execute(
        f'DELETE FROM {safe_ident(child_schema.name)} WHERE {where_conditions}',
        where_values
    )

    if not array_data:
        return

    for i, item in enumerate(array_data):
        if item is None:
            continue

        # Start with parent FK values and ordinal
        row_data = dict(parent_pk_dict)
        row_data["ord"] = i

        # Build composite key values for nested child tables
        # Nested children will reference this row's PK, which consists of:
        # - The FK columns from this table (which reference parent's PK)
        # - The ord column
        # We need to pass values keyed by THIS table's PK column names
        child_row_pk_dict = {}

        # Get this table's PK columns (FK columns + ord)
        this_table_pk_cols = child_schema.primary_key

        # Build the dict with PK column names and their values
        for pk_col in this_table_pk_cols:
            if pk_col == "ord":
                child_row_pk_dict[pk_col] = i
            elif pk_col in parent_pk_dict:
                child_row_pk_dict[pk_col] = parent_pk_dict[pk_col]
            else:
                # This shouldn't happen if schema is correct
                raise ValueError(f"PK column {pk_col} not found in parent values")

        if is_scalar(items_schema):
            # Scalar array item
            row_data["value"] = item
        else:
            # Complex object - unpack recursively
            if isinstance(item, dict):
                flat_item = unpacker.unpack_object(item, items_schema)

                # Map to columns
                for key, value in flat_item.items():
                    col_name = snake(key, unpacker.config.preserve_case)
                    if col_name in child_schema.columns:
                        row_data[col_name] = value

                # Store raw JSON if column exists
                if "raw_json" in child_schema.columns:
                    row_data["raw_json"] = json.dumps(item)

        # Insert row
        columns = list(row_data.keys())
        placeholders = ",".join(["?"] * len(columns))
        col_names = ",".join(safe_ident(c) for c in columns)

        cur.execute(
            f'INSERT INTO {safe_ident(child_schema.name)} ({col_names}) '
            f'VALUES ({placeholders})',
            [row_data[c] for c in columns]
        )

        # Handle nested arrays within this item
        if builder and isinstance(item, dict) and not is_scalar(items_schema):
            props = items_schema.get("properties", {})
            for nested_field_name, nested_field_schema in props.items():
                nested_field_schema = builder.resolver.resolve(nested_field_schema)
                if nested_field_schema.get("type") == "array" and nested_field_name in item:
                    nested_table_name = builder._get_child_table_name(
                        child_schema.name,
                        nested_field_name
                    )
                    nested_child_schema = builder.table_schemas.get(nested_table_name)
                    if nested_child_schema:
                        nested_array_data = item.get(nested_field_name, [])
                        nested_items_schema = builder.resolver.resolve(
                            nested_field_schema.get("items", {})
                        )
                        # Recursively insert nested array using composite PK dict
                        insert_child_array(
                            cur,
                            nested_child_schema,
                            child_row_pk_dict,
                            nested_items_schema,
                            nested_array_data,
                            unpacker,
                            builder
                        )

# -------------------------
# HTTP Client (unchanged)
# -------------------------

class HttpClient:
    """HTTP client with rate limiting and retries"""
    
    def __init__(self, base_url: str, api_key: str, rps: float, timeout: int, retry_max: int):
        self.base = base_url.rstrip("/")
        self.session = requests.Session()
        self.session.headers.update({"Accept": "application/json", "api-key": api_key})
        self.rps = max(0.1, rps)
        self.timeout = timeout
        self.retry_max = retry_max
        self._last_call = 0.0
    
    def _rate_limit(self):
        min_interval = 1.0 / self.rps
        now = time.time()
        delta = now - self._last_call
        if delta < min_interval:
            time.sleep(min_interval - delta)
        self._last_call = time.time()
    
    def get(self, path: str, params: Dict[str, Any]) -> Dict[str, Any]:
        url = f"{self.base}{path}"
        backoff = 1.0
        for attempt in range(1, self.retry_max + 1):
            self._rate_limit()
            try:
                resp = self.session.get(url, params=params, timeout=self.timeout)
                if 200 <= resp.status_code < 300:
                    return resp.json()
                if resp.status_code in (429, 500, 502, 503, 504):
                    time.sleep(backoff)
                    backoff = min(backoff * 2, 30)
                    continue
                raise RuntimeError(f"HTTP {resp.status_code}: {resp.text[:500]}")
            except requests.RequestException as e:
                if attempt == self.retry_max:
                    raise
                time.sleep(backoff)
                backoff = min(backoff * 2, 30)
        raise RuntimeError("Unreachable")

# -------------------------
# OpenAPI Endpoint Discovery
# -------------------------

@dataclass
class ListEndpoint:
    """Represents a paginated list endpoint"""
    path: str
    item_schema_ref: Optional[str]
    item_schema: Dict[str, Any]
    name: str
    page_param_size: str
    page_param_offset: str

def discover_list_endpoints(openapi: Dict[str, Any], resolver: SchemaResolver) -> List[ListEndpoint]:
    """Discover all list endpoints from OpenAPI spec"""
    paths = openapi.get("paths", {})
    endpoints: List[ListEndpoint] = []
    
    for path, ops in paths.items():
        get_op = ops.get("get")
        if not get_op:
            continue
        
        # Look for size/offset params (pagination)
        params = get_op.get("parameters", [])
        size_param = None
        offset_param = None
        
        for p in params:
            name = p.get("name", "")
            if name.lower() == "size":
                size_param = name
            if name.lower() == "offset":
                offset_param = name
        
        if not (size_param and offset_param):
            continue  # Not a paginated list endpoint
        
        # Get response schema
        responses = get_op.get("responses", {})
        ok = responses.get("200") or responses.get("201")
        if not ok:
            continue
        
        content = ok.get("content", {})
        app_json = content.get("application/json")
        if not app_json:
            continue
        
        schema = app_json.get("schema", {})
        schema = resolver.resolve(schema)
        
        # Expect object with "items" array property
        if schema.get("type") != "object":
            continue
        
        props = schema.get("properties", {})
        items_prop = props.get("items")
        if not items_prop or items_prop.get("type") != "array":
            continue
        
        # Get item schema
        items_schema = items_prop.get("items", {})
        items_schema = resolver.resolve(items_schema)
        
        # Determine endpoint name
        name = items_schema.get("title") or path.strip("/").split("/")[-1]
        
        endpoints.append(ListEndpoint(
            path=path,
            item_schema_ref=None,
            item_schema=items_schema,
            name=snake(name),
            page_param_size=size_param,
            page_param_offset=offset_param,
        ))
    
    return endpoints

# -------------------------
# Main Ingestion Logic
# -------------------------

def choose_pk(schema: Dict[str, Any], item: Dict[str, Any]) -> Tuple[str, str]:
    """Choose primary key field and value from item"""
    candidates = ["uuid", "id", "pureId"]
    for field in candidates:
        if field in item:
            return snake(field), str(item[field])
    
    # Fallback: generate synthetic ID from hash
    synthetic = sha256_json(item)[:16]
    return "uuid", synthetic

def ingest_endpoint(conn: sqlite3.Connection, client: HttpClient,
                   endpoint: ListEndpoint, resolver: SchemaResolver,
                   config: Config, page_size: int, max_items: Optional[int] = None) -> Dict[str, Any]:
    """Ingest data from a single endpoint

    Args:
        max_items: Maximum number of items to ingest (for testing). None = ingest all.

    Returns:
        Dict with statistics: {items: int, duration: float, errors: int, tables: int}
    """
    start_time = time.time()
    error_count = 0
    tables_created = 0

    print(f"\n{'='*60}")
    print(f"Ingesting endpoint: {endpoint.path}")
    logging.info(f"Starting ingestion of {endpoint.path}")
    if max_items:
        print(f"LIMIT: Will ingest maximum {max_items} items")
        logging.info(f"Limit set to {max_items} items")
    print(f"{'='*60}\n")
    
    # Create schema builder
    builder = EnhancedSQLiteBuilder(conn, resolver, config)
    unpacker = DataUnpacker(config)
    
    # Create parent table
    parent_schema = builder.create_parent_table(endpoint.name, endpoint.item_schema)
    tables_created += 1
    print(f"Created parent table: {parent_schema.name}")
    logging.info(f"Created parent table: {parent_schema.name}")

    # Identify array fields and create child tables
    item_schema = resolver.resolve(endpoint.item_schema)
    props = item_schema.get("properties", {})
    array_fields: Dict[str, Dict[str, Any]] = {}

    for prop_name, prop_schema in props.items():
        prop_schema = resolver.resolve(prop_schema)
        if prop_schema.get("type") == "array":
            items_schema = resolver.resolve(prop_schema.get("items", {}))
            array_fields[prop_name] = items_schema
            child_schema = builder.create_child_table(
                parent_schema.name,
                prop_name,
                items_schema
            )
            tables_created += 1
            print(f"Created child table: {child_schema.name}")
            logging.info(f"Created child table: {child_schema.name}")
    
    # Paginate through API
    offset = 0
    total = None
    item_count = 0
    cur = conn.cursor()
    page_count = 0
    last_progress_time = time.time()

    logging.info(f"Starting pagination for {endpoint.path}")

    while True:
        page_start_time = time.time()
        try:
            data = client.get(endpoint.path, {
                endpoint.page_param_size: page_size,
                endpoint.page_param_offset: offset
            })
            page_count += 1
        except Exception as e:
            error_count += 1
            error_msg = f"Error fetching page at offset {offset}: {e}"
            print(error_msg)
            logging.error(error_msg)
            break

        total = data.get("count", total)
        items = data.get("items") or []

        if not items:
            logging.info(f"No more items at offset {offset}")
            break

        page_items_processed = 0
        page_items_failed = 0

        for item in items:
            try:
                item_count += 1

                # Determine primary key
                pk_field, pk_value = choose_pk(item_schema, item)

                # Unpack item recursively
                flat_data = unpacker.unpack_object(item, item_schema)

                # Upsert parent record
                upsert_parent(cur, parent_schema, pk_value, item, flat_data, config)

                # Insert array children
                for field_name, items_schema in array_fields.items():
                    child_table_name = builder._get_child_table_name(parent_schema.name, field_name)
                    child_schema = builder.table_schemas.get(child_table_name)
                    if child_schema:
                        array_data = item.get(field_name, [])
                        insert_child_array(
                            cur, child_schema, pk_value,
                            items_schema, array_data, unpacker, builder
                        )

                page_items_processed += 1

            except Exception as e:
                error_count += 1
                page_items_failed += 1
                error_msg = f"Error processing item {item_count} (PK: {pk_value if 'pk_value' in locals() else 'unknown'}): {e}"
                print(f"  ⚠ {error_msg}")
                logging.error(error_msg)
                if config.verbose:
                    import traceback
                    logging.error(traceback.format_exc())

        conn.commit()

        page_duration = time.time() - page_start_time

        # Progress reporting
        progress_msg = f"Page {page_count}: Processed {page_items_processed} items"
        if page_items_failed > 0:
            progress_msg += f" ({page_items_failed} failed)"
        progress_msg += f" in {page_duration:.1f}s"

        if total:
            percent = (item_count / total) * 100
            progress_msg += f" | Total: {item_count}/{total} ({percent:.1f}%)"

            # Estimate time remaining
            elapsed = time.time() - start_time
            if item_count > 0:
                rate = item_count / elapsed
                remaining_items = total - item_count
                if max_items and item_count + remaining_items > max_items:
                    remaining_items = max_items - item_count
                eta_seconds = remaining_items / rate if rate > 0 else 0
                eta_minutes = eta_seconds / 60
                if eta_minutes > 60:
                    progress_msg += f" | ETA: {eta_minutes/60:.1f}h"
                else:
                    progress_msg += f" | ETA: {eta_minutes:.1f}m"
        else:
            progress_msg += f" | Total: {item_count} items"

        print(progress_msg)

        # Log progress periodically (every 30 seconds)
        now = time.time()
        if now - last_progress_time >= 30:
            logging.info(progress_msg)
            last_progress_time = now

        # Check if we've reached the limit
        if max_items and item_count >= max_items:
            limit_msg = f"Reached limit of {max_items} items"
            print(limit_msg)
            logging.info(limit_msg)
            break

        offset += page_size

        # Stop condition
        if total is not None and offset >= total:
            break

    duration = time.time() - start_time

    # Final summary
    summary = f"✓ Completed {endpoint.path}: {item_count} items ingested, {tables_created} tables created, {error_count} errors, {duration:.1f}s"
    print(f"\n{summary}")
    logging.info(summary)

    return {
        "items": item_count,
        "duration": duration,
        "errors": error_count,
        "tables": tables_created,
        "pages": page_count
    }

# -------------------------
# Main Entry Point
# -------------------------

def main():
    parser = argparse.ArgumentParser(
        description="Pure API → SQLite Ingestion Tool"
    )
    parser.add_argument("--openapi", required=True, help="Path to openapi.yaml")
    parser.add_argument("--db", required=True, help="Path to SQLite database file")
    parser.add_argument("--discover", action="store_true", 
                       help="Ingest all detected list endpoints")
    parser.add_argument("--paths", nargs="*", default=None,
                       help="Specific API paths to ingest")
    parser.add_argument("--max-depth", type=int, default=5,
                       help="Maximum nesting depth for unpacking (default: 5)")
    parser.add_argument("--prefix-style", choices=["underscore", "dot"], 
                       default="underscore",
                       help="Column naming style for nested fields")
    parser.add_argument("--page-size", type=int, default=100,
                       help="Page size for API requests")
    parser.add_argument("--rps", type=float, default=3.0,
                       help="Requests per second rate limit")
    parser.add_argument("--timeout", type=int, default=60,
                       help="HTTP timeout in seconds")
    parser.add_argument("--retry-max", type=int, default=5,
                       help="Maximum retries on 5xx/429")
    parser.add_argument("--env", default=".env",
                       help="Path to .env file")
    parser.add_argument("--verbose", action="store_true",
                       help="Enable verbose output")
    parser.add_argument("--no-indexes", action="store_true",
                       help="Skip creating indexes")
    parser.add_argument("--limit", type=int, default=None,
                       help="Maximum number of items to ingest per endpoint (for testing)")

    args = parser.parse_args()

    # Set up logging
    log_filename = f"pure_ingest_{time.strftime('%Y%m%d_%H%M%S')}.log"
    logging.basicConfig(
        level=logging.INFO if not args.verbose else logging.DEBUG,
        format='%(asctime)s - %(levelname)s - %(message)s',
        handlers=[
            logging.FileHandler(log_filename),
            logging.StreamHandler() if args.verbose else logging.NullHandler()
        ]
    )
    print(f"Logging to: {log_filename}")
    logging.info("="*60)
    logging.info("Pure API Ingestion Started")
    logging.info("="*60)

    # Load .env
    env_vars = load_env_file(args.env)
    for k, v in env_vars.items():
        os.environ.setdefault(k, v)

    # Get API credentials
    base_url = os.environ.get("PURE_BASE_URL")
    api_key = os.environ.get("PURE_API_KEY")

    if not base_url or not api_key:
        print("ERROR: PURE_BASE_URL and PURE_API_KEY must be set", file=sys.stderr)
        logging.error("PURE_BASE_URL and PURE_API_KEY must be set")
        sys.exit(2)

    logging.info(f"Base URL: {base_url}")
    logging.info(f"Database: {args.db}")
    logging.info(f"OpenAPI spec: {args.openapi}")
    logging.info(f"Max depth: {args.max_depth}")
    logging.info(f"Page size: {args.page_size}")
    logging.info(f"Rate limit: {args.rps} RPS")
    if args.limit:
        logging.info(f"Item limit: {args.limit} per endpoint")

    # Create config
    config = Config(
        max_depth=args.max_depth,
        prefix_separator="." if args.prefix_style == "dot" else "_",
        create_indexes=not args.no_indexes,
        verbose=args.verbose
    )
    
    # Load OpenAPI spec
    print(f"Loading OpenAPI spec: {args.openapi}")
    logging.info(f"Loading OpenAPI spec: {args.openapi}")
    with open(args.openapi, "r", encoding="utf-8") as f:
        openapi = yaml.safe_load(f)

    # Create resolver
    components = openapi.get("components", {})
    resolver = SchemaResolver(components)

    # Discover endpoints
    print("Discovering endpoints...")
    logging.info("Discovering endpoints...")
    endpoints = discover_list_endpoints(openapi, resolver)
    print(f"Found {len(endpoints)} list endpoints")
    logging.info(f"Found {len(endpoints)} list endpoints")

    # Filter by paths if specified
    if args.paths:
        wanted = set(args.paths)
        endpoints = [e for e in endpoints if e.path in wanted]
        if not endpoints:
            print("ERROR: No matching endpoints found", file=sys.stderr)
            logging.error("No matching endpoints found")
            sys.exit(3)
        logging.info(f"Filtered to {len(endpoints)} endpoints: {[e.path for e in endpoints]}")
    elif not args.discover:
        print("ERROR: Use --discover or specify --paths", file=sys.stderr)
        logging.error("Use --discover or specify --paths")
        sys.exit(1)

    print(f"\nWill ingest {len(endpoints)} endpoints:")
    for ep in endpoints:
        print(f"  - {ep.path} → {ep.name}")
        logging.info(f"Endpoint: {ep.path} → {ep.name}")

    # Setup database
    print(f"\nOpening database: {args.db}")
    logging.info(f"Opening database: {args.db}")
    conn = sqlite3.connect(args.db)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    conn.execute("PRAGMA foreign_keys=ON;")
    logging.info("Database pragmas configured (WAL mode, foreign keys enabled)")

    # Setup HTTP client
    client = HttpClient(base_url, api_key, args.rps, args.timeout, args.retry_max)

    # Ingest each endpoint
    start_time = time.time()
    total_stats = {
        "total_items": 0,
        "total_errors": 0,
        "total_tables": 0,
        "total_pages": 0,
        "endpoints_succeeded": 0,
        "endpoints_failed": 0
    }

    for ep in endpoints:
        try:
            stats = ingest_endpoint(conn, client, ep, resolver, config, args.page_size, args.limit)
            total_stats["total_items"] += stats.get("items", 0)
            total_stats["total_errors"] += stats.get("errors", 0)
            total_stats["total_tables"] += stats.get("tables", 0)
            total_stats["total_pages"] += stats.get("pages", 0)
            total_stats["endpoints_succeeded"] += 1
        except Exception as e:
            total_stats["endpoints_failed"] += 1
            error_msg = f"✗ Error ingesting {ep.path}: {e}"
            print(f"\n{error_msg}", file=sys.stderr)
            logging.error(error_msg)
            if args.verbose:
                import traceback
                tb = traceback.format_exc()
                logging.error(tb)
                traceback.print_exc()

    conn.close()
    logging.info("Database connection closed")

    elapsed = time.time() - start_time

    # Final summary
    print(f"\n{'='*60}")
    print(f"INGESTION COMPLETE")
    print(f"{'='*60}")
    print(f"Total duration: {elapsed:.1f}s ({elapsed/60:.1f}m)")
    print(f"Endpoints processed: {total_stats['endpoints_succeeded']}/{len(endpoints)}")
    if total_stats["endpoints_failed"] > 0:
        print(f"Endpoints failed: {total_stats['endpoints_failed']}")
    print(f"Total items ingested: {total_stats['total_items']:,}")
    print(f"Total pages fetched: {total_stats['total_pages']:,}")
    print(f"Total tables created: {total_stats['total_tables']}")
    if total_stats["total_errors"] > 0:
        print(f"Total errors: {total_stats['total_errors']}")
    if total_stats["total_items"] > 0:
        avg_rate = total_stats["total_items"] / elapsed
        print(f"Average rate: {avg_rate:.1f} items/second")
    print(f"{'='*60}")
    print(f"\nLog file: {log_filename}")

    logging.info("="*60)
    logging.info("INGESTION COMPLETE")
    logging.info("="*60)
    logging.info(f"Total duration: {elapsed:.1f}s")
    logging.info(f"Endpoints processed: {total_stats['endpoints_succeeded']}/{len(endpoints)}")
    logging.info(f"Total items ingested: {total_stats['total_items']}")
    logging.info(f"Total tables created: {total_stats['total_tables']}")
    logging.info(f"Total errors: {total_stats['total_errors']}")
    logging.info("="*60)

if __name__ == "__main__":
    main()