foxhunt/migrations/001_trading_events.sql

-- ================================================================================================
-- Migration 001: Trading Events Schema
-- Comprehensive PostgreSQL schema for HFT trading events with nanosecond precision
-- Production-ready with compliance, audit, and performance optimizations
-- ================================================================================================

-- Enable required extensions for HFT functionality
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
CREATE EXTENSION IF NOT EXISTS "timescaledb" CASCADE; -- For time-series optimization

-- ================================================================================================
-- NANOSECOND TIMESTAMP DOMAIN
-- Custom domain for nanosecond precision timestamps required for HFT
-- ================================================================================================
CREATE DOMAIN ns_timestamp AS BIGINT
CHECK (VALUE >= 0 AND VALUE <= 9223372036854775807); -- Max 64-bit signed integer

COMMENT ON DOMAIN ns_timestamp IS 'Nanoseconds since Unix epoch for HFT precision timing';

-- ================================================================================================
-- TRADING EVENT TYPES ENUM
-- Comprehensive event type classification for all trading activities
-- ================================================================================================
CREATE TYPE trading_event_type AS ENUM (
    'order_submitted',
    'order_accepted',
    'order_rejected',
    'order_modified',
    'order_cancelled',
    'order_expired',
    'order_filled',
    'order_partially_filled',
    'trade_executed',
    'trade_settled',
    'position_opened',
    'position_closed',
    'position_modified',
    'market_data_received',
    'signal_generated',
    'risk_breach',
    'system_startup',
    'system_shutdown',
    'heartbeat'
);

-- ================================================================================================
-- ORDER SIDE AND STATUS ENUMS
-- ================================================================================================
CREATE TYPE order_side AS ENUM ('buy', 'sell', 'short', 'cover');
CREATE TYPE order_type AS ENUM ('market', 'limit', 'stop', 'stop_limit', 'iceberg', 'twap', 'vwap');
CREATE TYPE order_status AS ENUM ('pending', 'accepted', 'rejected', 'partial', 'filled', 'cancelled', 'expired');
CREATE TYPE time_in_force AS ENUM ('day', 'gtc', 'ioc', 'fok', 'gtd');

-- ================================================================================================
-- CORE TRADING EVENTS TABLE
-- Immutable event store for all trading activities with nanosecond precision
-- ================================================================================================
CREATE TABLE trading_events (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    event_id BIGSERIAL NOT NULL, -- Sequential event ID for ordering
    correlation_id UUID NOT NULL, -- Links related events

    -- Timing with nanosecond precision
    event_timestamp ns_timestamp NOT NULL, -- Hardware RDTSC timestamp
    received_timestamp ns_timestamp NOT NULL, -- When system received the event
    processing_timestamp ns_timestamp NOT NULL, -- When system began processing

    -- Event classification
    event_type trading_event_type NOT NULL,
    event_source VARCHAR(100) NOT NULL, -- Component that generated event
    event_version SMALLINT NOT NULL DEFAULT 1, -- Schema version for evolution

    -- Trading context
    symbol VARCHAR(32) NOT NULL,
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),
    venue VARCHAR(50),

    -- Event payload (immutable JSON)
    event_data JSONB NOT NULL, -- Complete event payload
    metadata JSONB, -- Additional metadata

    -- Compliance and audit
    user_id VARCHAR(64),
    session_id UUID,
    request_id UUID, -- Original client request ID
    trace_id UUID, -- Distributed tracing ID

    -- System context
    node_id VARCHAR(50) NOT NULL, -- Which system node processed this
    process_id INTEGER NOT NULL, -- OS process ID
    thread_id INTEGER, -- Thread ID for debugging
    cpu_core SMALLINT, -- CPU core for performance analysis

    -- Checksums for integrity
    event_hash VARCHAR(64) NOT NULL, -- SHA-256 of event_data
    parent_hash VARCHAR(64), -- Hash of previous related event

    -- Partition key for performance
    event_date DATE NOT NULL,

    -- Constraints
    CONSTRAINT chk_timestamps CHECK (
        received_timestamp >= event_timestamp AND
        processing_timestamp >= received_timestamp
    ),

    -- Composite primary key including partition column
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

-- Create table comment
COMMENT ON TABLE trading_events IS 'Immutable event store for all trading activities with nanosecond precision and compliance features';

-- Trigger function to set event_date from event_timestamp (nanoseconds)
CREATE OR REPLACE FUNCTION set_trading_event_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.event_date := DATE(TO_TIMESTAMP(NEW.event_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to auto-populate event_date on insert
CREATE TRIGGER tg_set_trading_event_date
    BEFORE INSERT ON trading_events
    FOR EACH ROW
    EXECUTE FUNCTION set_trading_event_date();

-- ================================================================================================
-- ORDERS TABLE (CURRENT STATE)
-- Mutable state table for current order status (derived from events)
-- ================================================================================================
CREATE TABLE orders (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_order_id VARCHAR(128) UNIQUE, -- Client-provided ID
    exchange_order_id VARCHAR(128), -- Exchange-provided ID
    parent_order_id UUID, -- For child orders (iceberg, etc.)

    -- Order details
    symbol VARCHAR(32) NOT NULL,
    side order_side NOT NULL,
    order_type order_type NOT NULL,
    time_in_force time_in_force NOT NULL DEFAULT 'day',

    -- Quantities (in base units, scaled for precision)
    quantity BIGINT NOT NULL CHECK (quantity > 0),
    filled_quantity BIGINT NOT NULL DEFAULT 0 CHECK (filled_quantity >= 0),
    remaining_quantity BIGINT NOT NULL DEFAULT 0,

    -- Pricing (in cents or smallest currency unit)
    limit_price BIGINT, -- NULL for market orders
    stop_price BIGINT, -- For stop orders
    avg_fill_price BIGINT DEFAULT 0,

    -- Status and timing
    status order_status NOT NULL DEFAULT 'pending',
    created_at ns_timestamp NOT NULL,
    updated_at ns_timestamp NOT NULL,
    expires_at ns_timestamp,

    -- Trading context
    account_id VARCHAR(64) NOT NULL,
    strategy_id VARCHAR(100),
    venue VARCHAR(50) NOT NULL,

    -- Risk and compliance
    risk_check_passed BOOLEAN DEFAULT FALSE,
    compliance_approved BOOLEAN DEFAULT FALSE,
    estimated_commission BIGINT DEFAULT 0,

    -- Metadata
    tags JSONB, -- Flexible tagging system
    notes TEXT, -- Human-readable notes

    -- Audit trail
    created_by VARCHAR(64),
    last_modified_by VARCHAR(64),

    -- Constraints
    CONSTRAINT chk_quantities CHECK (filled_quantity <= quantity),
    CONSTRAINT chk_limit_price CHECK (
        (order_type IN ('market') AND limit_price IS NULL) OR
        (order_type IN ('limit', 'stop_limit') AND limit_price IS NOT NULL)
    ),
    CONSTRAINT chk_stop_price CHECK (
        (order_type IN ('stop', 'stop_limit') AND stop_price IS NOT NULL) OR
        (order_type NOT IN ('stop', 'stop_limit'))
    )
);

-- Trigger function to calculate remaining_quantity
CREATE OR REPLACE FUNCTION set_order_remaining_quantity()
RETURNS TRIGGER AS $$
BEGIN
    NEW.remaining_quantity := NEW.quantity - NEW.filled_quantity;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to auto-populate remaining_quantity on insert/update
CREATE TRIGGER tg_set_order_remaining_quantity
    BEFORE INSERT OR UPDATE OF quantity, filled_quantity ON orders
    FOR EACH ROW
    EXECUTE FUNCTION set_order_remaining_quantity();

-- ================================================================================================
-- FILLS TABLE (TRADE EXECUTIONS)
-- Immutable record of trade executions
-- ================================================================================================
CREATE TABLE fills (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL REFERENCES orders(id),
    execution_id VARCHAR(128) NOT NULL, -- Exchange execution ID
    trade_id VARCHAR(128), -- Exchange trade ID

    -- Execution details
    symbol VARCHAR(32) NOT NULL,
    side order_side NOT NULL,
    quantity BIGINT NOT NULL CHECK (quantity > 0),
    price BIGINT NOT NULL CHECK (price > 0),

    -- Fees and costs
    commission BIGINT NOT NULL DEFAULT 0,
    commission_currency VARCHAR(10) DEFAULT 'USD',
    sec_fee BIGINT DEFAULT 0, -- SEC fees
    taf_fee BIGINT DEFAULT 0, -- TAF fees
    clearing_fee BIGINT DEFAULT 0,

    -- Execution context
    venue VARCHAR(50) NOT NULL,
    execution_timestamp ns_timestamp NOT NULL,
    settlement_date DATE,

    -- Market making classification
    is_maker BOOLEAN, -- True if provided liquidity
    liquidity_flag CHAR(1), -- Exchange-specific liquidity flag

    -- Cross-reference and audit
    contra_broker VARCHAR(50), -- Counterparty broker
    contra_trader VARCHAR(100), -- Counterparty trader ID

    -- System timestamps
    received_at ns_timestamp NOT NULL,
    processed_at ns_timestamp NOT NULL,
    reported_at ns_timestamp, -- When reported to external systems

    -- Metadata
    execution_details JSONB, -- Exchange-specific details

    -- Constraints
    CONSTRAINT uk_fills_execution UNIQUE (venue, execution_id),
    CONSTRAINT chk_fill_timestamps CHECK (
        processed_at >= received_at AND
        execution_timestamp <= received_at
    )
);

-- ================================================================================================
-- POSITIONS TABLE (CURRENT HOLDINGS)
-- Real-time position tracking with mark-to-market
-- ================================================================================================
CREATE TABLE positions (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    account_id VARCHAR(64) NOT NULL,
    strategy_id VARCHAR(100),

    -- Position details
    quantity BIGINT NOT NULL DEFAULT 0, -- Signed: positive=long, negative=short
    avg_cost BIGINT NOT NULL DEFAULT 0, -- Average cost basis (cents)
    realized_pnl BIGINT NOT NULL DEFAULT 0, -- Realized P&L (cents)
    unrealized_pnl BIGINT NOT NULL DEFAULT 0, -- Unrealized P&L (cents)

    -- Market data
    last_price BIGINT NOT NULL DEFAULT 0, -- Last known market price
    market_value BIGINT NOT NULL DEFAULT 0,

    -- Risk metrics
    var_1d BIGINT, -- 1-day Value at Risk
    var_10d BIGINT, -- 10-day Value at Risk
    beta DECIMAL(8,4), -- Beta vs market

    -- Timing
    first_trade_time ns_timestamp, -- When position was opened
    last_trade_time ns_timestamp, -- Last trade affecting position
    last_updated ns_timestamp NOT NULL,

    -- Position limits
    max_position BIGINT, -- Maximum allowed position size
    current_exposure BIGINT NOT NULL DEFAULT 0,

    -- Audit
    version INTEGER NOT NULL DEFAULT 1, -- Optimistic locking version

    -- Constraints
    CONSTRAINT uk_positions_symbol_account UNIQUE (symbol, account_id, strategy_id),
    CONSTRAINT chk_position_times CHECK (
        last_trade_time IS NULL OR
        first_trade_time IS NULL OR
        last_trade_time >= first_trade_time
    )
);

-- Trigger function to calculate market_value and current_exposure
CREATE OR REPLACE FUNCTION set_position_calculated_fields()
RETURNS TRIGGER AS $$
BEGIN
    NEW.market_value := ABS(NEW.quantity) * NEW.last_price;
    NEW.current_exposure := ABS(NEW.quantity * NEW.last_price);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to auto-populate calculated fields on insert/update
CREATE TRIGGER tg_set_position_calculated_fields
    BEFORE INSERT OR UPDATE OF quantity, last_price ON positions
    FOR EACH ROW
    EXECUTE FUNCTION set_position_calculated_fields();

-- ================================================================================================
-- HIGH-PERFORMANCE INDEXES
-- Optimized for HFT query patterns and real-time operations
-- ================================================================================================

-- Trading events indexes (time-series optimized)
CREATE INDEX idx_trading_events_timestamp ON trading_events USING BTREE (event_timestamp);
CREATE INDEX idx_trading_events_symbol_timestamp ON trading_events USING BTREE (symbol, event_timestamp);
CREATE INDEX idx_trading_events_type_timestamp ON trading_events USING BTREE (event_type, event_timestamp);
CREATE INDEX idx_trading_events_correlation ON trading_events USING HASH (correlation_id);
CREATE INDEX idx_trading_events_account ON trading_events USING BTREE (account_id, event_timestamp);
CREATE INDEX idx_trading_events_venue ON trading_events USING BTREE (venue, event_timestamp);
CREATE INDEX idx_trading_events_strategy ON trading_events USING BTREE (strategy_id, event_timestamp);

-- GIN index for JSONB event_data (flexible querying)
CREATE INDEX idx_trading_events_data_gin ON trading_events USING GIN (event_data);
CREATE INDEX idx_trading_events_metadata_gin ON trading_events USING GIN (metadata);

-- Orders indexes (operational queries)
CREATE INDEX idx_orders_symbol_status ON orders USING BTREE (symbol, status);
CREATE INDEX idx_orders_account_status ON orders USING BTREE (account_id, status);
CREATE INDEX idx_orders_client_order_id ON orders USING HASH (client_order_id) WHERE client_order_id IS NOT NULL;
CREATE INDEX idx_orders_exchange_order_id ON orders USING HASH (exchange_order_id) WHERE exchange_order_id IS NOT NULL;
CREATE INDEX idx_orders_venue_status ON orders USING BTREE (venue, status);
CREATE INDEX idx_orders_strategy ON orders USING BTREE (strategy_id, created_at) WHERE strategy_id IS NOT NULL;
CREATE INDEX idx_orders_created_at ON orders USING BTREE (created_at);
CREATE INDEX idx_orders_expires_at ON orders USING BTREE (expires_at) WHERE expires_at IS NOT NULL;

-- Fills indexes (execution analysis)
CREATE INDEX idx_fills_order_id ON fills USING BTREE (order_id);
CREATE INDEX idx_fills_symbol_timestamp ON fills USING BTREE (symbol, execution_timestamp);
CREATE INDEX idx_fills_venue_timestamp ON fills USING BTREE (venue, execution_timestamp);
CREATE INDEX idx_fills_execution_timestamp ON fills USING BTREE (execution_timestamp);
CREATE INDEX idx_fills_settlement_date ON fills USING BTREE (settlement_date) WHERE settlement_date IS NOT NULL;

-- Positions indexes (real-time position management)
CREATE INDEX idx_positions_symbol ON positions USING BTREE (symbol);
CREATE INDEX idx_positions_account ON positions USING BTREE (account_id);
CREATE INDEX idx_positions_strategy ON positions USING BTREE (strategy_id) WHERE strategy_id IS NOT NULL;
CREATE INDEX idx_positions_last_updated ON positions USING BTREE (last_updated);
CREATE INDEX idx_positions_nonzero ON positions USING BTREE (symbol, account_id) WHERE quantity != 0;

-- ================================================================================================
-- AUTOMATIC PARTITIONING FOR TRADING EVENTS
-- Daily partitions for optimal performance and maintenance
-- ================================================================================================

-- Function to create daily partitions for trading events
CREATE OR REPLACE FUNCTION create_trading_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 := 'trading_events_' || to_char(start_date, 'YYYY_MM_DD');

    -- Create partition if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF trading_events
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes for performance
        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 (symbol, event_timestamp)',
                       'idx_' || partition_name || '_symbol_ts', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING HASH (correlation_id)',
                       'idx_' || partition_name || '_correlation', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create partitions for current and next 7 days
DO $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 0..7 LOOP
        PERFORM create_trading_events_partition(CURRENT_DATE + i);
    END LOOP;
END $$;

-- ================================================================================================
-- TRIGGER FUNCTIONS FOR DATA INTEGRITY AND AUTOMATION
-- ================================================================================================

-- Function to update position from fill
CREATE OR REPLACE FUNCTION update_position_from_fill()
RETURNS TRIGGER AS $$
DECLARE
    position_delta BIGINT;
    new_avg_cost BIGINT;
    existing_quantity BIGINT := 0;
    existing_avg_cost BIGINT := 0;
BEGIN
    -- Calculate position delta (buy = positive, sell = negative)
    position_delta := CASE
        WHEN NEW.side IN ('buy', 'cover') THEN NEW.quantity
        ELSE -NEW.quantity
    END;

    -- Get existing position
    SELECT quantity, avg_cost INTO existing_quantity, existing_avg_cost
    FROM positions
    WHERE symbol = NEW.symbol
      AND account_id = (SELECT account_id FROM orders WHERE id = NEW.order_id)
      AND strategy_id = (SELECT strategy_id FROM orders WHERE id = NEW.order_id);

    -- Calculate new average cost
    IF existing_quantity = 0 THEN
        new_avg_cost := NEW.price;
    ELSIF (existing_quantity > 0 AND position_delta > 0) OR
          (existing_quantity < 0 AND position_delta < 0) THEN
        -- Adding to existing position
        new_avg_cost := (ABS(existing_quantity) * existing_avg_cost + ABS(position_delta) * NEW.price)
                       / (ABS(existing_quantity) + ABS(position_delta));
    ELSE
        -- Reducing or reversing position, keep existing avg cost
        new_avg_cost := existing_avg_cost;
    END IF;

    -- Update or insert position
    INSERT INTO positions (
        symbol, account_id, strategy_id, quantity, avg_cost,
        last_price, last_trade_time, last_updated
    )
    SELECT
        NEW.symbol,
        o.account_id,
        o.strategy_id,
        position_delta,
        NEW.price,
        NEW.price,
        NEW.execution_timestamp,
        NEW.execution_timestamp
    FROM orders o WHERE o.id = NEW.order_id
    ON CONFLICT (symbol, account_id, strategy_id)
    DO UPDATE SET
        quantity = positions.quantity + position_delta,
        avg_cost = CASE
            WHEN positions.quantity = 0 THEN NEW.price
            ELSE new_avg_cost
        END,
        last_price = NEW.price,
        last_trade_time = NEW.execution_timestamp,
        last_updated = NEW.execution_timestamp,
        version = positions.version + 1;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to validate order constraints
CREATE OR REPLACE FUNCTION validate_order_constraints()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate price requirements
    IF NEW.order_type = 'limit' AND NEW.limit_price IS NULL THEN
        RAISE EXCEPTION 'Limit orders must specify limit_price';
    END IF;

    IF NEW.order_type IN ('stop', 'stop_limit') AND NEW.stop_price IS NULL THEN
        RAISE EXCEPTION 'Stop orders must specify stop_price';
    END IF;

    -- Validate quantity constraints
    IF NEW.filled_quantity > NEW.quantity THEN
        RAISE EXCEPTION 'Filled quantity cannot exceed order quantity';
    END IF;

    -- Auto-update timestamps
    IF TG_OP = 'INSERT' THEN
        NEW.created_at := EXTRACT(EPOCH FROM NOW()) * 1000000000;
        NEW.updated_at := NEW.created_at;
    ELSE
        NEW.updated_at := EXTRACT(EPOCH FROM NOW()) * 1000000000;
    END IF;

    -- Update status based on fill level
    IF NEW.filled_quantity = 0 THEN
        NEW.status := 'pending';
    ELSIF NEW.filled_quantity = NEW.quantity THEN
        NEW.status := 'filled';
    ELSIF NEW.filled_quantity > 0 THEN
        NEW.status := 'partial';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to generate trading events from orders
CREATE OR REPLACE FUNCTION generate_order_event()
RETURNS TRIGGER AS $$
DECLARE
    event_type_val trading_event_type;
    event_ts ns_timestamp;
BEGIN
    event_ts := EXTRACT(EPOCH FROM NOW()) * 1000000000;

    -- Determine event type
    IF TG_OP = 'INSERT' THEN
        event_type_val := 'order_submitted';
    ELSE
        -- Update case - determine based on status change
        CASE
            WHEN OLD.status != NEW.status THEN
                CASE NEW.status
                    WHEN 'accepted' THEN event_type_val := 'order_accepted';
                    WHEN 'rejected' THEN event_type_val := 'order_rejected';
                    WHEN 'cancelled' THEN event_type_val := 'order_cancelled';
                    WHEN 'expired' THEN event_type_val := 'order_expired';
                    WHEN 'filled' THEN event_type_val := 'order_filled';
                    WHEN 'partial' THEN event_type_val := 'order_partially_filled';
                    ELSE event_type_val := 'order_modified';
                END CASE;
            ELSE
                event_type_val := 'order_modified';
        END CASE;
    END IF;

    -- Insert trading event
    INSERT INTO trading_events (
        correlation_id, event_timestamp, received_timestamp, processing_timestamp,
        event_type, event_source, symbol, account_id, strategy_id, venue,
        event_data, node_id, process_id, event_hash
    ) VALUES (
        COALESCE(NEW.id, OLD.id),
        event_ts,
        event_ts,
        event_ts,
        event_type_val,
        'order_management',
        COALESCE(NEW.symbol, OLD.symbol),
        COALESCE(NEW.account_id, OLD.account_id),
        COALESCE(NEW.strategy_id, OLD.strategy_id),
        COALESCE(NEW.venue, OLD.venue),
        jsonb_build_object(
            'order_id', COALESCE(NEW.id, OLD.id),
            'client_order_id', COALESCE(NEW.client_order_id, OLD.client_order_id),
            'order_type', COALESCE(NEW.order_type, OLD.order_type),
            'side', COALESCE(NEW.side, OLD.side),
            'quantity', COALESCE(NEW.quantity, OLD.quantity),
            'filled_quantity', COALESCE(NEW.filled_quantity, OLD.filled_quantity),
            'limit_price', COALESCE(NEW.limit_price, OLD.limit_price),
            'status', COALESCE(NEW.status, OLD.status)
        ),
        'trading-node-01', -- node_id literal; overridden per-deployment via later migrations
        pg_backend_pid(),
        encode(sha256(COALESCE(NEW.id, OLD.id)::text::bytea), 'hex')
    );

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

-- ================================================================================================
-- CREATE TRIGGERS
-- ================================================================================================

-- Order validation and event generation
CREATE TRIGGER tg_validate_orders
    BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_order_constraints();

CREATE TRIGGER tg_generate_order_events
    AFTER INSERT OR UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION generate_order_event();

-- Position updates from fills
CREATE TRIGGER tg_update_position_from_fill
    AFTER INSERT ON fills
    FOR EACH ROW
    EXECUTE FUNCTION update_position_from_fill();

-- ================================================================================================
-- ANALYTICAL VIEWS FOR REPORTING
-- ================================================================================================

-- Real-time order book view
CREATE VIEW v_active_orders AS
SELECT
    o.id,
    o.symbol,
    o.side,
    o.order_type,
    o.quantity,
    o.filled_quantity,
    o.remaining_quantity,
    o.limit_price,
    o.status,
    o.created_at,
    o.account_id,
    o.venue
FROM orders o
WHERE o.status IN ('pending', 'accepted', 'partial')
ORDER BY o.symbol, o.side, o.limit_price;

-- Position summary view
CREATE VIEW v_position_summary AS
SELECT
    p.symbol,
    p.account_id,
    p.strategy_id,
    p.quantity,
    p.avg_cost,
    p.last_price,
    p.market_value,
    p.unrealized_pnl,
    p.realized_pnl,
    (p.unrealized_pnl + p.realized_pnl) as total_pnl,
    p.last_updated
FROM positions p
WHERE p.quantity != 0
ORDER BY ABS(p.market_value) DESC;

-- Daily trading summary view
CREATE VIEW v_daily_trading_summary AS
SELECT
    DATE(TO_TIMESTAMP(f.execution_timestamp / 1000000000.0)) as trade_date,
    f.symbol,
    o.account_id,
    COUNT(*) as trade_count,
    SUM(f.quantity) as total_volume,
    AVG(f.price) as avg_price,
    MIN(f.price) as min_price,
    MAX(f.price) as max_price,
    SUM(f.commission) as total_commission
FROM fills f
JOIN orders o ON f.order_id = o.id
GROUP BY DATE(TO_TIMESTAMP(f.execution_timestamp / 1000000000.0)), f.symbol, o.account_id
ORDER BY trade_date DESC, total_volume DESC;

-- ================================================================================================
-- PERFORMANCE MONITORING FUNCTIONS
-- ================================================================================================

-- Function to get trading events statistics
CREATE OR REPLACE FUNCTION get_trading_events_stats(
    start_time ns_timestamp DEFAULT NULL,
    end_time ns_timestamp DEFAULT NULL
) RETURNS TABLE (
    event_type trading_event_type,
    event_count BIGINT,
    avg_processing_time_ns NUMERIC,
    max_processing_time_ns BIGINT,
    events_per_second NUMERIC
) AS $$
DECLARE
    default_start ns_timestamp := EXTRACT(EPOCH FROM (NOW() - INTERVAL '1 hour')) * 1000000000;
    default_end ns_timestamp := EXTRACT(EPOCH FROM NOW()) * 1000000000;
    time_span_seconds NUMERIC;
BEGIN
    start_time := COALESCE(start_time, default_start);
    end_time := COALESCE(end_time, default_end);
    time_span_seconds := (end_time - start_time) / 1000000000.0;

    RETURN QUERY
    SELECT
        te.event_type,
        COUNT(*) as event_count,
        AVG(te.processing_timestamp - te.received_timestamp) as avg_processing_time_ns,
        MAX(te.processing_timestamp - te.received_timestamp) as max_processing_time_ns,
        (COUNT(*) / time_span_seconds) as events_per_second
    FROM trading_events te
    WHERE te.event_timestamp BETWEEN start_time AND end_time
    GROUP BY te.event_type
    ORDER BY event_count DESC;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- ARCHIVAL AND RETENTION POLICY
-- ================================================================================================

-- Function to archive old trading events (for 7+ year retention)
CREATE OR REPLACE FUNCTION archive_old_trading_events(retention_days INTEGER DEFAULT 2555) -- 7 years
RETURNS INTEGER AS $$
DECLARE
    archive_date DATE;
    archived_count INTEGER := 0;
BEGIN
    archive_date := CURRENT_DATE - INTERVAL '1 day' * retention_days;

    -- Move old partitions to archive schema (implement as needed)
    -- This is a placeholder for actual archival implementation

    RETURN archived_count;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- GRANTS AND PERMISSIONS
-- ================================================================================================
-- Note: Uncomment and modify these grants based on your specific user roles

-- Application user permissions
-- GRANT SELECT, INSERT ON trading_events TO trading_app_user;
-- GRANT SELECT, INSERT, UPDATE ON orders TO trading_app_user;
-- GRANT SELECT, INSERT ON fills TO trading_app_user;
-- GRANT SELECT, UPDATE ON positions TO trading_app_user;

-- Read-only analytics user
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;
-- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO analytics_user;

-- Risk management user (read positions and events)
-- GRANT SELECT ON trading_events, positions, orders, fills TO risk_user;

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

COMMENT ON TABLE trading_events IS 'Immutable event store for all trading activities with nanosecond precision. Partitioned by date for optimal performance. Never update or delete records for compliance.';
COMMENT ON TABLE orders IS 'Current state of trading orders. Mutable table derived from trading_events. Primary operational table for order management.';
COMMENT ON TABLE fills IS 'Immutable record of trade executions. Links to orders and triggers position updates. Critical for P&L calculation and reporting.';
COMMENT ON TABLE positions IS 'Current position holdings with real-time mark-to-market. Updated via triggers from fills. Primary table for risk management.';

COMMENT ON DOMAIN ns_timestamp IS 'Nanoseconds since Unix epoch (1970-01-01 00:00:00 UTC). Used for microsecond-precision timing in HFT systems.';

-- Performance notes
COMMENT ON INDEX idx_trading_events_timestamp IS 'Primary time-series index for trading events. Critical for chronological queries and event replay.';
COMMENT ON INDEX idx_orders_symbol_status IS 'Composite index for order book queries by symbol and status. Essential for active order management.';
COMMENT ON INDEX idx_positions_nonzero IS 'Partial index for non-zero positions only. Optimizes position management queries by excluding closed positions.';