#!/usr/bin/env bash
set -euo pipefail

create_database_if_missing() {
  local db_name="$1"
  local exists

  exists="$(
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname postgres \
      -tAc "SELECT 1 FROM pg_database WHERE datname = '${db_name}'" | tr -d '[:space:]'
  )"

  if [ "$exists" != "1" ]; then
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname postgres \
      --command "CREATE DATABASE \"${db_name}\";"
  fi
}

create_database_if_missing "${DATAIF_DB_NAME}"
create_database_if_missing "${AIRFLOW_DB_NAME}"
create_database_if_missing "${METABASE_APP_DB_NAME}"

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname postgres <<EOSQL
DO \$\$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${DATAIF_ETL_USER}') THEN
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '${DATAIF_ETL_USER}', '${DATAIF_ETL_PASSWORD}');
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${DATAIF_METABASE_USER}') THEN
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '${DATAIF_METABASE_USER}', '${DATAIF_METABASE_PASSWORD}');
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${DATAIF_VANNA_USER}') THEN
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '${DATAIF_VANNA_USER}', '${DATAIF_VANNA_PASSWORD}');
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${AIRFLOW_DB_USER}') THEN
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '${AIRFLOW_DB_USER}', '${AIRFLOW_DB_PASSWORD}');
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${METABASE_APP_DB_USER}') THEN
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', '${METABASE_APP_DB_USER}', '${METABASE_APP_DB_PASSWORD}');
  END IF;
END
\$\$;

GRANT ALL PRIVILEGES ON DATABASE ${AIRFLOW_DB_NAME} TO ${AIRFLOW_DB_USER};
GRANT ALL PRIVILEGES ON DATABASE ${METABASE_APP_DB_NAME} TO ${METABASE_APP_DB_USER};
GRANT CONNECT ON DATABASE ${DATAIF_DB_NAME} TO ${DATAIF_ETL_USER}, ${DATAIF_METABASE_USER}, ${DATAIF_VANNA_USER};
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$AIRFLOW_DB_NAME" <<EOSQL
ALTER SCHEMA public OWNER TO ${AIRFLOW_DB_USER};
GRANT ALL ON SCHEMA public TO ${AIRFLOW_DB_USER};
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$METABASE_APP_DB_NAME" <<EOSQL
ALTER SCHEMA public OWNER TO ${METABASE_APP_DB_USER};
GRANT ALL ON SCHEMA public TO ${METABASE_APP_DB_USER};
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$DATAIF_DB_NAME" <<EOSQL
CREATE SCHEMA IF NOT EXISTS config AUTHORIZATION ${DATAIF_ETL_USER};
CREATE SCHEMA IF NOT EXISTS raw AUTHORIZATION ${DATAIF_ETL_USER};
CREATE SCHEMA IF NOT EXISTS staging AUTHORIZATION ${DATAIF_ETL_USER};
CREATE SCHEMA IF NOT EXISTS mart AUTHORIZATION ${DATAIF_ETL_USER};
CREATE SCHEMA IF NOT EXISTS curated AUTHORIZATION ${DATAIF_ETL_USER};
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION ${DATAIF_ETL_USER};

\i /docker-entrypoint-initdb.d/pnp-raw-staging-curated.sqlinc
\i /docker-entrypoint-initdb.d/pnp-curated-views.sqlinc

ALTER TABLE config.app_settings OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_connections OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_instances OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_instance_selection OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_endpoint_tables OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_pipeline_endpoints OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_runs OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_run_steps OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_run_packages OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_catalog_entries OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_run_selection OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_downloads OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_download_columns OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_ingestion_quarantine OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_matriculas_src OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_eficiencia_academica_src OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_financeiro_src OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE raw.pnp_servidores_src OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE staging.pnp_ingestion_runs OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE staging.pnp_matriculas OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE staging.pnp_eficiencia_academica OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE staging.pnp_servidores OWNER TO ${DATAIF_ETL_USER};
ALTER TABLE staging.pnp_financeiro OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_admin_ingestao OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_qualidade_dados OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_matriculas_perfil OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_matriculas_oferta OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_eficiencia_situacao OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_servidores_quadro OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_financeiro_execucao OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_vanna_catalogo OWNER TO ${DATAIF_ETL_USER};
ALTER VIEW curated.vw_pnp_vanna_resumo OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_matriculas OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_eficiencia OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_servidores OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_financeiro OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_qualidade OWNER TO ${DATAIF_ETL_USER};
ALTER MATERIALIZED VIEW curated.mv_pnp_dashboard_ingestao OWNER TO ${DATAIF_ETL_USER};
ALTER FUNCTION raw.touch_updated_at() OWNER TO ${DATAIF_ETL_USER};

GRANT USAGE ON SCHEMA config, raw, staging, mart, audit, curated TO ${DATAIF_ETL_USER};
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA config, raw, staging, mart, audit TO ${DATAIF_ETL_USER};
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA config, raw, staging, mart, audit TO ${DATAIF_ETL_USER};

ALTER DEFAULT PRIVILEGES IN SCHEMA config GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA raw GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA mart GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DATAIF_ETL_USER};

ALTER DEFAULT PRIVILEGES IN SCHEMA config GRANT USAGE, SELECT ON SEQUENCES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA raw GRANT USAGE, SELECT ON SEQUENCES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT USAGE, SELECT ON SEQUENCES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA mart GRANT USAGE, SELECT ON SEQUENCES TO ${DATAIF_ETL_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT USAGE, SELECT ON SEQUENCES TO ${DATAIF_ETL_USER};

GRANT USAGE ON SCHEMA raw, staging, curated TO ${DATAIF_METABASE_USER};
GRANT SELECT ON ALL TABLES IN SCHEMA raw, staging, curated TO ${DATAIF_METABASE_USER};
ALTER DEFAULT PRIVILEGES FOR ROLE ${DATAIF_ETL_USER} IN SCHEMA raw GRANT SELECT ON TABLES TO ${DATAIF_METABASE_USER};
ALTER DEFAULT PRIVILEGES FOR ROLE ${DATAIF_ETL_USER} IN SCHEMA staging GRANT SELECT ON TABLES TO ${DATAIF_METABASE_USER};
ALTER DEFAULT PRIVILEGES FOR ROLE ${DATAIF_ETL_USER} IN SCHEMA curated GRANT SELECT ON TABLES TO ${DATAIF_METABASE_USER};

-- Rede de seguranca: uma query de dashboard mal escrita/sem indice adequado falha rapido e
-- com erro claro no Metabase em vez de pendurar ate o timeout do proxy (nginx).
ALTER ROLE ${DATAIF_METABASE_USER} SET statement_timeout = '30s';

GRANT USAGE ON SCHEMA config, curated TO ${DATAIF_VANNA_USER};
GRANT SELECT ON config.app_settings TO ${DATAIF_VANNA_USER};
GRANT SELECT ON ALL TABLES IN SCHEMA curated TO ${DATAIF_VANNA_USER};
ALTER DEFAULT PRIVILEGES FOR ROLE ${DATAIF_ETL_USER} IN SCHEMA curated GRANT SELECT ON TABLES TO ${DATAIF_VANNA_USER};
EOSQL