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

CREATE TABLE IF NOT EXISTS auth_external_identities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider TEXT NOT NULL,
  external_subject TEXT NOT NULL,
  username TEXT NOT NULL,
  email TEXT,
  full_name TEXT,
  role TEXT NOT NULL,
  linked_user_id TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  last_login_at TEXT 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 INTEGER PRIMARY KEY AUTOINCREMENT,
  state TEXT NOT NULL UNIQUE,
  nonce TEXT NOT NULL,
  code_verifier TEXT NOT NULL,
  redirect_uri TEXT NOT NULL,
  return_mode TEXT NOT NULL DEFAULT 'popup',
  created_at TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  consumed_at TEXT
);
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);