-- Migration 004: Stream Configuration
-- Creates stream_configurations table for per-stream configuration management

-- Stream configuration table
CREATE TABLE IF NOT EXISTS event_streaming.stream_configurations (
    stream_name VARCHAR(255) PRIMARY KEY,
    description TEXT,
    tags JSONB,

    -- Retention configuration
    retention_max_age_seconds BIGINT,
    retention_max_size_bytes BIGINT,
    retention_max_event_count BIGINT,
    retention_enable_partitioning BOOLEAN,
    retention_partition_interval_seconds BIGINT,

    -- Dead Letter Queue configuration
    dlq_enabled BOOLEAN DEFAULT FALSE,
    dlq_stream_name VARCHAR(255),
    dlq_max_delivery_attempts INTEGER DEFAULT 3,
    dlq_retry_delay_seconds BIGINT,
    dlq_store_original_event BOOLEAN DEFAULT TRUE,
    dlq_store_error_details BOOLEAN DEFAULT TRUE,

    -- Lifecycle configuration
    lifecycle_auto_create BOOLEAN DEFAULT TRUE,
    lifecycle_auto_archive BOOLEAN DEFAULT FALSE,
    lifecycle_archive_after_seconds BIGINT,
    lifecycle_archive_location TEXT,
    lifecycle_auto_delete BOOLEAN DEFAULT FALSE,
    lifecycle_delete_after_seconds BIGINT,

    -- Performance configuration
    performance_batch_size INTEGER,
    performance_enable_compression BOOLEAN,
    performance_compression_algorithm VARCHAR(50),
    performance_enable_indexing BOOLEAN,
    performance_indexed_fields JSONB,
    performance_cache_size INTEGER,

    -- Access control
    access_public_read BOOLEAN DEFAULT FALSE,
    access_public_write BOOLEAN DEFAULT FALSE,
    access_allowed_readers JSONB,
    access_allowed_writers JSONB,
    access_max_consumer_groups INTEGER,
    access_max_events_per_second BIGINT,

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    created_by VARCHAR(255),
    updated_by VARCHAR(255)
);

-- Index for efficient tag queries
CREATE INDEX IF NOT EXISTS idx_stream_config_tags
ON event_streaming.stream_configurations USING GIN (tags);

-- Index for lifecycle queries (used by background services)
CREATE INDEX IF NOT EXISTS idx_stream_config_lifecycle
ON event_streaming.stream_configurations (lifecycle_auto_archive, lifecycle_auto_delete)
WHERE lifecycle_auto_archive = TRUE OR lifecycle_auto_delete = TRUE;

-- Index for DLQ queries
CREATE INDEX IF NOT EXISTS idx_stream_config_dlq
ON event_streaming.stream_configurations (dlq_enabled)
WHERE dlq_enabled = TRUE;

-- Comments for documentation
COMMENT ON TABLE event_streaming.stream_configurations IS 'Per-stream configuration for retention, DLQ, lifecycle, performance, and access control';
COMMENT ON COLUMN event_streaming.stream_configurations.stream_name IS 'Unique stream name';
COMMENT ON COLUMN event_streaming.stream_configurations.tags IS 'JSON object with arbitrary tags for categorization';
COMMENT ON COLUMN event_streaming.stream_configurations.retention_max_age_seconds IS 'Maximum age of events in seconds before cleanup';
COMMENT ON COLUMN event_streaming.stream_configurations.retention_max_size_bytes IS 'Maximum total size in bytes before cleanup';
COMMENT ON COLUMN event_streaming.stream_configurations.retention_max_event_count IS 'Maximum number of events before cleanup';
COMMENT ON COLUMN event_streaming.stream_configurations.dlq_enabled IS 'Whether dead letter queue is enabled for this stream';
COMMENT ON COLUMN event_streaming.stream_configurations.dlq_stream_name IS 'Name of the dead letter stream (defaults to {stream_name}-dlq)';
COMMENT ON COLUMN event_streaming.stream_configurations.dlq_max_delivery_attempts IS 'Maximum delivery attempts before sending to DLQ';
COMMENT ON COLUMN event_streaming.stream_configurations.lifecycle_auto_create IS 'Whether to automatically create stream if it does not exist';
COMMENT ON COLUMN event_streaming.stream_configurations.lifecycle_auto_archive IS 'Whether to automatically archive old events';
COMMENT ON COLUMN event_streaming.stream_configurations.lifecycle_auto_delete IS 'Whether to automatically delete old events';
COMMENT ON COLUMN event_streaming.stream_configurations.access_public_read IS 'Whether anyone can read from this stream';
COMMENT ON COLUMN event_streaming.stream_configurations.access_public_write IS 'Whether anyone can write to this stream';