-- PostgreSQL Comprehensive Logging & Monitoring Configuration
-- Enterprise-grade audit trail and performance monitoring
-- App: rateright-db

-- ============================================
-- PART 1: LOGGING CONFIGURATION
-- ============================================

-- Configure comprehensive logging
ALTER SYSTEM SET logging_collector = 'on';
ALTER SYSTEM SET log_directory = 'log';
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';
ALTER SYSTEM SET log_rotation_age = '1d';
ALTER SYSTEM SET log_rotation_size = '100MB';
ALTER SYSTEM SET log_truncate_on_rotation = 'off';

-- Log line prefix for detailed context
ALTER SYSTEM SET log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h,session=%c,line=%l ';

-- Connection and disconnection logging
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_duration = 'off';  -- Use log_min_duration_statement instead

-- Statement logging
ALTER SYSTEM SET log_statement = 'ddl';  -- Log all DDL statements
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries over 1 second
ALTER SYSTEM SET log_min_duration_sample = 500;  -- Sample queries over 500ms
ALTER SYSTEM SET log_statement_sample_rate = 0.1;  -- Sample 10% of statements
ALTER SYSTEM SET log_transaction_sample_rate = 0.01;  -- Sample 1% of transactions

-- Error and warning logging
ALTER SYSTEM SET log_min_messages = 'warning';
ALTER SYSTEM SET log_min_error_statement = 'error';
ALTER SYSTEM SET log_error_verbosity = 'default';

-- Checkpoint and autovacuum logging
ALTER SYSTEM SET log_checkpoints = 'on';
ALTER SYSTEM SET log_autovacuum_min_duration = 0;

-- Lock logging
ALTER SYSTEM SET log_lock_waits = 'on';
ALTER SYSTEM SET deadlock_timeout = '1s';

-- Temp file logging
ALTER SYSTEM SET log_temp_files = 0;  -- Log all temp file usage

-- Apply configuration
SELECT pg_reload_conf();

-- ============================================
-- PART 2: AUDIT LOGGING TABLES
-- ============================================

-- Enhanced audit log table with more detail
CREATE TABLE IF NOT EXISTS audit_logs_enhanced (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    session_id TEXT,
    transaction_id BIGINT,
    user_id INTEGER,
    username TEXT,
    database_name TEXT,
    schema_name TEXT,
    table_name TEXT,
    operation TEXT,  -- SELECT, INSERT, UPDATE, DELETE, DDL
    object_type TEXT,  -- TABLE, INDEX, FUNCTION, etc.
    object_name TEXT,
    query TEXT,
    query_duration_ms NUMERIC,
    rows_affected INTEGER,
    client_address INET,
    application_name TEXT,
    backend_pid INTEGER,
    success BOOLEAN,
    error_message TEXT,
    context JSONB,  -- Additional context data
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for performance
CREATE INDEX idx_audit_logs_enhanced_timestamp ON audit_logs_enhanced(timestamp DESC);
CREATE INDEX idx_audit_logs_enhanced_user ON audit_logs_enhanced(user_id, timestamp DESC);
CREATE INDEX idx_audit_logs_enhanced_operation ON audit_logs_enhanced(operation, timestamp DESC);
CREATE INDEX idx_audit_logs_enhanced_table ON audit_logs_enhanced(table_name, timestamp DESC);

-- ============================================
-- PART 3: QUERY MONITORING
-- ============================================

-- Create query performance history table
CREATE TABLE IF NOT EXISTS query_performance_history (
    id BIGSERIAL PRIMARY KEY,
    captured_at TIMESTAMPTZ DEFAULT NOW(),
    query_id BIGINT,
    query TEXT,
    calls BIGINT,
    total_exec_time NUMERIC,
    mean_exec_time NUMERIC,
    max_exec_time NUMERIC,
    min_exec_time NUMERIC,
    stddev_exec_time NUMERIC,
    rows BIGINT,
    shared_blks_hit BIGINT,
    shared_blks_read BIGINT,
    shared_blks_written BIGINT,
    local_blks_hit BIGINT,
    local_blks_read BIGINT,
    local_blks_written BIGINT,
    temp_blks_read BIGINT,
    temp_blks_written BIGINT,
    blk_read_time NUMERIC,
    blk_write_time NUMERIC
);

-- Function to capture query statistics
CREATE OR REPLACE FUNCTION capture_query_statistics()
RETURNS void AS $$
BEGIN
    -- Ensure pg_stat_statements is available
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements') THEN
        INSERT INTO query_performance_history (
            query_id, query, calls, total_exec_time, mean_exec_time,
            max_exec_time, min_exec_time, stddev_exec_time, rows,
            shared_blks_hit, shared_blks_read, shared_blks_written,
            local_blks_hit, local_blks_read, local_blks_written,
            temp_blks_read, temp_blks_written, blk_read_time, blk_write_time
        )
        SELECT 
            queryid, query, calls, total_exec_time, mean_exec_time,
            max_exec_time, min_exec_time, stddev_exec_time, rows,
            shared_blks_hit, shared_blks_read, shared_blks_written,
            local_blks_hit, local_blks_read, local_blks_written,
            temp_blks_read, temp_blks_written, blk_read_time, blk_write_time
        FROM pg_stat_statements
        WHERE mean_exec_time > 100  -- Only queries over 100ms
        ORDER BY mean_exec_time DESC
        LIMIT 100;
        
        -- Reset statistics after capture
        PERFORM pg_stat_statements_reset();
    END IF;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- PART 4: MONITORING VIEWS
-- ============================================

-- Database activity monitoring view
CREATE OR REPLACE VIEW database_activity_monitor AS
SELECT 
    pid,
    usename as username,
    datname as database,
    application_name,
    client_addr,
    backend_start,
    state,
    state_change,
    CASE 
        WHEN state = 'active' THEN 
            EXTRACT(EPOCH FROM (NOW() - query_start))::INT
        ELSE NULL 
    END as query_duration_seconds,
    wait_event_type,
    wait_event,
    LEFT(query, 100) as query_snippet
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
ORDER BY 
    CASE WHEN state = 'active' THEN 0 ELSE 1 END,
    query_start;

-- Table activity monitoring view
CREATE OR REPLACE VIEW table_activity_monitor AS
SELECT 
    schemaname,
    tablename,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    n_tup_hot_upd as hot_updates,
    n_live_tup as live_tuples,
    n_dead_tup as dead_tuples,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_tuple_percent,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC;

-- Lock monitoring view
CREATE OR REPLACE VIEW lock_monitor AS
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement,
    blocked_activity.application_name AS blocked_application,
    blocking_activity.application_name AS blocking_application,
    EXTRACT(EPOCH FROM (NOW() - blocked_activity.query_start))::INT AS blocked_duration_seconds,
    EXTRACT(EPOCH FROM (NOW() - blocking_activity.query_start))::INT AS blocking_duration_seconds
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- ============================================
-- PART 5: ALERT FUNCTIONS
-- ============================================

-- Function to check for critical conditions
CREATE OR REPLACE FUNCTION check_critical_conditions()
RETURNS TABLE(
    alert_type TEXT,
    severity TEXT,
    message TEXT,
    details JSONB
) AS $$
BEGIN
    -- Check for long-running queries
    RETURN QUERY
    SELECT 
        'LONG_RUNNING_QUERY'::TEXT,
        CASE 
            WHEN EXTRACT(EPOCH FROM (NOW() - query_start)) > 900 THEN 'CRITICAL'
            WHEN EXTRACT(EPOCH FROM (NOW() - query_start)) > 300 THEN 'WARNING'
            ELSE 'INFO'
        END::TEXT,
        FORMAT('Query running for %s seconds', 
            EXTRACT(EPOCH FROM (NOW() - query_start))::INT)::TEXT,
        jsonb_build_object(
            'pid', pid,
            'user', usename,
            'query', LEFT(query, 200),
            'duration_seconds', EXTRACT(EPOCH FROM (NOW() - query_start))::INT
        )
    FROM pg_stat_activity
    WHERE state = 'active'
        AND pid != pg_backend_pid()
        AND query_start < NOW() - INTERVAL '5 minutes';
    
    -- Check for blocked queries
    RETURN QUERY
    SELECT 
        'BLOCKED_QUERY'::TEXT,
        'WARNING'::TEXT,
        FORMAT('%s queries are blocked', COUNT(*))::TEXT,
        jsonb_agg(jsonb_build_object(
            'blocked_pid', blocked_pid,
            'blocking_pid', blocking_pid,
            'duration', blocked_duration_seconds
        ))
    FROM lock_monitor
    GROUP BY blocking_pid
    HAVING COUNT(*) > 0;
    
    -- Check for high dead tuple ratio
    RETURN QUERY
    SELECT 
        'HIGH_DEAD_TUPLES'::TEXT,
        CASE 
            WHEN dead_tuple_percent > 30 THEN 'CRITICAL'
            WHEN dead_tuple_percent > 20 THEN 'WARNING'
            ELSE 'INFO'
        END::TEXT,
        FORMAT('Table %s.%s has %s%% dead tuples', 
            schemaname, tablename, dead_tuple_percent)::TEXT,
        jsonb_build_object(
            'schema', schemaname,
            'table', tablename,
            'dead_tuples', n_dead_tup,
            'live_tuples', n_live_tup,
            'percent', dead_tuple_percent
        )
    FROM table_activity_monitor
    WHERE dead_tuple_percent > 20;
    
    -- Check for tables not analyzed recently
    RETURN QUERY
    SELECT 
        'STALE_STATISTICS'::TEXT,
        'WARNING'::TEXT,
        FORMAT('Table %s.%s not analyzed for %s days', 
            schemaname, tablename, 
            EXTRACT(DAY FROM (NOW() - COALESCE(last_analyze, last_autoanalyze)))::INT)::TEXT,
        jsonb_build_object(
            'schema', schemaname,
            'table', tablename,
            'last_analyze', COALESCE(last_analyze, last_autoanalyze)
        )
    FROM pg_stat_user_tables
    WHERE COALESCE(last_analyze, last_autoanalyze) < NOW() - INTERVAL '7 days'
        OR COALESCE(last_analyze, last_autoanalyze) IS NULL;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- PART 6: COMPLIANCE LOGGING
-- ============================================

-- Australian compliance specific logging
CREATE OR REPLACE FUNCTION log_compliance_event()
RETURNS TRIGGER AS $$
BEGIN
    -- Log changes to compliance-critical tables
    IF TG_TABLE_NAME IN ('users', 'contracts', 'payments', 'invoices') THEN
        INSERT INTO audit_logs_enhanced (
            timestamp,
            session_id,
            transaction_id,
            user_id,
            username,
            database_name,
            schema_name,
            table_name,
            operation,
            object_type,
            object_name,
            rows_affected,
            client_address,
            application_name,
            backend_pid,
            success,
            context
        ) VALUES (
            NOW(),
            pg_backend_pid()::TEXT || '_' || pg_backend_start_time()::TEXT,
            txid_current(),
            current_setting('app.current_user_id', true)::INTEGER,
            current_user,
            current_database(),
            TG_TABLE_SCHEMA,
            TG_TABLE_NAME,
            TG_OP,
            'TABLE',
            TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
            CASE TG_OP
                WHEN 'INSERT' THEN 1
                WHEN 'UPDATE' THEN 1
                WHEN 'DELETE' THEN 1
                ELSE 0
            END,
            inet_client_addr(),
            current_setting('application_name'),
            pg_backend_pid(),
            true,
            jsonb_build_object(
                'operation_type', TG_OP,
                'when', TG_WHEN,
                'level', TG_LEVEL,
                'old_row', CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD) ELSE NULL END,
                'new_row', CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END
            )
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply compliance logging triggers
DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
            AND tablename IN ('users', 'contracts', 'payments', 'invoices')
    LOOP
        EXECUTE FORMAT('
            DROP TRIGGER IF EXISTS compliance_audit_%s ON %I;
            CREATE TRIGGER compliance_audit_%s
            AFTER INSERT OR UPDATE OR DELETE ON %I
            FOR EACH ROW
            EXECUTE FUNCTION log_compliance_event();
        ', tbl.tablename, tbl.tablename, tbl.tablename, tbl.tablename);
    END LOOP;
END $$;

-- ============================================
-- PART 7: MONITORING DASHBOARD QUERIES
-- ============================================

-- Dashboard summary function
CREATE OR REPLACE FUNCTION get_monitoring_dashboard()
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    WITH database_stats AS (
        SELECT 
            pg_database_size(current_database()) as db_size,
            (SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active') as active_connections,
            (SELECT COUNT(*) FROM pg_stat_activity) as total_connections,
            (SELECT setting::INT FROM pg_settings WHERE name = 'max_connections') as max_connections
    ),
    performance_stats AS (
        SELECT 
            (SELECT ROUND(100.0 * SUM(blks_hit) / NULLIF(SUM(blks_hit + blks_read), 0), 2) 
             FROM pg_stat_database WHERE datname = current_database()) as cache_hit_ratio,
            (SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '1 minute') as slow_queries,
            (SELECT COUNT(*) FROM lock_monitor) as blocked_queries
    ),
    table_stats AS (
        SELECT 
            COUNT(*) as total_tables,
            SUM(n_live_tup) as total_rows,
            SUM(n_dead_tup) as total_dead_rows,
            MAX(dead_tuple_percent) as max_dead_tuple_percent
        FROM table_activity_monitor
    ),
    recent_activity AS (
        SELECT 
            COUNT(*) FILTER (WHERE timestamp > NOW() - INTERVAL '1 hour') as events_last_hour,
            COUNT(*) FILTER (WHERE timestamp > NOW() - INTERVAL '24 hours') as events_last_day
        FROM audit_logs_enhanced
    )
    SELECT json_build_object(
        'timestamp', NOW(),
        'database', json_build_object(
            'size_bytes', db_size,
            'size_pretty', pg_size_pretty(db_size),
            'active_connections', active_connections,
            'total_connections', total_connections,
            'max_connections', max_connections,
            'connection_usage_percent', ROUND(100.0 * total_connections / max_connections, 2)
        ),
        'performance', json_build_object(
            'cache_hit_ratio', cache_hit_ratio,
            'slow_queries', slow_queries,
            'blocked_queries', blocked_queries
        ),
        'tables', json_build_object(
            'total_tables', total_tables,
            'total_rows', total_rows,
            'total_dead_rows', total_dead_rows,
            'max_dead_tuple_percent', max_dead_tuple_percent
        ),
        'audit', json_build_object(
            'events_last_hour', events_last_hour,
            'events_last_day', events_last_day
        ),
        'alerts', (SELECT json_agg(row_to_json(c)) FROM check_critical_conditions() c)
    ) INTO result
    FROM database_stats, performance_stats, table_stats, recent_activity;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- VERIFICATION
-- ============================================

-- Verify logging configuration
SELECT 
    name,
    setting
FROM pg_settings
WHERE name IN (
    'logging_collector',
    'log_connections',
    'log_disconnections',
    'log_min_duration_statement',
    'log_checkpoints',
    'log_lock_waits'
)
ORDER BY name;

-- Test monitoring dashboard
SELECT get_monitoring_dashboard();
