-- Development Database Initialization Script
-- This script sets up the development database with proper extensions and initial data

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- For full-text search
CREATE EXTENSION IF NOT EXISTS "unaccent"; -- For accent-insensitive search

-- Create development-specific functions for debugging
CREATE OR REPLACE FUNCTION log_query_performance()
RETURNS event_trigger AS $$
BEGIN
  -- Log slow queries in development
  IF current_setting('log_min_duration_statement') = '-1' THEN
    PERFORM set_config('log_min_duration_statement', '100', false); -- Log queries > 100ms
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Development-specific settings for better debugging
-- These will be automatically applied when the database starts
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = '100';
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';

-- Reload configuration
SELECT pg_reload_conf();

-- Create a development user with appropriate permissions
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'polartek_dev_readonly') THEN
    CREATE ROLE polartek_dev_readonly WITH LOGIN PASSWORD 'readonly_2024';
  END IF;
END
$$;

-- Grant read-only access for development queries
GRANT CONNECT ON DATABASE polartek_dev TO polartek_dev_readonly;
GRANT USAGE ON SCHEMA public TO polartek_dev_readonly;

-- This will be applied after Prisma creates the tables
-- Grant SELECT on all tables to readonly user (applied via script after migration)

-- Development notification
DO $$
BEGIN
  RAISE NOTICE 'Development database initialized successfully!';
  RAISE NOTICE 'Database: polartek_dev';
  RAISE NOTICE 'Main User: dev_user';
  RAISE NOTICE 'Readonly User: polartek_dev_readonly';
  RAISE NOTICE 'Extensions enabled: uuid-ossp, pg_trgm, unaccent';
END
$$;