#!/bin/bash
set -e

# This script runs during PostgreSQL initialization
# It creates the textiq and airflow databases if they don't exist

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "postgres" <<-EOSQL
    -- Create TextIQ database
    SELECT 'CREATE DATABASE textiq'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'textiq')\gexec

    -- Create Airflow database (owned by superuser)
    SELECT 'CREATE DATABASE airflow'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'airflow')\gexec

    -- Create non-superuser app user for textiq (RLS enforced)
    SELECT 'CREATE USER textiq_app WITH PASSWORD ''textiq_app_password'''
    WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'textiq_app')\gexec
EOSQL

# Grant permissions on textiq database for the app user
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "textiq" <<-EOSQL
    GRANT CONNECT ON DATABASE textiq TO textiq_app;
    GRANT USAGE, CREATE ON SCHEMA public TO textiq_app;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO textiq_app;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO textiq_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO textiq_app;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO textiq_app;
EOSQL

echo "Databases 'textiq' and 'airflow' are ready!"
echo "Users: $POSTGRES_USER (superuser), textiq_app (normal, RLS enforced)"