-- Wave 2 Track 1 auth/OIDC schema (PostgreSQL)

CREATE TABLE IF NOT EXISTS auth_external_identities (
  id SERIAL PRIMARY KEY,
  provider VARCHAR(64) NOT NULL,
  external_subject VARCHAR(256) NOT NULL,
  username VARCHAR(128) NOT NULL,
  email VARCHAR(256),
  full_name VARCHAR(256),
  role VARCHAR(32) NOT NULL,
  linked_user_id VARCHAR(64),
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NOT NULL,
  last_login_at VARCHAR(64) NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_provider ON auth_external_identities (provider);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_external_subject ON auth_external_identities (external_subject);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_username ON auth_external_identities (username);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_email ON auth_external_identities (email);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_role ON auth_external_identities (role);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_linked_user_id ON auth_external_identities (linked_user_id);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_created_at ON auth_external_identities (created_at);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_updated_at ON auth_external_identities (updated_at);
CREATE INDEX IF NOT EXISTS ix_auth_external_identities_last_login_at ON auth_external_identities (last_login_at);

CREATE TABLE IF NOT EXISTS auth_oidc_states (
  id SERIAL PRIMARY KEY,
  state VARCHAR(128) NOT NULL UNIQUE,
  nonce VARCHAR(128) NOT NULL,
  code_verifier VARCHAR(256) NOT NULL,
  redirect_uri VARCHAR(512) NOT NULL,
  return_mode VARCHAR(32) NOT NULL DEFAULT 'popup',
  created_at VARCHAR(64) NOT NULL,
  expires_at VARCHAR(64) NOT NULL,
  consumed_at VARCHAR(64)
);
CREATE INDEX IF NOT EXISTS ix_auth_oidc_states_state ON auth_oidc_states (state);
CREATE INDEX IF NOT EXISTS ix_auth_oidc_states_created_at ON auth_oidc_states (created_at);
CREATE INDEX IF NOT EXISTS ix_auth_oidc_states_expires_at ON auth_oidc_states (expires_at);
CREATE INDEX IF NOT EXISTS ix_auth_oidc_states_consumed_at ON auth_oidc_states (consumed_at);