-- init-db.sql
-- Database initialization script for PostgreSQL development and testing

-- Set timezone to UTC for consistent testing
SET timezone TO 'UTC';

-- Create useful extensions for badge storage and testing
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create additional test database for parallel testing
CREATE DATABASE showcase_test_parallel;
GRANT ALL PRIVILEGES ON DATABASE showcase_test_parallel TO showcase;

-- Create database for integration tests
CREATE DATABASE showcase_integration;
GRANT ALL PRIVILEGES ON DATABASE showcase_integration TO showcase;

-- Set up proper encoding and collation
UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_test';
UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_test_parallel';
UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_integration';

-- Connect to main test database to set up initial configuration
\c showcase_test;

-- Set up session parameters for optimal JSONB performance
SET default_statistics_target = 100;
SET random_page_cost = 1.1;
SET effective_cache_size = '256MB';

-- Create a function to reset test data (useful for integration tests)
CREATE OR REPLACE FUNCTION reset_test_data()
RETURNS void AS $$
BEGIN
    -- Drop all tables if they exist (for clean test runs)
    DROP TABLE IF EXISTS awards CASCADE;
    DROP TABLE IF EXISTS badges CASCADE;
    DROP TABLE IF EXISTS identities CASCADE;
    
    RAISE NOTICE 'Test data reset completed';
END;
$$ LANGUAGE plpgsql;

-- Create a function to get database statistics (useful for debugging)
CREATE OR REPLACE FUNCTION get_table_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_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;

-- Insert some test data for development (will be ignored if tables don't exist)
DO $$
BEGIN
    -- This block will only execute if we're in a development environment
    -- Production migrations should be handled by the Rust application
    IF current_setting('server_version_num')::int >= 170000 THEN
        RAISE NOTICE 'PostgreSQL 17+ detected - ready for JSONB optimizations';
    END IF;
    
    RAISE NOTICE 'Database initialization completed successfully';
    RAISE NOTICE 'Available databases: showcase_test, showcase_test_parallel, showcase_integration';
    RAISE NOTICE 'Available functions: reset_test_data(), get_table_stats()';
END;
$$;