foxhunt/migrations/003_audit_system.sql

-- ================================================================================================
-- Migration 003: Comprehensive Audit System Schema
-- Complete audit trail for regulatory compliance with immutable logging
-- Includes ML events, system events, and complete change tracking
-- ================================================================================================

-- ================================================================================================
-- AUDIT EVENT TYPES AND ENUMS
-- Comprehensive classification for all auditable events
-- ================================================================================================

CREATE TYPE audit_event_type AS ENUM (
    -- Trading audit events
    'order_created',
    'order_modified',
    'order_cancelled',
    'order_executed',
    'trade_settled',
    'position_updated',

    -- Risk management audit events
    'risk_limit_breached',
    'risk_limit_updated',
    'emergency_action_taken',
    'compliance_check_failed',
    'model_validation_failed',

    -- ML and Algorithm audit events
    'model_prediction',
    'model_training_started',
    'model_training_completed',
    'model_deployed',
    'model_rollback',
    'signal_generated',
    'algorithm_decision',
    'backtest_executed',

    -- System audit events
    'system_startup',
    'system_shutdown',
    'service_restart',
    'configuration_changed',
    'user_login',
    'user_logout',
    'permission_granted',
    'permission_revoked',
    'data_export',
    'data_import',

    -- Security audit events
    'authentication_success',
    'authentication_failure',
    'authorization_failure',
    'suspicious_activity',
    'security_breach_detected',
    'encryption_key_rotated',

    -- Market data audit events
    'market_data_received',
    'market_data_gap_detected',
    'circuit_breaker_triggered',
    'trading_halt_detected',

    -- Compliance audit events
    'regulatory_report_generated',
    'audit_trail_accessed',
    'data_retention_policy_applied',
    'compliance_validation_completed'
);

CREATE TYPE audit_severity AS ENUM (
    'trace',       -- Detailed debugging information
    'debug',       -- General debugging information
    'info',        -- General information
    'notice',      -- Normal but significant condition
    'warning',     -- Warning conditions
    'error',       -- Error conditions
    'critical',    -- Critical conditions requiring immediate attention
    'alert',       -- Action must be taken immediately
    'emergency'    -- System is unusable
);

CREATE TYPE system_component AS ENUM (
    'trading_engine',
    'risk_management',
    'market_data',
    'order_management',
    'portfolio_management',
    'ml_engine',
    'execution_engine',
    'compliance_engine',
    'authentication',
    'configuration',
    'database',
    'api_gateway',
    'user_interface',
    'reporting',
    'monitoring',
    'backup_system'
);

-- ================================================================================================
-- COMPREHENSIVE AUDIT LOG TABLE
-- Immutable audit trail for all system activities
-- ================================================================================================
CREATE TABLE audit_log (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    audit_id BIGSERIAL NOT NULL, -- Sequential audit ID for ordering
    correlation_id UUID, -- Links related audit entries

    -- Timing with nanosecond precision
    event_timestamp ns_timestamp NOT NULL, -- When the event actually occurred
    recorded_timestamp ns_timestamp NOT NULL, -- When the audit entry was created
    processing_timestamp ns_timestamp NOT NULL, -- When the system processed the event

    -- Event classification
    event_type audit_event_type NOT NULL,
    severity audit_severity NOT NULL DEFAULT 'info',
    component system_component NOT NULL,

    -- Event context and scope
    entity_type VARCHAR(100), -- Type of entity affected (order, position, user, etc.)
    entity_id UUID, -- ID of the affected entity
    parent_entity_type VARCHAR(100), -- Parent entity type
    parent_entity_id UUID, -- Parent entity ID

    -- User and session context
    user_id VARCHAR(64),
    session_id UUID,
    impersonated_user_id VARCHAR(64), -- If acting on behalf of another user
    client_id VARCHAR(100), -- Application or API client

    -- Request context
    request_id UUID, -- Original request that triggered this event
    trace_id UUID, -- Distributed tracing ID
    span_id UUID, -- Distributed tracing span ID

    -- Network and system context
    source_ip INET,
    user_agent TEXT,
    source_host VARCHAR(255),
    source_port INTEGER,

    -- Event details
    action VARCHAR(100) NOT NULL, -- Specific action taken
    resource VARCHAR(200), -- Resource or endpoint accessed
    method VARCHAR(20), -- HTTP method or operation type

    -- Data changes (for compliance)
    old_values JSONB, -- Previous state before change
    new_values JSONB, -- New state after change
    affected_fields TEXT[], -- List of fields that were modified

    -- Event payload and metadata
    event_data JSONB NOT NULL, -- Complete event details
    tags JSONB, -- Searchable tags and labels
    metadata JSONB, -- Additional metadata

    -- System and performance context
    node_id VARCHAR(50) NOT NULL, -- Which system node recorded this
    process_id INTEGER NOT NULL, -- OS process ID
    thread_id INTEGER, -- Thread ID
    execution_time_ns BIGINT, -- How long the operation took (nanoseconds)
    memory_usage_bytes BIGINT, -- Memory usage at time of event
    cpu_usage_percent DECIMAL(5,2), -- CPU usage percentage

    -- Security and integrity
    checksum VARCHAR(64) NOT NULL, -- SHA-256 hash of event content
    digital_signature TEXT, -- Digital signature for non-repudiation
    encryption_key_id VARCHAR(100), -- If event data is encrypted

    -- Compliance and retention
    retention_category VARCHAR(50) DEFAULT 'standard', -- Retention policy category
    retention_until DATE, -- When this record can be archived/deleted
    is_sensitive BOOLEAN DEFAULT FALSE, -- Contains sensitive data
    compliance_flags TEXT[], -- Regulatory compliance flags

    -- Error handling
    is_error BOOLEAN DEFAULT FALSE,
    error_code VARCHAR(50),
    error_message TEXT,
    stack_trace TEXT,

    -- Partition key for performance
    audit_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, audit_date),
    CONSTRAINT chk_audit_timestamps CHECK (
        recorded_timestamp >= event_timestamp AND
        processing_timestamp >= recorded_timestamp
    ),
    CONSTRAINT chk_execution_time CHECK (execution_time_ns IS NULL OR execution_time_ns >= 0),
    CONSTRAINT chk_cpu_usage CHECK (cpu_usage_percent IS NULL OR (cpu_usage_percent >= 0 AND cpu_usage_percent <= 100))
) PARTITION BY RANGE (audit_date);

-- ================================================================================================
-- ML EVENTS TABLE
-- Specialized tracking for machine learning operations
-- ================================================================================================
CREATE TABLE ml_events (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    model_id VARCHAR(200) NOT NULL, -- Unique model identifier
    model_version VARCHAR(50) NOT NULL,

    -- Timing
    event_timestamp ns_timestamp NOT NULL,

    -- ML event classification
    event_type VARCHAR(100) NOT NULL, -- prediction, training, validation, deployment, etc.
    event_subtype VARCHAR(100), -- More specific classification

    -- Model context
    model_name VARCHAR(200) NOT NULL,
    model_architecture VARCHAR(100), -- transformer, lstm, cnn, etc.
    framework VARCHAR(50), -- tensorflow, pytorch, etc.
    framework_version VARCHAR(50),

    -- Input/Output data
    input_features JSONB, -- Model input features and values
    predictions JSONB, -- Model predictions/outputs
    confidence_scores JSONB, -- Confidence levels for predictions
    feature_importance JSONB, -- Feature importance scores

    -- Performance metrics
    inference_time_ns BIGINT, -- Time taken for inference
    model_accuracy DECIMAL(8,6), -- Model accuracy if available
    model_loss DECIMAL(12,8), -- Model loss/error
    prediction_confidence DECIMAL(8,6), -- Overall prediction confidence

    -- Training context (for training events)
    training_dataset_id VARCHAR(200),
    training_dataset_size INTEGER,
    training_epochs INTEGER,
    learning_rate DECIMAL(10,8),
    batch_size INTEGER,

    -- Validation and testing
    validation_score DECIMAL(8,6),
    test_score DECIMAL(8,6),
    cross_validation_scores DECIMAL(8,6)[],

    -- Model drift and monitoring
    data_drift_score DECIMAL(8,6), -- How much input data has drifted
    model_drift_score DECIMAL(8,6), -- How much model performance has drifted
    feature_drift_scores JSONB, -- Per-feature drift scores
    anomaly_score DECIMAL(8,6), -- Anomaly detection score

    -- Business context
    symbol VARCHAR(32), -- Trading symbol if applicable
    strategy_id VARCHAR(100),
    account_id VARCHAR(64),
    signal_strength DECIMAL(8,6), -- Trading signal strength

    -- System context
    node_id VARCHAR(50) NOT NULL,
    gpu_device_id INTEGER, -- GPU device used
    memory_usage_mb INTEGER,
    gpu_memory_usage_mb INTEGER,

    -- Model artifacts and references
    model_artifact_path TEXT, -- Path to model file
    checkpoint_id VARCHAR(200), -- Training checkpoint reference
    experiment_id VARCHAR(200), -- ML experiment tracking ID

    -- Additional metadata
    hyperparameters JSONB, -- Model hyperparameters
    environment_info JSONB, -- Runtime environment details
    custom_metrics JSONB, -- Domain-specific metrics

    -- Partition key
    event_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

-- ================================================================================================
-- SYSTEM EVENTS TABLE
-- Specialized tracking for system health and performance
-- ================================================================================================
CREATE TABLE system_events (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    event_timestamp ns_timestamp NOT NULL,

    -- Event classification
    event_type VARCHAR(100) NOT NULL, -- startup, shutdown, health_check, performance_alert, etc.
    severity audit_severity NOT NULL,
    component system_component NOT NULL,
    service_name VARCHAR(100),

    -- System metrics
    cpu_usage_percent DECIMAL(5,2),
    memory_usage_mb INTEGER,
    memory_total_mb INTEGER,
    disk_usage_gb INTEGER,
    disk_total_gb INTEGER,
    network_rx_bytes BIGINT,
    network_tx_bytes BIGINT,
    load_average_1m DECIMAL(8,4),
    load_average_5m DECIMAL(8,4),
    load_average_15m DECIMAL(8,4),

    -- Performance metrics
    latency_p50_ns BIGINT, -- 50th percentile latency
    latency_p95_ns BIGINT, -- 95th percentile latency
    latency_p99_ns BIGINT, -- 99th percentile latency
    throughput_ops_per_sec DECIMAL(12,2),
    error_rate_percent DECIMAL(5,2),

    -- Application-specific metrics
    active_connections INTEGER,
    pending_requests INTEGER,
    orders_per_second DECIMAL(10,2),
    fills_per_second DECIMAL(10,2),
    market_data_messages_per_second DECIMAL(12,2),

    -- Health check details
    health_status VARCHAR(50), -- healthy, degraded, unhealthy, unknown
    health_checks JSONB, -- Individual health check results
    dependencies_status JSONB, -- Status of external dependencies

    -- Configuration and version info
    application_version VARCHAR(100),
    configuration_version VARCHAR(100),
    database_version VARCHAR(100),

    -- Error and debugging information
    error_details JSONB,
    debug_info JSONB,

    -- System context
    node_id VARCHAR(50) NOT NULL,
    process_id INTEGER NOT NULL,
    container_id VARCHAR(100), -- Docker/Kubernetes container ID
    pod_name VARCHAR(100), -- Kubernetes pod name
    namespace VARCHAR(100), -- Kubernetes namespace

    -- Partition key
    event_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

-- ================================================================================================
-- CHANGE TRACKING TABLE
-- Detailed tracking of all data changes for compliance
-- ================================================================================================
CREATE TABLE change_tracking (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    change_timestamp ns_timestamp NOT NULL,

    -- Change context
    table_name VARCHAR(100) NOT NULL,
    operation VARCHAR(20) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    primary_key_values JSONB NOT NULL, -- Primary key values of affected record

    -- Change details
    changed_columns TEXT[], -- Names of columns that changed
    old_row_data JSONB, -- Complete old row data (for UPDATE/DELETE)
    new_row_data JSONB, -- Complete new row data (for INSERT/UPDATE)
    column_changes JSONB, -- Detailed before/after for each changed column

    -- User and session context
    user_id VARCHAR(64),
    session_id UUID,
    application_name VARCHAR(100),

    -- Transaction context
    transaction_id BIGINT, -- Database transaction ID
    statement_id INTEGER, -- Statement within transaction

    -- System context
    node_id VARCHAR(50) NOT NULL,
    process_id INTEGER NOT NULL,

    -- Audit metadata
    audit_log_id UUID, -- Reference to audit_log entry
    checksum VARCHAR(64) NOT NULL, -- Integrity check

    -- Partition key
    change_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, change_date)
) PARTITION BY RANGE (change_date);

-- ================================================================================================
-- COMPLIANCE ANNOTATIONS TABLE
-- Additional compliance metadata for audit entries
-- ================================================================================================
CREATE TABLE compliance_annotations (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    audit_log_id UUID NOT NULL, -- FK removed: audit_log has composite PK (id, audit_date)

    -- Compliance framework
    regulation_name VARCHAR(100) NOT NULL, -- MiFID II, GDPR, SOX, etc.
    requirement_section VARCHAR(100), -- Specific section/article
    compliance_category VARCHAR(100), -- trade_reporting, record_keeping, etc.

    -- Annotation details
    annotation_type VARCHAR(50) NOT NULL, -- tag, note, exemption, etc.
    annotation_value TEXT,
    is_required BOOLEAN DEFAULT TRUE,

    -- Validation and review
    validated_by VARCHAR(64),
    validated_at TIMESTAMP WITH TIME ZONE,
    review_status VARCHAR(50), -- pending, approved, rejected
    reviewer_notes TEXT,

    -- Retention and archival
    retention_years INTEGER NOT NULL DEFAULT 7,
    archive_after_years INTEGER DEFAULT 10,

    -- Timing
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- ================================================================================================
-- HIGH-PERFORMANCE INDEXES
-- ================================================================================================

-- Audit log indexes (optimized for compliance queries)
CREATE INDEX idx_audit_log_timestamp ON audit_log USING BTREE (event_timestamp);
CREATE INDEX idx_audit_log_user_timestamp ON audit_log USING BTREE (user_id, event_timestamp) WHERE user_id IS NOT NULL;
CREATE INDEX idx_audit_log_entity ON audit_log USING BTREE (entity_type, entity_id) WHERE entity_type IS NOT NULL AND entity_id IS NOT NULL;
CREATE INDEX idx_audit_log_component_type ON audit_log USING BTREE (component, event_type);
CREATE INDEX idx_audit_log_severity ON audit_log USING BTREE (severity, event_timestamp) WHERE severity IN ('error', 'critical', 'alert', 'emergency');
CREATE INDEX idx_audit_log_session ON audit_log USING HASH (session_id) WHERE session_id IS NOT NULL;
CREATE INDEX idx_audit_log_correlation ON audit_log USING HASH (correlation_id) WHERE correlation_id IS NOT NULL;
CREATE INDEX idx_audit_log_trace ON audit_log USING HASH (trace_id) WHERE trace_id IS NOT NULL;
CREATE INDEX idx_audit_log_sensitive ON audit_log USING BTREE (event_timestamp) WHERE is_sensitive = TRUE;

-- GIN indexes for JSONB columns (flexible querying)
CREATE INDEX idx_audit_log_event_data_gin ON audit_log USING GIN (event_data);
CREATE INDEX idx_audit_log_old_values_gin ON audit_log USING GIN (old_values);
CREATE INDEX idx_audit_log_new_values_gin ON audit_log USING GIN (new_values);
CREATE INDEX idx_audit_log_tags_gin ON audit_log USING GIN (tags);

-- ML events indexes
CREATE INDEX idx_ml_events_timestamp ON ml_events USING BTREE (event_timestamp);
CREATE INDEX idx_ml_events_model ON ml_events USING BTREE (model_name, model_version, event_timestamp);
CREATE INDEX idx_ml_events_symbol ON ml_events USING BTREE (symbol, event_timestamp) WHERE symbol IS NOT NULL;
CREATE INDEX idx_ml_events_strategy ON ml_events USING BTREE (strategy_id, event_timestamp) WHERE strategy_id IS NOT NULL;
CREATE INDEX idx_ml_events_type ON ml_events USING BTREE (event_type, event_timestamp);

-- System events indexes
CREATE INDEX idx_system_events_timestamp ON system_events USING BTREE (event_timestamp);
CREATE INDEX idx_system_events_component ON system_events USING BTREE (component, event_timestamp);
CREATE INDEX idx_system_events_severity ON system_events USING BTREE (severity, event_timestamp);
CREATE INDEX idx_system_events_health ON system_events USING BTREE (health_status, event_timestamp) WHERE health_status IS NOT NULL;
CREATE INDEX idx_system_events_node ON system_events USING BTREE (node_id, event_timestamp);

-- Change tracking indexes
CREATE INDEX idx_change_tracking_timestamp ON change_tracking USING BTREE (change_timestamp);
CREATE INDEX idx_change_tracking_table ON change_tracking USING BTREE (table_name, change_timestamp);
CREATE INDEX idx_change_tracking_user ON change_tracking USING BTREE (user_id, change_timestamp) WHERE user_id IS NOT NULL;
CREATE INDEX idx_change_tracking_audit_log ON change_tracking USING HASH (audit_log_id) WHERE audit_log_id IS NOT NULL;

-- Compliance annotations indexes
CREATE INDEX idx_compliance_annotations_audit_log ON compliance_annotations USING HASH (audit_log_id);
CREATE INDEX idx_compliance_annotations_regulation ON compliance_annotations USING BTREE (regulation_name, compliance_category);
CREATE INDEX idx_compliance_annotations_review ON compliance_annotations USING BTREE (review_status, created_at);

-- ================================================================================================
-- AUTOMATIC PARTITIONING
-- ================================================================================================

-- Function to create daily partitions for audit_log
CREATE OR REPLACE FUNCTION create_audit_log_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := target_date;
    end_date := target_date + INTERVAL '1 day';
    partition_name := 'audit_log_' || to_char(start_date, 'YYYY_MM_DD');

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF audit_log
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (event_timestamp)',
                       'idx_' || partition_name || '_timestamp', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (user_id, event_timestamp) WHERE user_id IS NOT NULL',
                       'idx_' || partition_name || '_user_ts', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (component, event_type)',
                       'idx_' || partition_name || '_comp_type', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Function to create daily partitions for ML events
CREATE OR REPLACE FUNCTION create_ml_events_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := target_date;
    end_date := target_date + INTERVAL '1 day';
    partition_name := 'ml_events_' || to_char(start_date, 'YYYY_MM_DD');

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF ml_events
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (event_timestamp)',
                       'idx_' || partition_name || '_timestamp', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (model_name, event_timestamp)',
                       'idx_' || partition_name || '_model_ts', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Function to create daily partitions for system events
CREATE OR REPLACE FUNCTION create_system_events_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := target_date;
    end_date := target_date + INTERVAL '1 day';
    partition_name := 'system_events_' || to_char(start_date, 'YYYY_MM_DD');

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF system_events
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (event_timestamp)',
                       'idx_' || partition_name || '_timestamp', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (component, event_timestamp)',
                       'idx_' || partition_name || '_comp_ts', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create initial partitions for all audit tables
DO $$
DECLARE
    i INTEGER;
BEGIN
    -- Create partitions for current and next 30 days
    FOR i IN 0..30 LOOP
        PERFORM create_audit_log_partition(CURRENT_DATE + i);
        PERFORM create_ml_events_partition(CURRENT_DATE + i);
        PERFORM create_system_events_partition(CURRENT_DATE + i);
        -- Reuse trading_events partition function for change_tracking
        PERFORM create_trading_events_partition(CURRENT_DATE + i);
    END LOOP;
END $$;

-- ================================================================================================
-- AUDIT HELPER FUNCTIONS
-- ================================================================================================

-- Function to create audit log entry
CREATE OR REPLACE FUNCTION create_audit_entry(
    p_event_type audit_event_type,
    p_component system_component,
    p_action VARCHAR(100),
    p_entity_type VARCHAR(100) DEFAULT NULL,
    p_entity_id UUID DEFAULT NULL,
    p_user_id VARCHAR(64) DEFAULT NULL,
    p_session_id UUID DEFAULT NULL,
    p_event_data JSONB DEFAULT '{}'::jsonb,
    p_severity audit_severity DEFAULT 'info',
    p_old_values JSONB DEFAULT NULL,
    p_new_values JSONB DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
    audit_entry_id UUID;
    current_timestamp_ns ns_timestamp;
BEGIN
    audit_entry_id := uuid_generate_v4();
    current_timestamp_ns := EXTRACT(EPOCH FROM NOW()) * 1000000000;

    INSERT INTO audit_log (
        id, event_timestamp, recorded_timestamp, processing_timestamp,
        event_type, severity, component, entity_type, entity_id,
        user_id, session_id, action, event_data,
        old_values, new_values, node_id, process_id, checksum
    ) VALUES (
        audit_entry_id,
        current_timestamp_ns,
        current_timestamp_ns,
        current_timestamp_ns,
        p_event_type,
        p_severity,
        p_component,
        p_entity_type,
        p_entity_id,
        p_user_id,
        p_session_id,
        p_action,
        p_event_data,
        p_old_values,
        p_new_values,
        'audit-node-01', -- node_id literal; overridden per-deployment via later migrations
        pg_backend_pid(),
        encode(sha256(audit_entry_id::text::bytea), 'hex')
    );

    RETURN audit_entry_id;
END;
$$ LANGUAGE plpgsql;

-- Function to log ML event
CREATE OR REPLACE FUNCTION log_ml_event(
    p_model_name VARCHAR(200),
    p_model_version VARCHAR(50),
    p_event_type VARCHAR(100),
    p_predictions JSONB DEFAULT NULL,
    p_confidence_scores JSONB DEFAULT NULL,
    p_symbol VARCHAR(32) DEFAULT NULL,
    p_strategy_id VARCHAR(100) DEFAULT NULL,
    p_inference_time_ns BIGINT DEFAULT NULL,
    p_additional_data JSONB DEFAULT '{}'::jsonb
) RETURNS UUID AS $$
DECLARE
    ml_event_id UUID;
    current_timestamp_ns ns_timestamp;
BEGIN
    ml_event_id := uuid_generate_v4();
    current_timestamp_ns := EXTRACT(EPOCH FROM NOW()) * 1000000000;

    INSERT INTO ml_events (
        id, model_id, model_version, event_timestamp,
        event_type, model_name, predictions, confidence_scores,
        symbol, strategy_id, inference_time_ns, node_id,
        custom_metrics
    ) VALUES (
        ml_event_id,
        p_model_name || ':' || p_model_version,
        p_model_version,
        current_timestamp_ns,
        p_event_type,
        p_model_name,
        p_predictions,
        p_confidence_scores,
        p_symbol,
        p_strategy_id,
        p_inference_time_ns,
        'ml-node-01', -- node_id literal; overridden per-deployment via later migrations
        p_additional_data
    );

    RETURN ml_event_id;
END;
$$ LANGUAGE plpgsql;

-- Function to log system event
CREATE OR REPLACE FUNCTION log_system_event(
    p_event_type VARCHAR(100),
    p_component system_component,
    p_severity audit_severity DEFAULT 'info',
    p_service_name VARCHAR(100) DEFAULT NULL,
    p_health_status VARCHAR(50) DEFAULT NULL,
    p_metrics JSONB DEFAULT '{}'::jsonb,
    p_error_details JSONB DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
    system_event_id UUID;
    current_timestamp_ns ns_timestamp;
BEGIN
    system_event_id := uuid_generate_v4();
    current_timestamp_ns := EXTRACT(EPOCH FROM NOW()) * 1000000000;

    INSERT INTO system_events (
        id, event_timestamp, event_type, severity, component,
        service_name, health_status, node_id, process_id,
        error_details, debug_info
    ) VALUES (
        system_event_id,
        current_timestamp_ns,
        p_event_type,
        p_severity,
        p_component,
        p_service_name,
        p_health_status,
        'system-node-01', -- node_id literal; overridden per-deployment via later migrations
        pg_backend_pid(),
        p_error_details,
        p_metrics
    );

    RETURN system_event_id;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- AUTOMATED CHANGE TRACKING TRIGGERS
-- ================================================================================================

-- Generic function to track changes on any table
CREATE OR REPLACE FUNCTION track_table_changes()
RETURNS TRIGGER AS $$
DECLARE
    change_record_id UUID;
    current_timestamp_ns ns_timestamp;
    old_data JSONB;
    new_data JSONB;
    changed_cols TEXT[];
BEGIN
    change_record_id := uuid_generate_v4();
    current_timestamp_ns := EXTRACT(EPOCH FROM NOW()) * 1000000000;

    -- Convert row data to JSONB
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        new_data := NULL;
    ELSIF TG_OP = 'INSERT' THEN
        old_data := NULL;
        new_data := to_jsonb(NEW);
    ELSE -- UPDATE
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);

        -- Identify changed columns
        SELECT array_agg(key) INTO changed_cols
        FROM (
            SELECT key
            FROM jsonb_each_text(old_data) o
            FULL OUTER JOIN jsonb_each_text(new_data) n USING (key)
            WHERE o.value IS DISTINCT FROM n.value
        ) t;
    END IF;

    -- Insert change tracking record
    INSERT INTO change_tracking (
        id, change_timestamp, table_name, operation,
        primary_key_values, changed_columns, old_row_data, new_row_data,
        node_id, process_id, checksum
    ) VALUES (
        change_record_id,
        current_timestamp_ns,
        TG_TABLE_NAME,
        TG_OP,
        CASE
            WHEN TG_OP = 'DELETE' THEN jsonb_build_object('id', OLD.id)
            ELSE jsonb_build_object('id', NEW.id)
        END,
        changed_cols,
        old_data,
        new_data,
        'change-tracker-01', -- node_id literal; overridden per-deployment via later migrations
        pg_backend_pid(),
        encode(sha256(change_record_id::text::bytea), 'hex')
    );

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Function to automatically update compliance annotations timestamp
CREATE OR REPLACE FUNCTION update_compliance_annotations_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- TRIGGER FUNCTIONS FOR GENERATED COLUMNS (converted from GENERATED ALWAYS)
-- ================================================================================================

-- Set audit_date for audit_log
CREATE OR REPLACE FUNCTION set_audit_log_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.audit_date := DATE(TO_TIMESTAMP(NEW.event_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_audit_log_date
    BEFORE INSERT OR UPDATE ON audit_log
    FOR EACH ROW
    EXECUTE FUNCTION set_audit_log_date();

-- Set event_date for ml_events
CREATE OR REPLACE FUNCTION set_ml_event_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.event_date := DATE(TO_TIMESTAMP(NEW.event_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_ml_event_date
    BEFORE INSERT OR UPDATE ON ml_events
    FOR EACH ROW
    EXECUTE FUNCTION set_ml_event_date();

-- Set event_date for system_events
CREATE OR REPLACE FUNCTION set_system_event_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.event_date := DATE(TO_TIMESTAMP(NEW.event_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_system_event_date
    BEFORE INSERT OR UPDATE ON system_events
    FOR EACH ROW
    EXECUTE FUNCTION set_system_event_date();

-- Set change_date for change_tracking
CREATE OR REPLACE FUNCTION set_change_tracking_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.change_date := DATE(TO_TIMESTAMP(NEW.change_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_change_tracking_date
    BEFORE INSERT OR UPDATE ON change_tracking
    FOR EACH ROW
    EXECUTE FUNCTION set_change_tracking_date();

-- ================================================================================================
-- CREATE TRIGGERS FOR CHANGE TRACKING
-- ================================================================================================

-- Enable change tracking on core trading tables
CREATE TRIGGER tg_track_orders_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION track_table_changes();

CREATE TRIGGER tg_track_fills_changes
    AFTER INSERT OR UPDATE OR DELETE ON fills
    FOR EACH ROW
    EXECUTE FUNCTION track_table_changes();

CREATE TRIGGER tg_track_positions_changes
    AFTER INSERT OR UPDATE OR DELETE ON positions
    FOR EACH ROW
    EXECUTE FUNCTION track_table_changes();

CREATE TRIGGER tg_track_risk_limits_changes
    AFTER INSERT OR UPDATE OR DELETE ON risk_limits
    FOR EACH ROW
    EXECUTE FUNCTION track_table_changes();

-- Compliance annotations timestamp trigger
CREATE TRIGGER tg_update_compliance_annotations_timestamp
    BEFORE UPDATE ON compliance_annotations
    FOR EACH ROW
    EXECUTE FUNCTION update_compliance_annotations_timestamp();

-- ================================================================================================
-- AUDIT SEARCH AND REPORTING FUNCTIONS
-- ================================================================================================

-- Function to search audit log with flexible filters
CREATE OR REPLACE FUNCTION search_audit_log(
    p_start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW() - INTERVAL '24 hours',
    p_end_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    p_user_id VARCHAR(64) DEFAULT NULL,
    p_component system_component DEFAULT NULL,
    p_event_type audit_event_type DEFAULT NULL,
    p_severity audit_severity DEFAULT NULL,
    p_entity_type VARCHAR(100) DEFAULT NULL,
    p_entity_id UUID DEFAULT NULL,
    p_search_text TEXT DEFAULT NULL,
    p_limit INTEGER DEFAULT 1000
) RETURNS TABLE (
    id UUID,
    event_timestamp TIMESTAMP WITH TIME ZONE,
    event_type audit_event_type,
    severity audit_severity,
    component system_component,
    user_id VARCHAR(64),
    action VARCHAR(100),
    entity_type VARCHAR(100),
    entity_id UUID,
    description TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        al.id,
        TO_TIMESTAMP(al.event_timestamp / 1000000000.0),
        al.event_type,
        al.severity,
        al.component,
        al.user_id,
        al.action,
        al.entity_type,
        al.entity_id,
        COALESCE(al.event_data->>'description', al.action) as description
    FROM audit_log al
    WHERE al.event_timestamp >= EXTRACT(EPOCH FROM p_start_time) * 1000000000
      AND al.event_timestamp <= EXTRACT(EPOCH FROM p_end_time) * 1000000000
      AND (p_user_id IS NULL OR al.user_id = p_user_id)
      AND (p_component IS NULL OR al.component = p_component)
      AND (p_event_type IS NULL OR al.event_type = p_event_type)
      AND (p_severity IS NULL OR al.severity = p_severity)
      AND (p_entity_type IS NULL OR al.entity_type = p_entity_type)
      AND (p_entity_id IS NULL OR al.entity_id = p_entity_id)
      AND (p_search_text IS NULL OR
           al.event_data::text ILIKE '%' || p_search_text || '%' OR
           al.action ILIKE '%' || p_search_text || '%')
    ORDER BY al.event_timestamp DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

-- Function to get audit trail for specific entity
CREATE OR REPLACE FUNCTION get_entity_audit_trail(
    p_entity_type VARCHAR(100),
    p_entity_id UUID,
    p_start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW() - INTERVAL '30 days'
) RETURNS TABLE (
    event_timestamp TIMESTAMP WITH TIME ZONE,
    event_type audit_event_type,
    action VARCHAR(100),
    user_id VARCHAR(64),
    old_values JSONB,
    new_values JSONB,
    description TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        TO_TIMESTAMP(al.event_timestamp / 1000000000.0),
        al.event_type,
        al.action,
        al.user_id,
        al.old_values,
        al.new_values,
        COALESCE(al.event_data->>'description', al.action) as description
    FROM audit_log al
    WHERE al.entity_type = p_entity_type
      AND al.entity_id = p_entity_id
      AND al.event_timestamp >= EXTRACT(EPOCH FROM p_start_time) * 1000000000
    ORDER BY al.event_timestamp ASC;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- REPORTING VIEWS
-- ================================================================================================

-- Daily audit summary view
CREATE VIEW v_daily_audit_summary AS
SELECT
    DATE(TO_TIMESTAMP(al.event_timestamp / 1000000000.0)) as audit_date,
    al.component,
    al.event_type,
    al.severity,
    COUNT(*) as event_count,
    COUNT(DISTINCT al.user_id) as unique_users,
    COUNT(*) FILTER (WHERE al.is_error = TRUE) as error_count,
    COUNT(*) FILTER (WHERE al.severity IN ('critical', 'alert', 'emergency')) as critical_count
FROM audit_log al
WHERE al.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '30 days')) * 1000000000
GROUP BY
    DATE(TO_TIMESTAMP(al.event_timestamp / 1000000000.0)),
    al.component,
    al.event_type,
    al.severity
ORDER BY audit_date DESC, event_count DESC;

-- User activity summary view
CREATE VIEW v_user_activity_summary AS
SELECT
    al.user_id,
    DATE(TO_TIMESTAMP(al.event_timestamp / 1000000000.0)) as activity_date,
    COUNT(*) as total_actions,
    COUNT(DISTINCT al.component) as components_accessed,
    COUNT(*) FILTER (WHERE al.severity = 'error') as error_count,
    MIN(TO_TIMESTAMP(al.event_timestamp / 1000000000.0)) as first_activity,
    MAX(TO_TIMESTAMP(al.event_timestamp / 1000000000.0)) as last_activity,
    array_agg(DISTINCT al.event_type ORDER BY al.event_type) as event_types
FROM audit_log al
WHERE al.user_id IS NOT NULL
  AND al.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '30 days')) * 1000000000
GROUP BY al.user_id, DATE(TO_TIMESTAMP(al.event_timestamp / 1000000000.0))
ORDER BY activity_date DESC, total_actions DESC;

-- System health summary view
CREATE VIEW v_system_health_summary AS
SELECT
    se.component,
    se.health_status,
    COUNT(*) as status_count,
    AVG(se.cpu_usage_percent) as avg_cpu_usage,
    AVG(se.memory_usage_mb) as avg_memory_usage,
    AVG(se.latency_p95_ns) / 1000000.0 as avg_p95_latency_ms,
    MAX(TO_TIMESTAMP(se.event_timestamp / 1000000000.0)) as last_reported
FROM system_events se
WHERE se.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '4 hours')) * 1000000000
  AND se.health_status IS NOT NULL
GROUP BY se.component, se.health_status
ORDER BY se.component, status_count DESC;

-- ================================================================================================
-- RETENTION AND ARCHIVAL POLICIES
-- ================================================================================================

-- Function to archive old audit data
CREATE OR REPLACE FUNCTION archive_audit_data(retention_years INTEGER DEFAULT 7)
RETURNS INTEGER AS $$
DECLARE
    archive_date DATE;
    archived_count INTEGER := 0;
    partition_name TEXT;
BEGIN
    archive_date := CURRENT_DATE - INTERVAL '1 year' * retention_years;

    -- Archive old partitions (implementation depends on archival strategy)
    -- This is a placeholder for actual archival implementation

    -- Drop partitions older than retention period
    FOR partition_name IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_name LIKE 'audit_log_%'
          AND table_name < 'audit_log_' || to_char(archive_date, 'YYYY_MM_DD')
    LOOP
        -- Move to archive or drop (implement based on requirements)
        EXECUTE format('DROP TABLE %I', partition_name);
        archived_count := archived_count + 1;
    END LOOP;

    RETURN archived_count;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- COMMENTS AND DOCUMENTATION
-- ================================================================================================

COMMENT ON TABLE audit_log IS 'Comprehensive immutable audit trail for all system activities. Partitioned by date with 7+ year retention for regulatory compliance.';
COMMENT ON TABLE ml_events IS 'Specialized audit log for machine learning operations including model predictions, training, and deployment events.';
COMMENT ON TABLE system_events IS 'System health and performance event tracking with metrics and health status monitoring.';
COMMENT ON TABLE change_tracking IS 'Detailed change tracking for all data modifications with before/after values for compliance reporting.';
COMMENT ON TABLE compliance_annotations IS 'Additional compliance metadata and annotations for audit entries to support regulatory requirements.';

COMMENT ON FUNCTION create_audit_entry IS 'Helper function to create standardized audit log entries with proper formatting and security.';
COMMENT ON FUNCTION log_ml_event IS 'Helper function to log machine learning events with standardized schema and performance metrics.';
COMMENT ON FUNCTION search_audit_log IS 'Flexible audit log search function supporting various filters for compliance reporting and investigation.';
COMMENT ON FUNCTION get_entity_audit_trail IS 'Get complete audit trail for a specific entity showing all changes and activities over time.';