-- Migration: 037-add-project-db-connections.sql
-- Description: Add project_db_connections and user_app_mappings tables for multi-project integration
-- Date: 2026-06-15

CREATE TABLE IF NOT EXISTS project_db_connections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  app_code TEXT NOT NULL,
  provider TEXT NOT NULL DEFAULT 'postgresql',
  connection_string_env TEXT NOT NULL,
  user_table TEXT NOT NULL DEFAULT 'users',
  user_id_column TEXT NOT NULL DEFAULT 'id',
  email_column TEXT NOT NULL DEFAULT 'email',
  json_profile_column TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(app_code)
);

CREATE TABLE IF NOT EXISTS user_app_mappings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  sso_user_id UUID NOT NULL,
  client_id UUID NOT NULL,
  external_user_id TEXT NOT NULL,
  external_email TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(client_id, external_user_id)
);

COMMENT ON TABLE project_db_connections IS 'Stores connection configs for external project databases (keyed by app_code)';
COMMENT ON TABLE user_app_mappings IS 'Maps SSO user IDs to external project user IDs';

CREATE INDEX IF NOT EXISTS idx_project_db_connections_app_code ON project_db_connections(app_code);
CREATE INDEX IF NOT EXISTS idx_project_db_connections_status ON project_db_connections(status);
CREATE INDEX IF NOT EXISTS idx_user_app_mappings_sso_user_id ON user_app_mappings(sso_user_id);
CREATE INDEX IF NOT EXISTS idx_user_app_mappings_client_id ON user_app_mappings(client_id);
