foxhunt/migrations/047_broker_gateway_tables.sql

-- ================================================================================================
-- Migration 047: Broker Gateway Tables for AMP Futures FIX Integration
-- Purpose: Database schema for FIX protocol broker integration with session management,
--          order tracking, execution reports, and position reconciliation
-- Date: 2025-11-09
-- Agent: 1 (Broker Gateway Database Schema)
-- ================================================================================================

-- ================================================================================================
-- TABLE 1: BROKER_SESSIONS
-- Tracks FIX session state for recovery after disconnects
-- ================================================================================================

CREATE TABLE broker_sessions (
    -- Primary identifier
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- FIX session identifiers
    session_id VARCHAR(128) NOT NULL UNIQUE,          -- Composite: "FOXHUNT_CLIENT-CQG"
    sender_comp_id VARCHAR(64) NOT NULL,              -- Tag 49 (SenderCompID)
    target_comp_id VARCHAR(64) NOT NULL,              -- Tag 56 (TargetCompID)

    -- FIX sequence numbers (critical for message ordering)
    sender_seq_num BIGINT NOT NULL DEFAULT 1,         -- Our outgoing sequence number
    target_seq_num BIGINT NOT NULL DEFAULT 1,         -- Expected incoming sequence number

    -- Session state
    session_state VARCHAR(32) NOT NULL,               -- DISCONNECTED, CONNECTED, LOGGING_IN, ACTIVE, LOGGING_OUT, RECONNECTING

    -- Heartbeat monitoring
    last_heartbeat_sent TIMESTAMPTZ,                  -- Last heartbeat we sent (Tag 35=0)
    last_heartbeat_received TIMESTAMPTZ,              -- Last heartbeat we received

    -- Connection lifecycle
    connected_at TIMESTAMPTZ,                         -- TCP connection established
    disconnected_at TIMESTAMPTZ,                      -- TCP connection closed

    -- Audit timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- Session first created
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- Last state update

    -- Constraints
    CONSTRAINT chk_broker_session_state CHECK (
        session_state IN ('DISCONNECTED', 'CONNECTED', 'LOGGING_IN', 'ACTIVE', 'LOGGING_OUT', 'RECONNECTING')
    ),
    CONSTRAINT chk_broker_session_sequences CHECK (
        sender_seq_num >= 1 AND target_seq_num >= 1
    )
);

-- Add table comment
COMMENT ON TABLE broker_sessions IS 'FIX session state tracking for broker gateway - manages sequence numbers and connection lifecycle';

-- Add column comments
COMMENT ON COLUMN broker_sessions.session_id IS 'Unique FIX session identifier (SenderCompID-TargetCompID)';
COMMENT ON COLUMN broker_sessions.sender_comp_id IS 'FIX SenderCompID (Tag 49) - our client identifier';
COMMENT ON COLUMN broker_sessions.target_comp_id IS 'FIX TargetCompID (Tag 56) - broker gateway identifier';
COMMENT ON COLUMN broker_sessions.sender_seq_num IS 'Outgoing FIX message sequence number (Tag 34) - persisted for recovery';
COMMENT ON COLUMN broker_sessions.target_seq_num IS 'Expected incoming FIX message sequence number - persisted for gap detection';
COMMENT ON COLUMN broker_sessions.session_state IS 'Current FIX session state (DISCONNECTED, ACTIVE, etc.)';
COMMENT ON COLUMN broker_sessions.last_heartbeat_sent IS 'Timestamp of last Heartbeat message sent (Tag 35=0)';
COMMENT ON COLUMN broker_sessions.last_heartbeat_received IS 'Timestamp of last Heartbeat message received - used for timeout detection';

-- ================================================================================================
-- TABLE 2: BROKER_ORDERS
-- Audit trail for all orders routed to broker via FIX protocol
-- ================================================================================================

CREATE TABLE broker_orders (
    -- Primary identifier
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- FIX order identifiers
    client_order_id VARCHAR(64) NOT NULL UNIQUE,      -- Our ClOrdID (Tag 11) - UUID format
    broker_order_id VARCHAR(64),                      -- Broker OrderID (Tag 37) - filled after ExecutionReport

    -- Integration with internal order tracking
    internal_order_id UUID,                           -- Optional FK to orders table

    -- Trading context
    account_id VARCHAR(64) NOT NULL,                  -- AMP account identifier
    symbol VARCHAR(32) NOT NULL,                      -- Instrument symbol (ES, NQ, etc.)
    side VARCHAR(4) NOT NULL,                         -- BUY, SELL
    order_type VARCHAR(16) NOT NULL,                  -- MARKET, LIMIT, STOP, STOP_LIMIT

    -- Order quantities
    quantity NUMERIC(18, 8) NOT NULL,                 -- Order quantity in contracts
    filled_quantity NUMERIC(18, 8) NOT NULL DEFAULT 0, -- Quantity filled so far

    -- Pricing (NULL for market orders)
    price NUMERIC(18, 8),                             -- Limit price (Tag 44)
    stop_price NUMERIC(18, 8),                        -- Stop price (Tag 99) for stop orders
    avg_fill_price NUMERIC(18, 8),                    -- Average fill price (Tag 6)

    -- Order parameters
    time_in_force VARCHAR(8) NOT NULL DEFAULT 'DAY',  -- DAY, GTC, IOC, FOK, GTD

    -- Order lifecycle status
    status VARCHAR(32) NOT NULL,                      -- PENDING_SUBMIT, SUBMITTED, PARTIALLY_FILLED, FILLED, REJECTED, CANCELLED, EXPIRED

    -- Financial tracking
    commission NUMERIC(18, 8),                        -- Commission charged by broker

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,               -- Strategy, model, additional context

    -- Timing
    submitted_at TIMESTAMPTZ,                         -- When order was submitted to broker
    filled_at TIMESTAMPTZ,                            -- When order was fully filled
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- When record was created
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- Last update

    -- Constraints
    CONSTRAINT chk_broker_order_side CHECK (side IN ('BUY', 'SELL')),
    CONSTRAINT chk_broker_order_type CHECK (
        order_type IN ('MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT')
    ),
    CONSTRAINT chk_broker_order_tif CHECK (
        time_in_force IN ('DAY', 'GTC', 'IOC', 'FOK', 'GTD')
    ),
    CONSTRAINT chk_broker_order_status CHECK (
        status IN ('PENDING_SUBMIT', 'SUBMITTED', 'PARTIALLY_FILLED', 'FILLED',
                   'REJECTED', 'CANCELLED', 'EXPIRED', 'CANCEL_PENDING')
    ),
    CONSTRAINT chk_broker_order_quantity CHECK (quantity > 0),
    CONSTRAINT chk_broker_order_filled CHECK (
        filled_quantity >= 0 AND filled_quantity <= quantity
    ),
    CONSTRAINT chk_broker_order_price CHECK (
        (order_type = 'MARKET' AND price IS NULL) OR
        (order_type IN ('LIMIT', 'STOP_LIMIT') AND price IS NOT NULL)
    ),
    CONSTRAINT chk_broker_order_stop_price CHECK (
        (order_type IN ('STOP', 'STOP_LIMIT') AND stop_price IS NOT NULL) OR
        (order_type IN ('MARKET', 'LIMIT') AND stop_price IS NULL)
    )
);

-- Add table comment
COMMENT ON TABLE broker_orders IS 'Broker order audit trail - tracks all orders routed to broker via FIX protocol';

-- Add column comments
COMMENT ON COLUMN broker_orders.client_order_id IS 'FIX ClOrdID (Tag 11) - unique client order identifier (UUID format)';
COMMENT ON COLUMN broker_orders.broker_order_id IS 'FIX OrderID (Tag 37) - broker-assigned order identifier (filled after ExecutionReport)';
COMMENT ON COLUMN broker_orders.internal_order_id IS 'Optional foreign key to internal orders table for tracking';
COMMENT ON COLUMN broker_orders.account_id IS 'AMP Futures account identifier';
COMMENT ON COLUMN broker_orders.symbol IS 'Trading symbol (ES, NQ, CL, etc.)';
COMMENT ON COLUMN broker_orders.side IS 'Order side: BUY or SELL (FIX Tag 54: 1=Buy, 2=Sell)';
COMMENT ON COLUMN broker_orders.order_type IS 'Order type: MARKET, LIMIT, STOP, STOP_LIMIT (FIX Tag 40)';
COMMENT ON COLUMN broker_orders.quantity IS 'Order quantity in contracts (FIX Tag 38)';
COMMENT ON COLUMN broker_orders.filled_quantity IS 'Cumulative filled quantity (FIX Tag 14)';
COMMENT ON COLUMN broker_orders.price IS 'Limit price for LIMIT/STOP_LIMIT orders (FIX Tag 44)';
COMMENT ON COLUMN broker_orders.stop_price IS 'Stop price for STOP/STOP_LIMIT orders (FIX Tag 99)';
COMMENT ON COLUMN broker_orders.avg_fill_price IS 'Average fill price across all executions (FIX Tag 6)';
COMMENT ON COLUMN broker_orders.time_in_force IS 'Time in force: DAY, GTC, IOC, FOK (FIX Tag 59)';
COMMENT ON COLUMN broker_orders.status IS 'Order status in lifecycle (mapped from FIX Tag 39)';
COMMENT ON COLUMN broker_orders.metadata IS 'Additional order metadata (strategy, model, risk checks, etc.)';

-- ================================================================================================
-- TABLE 3: BROKER_FILLS
-- Stores every ExecutionReport (FIX Tag 35=8) received from broker
-- ================================================================================================

CREATE TABLE broker_fills (
    -- Primary identifier
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- FIX execution identifiers
    execution_id VARCHAR(64) NOT NULL UNIQUE,         -- ExecID (Tag 17) - unique per fill
    broker_order_id VARCHAR(64) NOT NULL,             -- OrderID (Tag 37)
    client_order_id VARCHAR(64) NOT NULL,             -- ClOrdID (Tag 11) - links to broker_orders

    -- Integration with internal execution tracking
    internal_execution_id UUID,                       -- Optional FK to executions table

    -- Trade details
    symbol VARCHAR(32) NOT NULL,                      -- Instrument symbol
    side VARCHAR(4) NOT NULL,                         -- BUY, SELL

    -- Execution type and status
    exec_type VARCHAR(16) NOT NULL,                   -- NEW, TRADE, CANCELED, REJECTED (Tag 150)
    order_status VARCHAR(32) NOT NULL,                -- Order status after this execution (Tag 39)

    -- Fill quantities and pricing
    last_qty NUMERIC(18, 8),                          -- Quantity filled in this report (Tag 32)
    last_price NUMERIC(18, 8),                        -- Fill price for this report (Tag 31)
    cum_qty NUMERIC(18, 8),                           -- Total filled quantity (Tag 14)
    avg_price NUMERIC(18, 8),                         -- Average fill price (Tag 6)
    leaves_qty NUMERIC(18, 8),                        -- Remaining unfilled quantity (Tag 151)

    -- Financial tracking
    commission NUMERIC(18, 8),                        -- Commission charged (if provided)

    -- Timing
    transact_time TIMESTAMPTZ NOT NULL,               -- Exchange execution time (Tag 60)
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),   -- When we received the ExecutionReport

    -- Audit trail
    text TEXT,                                        -- Reject reason or notes (Tag 58)
    raw_fix_message TEXT,                             -- Full FIX message for regulatory compliance
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- Record creation time

    -- Constraints
    CONSTRAINT chk_broker_fill_side CHECK (side IN ('BUY', 'SELL')),
    CONSTRAINT chk_broker_fill_exec_type CHECK (
        exec_type IN ('NEW', 'TRADE', 'CANCELED', 'REJECTED', 'PENDING_CANCEL',
                      'REPLACED', 'PENDING_REPLACE', 'STOPPED', 'SUSPENDED',
                      'RESTATED', 'EXPIRED')
    ),
    CONSTRAINT chk_broker_fill_order_status CHECK (
        order_status IN ('NEW', 'PARTIALLY_FILLED', 'FILLED', 'CANCELED',
                         'REJECTED', 'EXPIRED', 'PENDING_CANCEL', 'PENDING_REPLACE')
    ),
    CONSTRAINT chk_broker_fill_quantities CHECK (
        (last_qty IS NULL OR last_qty >= 0) AND
        (cum_qty IS NULL OR cum_qty >= 0) AND
        (leaves_qty IS NULL OR leaves_qty >= 0)
    )
);

-- Add table comment
COMMENT ON TABLE broker_fills IS 'Broker execution reports - stores every ExecutionReport (FIX Tag 35=8) for audit and compliance';

-- Add column comments
COMMENT ON COLUMN broker_fills.execution_id IS 'FIX ExecID (Tag 17) - unique execution identifier';
COMMENT ON COLUMN broker_fills.broker_order_id IS 'FIX OrderID (Tag 37) - broker-assigned order identifier';
COMMENT ON COLUMN broker_fills.client_order_id IS 'FIX ClOrdID (Tag 11) - links to broker_orders table';
COMMENT ON COLUMN broker_fills.exec_type IS 'FIX ExecType (Tag 150) - execution type (NEW, TRADE, CANCELED, REJECTED)';
COMMENT ON COLUMN broker_fills.order_status IS 'FIX OrdStatus (Tag 39) - order status after this execution';
COMMENT ON COLUMN broker_fills.last_qty IS 'FIX LastQty (Tag 32) - quantity filled in this report';
COMMENT ON COLUMN broker_fills.last_price IS 'FIX LastPx (Tag 31) - fill price for this report';
COMMENT ON COLUMN broker_fills.cum_qty IS 'FIX CumQty (Tag 14) - total filled quantity across all fills';
COMMENT ON COLUMN broker_fills.avg_price IS 'FIX AvgPx (Tag 6) - average fill price';
COMMENT ON COLUMN broker_fills.leaves_qty IS 'FIX LeavesQty (Tag 151) - remaining unfilled quantity';
COMMENT ON COLUMN broker_fills.transact_time IS 'FIX TransactTime (Tag 60) - exchange execution timestamp';
COMMENT ON COLUMN broker_fills.text IS 'FIX Text (Tag 58) - reject reason or notes';
COMMENT ON COLUMN broker_fills.raw_fix_message IS 'Complete FIX message for regulatory compliance and debugging';

-- ================================================================================================
-- TABLE 4: BROKER_POSITIONS
-- Real-time position reconciliation with broker
-- ================================================================================================

CREATE TABLE broker_positions (
    -- Primary identifier
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Position identifiers
    account_id VARCHAR(64) NOT NULL,                  -- AMP account identifier
    symbol VARCHAR(32) NOT NULL,                      -- Instrument symbol

    -- Position details
    quantity NUMERIC(18, 8) NOT NULL DEFAULT 0,       -- Signed: positive=long, negative=short
    avg_entry_price NUMERIC(18, 8),                   -- Average entry price

    -- Market value and P&L
    market_value NUMERIC(18, 8),                      -- Current market value
    unrealized_pnl NUMERIC(18, 8),                    -- Unrealized profit/loss
    realized_pnl NUMERIC(18, 8),                      -- Realized profit/loss (closed trades)

    -- Timing
    last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- Last position update from broker
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    -- Position first opened

    -- Constraints
    CONSTRAINT uk_broker_positions_account_symbol UNIQUE (account_id, symbol)
);

-- Add table comment
COMMENT ON TABLE broker_positions IS 'Real-time position cache from broker - used for reconciliation and risk checks';

-- Add column comments
COMMENT ON COLUMN broker_positions.account_id IS 'AMP Futures account identifier';
COMMENT ON COLUMN broker_positions.symbol IS 'Trading symbol (ES, NQ, CL, etc.)';
COMMENT ON COLUMN broker_positions.quantity IS 'Position quantity (signed: positive=long, negative=short)';
COMMENT ON COLUMN broker_positions.avg_entry_price IS 'Average entry price across all trades';
COMMENT ON COLUMN broker_positions.market_value IS 'Current market value of position';
COMMENT ON COLUMN broker_positions.unrealized_pnl IS 'Unrealized profit/loss on open position';
COMMENT ON COLUMN broker_positions.realized_pnl IS 'Realized profit/loss from closed trades';
COMMENT ON COLUMN broker_positions.last_updated IS 'Last update timestamp from broker';

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

-- Broker sessions indexes
CREATE INDEX idx_broker_sessions_state ON broker_sessions(session_state);
CREATE INDEX idx_broker_sessions_updated_at ON broker_sessions(updated_at DESC);

COMMENT ON INDEX idx_broker_sessions_state IS 'Fast lookups for active FIX sessions';
COMMENT ON INDEX idx_broker_sessions_updated_at IS 'Time-series queries for session lifecycle';

-- Broker orders indexes
CREATE INDEX idx_broker_orders_broker_order_id ON broker_orders(broker_order_id);
CREATE INDEX idx_broker_orders_account_id ON broker_orders(account_id);
CREATE INDEX idx_broker_orders_symbol ON broker_orders(symbol);
CREATE INDEX idx_broker_orders_status ON broker_orders(status);
CREATE INDEX idx_broker_orders_created_at ON broker_orders(created_at DESC);
CREATE INDEX idx_broker_orders_internal_order_id ON broker_orders(internal_order_id);

-- Composite indexes for common queries
CREATE INDEX idx_broker_orders_symbol_status ON broker_orders(symbol, status);
CREATE INDEX idx_broker_orders_account_status ON broker_orders(account_id, status);

COMMENT ON INDEX idx_broker_orders_broker_order_id IS 'Fast lookups by broker-assigned OrderID (Tag 37)';
COMMENT ON INDEX idx_broker_orders_account_id IS 'Account-based queries for order history';
COMMENT ON INDEX idx_broker_orders_symbol_status IS 'Composite index for active orders per symbol';
COMMENT ON INDEX idx_broker_orders_internal_order_id IS 'Link to internal orders table';

-- Broker fills indexes
CREATE INDEX idx_broker_fills_broker_order_id ON broker_fills(broker_order_id);
CREATE INDEX idx_broker_fills_client_order_id ON broker_fills(client_order_id);
CREATE INDEX idx_broker_fills_transact_time ON broker_fills(transact_time DESC);
CREATE INDEX idx_broker_fills_symbol ON broker_fills(symbol);
CREATE INDEX idx_broker_fills_internal_execution_id ON broker_fills(internal_execution_id);

COMMENT ON INDEX idx_broker_fills_broker_order_id IS 'Fast lookups of fills by broker OrderID';
COMMENT ON INDEX idx_broker_fills_client_order_id IS 'Fast lookups of fills by client ClOrdID';
COMMENT ON INDEX idx_broker_fills_transact_time IS 'Time-series queries for execution history';
COMMENT ON INDEX idx_broker_fills_internal_execution_id IS 'Link to internal executions table';

-- Broker positions indexes
CREATE INDEX idx_broker_positions_account_id ON broker_positions(account_id);
CREATE INDEX idx_broker_positions_symbol ON broker_positions(symbol);
CREATE INDEX idx_broker_positions_last_updated ON broker_positions(last_updated DESC);

COMMENT ON INDEX idx_broker_positions_account_id IS 'Fast lookups of all positions for an account';
COMMENT ON INDEX idx_broker_positions_symbol IS 'Fast lookups of positions by symbol';
COMMENT ON INDEX idx_broker_positions_last_updated IS 'Detect stale position data';

-- ================================================================================================
-- FOREIGN KEY CONSTRAINTS
-- ================================================================================================

-- broker_fills → broker_orders (required relationship)
ALTER TABLE broker_fills
ADD CONSTRAINT fk_broker_fills_client_order_id
FOREIGN KEY (client_order_id) REFERENCES broker_orders(client_order_id)
ON DELETE CASCADE;

COMMENT ON CONSTRAINT fk_broker_fills_client_order_id ON broker_fills IS 'Links execution reports to parent orders - CASCADE delete removes fills when order is deleted';

-- broker_orders → orders (optional relationship, only if orders table exists)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'orders') THEN
        ALTER TABLE broker_orders
        ADD CONSTRAINT fk_broker_orders_internal_order_id
        FOREIGN KEY (internal_order_id) REFERENCES orders(id)
        ON DELETE SET NULL;

        RAISE NOTICE 'Created foreign key: broker_orders.internal_order_id → orders.id';
    ELSE
        RAISE NOTICE 'Skipped foreign key: orders table does not exist (will be added later)';
    END IF;
END $$;

-- broker_fills → executions (optional relationship, only if executions table exists)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'executions') THEN
        ALTER TABLE broker_fills
        ADD CONSTRAINT fk_broker_fills_internal_execution_id
        FOREIGN KEY (internal_execution_id) REFERENCES executions(id)
        ON DELETE SET NULL;

        RAISE NOTICE 'Created foreign key: broker_fills.internal_execution_id → executions.id';
    ELSE
        RAISE NOTICE 'Skipped foreign key: executions table does not exist (will be added later)';
    END IF;
END $$;

-- ================================================================================================
-- TRIGGER FUNCTIONS FOR DATA INTEGRITY
-- ================================================================================================

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

-- Trigger to auto-update broker_orders.updated_at
CREATE TRIGGER tg_broker_orders_update_timestamp
    BEFORE UPDATE ON broker_orders
    FOR EACH ROW
    EXECUTE FUNCTION update_broker_orders_timestamp();

-- Function to auto-update broker_sessions.updated_at
CREATE OR REPLACE FUNCTION update_broker_sessions_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update broker_sessions.updated_at
CREATE TRIGGER tg_broker_sessions_update_timestamp
    BEFORE UPDATE ON broker_sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_broker_sessions_timestamp();

COMMENT ON FUNCTION update_broker_orders_timestamp() IS 'Auto-updates broker_orders.updated_at on modification';
COMMENT ON FUNCTION update_broker_sessions_timestamp() IS 'Auto-updates broker_sessions.updated_at on modification';

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

-- View for active broker orders
CREATE OR REPLACE VIEW v_active_broker_orders AS
SELECT
    bo.id,
    bo.client_order_id,
    bo.broker_order_id,
    bo.account_id,
    bo.symbol,
    bo.side,
    bo.order_type,
    bo.quantity,
    bo.filled_quantity,
    bo.price,
    bo.avg_fill_price,
    bo.status,
    bo.time_in_force,
    bo.submitted_at,
    bo.created_at,
    EXTRACT(EPOCH FROM (NOW() - bo.created_at)) AS age_seconds,
    (bo.quantity - bo.filled_quantity) AS remaining_quantity
FROM broker_orders bo
WHERE bo.status IN ('PENDING_SUBMIT', 'SUBMITTED', 'PARTIALLY_FILLED', 'CANCEL_PENDING')
ORDER BY bo.created_at DESC;

COMMENT ON VIEW v_active_broker_orders IS 'Active broker orders (not yet fully filled, cancelled, or rejected)';

-- View for broker order fill summary
CREATE OR REPLACE VIEW v_broker_order_fill_summary AS
SELECT
    bo.client_order_id,
    bo.broker_order_id,
    bo.symbol,
    bo.side,
    bo.quantity AS order_qty,
    bo.filled_quantity AS total_filled,
    bo.avg_fill_price,
    bo.status,
    bo.created_at,
    COUNT(bf.id) AS num_fills,
    MIN(bf.transact_time) AS first_fill_time,
    MAX(bf.transact_time) AS last_fill_time,
    SUM(bf.commission) AS total_commission
FROM broker_orders bo
LEFT JOIN broker_fills bf ON bo.client_order_id = bf.client_order_id AND bf.exec_type = 'TRADE'
GROUP BY bo.client_order_id, bo.broker_order_id, bo.symbol, bo.side, bo.quantity, bo.filled_quantity, bo.avg_fill_price, bo.status, bo.created_at
ORDER BY bo.created_at DESC;

COMMENT ON VIEW v_broker_order_fill_summary IS 'Summary of fills per order - useful for fill rate analysis';

-- View for position reconciliation
CREATE OR REPLACE VIEW v_broker_position_reconciliation AS
SELECT
    bp.account_id,
    bp.symbol,
    bp.quantity AS broker_qty,
    COALESCE(p.quantity / 100000000, 0) AS internal_qty,  -- Convert from base units (cents)
    (bp.quantity - COALESCE(p.quantity / 100000000, 0)) AS delta,
    bp.unrealized_pnl AS broker_unrealized_pnl,
    p.unrealized_pnl / 100 AS internal_unrealized_pnl,    -- Convert from cents
    bp.last_updated AS broker_last_updated,
    p.last_updated AS internal_last_updated
FROM broker_positions bp
LEFT JOIN positions p ON bp.symbol = p.symbol AND bp.account_id = p.account_id
ORDER BY ABS(bp.quantity - COALESCE(p.quantity / 100000000, 0)) DESC;

COMMENT ON VIEW v_broker_position_reconciliation IS 'Position reconciliation between broker and internal tracking - highlights mismatches';

-- ================================================================================================
-- GRANT PERMISSIONS
-- ================================================================================================

-- Grant permissions to foxhunt role
GRANT SELECT, INSERT, UPDATE ON broker_sessions TO foxhunt;
GRANT SELECT, INSERT, UPDATE ON broker_orders TO foxhunt;
GRANT SELECT, INSERT ON broker_fills TO foxhunt;
GRANT SELECT, INSERT, UPDATE, DELETE ON broker_positions TO foxhunt;

-- Grant view access
GRANT SELECT ON v_active_broker_orders TO foxhunt;
GRANT SELECT ON v_broker_order_fill_summary TO foxhunt;
GRANT SELECT ON v_broker_position_reconciliation TO foxhunt;

-- ================================================================================================
-- FINAL VALIDATION
-- ================================================================================================

DO $$
BEGIN
    -- Verify tables exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'broker_sessions') THEN
        RAISE EXCEPTION 'Migration 047 failed: broker_sessions table not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'broker_orders') THEN
        RAISE EXCEPTION 'Migration 047 failed: broker_orders table not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'broker_fills') THEN
        RAISE EXCEPTION 'Migration 047 failed: broker_fills table not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'broker_positions') THEN
        RAISE EXCEPTION 'Migration 047 failed: broker_positions table not created';
    END IF;

    -- Verify views exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.views WHERE table_name = 'v_active_broker_orders') THEN
        RAISE EXCEPTION 'Migration 047 failed: v_active_broker_orders view not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.views WHERE table_name = 'v_broker_order_fill_summary') THEN
        RAISE EXCEPTION 'Migration 047 failed: v_broker_order_fill_summary view not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.views WHERE table_name = 'v_broker_position_reconciliation') THEN
        RAISE EXCEPTION 'Migration 047 failed: v_broker_position_reconciliation view not created';
    END IF;

    -- Success message
    RAISE NOTICE '========================================';
    RAISE NOTICE 'Migration 047 completed successfully!';
    RAISE NOTICE 'Created 4 tables: broker_sessions, broker_orders, broker_fills, broker_positions';
    RAISE NOTICE 'Created 3 views: v_active_broker_orders, v_broker_order_fill_summary, v_broker_position_reconciliation';
    RAISE NOTICE 'Created 14 indexes for high-performance queries';
    RAISE NOTICE 'Created 2 trigger functions for automatic timestamp updates';
    RAISE NOTICE 'Broker Gateway database schema ready for AMP Futures FIX integration';
    RAISE NOTICE '========================================';
END $$;

-- ================================================================================================
-- ROLLBACK INSTRUCTIONS
-- ================================================================================================

-- To rollback this migration, run the following commands in order:
-- DROP VIEW IF EXISTS v_broker_position_reconciliation CASCADE;
-- DROP VIEW IF EXISTS v_broker_order_fill_summary CASCADE;
-- DROP VIEW IF EXISTS v_active_broker_orders CASCADE;
-- DROP TRIGGER IF EXISTS tg_broker_sessions_update_timestamp ON broker_sessions;
-- DROP TRIGGER IF EXISTS tg_broker_orders_update_timestamp ON broker_orders;
-- DROP FUNCTION IF EXISTS update_broker_sessions_timestamp() CASCADE;
-- DROP FUNCTION IF EXISTS update_broker_orders_timestamp() CASCADE;
-- DROP TABLE IF EXISTS broker_fills CASCADE;
-- DROP TABLE IF EXISTS broker_positions CASCADE;
-- DROP TABLE IF EXISTS broker_orders CASCADE;
-- DROP TABLE IF EXISTS broker_sessions CASCADE;