-- PostgreSQL Performance Optimization for RateRight
-- Execute after security configuration
-- Optimized for Fly.io deployment

-- ============================================
-- PART 1: CRITICAL INDEXES
-- ============================================

-- User authentication and lookup indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_abn ON users(abn_number);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_role ON users(role) WHERE role IN ('worker', 'contractor');
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_active ON users(is_active) WHERE is_active = true;

-- Jobs performance indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_status ON jobs(status);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_category ON jobs(category_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_location ON jobs(location);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_created ON jobs(created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_contractor ON jobs(contractor_id);

-- Contracts performance indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_status ON contracts(status);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_contractor ON contracts(contractor_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_worker ON contracts(worker_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_job ON contracts(job_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_rating_pending 
    ON contracts(contractor_rated, worker_rated) 
    WHERE contractor_rated = false OR worker_rated = false;

-- Messages and notifications indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_sender ON messages(sender_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_receiver ON messages(receiver_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_unread ON messages(receiver_id, is_read) WHERE is_read = false;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_notifications_user ON notifications(user_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_notifications_unread ON notifications(user_id, is_read) WHERE is_read = false;

-- Payments and invoices indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_payer ON payments(payer_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_payee ON payments(payee_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_date ON payments(created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_invoices_user ON invoices(user_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_invoices_status ON invoices(status);

-- Availability and bookings indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_availability_user_date ON availability(user_id, date);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bookings_contractor ON bookings(contractor_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bookings_worker ON bookings(worker_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bookings_date ON bookings(booking_date);

-- ============================================
-- PART 2: QUERY PERFORMANCE MONITORING
-- ============================================

-- Enable query performance extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- Configure pg_stat_statements
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.track_utility = 'on';
ALTER SYSTEM SET pg_stat_statements.save = 'on';

-- Create view for slow queries
CREATE OR REPLACE VIEW slow_queries AS
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    stddev_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE mean_exec_time > 100  -- Queries taking more than 100ms
ORDER BY mean_exec_time DESC
LIMIT 50;

-- ============================================
-- PART 3: CONNECTION POOLING CONFIGURATION
-- ============================================

-- Connection pool settings for Fly.io
-- These should be set via fly postgres config update command
-- Documenting recommended values here:

-- Recommended connection settings:
-- max_connections = 100
-- superuser_reserved_connections = 3
-- 
-- Connection pooling (PgBouncer recommended):
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 25
-- reserve_pool_size = 5
-- reserve_pool_timeout = 3
-- server_lifetime = 3600
-- server_idle_timeout = 600

-- ============================================
-- PART 4: MEMORY OPTIMIZATION
-- ============================================

-- Memory settings optimized for Fly.io instance
-- Apply using: fly postgres config update --app rateright-db

ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET effective_cache_size = '1GB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';
ALTER SYSTEM SET work_mem = '4MB';
ALTER SYSTEM SET wal_buffers = '16MB';

-- Checkpoint settings for write performance
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET max_wal_size = '1GB';
ALTER SYSTEM SET min_wal_size = '80MB';

-- ============================================
-- PART 5: AUTOVACUUM OPTIMIZATION
-- ============================================

-- Autovacuum settings for consistent performance
ALTER SYSTEM SET autovacuum = 'on';
ALTER SYSTEM SET autovacuum_max_workers = 3;
ALTER SYSTEM SET autovacuum_naptime = '1min';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200;

-- Table-specific autovacuum for high-activity tables
ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE contracts SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE messages SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE notifications SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE audit_logs SET (autovacuum_vacuum_scale_factor = 0.02);

-- ============================================
-- PART 6: QUERY OPTIMIZATION SETTINGS
-- ============================================

-- Planner settings
ALTER SYSTEM SET random_page_cost = 1.1;  -- SSD optimization
ALTER SYSTEM SET effective_io_concurrency = 200;  -- SSD optimization
ALTER SYSTEM SET default_statistics_target = 100;

-- Enable parallel query execution
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET max_parallel_maintenance_workers = 2;
ALTER SYSTEM SET parallel_setup_cost = 100;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;

-- JIT compilation (if available)
ALTER SYSTEM SET jit = 'on';
ALTER SYSTEM SET jit_above_cost = 100000;

-- ============================================
-- PART 7: LOGGING FOR PERFORMANCE ANALYSIS
-- ============================================

-- Performance logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries over 1 second
ALTER SYSTEM SET log_checkpoints = 'on';
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_lock_waits = 'on';
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_autovacuum_min_duration = 0;

-- Query plan logging for slow queries
ALTER SYSTEM SET auto_explain.log_min_duration = 1000;
ALTER SYSTEM SET auto_explain.log_analyze = 'on';
ALTER SYSTEM SET auto_explain.log_buffers = 'on';
ALTER SYSTEM SET auto_explain.log_timing = 'on';

-- ============================================
-- PART 8: TABLE STATISTICS UPDATE
-- ============================================

-- Update table statistics for query planner
ANALYZE users;
ANALYZE jobs;
ANALYZE contracts;
ANALYZE applications;
ANALYZE messages;
ANALYZE notifications;
ANALYZE payments;
ANALYZE invoices;
ANALYZE bookings;
ANALYZE availability;

-- ============================================
-- PART 9: PERFORMANCE MONITORING QUERIES
-- ============================================

-- Check index usage
CREATE OR REPLACE VIEW index_usage AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Check table bloat
CREATE OR REPLACE VIEW table_bloat AS
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS external_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check cache hit ratio
CREATE OR REPLACE VIEW cache_hit_ratio AS
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

-- ============================================
-- VERIFICATION & RESTART REQUIRED
-- ============================================

-- Note: After applying ALTER SYSTEM commands, you need to:
-- 1. Restart PostgreSQL for shared_preload_libraries changes
-- 2. Reload configuration for other settings
-- 
-- On Fly.io:
-- fly postgres restart --app rateright-db
-- 
-- Or reload without restart:
-- SELECT pg_reload_conf();

SELECT pg_reload_conf();
