-- PostgreSQL Initialization Script for Stream Processor
-- This script sets up the initial database configuration and optimizations

-- Create database extensions for better performance and functionality
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- Create additional databases for testing if needed
-- CREATE DATABASE stream_processor_test;

-- Performance optimizations for the stream processor workload
-- These settings are optimized for a streaming application with frequent writes

-- Connection and authentication settings
ALTER SYSTEM SET max_connections = '200';
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- Memory settings (adjust based on available RAM)
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET effective_cache_size = '1GB';
ALTER SYSTEM SET work_mem = '4MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- Checkpoint and WAL settings for write-heavy workload
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '1GB';
ALTER SYSTEM SET min_wal_size = '80MB';

-- Query planner settings
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_io_concurrency = '200';

-- Logging settings for debugging
ALTER SYSTEM SET log_min_duration_statement = '1000';
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_line_prefix = '%t [%p-%l] %q%u@%d ';

-- Apply the configuration changes
SELECT pg_reload_conf();

-- Create a function to monitor streaming performance
CREATE OR REPLACE FUNCTION get_stream_stats()
RETURNS TABLE(
    table_name TEXT,
    row_count BIGINT,
    table_size TEXT,
    index_size TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        schemaname||'.'||tablename as table_name,
        n_tup_ins + n_tup_upd + n_tup_del as row_count,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as table_size,
        pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size
    FROM pg_stat_user_tables 
    WHERE schemaname = 'public'
    ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
END;
$$ LANGUAGE plpgsql;

-- Create a maintenance function for the streaming application
CREATE OR REPLACE FUNCTION cleanup_old_data(days_to_keep INTEGER DEFAULT 30)
RETURNS TEXT AS $$
DECLARE
    result_text TEXT := '';
    cutoff_date TIMESTAMP;
BEGIN
    cutoff_date := NOW() - INTERVAL '1 day' * days_to_keep;
    
    -- This function can be used to clean up old streaming data
    -- The actual cleanup logic will be implemented in Django
    
    result_text := 'Cleanup function ready for data older than ' || cutoff_date;
    
    RETURN result_text;
END;
$$ LANGUAGE plpgsql;

-- Create indexes that will be useful for the streaming application
-- These will be created by Django migrations, but we can prepare the database

-- Notify that initialization is complete
DO $$
BEGIN
    RAISE NOTICE 'Stream Processor database initialization completed successfully';
    RAISE NOTICE 'Database is optimized for streaming workloads';
    RAISE NOTICE 'Monitoring functions created: get_stream_stats(), cleanup_old_data()';
END $$;
