create table if not exists public.tenant_auth_providers (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  provider text not null,
  status text not null default 'disabled' check (status in ('active', 'disabled', 'testing')),
  display_name text,
  config_public jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (tenant_id, provider)
);

comment on table public.tenant_auth_providers is
  'Public, non-secret auth provider settings for each tenant. Secrets stay in app_private.tenant_secrets or an external vault.';

create table if not exists public.sms_verification_codes (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  phone text not null,
  purpose text not null default 'login' check (purpose in ('login', 'bind_phone', 'reset_password')),
  code_hash text not null,
  provider text not null default 'mock',
  status text not null default 'pending' check (status in ('pending', 'sent', 'verified', 'expired', 'blocked')),
  attempts integer not null default 0,
  expires_at timestamptz not null,
  consumed_at timestamptz,
  ip_address text,
  user_agent text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

comment on table public.sms_verification_codes is
  'SMS verification records. Plain verification codes are never stored; only one-way hashes are kept.';

create table if not exists public.auth_login_events (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  user_id uuid references public.platform_users(id) on delete set null,
  provider text not null,
  identifier text,
  result text not null check (result in ('sent', 'success', 'failed', 'blocked')),
  failure_code text,
  ip_address text,
  user_agent text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists app_private.auth_sessions (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  user_id uuid not null references public.platform_users(id) on delete cascade,
  token_hash text not null unique,
  provider text not null,
  expires_at timestamptz not null,
  revoked_at timestamptz,
  ip_address text,
  user_agent text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

comment on table app_private.auth_sessions is
  'API-issued session token hashes for the migration period before full Supabase Auth JWT adoption.';

create index if not exists idx_auth_providers_tenant_status
  on public.tenant_auth_providers(tenant_id, provider, status);

create index if not exists idx_sms_codes_tenant_phone_purpose
  on public.sms_verification_codes(tenant_id, phone, purpose, expires_at desc);

create index if not exists idx_sms_codes_pending_lookup
  on public.sms_verification_codes(tenant_id, phone, purpose, created_at desc)
  where consumed_at is null and status in ('pending', 'sent');

create index if not exists idx_auth_login_events_tenant_user
  on public.auth_login_events(tenant_id, user_id, created_at desc);

create index if not exists idx_auth_sessions_user
  on app_private.auth_sessions(tenant_id, user_id, expires_at desc)
  where revoked_at is null;

alter table public.tenant_auth_providers enable row level security;
alter table public.sms_verification_codes enable row level security;
alter table public.auth_login_events enable row level security;
alter table app_private.auth_sessions enable row level security;

drop policy if exists tenant_isolation on public.tenant_auth_providers;
create policy tenant_isolation on public.tenant_auth_providers
  for all
  using (tenant_id = app.current_tenant_id() or app.is_platform_admin())
  with check (tenant_id = app.current_tenant_id() or app.is_platform_admin());

drop policy if exists tenant_isolation on public.sms_verification_codes;
create policy tenant_isolation on public.sms_verification_codes
  for all
  using (tenant_id = app.current_tenant_id() or app.is_platform_admin())
  with check (tenant_id = app.current_tenant_id() or app.is_platform_admin());

drop policy if exists tenant_isolation on public.auth_login_events;
create policy tenant_isolation on public.auth_login_events
  for all
  using (tenant_id = app.current_tenant_id() or app.is_platform_admin())
  with check (tenant_id = app.current_tenant_id() or app.is_platform_admin());

drop policy if exists platform_admin_auth_sessions on app_private.auth_sessions;
create policy platform_admin_auth_sessions on app_private.auth_sessions
  for all
  using (app.is_platform_admin())
  with check (app.is_platform_admin());

drop trigger if exists set_updated_at on public.tenant_auth_providers;
create trigger set_updated_at
  before update on public.tenant_auth_providers
  for each row execute function app.touch_updated_at();

drop trigger if exists set_updated_at on app_private.auth_sessions;
create trigger set_updated_at
  before update on app_private.auth_sessions
  for each row execute function app.touch_updated_at();
