foxhunt/migrations/016_trading_service_events.sql

-- Migration: Comprehensive Event Storage for Trading Service
-- This migration implements comprehensive event storage for HFT compliance and audit trails
-- Designed for regulatory compliance (MiFID II, SOX, Dodd-Frank) with nanosecond precision

-- Enable required extensions for HFT event storage
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- =======================================================================================
-- TRADING EVENTS TABLE - Core order lifecycle events
-- =======================================================================================
CREATE TABLE IF NOT EXISTS trading_events (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL DEFAULT uuid_generate_v4(), -- Unique event identifier
    correlation_id UUID, -- Link related events together
    event_type VARCHAR(50) NOT NULL, -- 'order_created', 'order_modified', 'order_cancelled', 'order_filled', 'order_expired', 'order_rejected'
    event_subtype VARCHAR(50), -- Additional event classification

    -- Core order information
    order_id UUID, -- Reference to orders table
    original_order_id UUID, -- For tracking order modifications
    client_order_id VARCHAR(128), -- Client-provided identifier
    exchange_order_id VARCHAR(128), -- Exchange-provided identifier

    -- Instrument and trading details
    symbol VARCHAR(32) NOT NULL,
    instrument_id VARCHAR(64),
    side VARCHAR(10) NOT NULL CHECK (side IN ('buy', 'sell')),
    order_type VARCHAR(20) NOT NULL,
    quantity BIGINT NOT NULL CHECK (quantity > 0),
    price BIGINT, -- Price in fixed-point cents
    filled_quantity BIGINT DEFAULT 0,
    remaining_quantity BIGINT DEFAULT 0,

    -- Execution details
    execution_price BIGINT, -- Actual fill price
    execution_quantity BIGINT, -- Quantity filled in this event
    execution_id VARCHAR(128), -- Venue execution ID
    venue VARCHAR(64), -- Execution venue
    is_maker BOOLEAN, -- Maker/taker flag
    fees BIGINT, -- Trading fees in fixed-point cents
    fee_currency VARCHAR(10), -- Fee currency

    -- Account and strategy information
    account_id VARCHAR(64),
    portfolio_id VARCHAR(64),
    strategy_id VARCHAR(100),
    trader_id VARCHAR(64),

    -- Risk and compliance
    risk_check_result VARCHAR(20), -- 'approved', 'rejected', 'warning'
    risk_violations JSONB, -- Array of risk violations
    compliance_flags JSONB, -- Regulatory compliance flags

    -- Timing information (nanosecond precision for HFT)
    event_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    market_timestamp TIMESTAMP WITH TIME ZONE, -- Exchange timestamp
    received_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    gateway_timestamp TIMESTAMP WITH TIME ZONE, -- Gateway receive time
    processing_start_timestamp TIMESTAMP WITH TIME ZONE, -- When processing started
    processing_end_timestamp TIMESTAMP WITH TIME ZONE, -- When processing completed
    latency_ns BIGINT, -- Processing latency in nanoseconds

    -- Order state tracking
    order_status VARCHAR(20), -- Current order status
    previous_status VARCHAR(20), -- Previous order status
    status_reason VARCHAR(255), -- Reason for status change

    -- Market conditions
    market_data_snapshot JSONB, -- Market data at time of event
    bid_price BIGINT, -- Best bid at time of event
    ask_price BIGINT, -- Best ask at time of event
    last_price BIGINT, -- Last trade price
    volume BIGINT, -- Market volume

    -- System information
    source_system VARCHAR(50) NOT NULL, -- System that generated the event
    source_component VARCHAR(50), -- Component within system
    version VARCHAR(20), -- Event schema version

    -- Additional data
    metadata JSONB, -- Flexible additional data
    raw_message TEXT, -- Original message (if applicable)

    PRIMARY KEY (id, event_timestamp) -- Composite key for partitioning
) PARTITION BY RANGE (event_timestamp);

-- Create partitions for trading events (6 months of partitions)
-- Only create monthly partitions if the table doesn't already have partitions (from migration 001)
DO $$
DECLARE
    partition_start DATE;
    partition_end DATE;
    partition_name TEXT;
    i INTEGER;
    existing_partitions INTEGER;
BEGIN
    -- Check if partitions already exist (from migration 001 which creates daily partitions)
    SELECT COUNT(*) INTO existing_partitions
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    WHERE parent.relname = 'trading_events';

    -- Only create monthly partitions if no partitions exist yet
    IF existing_partitions = 0 THEN
        FOR i IN 0..5 LOOP
            partition_start := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
            partition_end := partition_start + INTERVAL '1 month';
            partition_name := 'trading_events_' || to_char(partition_start, 'YYYY_MM');

            EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF trading_events
                           FOR VALUES FROM (%L) TO (%L)',
                           partition_name, partition_start, partition_end);

            -- Add indexes to each partition
            EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I(order_id, event_timestamp)',
                           'idx_' || partition_name || '_order_id', partition_name);
            EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I(symbol, event_type, event_timestamp)',
                           'idx_' || partition_name || '_symbol_type', partition_name);
            EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I(account_id, event_timestamp)',
                           'idx_' || partition_name || '_account', partition_name);
        END LOOP;
    END IF;
END $$;

-- =======================================================================================
-- RISK EVENTS TABLE - Risk management decisions and alerts
-- =======================================================================================
CREATE TABLE IF NOT EXISTS risk_events (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL DEFAULT uuid_generate_v4(),
    correlation_id UUID, -- Link to trading events

    -- Event classification
    event_type VARCHAR(50) NOT NULL, -- 'limit_check', 'violation', 'alert', 'emergency_stop', 'position_limit', 'var_breach'
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('info', 'warning', 'error', 'critical')),

    -- Risk details
    risk_type VARCHAR(50) NOT NULL, -- 'position_size', 'concentration', 'var', 'leverage', 'drawdown', 'exposure'
    violation_type VARCHAR(50), -- Specific violation type

    -- Values and limits
    current_value DECIMAL(20, 8), -- Current risk metric value
    limit_value DECIMAL(20, 8), -- Risk limit value
    breach_amount DECIMAL(20, 8), -- Amount of breach
    breach_percentage DECIMAL(5, 4), -- Percentage of breach

    -- Context information
    symbol VARCHAR(32),
    account_id VARCHAR(64),
    portfolio_id VARCHAR(64),
    strategy_id VARCHAR(100),
    instrument_id VARCHAR(64),

    -- Order context (if related to an order)
    order_id UUID,
    trade_id UUID,
    position_id UUID,

    -- Risk calculation details
    calculation_method VARCHAR(50), -- 'real_time', 'batch', 'stress_test'
    model_version VARCHAR(20), -- Risk model version
    parameters JSONB, -- Risk calculation parameters

    -- Actions taken
    action_taken VARCHAR(100), -- 'blocked', 'warning_issued', 'position_reduced', 'trading_halted'
    auto_action BOOLEAN DEFAULT false, -- Whether action was automatic
    manual_override BOOLEAN DEFAULT false, -- Whether manually overridden
    override_reason TEXT, -- Reason for manual override
    override_user VARCHAR(64), -- User who performed override

    -- Timing
    event_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    detection_timestamp TIMESTAMP WITH TIME ZONE, -- When risk was detected
    resolution_timestamp TIMESTAMP WITH TIME ZONE, -- When risk was resolved

    -- Resolution
    resolution_status VARCHAR(20) DEFAULT 'open' CHECK (resolution_status IN ('open', 'acknowledged', 'resolved', 'false_positive')),
    resolution_notes TEXT,
    resolved_by VARCHAR(64),

    -- System information
    source_system VARCHAR(50) NOT NULL,
    risk_engine_version VARCHAR(20),

    -- Additional data
    metadata JSONB,

    PRIMARY KEY (id, event_timestamp)
) PARTITION BY RANGE (event_timestamp);

-- Create partitions for risk events
-- Only create monthly partitions if the table doesn't already have partitions (from migration 002)
DO $$
DECLARE
    partition_start DATE;
    partition_end DATE;
    partition_name TEXT;
    i INTEGER;
    existing_partitions INTEGER;
BEGIN
    -- Check if partitions already exist (from migration 002 which creates daily partitions)
    SELECT COUNT(*) INTO existing_partitions
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    WHERE parent.relname = 'risk_events';

    -- Only create monthly partitions if no partitions exist yet
    IF existing_partitions = 0 THEN
        FOR i IN 0..5 LOOP
            partition_start := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
            partition_end := partition_start + INTERVAL '1 month';
            partition_name := 'risk_events_' || to_char(partition_start, 'YYYY_MM');

            EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF risk_events
                           FOR VALUES FROM (%L) TO (%L)',
                           partition_name, partition_start, partition_end);
        END LOOP;
    END IF;
END $$;

-- =======================================================================================
-- AUDIT TRAIL TABLE - Configuration changes and administrative actions
-- =======================================================================================
CREATE TABLE IF NOT EXISTS audit_trail (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL DEFAULT uuid_generate_v4(),

    -- Event classification
    event_type VARCHAR(50) NOT NULL, -- 'config_change', 'user_action', 'system_action', 'data_modification'
    action VARCHAR(100) NOT NULL, -- Specific action taken

    -- Actor information
    user_id VARCHAR(64), -- User who performed action
    username VARCHAR(100), -- Username
    user_role VARCHAR(50), -- User role/permission level
    session_id VARCHAR(128), -- Session identifier
    ip_address INET, -- Source IP address
    user_agent TEXT, -- Browser/client information

    -- Target information
    target_type VARCHAR(50), -- 'user', 'account', 'configuration', 'strategy', 'limit'
    target_id VARCHAR(128), -- ID of the target object
    target_name VARCHAR(255), -- Human-readable name of target

    -- Change details
    operation VARCHAR(20) CHECK (operation IN ('CREATE', 'READ', 'UPDATE', 'DELETE')),
    field_name VARCHAR(100), -- Specific field changed
    old_value TEXT, -- Previous value
    new_value TEXT, -- New value
    change_reason TEXT, -- Reason for change

    -- Context
    system_name VARCHAR(50), -- System where change occurred
    component VARCHAR(50), -- System component
    environment VARCHAR(20), -- 'production', 'staging', 'development'

    -- Timing
    event_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    effective_timestamp TIMESTAMP WITH TIME ZONE, -- When change becomes effective

    -- Approval workflow
    approval_required BOOLEAN DEFAULT false,
    approval_status VARCHAR(20), -- 'pending', 'approved', 'rejected'
    approved_by VARCHAR(64), -- User who approved
    approval_timestamp TIMESTAMP WITH TIME ZONE,
    approval_comments TEXT,

    -- Compliance
    regulatory_impact BOOLEAN DEFAULT false, -- Whether change has regulatory impact
    compliance_review_required BOOLEAN DEFAULT false,
    compliance_reviewer VARCHAR(64),
    compliance_review_date DATE,

    -- Additional data
    metadata JSONB,
    request_payload JSONB, -- Full request data
    response_payload JSONB, -- Full response data

    PRIMARY KEY (id, event_timestamp)
) PARTITION BY RANGE (event_timestamp);

-- Create partitions for audit trail
DO $$
DECLARE
    partition_start DATE;
    partition_end DATE;
    partition_name TEXT;
    i INTEGER;
    existing_partitions INTEGER;
BEGIN
    -- Check if partitions already exist (could be from migration 003)
    SELECT COUNT(*) INTO existing_partitions
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    WHERE parent.relname = 'audit_trail';

    -- Only create monthly partitions if no partitions exist yet
    IF existing_partitions = 0 THEN
        FOR i IN 0..11 LOOP
            partition_start := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
            partition_end := partition_start + INTERVAL '1 month';
            partition_name := 'audit_trail_' || to_char(partition_start, 'YYYY_MM');

            EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF audit_trail
                           FOR VALUES FROM (%L) TO (%L)',
                           partition_name, partition_start, partition_end);
        END LOOP;
    END IF;
END $$;

-- =======================================================================================
-- ML SIGNALS TABLE - Machine learning predictions and signals
-- =======================================================================================
CREATE TABLE IF NOT EXISTS ml_signals (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    signal_id UUID NOT NULL DEFAULT uuid_generate_v4(),

    -- Model information
    model_name VARCHAR(100) NOT NULL, -- 'dqn', 'ppo', 'transformer', 'mamba', 'tft'
    model_version VARCHAR(20) NOT NULL,
    model_type VARCHAR(50), -- 'reinforcement_learning', 'supervised', 'unsupervised'
    algorithm VARCHAR(50), -- Specific algorithm used

    -- Signal details
    signal_type VARCHAR(50) NOT NULL, -- 'trade_signal', 'market_prediction', 'risk_alert', 'anomaly_detection'
    signal_strength DECIMAL(5, 4), -- Signal strength 0.0-1.0
    confidence DECIMAL(5, 4), -- Model confidence 0.0-1.0

    -- Prediction/signal content
    prediction_type VARCHAR(50), -- 'price_direction', 'volatility', 'volume', 'risk_level'
    predicted_value DECIMAL(20, 8), -- Numerical prediction
    predicted_direction VARCHAR(10), -- 'up', 'down', 'neutral'
    time_horizon INTEGER, -- Prediction time horizon in minutes

    -- Market context
    symbol VARCHAR(32) NOT NULL,
    market_data_snapshot JSONB, -- Market data used for prediction
    feature_vector JSONB, -- Input features used

    -- Trading context
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),

    -- Model performance tracking
    execution_time_ms INTEGER, -- Model execution time
    input_data_hash VARCHAR(64), -- Hash of input data for reproducibility
    model_parameters JSONB, -- Model parameters used

    -- Outcome tracking (filled after signal verification)
    actual_outcome DECIMAL(20, 8), -- Actual result (for backtesting)
    outcome_timestamp TIMESTAMP WITH TIME ZONE, -- When outcome was recorded
    accuracy_score DECIMAL(5, 4), -- How accurate was the prediction
    signal_pnl DECIMAL(20, 8), -- P&L attributed to this signal

    -- Timing
    signal_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    market_timestamp TIMESTAMP WITH TIME ZONE, -- Market data timestamp

    -- Signal lifecycle
    signal_status VARCHAR(20) DEFAULT 'active', -- 'active', 'executed', 'expired', 'cancelled'
    expiry_timestamp TIMESTAMP WITH TIME ZONE,

    -- System information
    source_system VARCHAR(50) NOT NULL,
    gpu_used BOOLEAN DEFAULT false,
    cuda_version VARCHAR(20),

    -- Additional data
    metadata JSONB,
    raw_features JSONB, -- Raw feature data

    PRIMARY KEY (id, signal_timestamp)
) PARTITION BY RANGE (signal_timestamp);

-- Create partitions for ML signals
DO $$
DECLARE
    partition_start DATE;
    partition_end DATE;
    partition_name TEXT;
    i INTEGER;
    existing_partitions INTEGER;
BEGIN
    -- Check if partitions already exist
    SELECT COUNT(*) INTO existing_partitions
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    WHERE parent.relname = 'ml_signals';

    -- Only create monthly partitions if no partitions exist yet
    IF existing_partitions = 0 THEN
        FOR i IN 0..2 LOOP
            partition_start := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
            partition_end := partition_start + INTERVAL '1 month';
            partition_name := 'ml_signals_' || to_char(partition_start, 'YYYY_MM');

            EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF ml_signals
                           FOR VALUES FROM (%L) TO (%L)',
                           partition_name, partition_start, partition_end);
        END LOOP;
    END IF;
END $$;

-- =======================================================================================
-- SYSTEM EVENTS TABLE - Health, performance, and error events
-- =======================================================================================
CREATE TABLE IF NOT EXISTS system_events (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL DEFAULT uuid_generate_v4(),

    -- Event classification
    event_type VARCHAR(50) NOT NULL, -- 'health_check', 'performance_metric', 'error', 'startup', 'shutdown', 'deployment'
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('debug', 'info', 'warning', 'error', 'critical')),
    category VARCHAR(50), -- 'connectivity', 'latency', 'memory', 'disk', 'network', 'database'

    -- System information
    system_name VARCHAR(50) NOT NULL,
    service_name VARCHAR(50),
    component_name VARCHAR(50),
    hostname VARCHAR(100),
    instance_id VARCHAR(100),
    version VARCHAR(20),
    build_id VARCHAR(50),

    -- Performance metrics
    latency_ns BIGINT, -- Latency in nanoseconds
    throughput_per_second INTEGER, -- Operations per second
    memory_usage_mb INTEGER, -- Memory usage in MB
    cpu_usage_percent DECIMAL(5, 2), -- CPU usage percentage
    disk_usage_percent DECIMAL(5, 2), -- Disk usage percentage
    network_bytes_in BIGINT, -- Network bytes received
    network_bytes_out BIGINT, -- Network bytes sent

    -- Health information
    health_status VARCHAR(20), -- 'healthy', 'degraded', 'unhealthy', 'critical'
    health_score DECIMAL(5, 2), -- Health score 0-100
    uptime_seconds BIGINT, -- System uptime

    -- Error information
    error_code VARCHAR(50), -- Error code
    error_message TEXT, -- Error message
    stack_trace TEXT, -- Error stack trace
    error_count INTEGER DEFAULT 1, -- Number of occurrences

    -- Market data quality
    market_data_lag_ms INTEGER, -- Market data lag
    missing_ticks INTEGER, -- Number of missing market data ticks
    data_quality_score DECIMAL(5, 2), -- Data quality score 0-100

    -- Database performance
    db_connection_count INTEGER, -- Active database connections
    db_query_time_ms INTEGER, -- Database query time
    db_connection_pool_usage DECIMAL(5, 2), -- Connection pool usage percentage

    -- Timing
    event_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    metric_timestamp TIMESTAMP WITH TIME ZONE, -- When metric was measured

    -- Resolution tracking
    incident_id VARCHAR(100), -- Related incident ID
    resolution_time_minutes INTEGER, -- Time to resolve issue
    resolution_notes TEXT,

    -- Additional data
    metadata JSONB,
    metrics JSONB, -- Additional metrics
    environment_data JSONB, -- Environment variables, config

    PRIMARY KEY (id, event_timestamp)
) PARTITION BY RANGE (event_timestamp);

-- Create partitions for system events
DO $$
DECLARE
    partition_start DATE;
    partition_end DATE;
    partition_name TEXT;
    i INTEGER;
    existing_partitions INTEGER;
BEGIN
    -- Check if partitions already exist
    SELECT COUNT(*) INTO existing_partitions
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    WHERE parent.relname = 'system_events';

    -- Only create monthly partitions if no partitions exist yet
    IF existing_partitions = 0 THEN
        FOR i IN 0..2 LOOP
            partition_start := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
            partition_end := partition_start + INTERVAL '1 month';
            partition_name := 'system_events_' || to_char(partition_start, 'YYYY_MM');

            EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF system_events
                           FOR VALUES FROM (%L) TO (%L)',
                           partition_name, partition_start, partition_end);
        END LOOP;
    END IF;
END $$;

-- =======================================================================================
-- INDEXES FOR HIGH-PERFORMANCE QUERIES
-- =======================================================================================

-- Trading Events Indexes (only create if columns exist - these may be from migration 001 or this migration)
DO $$
BEGIN
    -- Index for order_id if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'order_id') THEN
        CREATE INDEX IF NOT EXISTS idx_trading_events_order_id ON trading_events(order_id, event_timestamp);
    END IF;

    -- Index for compliance_flags if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'compliance_flags') THEN
        CREATE INDEX IF NOT EXISTS idx_trading_events_compliance ON trading_events USING GIN(compliance_flags) WHERE compliance_flags IS NOT NULL;
    END IF;

    -- Index for risk_violations if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'risk_violations') THEN
        CREATE INDEX IF NOT EXISTS idx_trading_events_risk ON trading_events USING GIN(risk_violations) WHERE risk_violations IS NOT NULL;
    END IF;

    -- Index for latency_ns if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'latency_ns') THEN
        CREATE INDEX IF NOT EXISTS idx_trading_events_latency ON trading_events(latency_ns) WHERE latency_ns IS NOT NULL;
    END IF;
END $$;

-- These indexes should work with both migration 001 and 016 schemas
CREATE INDEX IF NOT EXISTS idx_trading_events_symbol_type ON trading_events(symbol, event_type, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_trading_events_account ON trading_events(account_id, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_trading_events_strategy ON trading_events(strategy_id, event_timestamp) WHERE strategy_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_trading_events_correlation ON trading_events(correlation_id) WHERE correlation_id IS NOT NULL;

-- Risk Events Indexes (conditional on column existence)
DO $$
BEGIN
    -- risk_type may not exist in migration 001 schema
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'risk_events' AND column_name = 'risk_type') THEN
        CREATE INDEX IF NOT EXISTS idx_risk_events_type_severity ON risk_events(risk_type, severity, event_timestamp);
    ELSE
        -- Use event_type instead if risk_type doesn't exist
        CREATE INDEX IF NOT EXISTS idx_risk_events_type_severity ON risk_events(event_type, severity, event_timestamp);
    END IF;

    -- resolution_status if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'risk_events' AND column_name = 'resolution_status') THEN
        CREATE INDEX IF NOT EXISTS idx_risk_events_resolution ON risk_events(resolution_status, event_timestamp);
    END IF;

    -- order_id if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'risk_events' AND column_name = 'order_id') THEN
        CREATE INDEX IF NOT EXISTS idx_risk_events_order ON risk_events(order_id) WHERE order_id IS NOT NULL;
    END IF;

    -- breach_amount if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'risk_events' AND column_name = 'breach_amount') THEN
        CREATE INDEX IF NOT EXISTS idx_risk_events_breach_amount ON risk_events(breach_amount) WHERE breach_amount IS NOT NULL;
    END IF;
END $$;

-- These indexes should work with both schemas
CREATE INDEX IF NOT EXISTS idx_risk_events_account ON risk_events(account_id, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_risk_events_symbol ON risk_events(symbol, event_timestamp) WHERE symbol IS NOT NULL;

-- Audit Trail Indexes
CREATE INDEX IF NOT EXISTS idx_audit_trail_user ON audit_trail(user_id, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_trail_target ON audit_trail(target_type, target_id, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_trail_operation ON audit_trail(operation, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_trail_approval ON audit_trail(approval_status, event_timestamp) WHERE approval_required = true;
CREATE INDEX IF NOT EXISTS idx_audit_trail_compliance ON audit_trail(regulatory_impact, event_timestamp) WHERE regulatory_impact = true;
CREATE INDEX IF NOT EXISTS idx_audit_trail_ip ON audit_trail(ip_address, event_timestamp);

-- ML Signals Indexes
CREATE INDEX IF NOT EXISTS idx_ml_signals_model ON ml_signals(model_name, model_version, signal_timestamp);
CREATE INDEX IF NOT EXISTS idx_ml_signals_symbol ON ml_signals(symbol, signal_timestamp);
CREATE INDEX IF NOT EXISTS idx_ml_signals_strategy ON ml_signals(strategy_id, signal_timestamp) WHERE strategy_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ml_signals_confidence ON ml_signals(confidence, signal_timestamp);
CREATE INDEX IF NOT EXISTS idx_ml_signals_status ON ml_signals(signal_status, expiry_timestamp);
CREATE INDEX IF NOT EXISTS idx_ml_signals_accuracy ON ml_signals(accuracy_score) WHERE accuracy_score IS NOT NULL;

-- System Events Indexes (conditional on column existence)
DO $$
BEGIN
    -- system_name if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'system_events' AND column_name = 'system_name') THEN
        CREATE INDEX IF NOT EXISTS idx_system_events_system ON system_events(system_name, service_name, event_timestamp);
    END IF;

    -- error_code if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'system_events' AND column_name = 'error_code') THEN
        CREATE INDEX IF NOT EXISTS idx_system_events_error ON system_events(error_code, event_timestamp) WHERE error_code IS NOT NULL;
    END IF;

    -- latency_ns if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'system_events' AND column_name = 'latency_ns') THEN
        CREATE INDEX IF NOT EXISTS idx_system_events_latency ON system_events(latency_ns) WHERE latency_ns IS NOT NULL;
    END IF;

    -- incident_id if it exists
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'system_events' AND column_name = 'incident_id') THEN
        CREATE INDEX IF NOT EXISTS idx_system_events_incident ON system_events(incident_id) WHERE incident_id IS NOT NULL;
    END IF;
END $$;

-- These indexes should work with both schemas
CREATE INDEX IF NOT EXISTS idx_system_events_severity ON system_events(severity, event_timestamp);
CREATE INDEX IF NOT EXISTS idx_system_events_health ON system_events(health_status, event_timestamp) WHERE health_status IS NOT NULL;

-- =======================================================================================
-- DATA RETENTION POLICIES
-- =======================================================================================

-- Create function to manage partition retention
CREATE OR REPLACE FUNCTION manage_event_partitions()
RETURNS VOID AS $$
DECLARE
    table_names TEXT[] := ARRAY['trading_events', 'risk_events', 'audit_trail', 'ml_signals', 'system_events'];
    retention_months INTEGER[] := ARRAY[24, 12, 84, 6, 3]; -- Retention periods in months
    table_name TEXT;
    retention_period INTEGER;
    cutoff_date DATE;
    partition_name TEXT;
    partition_record RECORD;
    i INTEGER;
BEGIN
    FOR i IN 1..array_length(table_names, 1) LOOP
        table_name := table_names[i];
        retention_period := retention_months[i];
        cutoff_date := CURRENT_DATE - (retention_period || ' months')::INTERVAL;

        -- Drop old partitions
        FOR partition_record IN
            SELECT schemaname, tablename
            FROM pg_tables
            WHERE tablename LIKE table_name || '_%'
            AND tablename ~ '\d{4}_\d{2}$'
        LOOP
            -- Extract date from partition name
            partition_name := partition_record.tablename;

            -- Drop if older than retention period
            EXECUTE format('SELECT to_date(substring(%L from ''%s_(\d{4}_\d{2})$''), ''YYYY_MM'') < %L',
                         partition_name, table_name, cutoff_date)
            INTO partition_record;

            IF partition_record IS NOT NULL THEN
                EXECUTE format('DROP TABLE IF EXISTS %I CASCADE', partition_name);
                RAISE NOTICE 'Dropped old partition: %', partition_name;
            END IF;
        END LOOP;

        -- Create future partitions (next 3 months)
        FOR i IN 1..3 LOOP
            DECLARE
                partition_start DATE := date_trunc('month', CURRENT_DATE) + (i || ' months')::INTERVAL;
                partition_end DATE := partition_start + INTERVAL '1 month';
                new_partition_name TEXT := table_name || '_' || to_char(partition_start, 'YYYY_MM');
            BEGIN
                EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
                               FOR VALUES FROM (%L) TO (%L)',
                               new_partition_name, table_name, partition_start, partition_end);
            END;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- =======================================================================================
-- UTILITY FUNCTIONS
-- =======================================================================================

-- Function to get event statistics
CREATE OR REPLACE FUNCTION get_event_statistics(
    start_date DATE DEFAULT CURRENT_DATE - INTERVAL '7 days',
    end_date DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE(
    table_name TEXT,
    event_count BIGINT,
    avg_events_per_hour NUMERIC,
    max_events_per_hour BIGINT,
    data_size_mb NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH event_stats AS (
        SELECT 'trading_events'::TEXT as tbl,
               COUNT(*) as cnt,
               EXTRACT(EPOCH FROM (end_date - start_date)) / 3600 as hours
        FROM trading_events
        WHERE event_timestamp BETWEEN start_date AND end_date

        UNION ALL

        SELECT 'risk_events'::TEXT,
               COUNT(*),
               EXTRACT(EPOCH FROM (end_date - start_date)) / 3600
        FROM risk_events
        WHERE event_timestamp BETWEEN start_date AND end_date

        UNION ALL

        SELECT 'audit_trail'::TEXT,
               COUNT(*),
               EXTRACT(EPOCH FROM (end_date - start_date)) / 3600
        FROM audit_trail
        WHERE event_timestamp BETWEEN start_date AND end_date

        UNION ALL

        SELECT 'ml_signals'::TEXT,
               COUNT(*),
               EXTRACT(EPOCH FROM (end_date - start_date)) / 3600
        FROM ml_signals
        WHERE signal_timestamp BETWEEN start_date AND end_date

        UNION ALL

        SELECT 'system_events'::TEXT,
               COUNT(*),
               EXTRACT(EPOCH FROM (end_date - start_date)) / 3600
        FROM system_events
        WHERE event_timestamp BETWEEN start_date AND end_date
    )
    SELECT
        s.tbl,
        s.cnt,
        CASE WHEN s.hours > 0 THEN s.cnt / s.hours ELSE 0 END,
        0::BIGINT, -- Placeholder for max events per hour
        0::NUMERIC -- Placeholder for data size
    FROM event_stats s;
END;
$$ LANGUAGE plpgsql;

-- Function to create event with correlation
CREATE OR REPLACE FUNCTION create_correlated_event(
    p_table_name TEXT,
    p_correlation_id UUID,
    p_event_data JSONB
)
RETURNS UUID AS $$
DECLARE
    new_event_id UUID := uuid_generate_v4();
BEGIN
    -- This is a template function - specific implementations would be created
    -- for each event type with proper validation and insertion logic
    RETURN new_event_id;
END;
$$ LANGUAGE plpgsql;

-- =======================================================================================
-- TRIGGERS FOR DATA INTEGRITY
-- =======================================================================================

-- Function to validate trading event data
CREATE OR REPLACE FUNCTION validate_trading_event()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate required fields based on event type
    IF NEW.event_type = 'order_filled' AND NEW.execution_price IS NULL THEN
        RAISE EXCEPTION 'Fill events must have execution price';
    END IF;

    IF NEW.event_type = 'order_filled' AND NEW.execution_quantity IS NULL THEN
        RAISE EXCEPTION 'Fill events must have execution quantity';
    END IF;

    -- Calculate latency if timestamps are available
    IF NEW.processing_start_timestamp IS NOT NULL AND NEW.processing_end_timestamp IS NOT NULL THEN
        NEW.latency_ns := EXTRACT(EPOCH FROM (NEW.processing_end_timestamp - NEW.processing_start_timestamp)) * 1000000000;
    END IF;

    -- Set remaining quantity
    IF NEW.quantity IS NOT NULL AND NEW.filled_quantity IS NOT NULL THEN
        NEW.remaining_quantity := NEW.quantity - NEW.filled_quantity;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply validation trigger to trading events
CREATE TRIGGER trigger_validate_trading_events
    BEFORE INSERT OR UPDATE ON trading_events
    FOR EACH ROW
    EXECUTE FUNCTION validate_trading_event();

-- =======================================================================================
-- MATERIALIZED VIEWS FOR REPORTING
-- =======================================================================================

-- Real-time trading activity summary
-- Note: ns_timestamp is BIGINT (nanoseconds), convert to timestamptz via to_timestamp
-- Uses actual trading_event_type enum values from migration 001
CREATE MATERIALIZED VIEW IF NOT EXISTS trading_activity_summary AS
SELECT
    date_trunc('hour', to_timestamp(event_timestamp::bigint / 1000000000.0)) as hour,
    symbol,
    COUNT(*) as total_events,
    COUNT(*) FILTER (WHERE event_type = 'order_submitted') as orders_submitted,
    COUNT(*) FILTER (WHERE event_type = 'order_filled') as orders_filled,
    COUNT(*) FILTER (WHERE event_type = 'order_cancelled') as orders_cancelled,
    SUM((event_data->>'quantity')::numeric) FILTER (WHERE event_type = 'order_submitted') as total_quantity,
    SUM((event_data->>'quantity')::numeric) FILTER (WHERE event_type = 'order_filled') as filled_quantity,
    AVG((event_data->>'latency_ns')::bigint) FILTER (WHERE event_data->>'latency_ns' IS NOT NULL) as avg_latency_ns,
    MAX((event_data->>'latency_ns')::bigint) FILTER (WHERE event_data->>'latency_ns' IS NOT NULL) as max_latency_ns
FROM trading_events
WHERE to_timestamp(event_timestamp::bigint / 1000000000.0) >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY date_trunc('hour', to_timestamp(event_timestamp::bigint / 1000000000.0)), symbol;

-- Risk events summary
-- Note: ns_timestamp is BIGINT (nanoseconds), convert to timestamptz via to_timestamp
CREATE MATERIALIZED VIEW IF NOT EXISTS risk_events_summary AS
SELECT
    date_trunc('day', to_timestamp(event_timestamp::bigint / 1000000000.0)) as day,
    event_type as risk_type, -- migration 001 schema uses event_type not risk_type
    severity,
    COUNT(*) as event_count,
    COUNT(*) FILTER (WHERE resolved_timestamp IS NOT NULL) as resolved_count,
    AVG(EXTRACT(EPOCH FROM (
        to_timestamp(resolved_timestamp::bigint / 1000000000.0) -
        to_timestamp(event_timestamp::bigint / 1000000000.0)
    ))/60) FILTER (WHERE resolved_timestamp IS NOT NULL) as avg_resolution_minutes
FROM risk_events
WHERE to_timestamp(event_timestamp::bigint / 1000000000.0) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY date_trunc('day', to_timestamp(event_timestamp::bigint / 1000000000.0)), event_type, severity;

-- Create unique indexes on materialized views
CREATE UNIQUE INDEX IF NOT EXISTS idx_trading_activity_summary_hour_symbol
ON trading_activity_summary(hour, symbol);

CREATE UNIQUE INDEX IF NOT EXISTS idx_risk_events_summary_day_type_severity
ON risk_events_summary(day, risk_type, severity);

-- =======================================================================================
-- COMMENTS FOR DOCUMENTATION
-- =======================================================================================

COMMENT ON TABLE trading_events IS 'Comprehensive audit trail of all trading lifecycle events with nanosecond precision for HFT compliance';
COMMENT ON TABLE risk_events IS 'Risk management events, violations, and alerts with automated resolution tracking';
COMMENT ON TABLE audit_trail IS 'Complete audit trail of system changes and administrative actions for regulatory compliance';
COMMENT ON TABLE ml_signals IS 'Machine learning model predictions and signals with performance tracking';
COMMENT ON TABLE system_events IS 'System health, performance metrics, and operational events';

-- Column comments (only add if columns exist)
DO $$
BEGIN
    -- trading_events comments
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'latency_ns') THEN
        COMMENT ON COLUMN trading_events.latency_ns IS 'Processing latency in nanoseconds for HFT performance monitoring';
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'trading_events' AND column_name = 'correlation_id') THEN
        COMMENT ON COLUMN trading_events.correlation_id IS 'Links related events together for complete transaction tracking';
    END IF;

    -- risk_events comments
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'risk_events' AND column_name = 'breach_percentage') THEN
        COMMENT ON COLUMN risk_events.breach_percentage IS 'Percentage by which limit was breached (>1.0 = breach)';
    END IF;

    -- audit_trail comments
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'audit_trail' AND column_name = 'regulatory_impact') THEN
        COMMENT ON COLUMN audit_trail.regulatory_impact IS 'Flags changes that have regulatory compliance implications';
    END IF;

    -- ml_signals comments
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ml_signals' AND column_name = 'confidence') THEN
        COMMENT ON COLUMN ml_signals.confidence IS 'Model confidence in prediction (0.0-1.0)';
    END IF;

    -- system_events comments
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'system_events' AND column_name = 'latency_ns') THEN
        COMMENT ON COLUMN system_events.latency_ns IS 'System operation latency in nanoseconds';
    END IF;
END $$;

-- Grant appropriate permissions
-- GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO foxhunt_trading_service;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO foxhunt_trading_service;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO foxhunt_trading_service;

-- Schedule partition management (requires pg_cron extension)
-- SELECT cron.schedule('manage-event-partitions', '0 2 * * 0', 'SELECT manage_event_partitions();');