-- PANELS · Postgres · Fase 0 (aprovisionamiento de bases)
--
-- Crea las dos bases de datos físicamente separadas del monolito modular:
--   panels_platform  -> control plane SaaS (tenants, platform_users, smtp_settings)
--   panels_product   -> producto que usan los tenants (esquemas iam/core dentro)
--
-- Requiere haber corrido antes 01-roles.sql. Idempotente vía el idiom
-- \gexec (solo emite el CREATE DATABASE si todavía no existe).
--
-- Uso: psql "$SUPERUSER_URL" -f 02-databases.sql

SELECT 'CREATE DATABASE panels_platform OWNER panels_platform_owner'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'panels_platform')\gexec

SELECT 'CREATE DATABASE panels_product OWNER panels_iam_owner'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'panels_product')\gexec

-- panels_core_owner también necesita poder crear su esquema dentro de panels_product,
-- aunque el owner nominal de la base sea panels_iam_owner.
GRANT CREATE ON DATABASE panels_product TO panels_core_owner;
REVOKE CREATE ON DATABASE panels_product FROM PUBLIC;
REVOKE CREATE ON DATABASE panels_platform FROM PUBLIC;

-- Postgres otorga CONNECT a PUBLIC por defecto en toda base nueva: sin este
-- REVOKE, panels_platform_app podría autenticarse contra panels_product (o
-- viceversa) aunque no tenga privilegios dentro -- justo el cruce que este
-- diseño busca evitar. Se revoca de PUBLIC y se otorga solo a los roles que
-- corresponden a cada base.
REVOKE CONNECT ON DATABASE panels_platform FROM PUBLIC;
GRANT CONNECT ON DATABASE panels_platform TO panels_platform_owner, panels_platform_app;

REVOKE CONNECT ON DATABASE panels_product FROM PUBLIC;
GRANT CONNECT ON DATABASE panels_product TO panels_iam_owner, panels_iam_app, panels_core_owner, panels_core_app;