#!/bin/bash
set -e

echo "🚀 Initializing PostgreSQL database for $PROJECT_NAME..."

# Project-specific initialization (optional)
if [ -n "${PROJECT_SCHEMA}" ] && [ "${PROJECT_SCHEMA}" != "none" ]; then
    echo "📋 Loading project-specific schema: ${PROJECT_SCHEMA}"

    # Check if custom schema file exists
    if [ -f "/docker-entrypoint-initdb.d/${PROJECT_SCHEMA}" ]; then
        psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -f "/docker-entrypoint-initdb.d/${PROJECT_SCHEMA}"
        echo "✅ Project schema ${PROJECT_SCHEMA} loaded successfully"
    else
        echo "⚠️  Warning: Project schema file ${PROJECT_SCHEMA} not found"
    fi
fi

# Base PostgreSQL setup with useful extensions
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    -- Enable useful extensions for all projects
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
    CREATE EXTENSION IF NOT EXISTS "pg_trgm";
    CREATE EXTENSION IF NOT EXISTS "btree_gin";
    CREATE EXTENSION IF NOT EXISTS "btree_gist";

    -- Create utility functions that can be used by any project
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS \$\$
    BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
    END;
    \$\$ language 'plpgsql';

    -- Create function to safely refresh materialized views
    CREATE OR REPLACE FUNCTION refresh_materialized_view(view_name TEXT)
    RETURNS void AS \$\$
    DECLARE
        view_exists BOOLEAN;
    BEGIN
        SELECT EXISTS (
            SELECT 1 FROM pg_matviews
            WHERE matviewname = view_name
        ) INTO view_exists;

        IF view_exists THEN
            EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || view_name;
            RAISE NOTICE 'Materialized view % refreshed successfully', view_name;
        ELSE
            RAISE NOTICE 'Materialized view % does not exist', view_name;
        END IF;
    END;
    \$\$ LANGUAGE plpgsql;

    -- Create function to monitor query performance
    CREATE OR REPLACE FUNCTION get_slow_queries(min_duration_ms INTEGER DEFAULT 1000)
    RETURNS TABLE (
        query TEXT,
        calls BIGINT,
        total_time DOUBLE PRECISION,
        mean_time DOUBLE PRECISION,
        rows BIGINT
    ) AS \$\$
    BEGIN
        RETURN QUERY
        SELECT
            query,
            calls,
            total_exec_time AS total_time,
            mean_exec_time AS mean_time,
            rows
        FROM pg_stat_statements
        WHERE mean_exec_time > (min_duration_ms / 1000.0)
        ORDER BY mean_exec_time DESC
        LIMIT 20;
    END;
    \$\$ LANGUAGE plpgsql;

    -- Create function for database statistics
    CREATE OR REPLACE FUNCTION get_database_stats()
    RETURNS TABLE (
        database_name TEXT,
        size TEXT,
        connections INTEGER,
        active_connections INTEGER
    ) AS \$\$
    BEGIN
        RETURN QUERY
        SELECT
            current_database() as database_name,
            pg_size_pretty(pg_database_size(current_database())) as size,
            count(*) as connections,
            count(*) FILTER (WHERE state = 'active') as active_connections
        FROM pg_stat_activity
        WHERE datname = current_database();
    END;
    \$\$ LANGUAGE plpgsql;

    -- Grant permissions for database user
    GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO $POSTGRES_USER;

    -- Set up default permissions for future objects
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $POSTGRES_USER;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $POSTGRES_USER;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO $POSTGRES_USER;

EOSQL

# Load any additional SQL files if they exist
if [ -d "/docker-entrypoint-initdb.d/sql" ]; then
    echo "📋 Loading additional SQL files..."
    for file in /docker-entrypoint-initdb.d/sql/*.sql; do
        if [ -f "$file" ]; then
            echo "   Loading $(basename "$file")..."
            psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -f "$file"
        fi
    done
fi

echo "✅ PostgreSQL initialization completed successfully!"
echo "🎉 Database ready for ${PROJECT_NAME:-general use}!"

# Show basic database info
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "
    SELECT
        'Database Size' as metric,
        pg_size_pretty(pg_database_size('$POSTGRES_DB')) as value
    UNION ALL
    SELECT
        'Extensions Loaded' as metric,
        COUNT(*)::text as value
    FROM pg_extension
    UNION ALL
    SELECT
        'Current User' as metric,
        current_user as value;
"