-- ============================================================================
-- TECS REVISED DATABASE SCHEMA
-- Texas Executive Clinical Systems
-- Purpose: Production-oriented Supabase/PostgreSQL schema incorporating
--          corrected RLS, encrypted-content storage patterns, credential
--          verification records, append-only incident addenda, and audit logging.
-- Author: Manus AI
-- Date: 2026-05-20
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 0. EXTENSIONS AND SCHEMA
-- ---------------------------------------------------------------------------

create extension if not exists pgcrypto;
create extension if not exists "uuid-ossp";
-- Enable pgaudit from the Supabase dashboard when available, then configure
-- role/object logging outside this migration. Some hosted environments restrict
-- direct CREATE EXTENSION for pgaudit.
-- create extension if not exists pgaudit;

create schema if not exists tecs;

-- ---------------------------------------------------------------------------
-- 1. CONTROLLED VOCABULARIES
-- ---------------------------------------------------------------------------

do $$ begin
  create type tecs.member_role as enum (
    'facility_admin',
    'scheduler',
    'compliance_officer',
    'clinical_reviewer',
    'read_only_auditor'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.license_status as enum (
    'active',
    'expired',
    'suspended',
    'encumbered',
    'under_review',
    'unknown'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.compliance_status as enum (
    'green',
    'yellow',
    'red',
    'manual_review'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.credential_source as enum (
    'nursys_enotify_api',
    'texas_bon_portal_manual',
    'document_upload',
    'facility_attestation',
    'system_backfill'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.verification_result as enum (
    'clear',
    'flagged',
    'not_found',
    'source_unavailable',
    'manual_review_required'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.shift_status as enum (
    'open',
    'assigned',
    'locked',
    'released',
    'cancelled',
    'completed'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.incident_severity as enum (
    'tier_1_critical',
    'tier_2_clinical',
    'tier_3_admin'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.audit_action as enum (
    'insert',
    'update',
    'delete_attempt_blocked',
    'credential_scan_started',
    'credential_scan_completed',
    'credential_flag_detected',
    'hard_stop_applied',
    'shift_released',
    'incident_created',
    'incident_addendum_created',
    'legal_hold_applied',
    'security_event'
  );
exception when duplicate_object then null;
end $$;

-- ---------------------------------------------------------------------------
-- 2. IDENTITY, TENANCY, AND ROLE MEMBERSHIP
-- ---------------------------------------------------------------------------

create table if not exists tecs.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  legal_name text,
  tax_identifier_last4 text,
  business_associate_agreement_on_file boolean not null default false,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists tecs.facilities (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references tecs.organizations(id) on delete restrict,
  name text not null,
  region text not null default 'DFW',
  npi text,
  state_license_number text,
  address_line1 text,
  address_line2 text,
  city text,
  state text not null default 'TX',
  postal_code text,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (organization_id, name)
);

create table if not exists tecs.user_profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  display_name text not null,
  email text,
  phone text,
  is_platform_admin boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists tecs.facility_memberships (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references tecs.facilities(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role tecs.member_role not null,
  is_active boolean not null default true,
  granted_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (facility_id, user_id, role)
);

create index if not exists idx_facility_memberships_user_facility
  on tecs.facility_memberships(user_id, facility_id)
  where is_active = true;

-- ---------------------------------------------------------------------------
-- 3. NURSE MASTER RECORDS AND FACILITY ASSIGNMENTS
-- ---------------------------------------------------------------------------

create table if not exists tecs.nurses (
  id uuid primary key default gen_random_uuid(),
  legal_name text not null,
  preferred_name text,
  date_of_birth_hash text,
  license_number text not null,
  license_jurisdiction text not null default 'TX',
  license_type text not null default 'RN',
  ncsbn_id text,
  license_status tecs.license_status not null default 'unknown',
  compliance_status tecs.compliance_status not null default 'manual_review',
  bls_expiration date,
  tb_expiration date,
  compact_license boolean not null default false,
  last_verified_at timestamptz,
  next_verification_due_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (license_jurisdiction, license_number)
);

create index if not exists idx_nurses_status_due
  on tecs.nurses(compliance_status, next_verification_due_at);

create table if not exists tecs.facility_nurses (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references tecs.facilities(id) on delete cascade,
  nurse_id uuid not null references tecs.nurses(id) on delete restrict,
  relationship_status text not null default 'active',
  onboarding_completed_at timestamptz,
  offboarded_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (facility_id, nurse_id)
);

create index if not exists idx_facility_nurses_nurse
  on tecs.facility_nurses(nurse_id);

-- ---------------------------------------------------------------------------
-- 4. CREDENTIAL DOCUMENTS AND REAL-TIME VERIFICATION RECORDS
-- ---------------------------------------------------------------------------

create table if not exists tecs.credential_documents (
  id uuid primary key default gen_random_uuid(),
  nurse_id uuid not null references tecs.nurses(id) on delete cascade,
  facility_id uuid references tecs.facilities(id) on delete cascade,
  document_type text not null,
  storage_bucket text not null,
  storage_path text not null,
  file_sha256 text not null,
  issued_at date,
  expires_at date,
  verified_by uuid references auth.users(id) on delete set null,
  verified_at timestamptz,
  created_at timestamptz not null default now(),
  unique (storage_bucket, storage_path)
);

create table if not exists tecs.credential_verifications (
  id uuid primary key default gen_random_uuid(),
  nurse_id uuid not null references tecs.nurses(id) on delete cascade,
  source tecs.credential_source not null,
  result tecs.verification_result not null,
  license_status tecs.license_status not null default 'unknown',
  source_reference_id text,
  source_checked_at timestamptz not null default now(),
  raw_payload jsonb,
  raw_payload_sha256 text,
  parsed_payload jsonb,
  risk_score integer not null default 0 check (risk_score between 0 and 100),
  reason text,
  reviewed_by uuid references auth.users(id) on delete set null,
  reviewed_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists idx_credential_verifications_nurse_created
  on tecs.credential_verifications(nurse_id, created_at desc);

create index if not exists idx_credential_verifications_result
  on tecs.credential_verifications(result, source_checked_at desc);

-- ---------------------------------------------------------------------------
-- 5. SHIFT DISPATCH CORE
-- ---------------------------------------------------------------------------

create table if not exists tecs.shifts (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references tecs.facilities(id) on delete cascade,
  nurse_id uuid references tecs.nurses(id) on delete set null,
  unit_type text not null,
  hourly_rate numeric(8,2) not null check (hourly_rate >= 0),
  shift_start timestamptz not null,
  shift_end timestamptz not null,
  status tecs.shift_status not null default 'open',
  locked_at timestamptz,
  released_reason text,
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (shift_end > shift_start),
  check ((status in ('assigned','locked','completed') and nurse_id is not null) or status in ('open','released','cancelled'))
);

create index if not exists idx_shifts_facility_window
  on tecs.shifts(facility_id, shift_start, shift_end);

create index if not exists idx_shifts_nurse_future
  on tecs.shifts(nurse_id, shift_start)
  where nurse_id is not null;

-- ---------------------------------------------------------------------------
-- 6. ENCRYPTION-READY INCIDENT REPORTS AND ADDENDA
-- ---------------------------------------------------------------------------

create table if not exists tecs.incident_reports (
  id uuid primary key default gen_random_uuid(),
  nurse_id uuid not null references tecs.nurses(id) on delete restrict,
  facility_id uuid not null references tecs.facilities(id) on delete restrict,
  severity_tier tecs.incident_severity not null,
  incident_type text not null,
  narrative_ciphertext bytea not null,
  narrative_sha256 text not null,
  encryption_key_ref text not null,
  encryption_algorithm text not null default 'app-managed-aead-xchacha20poly1305',
  is_legal_hold boolean not null default false,
  legal_hold_reason text,
  gps_latitude numeric(9,6),
  gps_longitude numeric(9,6),
  reported_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_incident_reports_facility_created
  on tecs.incident_reports(facility_id, created_at desc);

create index if not exists idx_incident_reports_nurse_created
  on tecs.incident_reports(nurse_id, created_at desc);

create table if not exists tecs.incident_addenda (
  id uuid primary key default gen_random_uuid(),
  incident_id uuid not null references tecs.incident_reports(id) on delete restrict,
  addendum_ciphertext bytea not null,
  addendum_sha256 text not null,
  encryption_key_ref text not null,
  encryption_algorithm text not null default 'app-managed-aead-xchacha20poly1305',
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);

create index if not exists idx_incident_addenda_incident_created
  on tecs.incident_addenda(incident_id, created_at asc);

-- ---------------------------------------------------------------------------
-- 7. APPEND-ONLY AUDIT EVENTS
-- ---------------------------------------------------------------------------

create table if not exists tecs.audit_events (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid references tecs.facilities(id) on delete set null,
  nurse_id uuid references tecs.nurses(id) on delete set null,
  actor_user_id uuid references auth.users(id) on delete set null,
  actor_kind text not null default 'system',
  action tecs.audit_action not null,
  entity_schema text not null default 'tecs',
  entity_table text,
  entity_id uuid,
  reason text,
  metadata jsonb not null default '{}'::jsonb,
  source_ip inet,
  user_agent text,
  request_id text,
  created_at timestamptz not null default now()
);

create index if not exists idx_audit_events_facility_created
  on tecs.audit_events(facility_id, created_at desc);

create index if not exists idx_audit_events_entity
  on tecs.audit_events(entity_table, entity_id, created_at desc);

-- ---------------------------------------------------------------------------
-- 8. UPDATED_AT AUTOMATION
-- ---------------------------------------------------------------------------

create or replace function tecs.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger trg_organizations_updated_at
before update on tecs.organizations
for each row execute function tecs.set_updated_at();

create trigger trg_facilities_updated_at
before update on tecs.facilities
for each row execute function tecs.set_updated_at();

create trigger trg_user_profiles_updated_at
before update on tecs.user_profiles
for each row execute function tecs.set_updated_at();

create trigger trg_facility_memberships_updated_at
before update on tecs.facility_memberships
for each row execute function tecs.set_updated_at();

create trigger trg_nurses_updated_at
before update on tecs.nurses
for each row execute function tecs.set_updated_at();

create trigger trg_facility_nurses_updated_at
before update on tecs.facility_nurses
for each row execute function tecs.set_updated_at();

create trigger trg_shifts_updated_at
before update on tecs.shifts
for each row execute function tecs.set_updated_at();

create trigger trg_incident_reports_updated_at
before update on tecs.incident_reports
for each row execute function tecs.set_updated_at();

-- ---------------------------------------------------------------------------
-- 9. IMMUTABILITY AND HARD-STOP TRIGGERS
-- ---------------------------------------------------------------------------

create or replace function tecs.block_incident_mutation()
returns trigger
language plpgsql
security definer
set search_path = tecs, public
as $$
begin
  if tg_op = 'DELETE' then
    insert into tecs.audit_events(action, actor_kind, entity_table, entity_id, facility_id, nurse_id, reason)
    values ('delete_attempt_blocked', 'database_trigger', 'incident_reports', old.id, old.facility_id, old.nurse_id,
            'Attempted deletion of immutable incident report was blocked.');
    raise exception 'Audit violation: incident reports are append-only and cannot be deleted.';
  end if;

  if old.narrative_ciphertext is distinct from new.narrative_ciphertext
     or old.narrative_sha256 is distinct from new.narrative_sha256
     or old.encryption_key_ref is distinct from new.encryption_key_ref then
    raise exception 'Audit violation: incident narratives are immutable. Create an incident_addenda record instead.';
  end if;

  if old.is_legal_hold = true and new.is_legal_hold = false then
    raise exception 'Audit violation: legal holds cannot be removed through standard update paths.';
  end if;

  return new;
end;
$$;

create trigger trg_block_incident_update
before update on tecs.incident_reports
for each row execute function tecs.block_incident_mutation();

create trigger trg_block_incident_delete
before delete on tecs.incident_reports
for each row execute function tecs.block_incident_mutation();

create or replace function tecs.block_audit_event_mutation()
returns trigger
language plpgsql
as $$
begin
  raise exception 'Audit violation: audit_events are append-only and cannot be updated or deleted.';
end;
$$;

create trigger trg_block_audit_event_update
before update on tecs.audit_events
for each row execute function tecs.block_audit_event_mutation();

create trigger trg_block_audit_event_delete
before delete on tecs.audit_events
for each row execute function tecs.block_audit_event_mutation();

create or replace function tecs.log_incident_insert()
returns trigger
language plpgsql
security definer
set search_path = tecs, public
as $$
begin
  insert into tecs.audit_events(action, actor_kind, actor_user_id, entity_table, entity_id, facility_id, nurse_id, reason)
  values ('incident_created', 'authenticated_user', auth.uid(), 'incident_reports', new.id, new.facility_id, new.nurse_id,
          'Incident report created as immutable encrypted record.');
  return new;
end;
$$;

create trigger trg_log_incident_insert
after insert on tecs.incident_reports
for each row execute function tecs.log_incident_insert();

create or replace function tecs.log_incident_addendum_insert()
returns trigger
language plpgsql
security definer
set search_path = tecs, public
as $$
declare
  v_facility_id uuid;
  v_nurse_id uuid;
begin
  select facility_id, nurse_id into v_facility_id, v_nurse_id
  from tecs.incident_reports
  where id = new.incident_id;

  insert into tecs.audit_events(action, actor_kind, actor_user_id, entity_table, entity_id, facility_id, nurse_id, reason)
  values ('incident_addendum_created', 'authenticated_user', auth.uid(), 'incident_addenda', new.id, v_facility_id, v_nurse_id,
          'Incident addendum appended without modifying original incident narrative.');
  return new;
end;
$$;

create trigger trg_log_incident_addendum_insert
after insert on tecs.incident_addenda
for each row execute function tecs.log_incident_addendum_insert();

-- ---------------------------------------------------------------------------
-- 10. RLS HELPER FUNCTIONS
-- ---------------------------------------------------------------------------

create or replace function tecs.is_platform_admin()
returns boolean
language sql
security definer
set search_path = tecs, public
stable
as $$
  select exists (
    select 1
    from tecs.user_profiles up
    where up.user_id = auth.uid()
      and up.is_platform_admin = true
  );
$$;

create or replace function tecs.is_facility_member(p_facility_id uuid)
returns boolean
language sql
security definer
set search_path = tecs, public
stable
as $$
  select auth.uid() is not null and (
    tecs.is_platform_admin()
    or exists (
      select 1
      from tecs.facility_memberships fm
      where fm.facility_id = p_facility_id
        and fm.user_id = auth.uid()
        and fm.is_active = true
    )
  );
$$;

create or replace function tecs.has_facility_role(p_facility_id uuid, p_roles tecs.member_role[])
returns boolean
language sql
security definer
set search_path = tecs, public
stable
as $$
  select auth.uid() is not null and (
    tecs.is_platform_admin()
    or exists (
      select 1
      from tecs.facility_memberships fm
      where fm.facility_id = p_facility_id
        and fm.user_id = auth.uid()
        and fm.role = any(p_roles)
        and fm.is_active = true
    )
  );
$$;

create or replace function tecs.can_access_nurse(p_nurse_id uuid)
returns boolean
language sql
security definer
set search_path = tecs, public
stable
as $$
  select auth.uid() is not null and (
    tecs.is_platform_admin()
    or exists (
      select 1
      from tecs.facility_nurses fn
      join tecs.facility_memberships fm on fm.facility_id = fn.facility_id
      where fn.nurse_id = p_nurse_id
        and fm.user_id = auth.uid()
        and fm.is_active = true
        and fn.relationship_status = 'active'
    )
  );
$$;

-- ---------------------------------------------------------------------------
-- 11. ROW LEVEL SECURITY
-- ---------------------------------------------------------------------------

alter table tecs.organizations enable row level security;
alter table tecs.facilities enable row level security;
alter table tecs.user_profiles enable row level security;
alter table tecs.facility_memberships enable row level security;
alter table tecs.nurses enable row level security;
alter table tecs.facility_nurses enable row level security;
alter table tecs.credential_documents enable row level security;
alter table tecs.credential_verifications enable row level security;
alter table tecs.shifts enable row level security;
alter table tecs.incident_reports enable row level security;
alter table tecs.incident_addenda enable row level security;
alter table tecs.audit_events enable row level security;

-- Organizations
create policy "organization members can read their organizations"
on tecs.organizations for select
using (
  tecs.is_platform_admin()
  or exists (
    select 1
    from tecs.facilities f
    join tecs.facility_memberships fm on fm.facility_id = f.id
    where f.organization_id = organizations.id
      and fm.user_id = auth.uid()
      and fm.is_active = true
  )
);

create policy "platform admins can manage organizations"
on tecs.organizations for all
using (tecs.is_platform_admin())
with check (tecs.is_platform_admin());

-- Facilities
create policy "facility members can read facilities"
on tecs.facilities for select
using (tecs.is_facility_member(id));

create policy "facility admins can update their facilities"
on tecs.facilities for update
using (tecs.has_facility_role(id, array['facility_admin']::tecs.member_role[]))
with check (tecs.has_facility_role(id, array['facility_admin']::tecs.member_role[]));

-- User profiles
create policy "users can read own profile"
on tecs.user_profiles for select
using (auth.uid() is not null and user_id = auth.uid());

create policy "users can update own limited profile"
on tecs.user_profiles for update
using (auth.uid() is not null and user_id = auth.uid())
with check (auth.uid() is not null and user_id = auth.uid() and is_platform_admin = false);

create policy "platform admins can read profiles"
on tecs.user_profiles for select
using (tecs.is_platform_admin());

-- Facility memberships
create policy "facility admins can read memberships"
on tecs.facility_memberships for select
using (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]));

create policy "facility admins can manage memberships"
on tecs.facility_memberships for all
using (tecs.has_facility_role(facility_id, array['facility_admin']::tecs.member_role[]))
with check (tecs.has_facility_role(facility_id, array['facility_admin']::tecs.member_role[]));

-- Nurses
create policy "facility members can read linked nurses"
on tecs.nurses for select
using (tecs.can_access_nurse(id));

create policy "compliance officers can manage linked nurses"
on tecs.nurses for update
using (
  tecs.is_platform_admin()
  or exists (
    select 1
    from tecs.facility_nurses fn
    where fn.nurse_id = nurses.id
      and tecs.has_facility_role(fn.facility_id, array['facility_admin','compliance_officer']::tecs.member_role[])
  )
)
with check (
  tecs.is_platform_admin()
  or exists (
    select 1
    from tecs.facility_nurses fn
    where fn.nurse_id = nurses.id
      and tecs.has_facility_role(fn.facility_id, array['facility_admin','compliance_officer']::tecs.member_role[])
  )
);

-- Facility-nurse assignments
create policy "facility members can read facility nurse assignments"
on tecs.facility_nurses for select
using (tecs.is_facility_member(facility_id));

create policy "facility compliance can manage nurse assignments"
on tecs.facility_nurses for all
using (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]))
with check (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]));

-- Credential documents
create policy "facility compliance can read credential documents"
on tecs.credential_documents for select
using (
  tecs.is_platform_admin()
  or (facility_id is not null and tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]))
  or tecs.can_access_nurse(nurse_id)
);

create policy "facility compliance can manage credential documents"
on tecs.credential_documents for all
using (tecs.is_platform_admin() or (facility_id is not null and tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[])))
with check (tecs.is_platform_admin() or (facility_id is not null and tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[])));

-- Credential verifications
create policy "facility compliance can read verification history"
on tecs.credential_verifications for select
using (tecs.is_platform_admin() or tecs.can_access_nurse(nurse_id));

-- Writes to credential_verifications should normally be performed by service_role
-- from Edge Functions or back-office jobs. Human users may only update review fields
-- through controlled RPC functions, not direct table updates.

-- Shifts
create policy "facility members can read shifts"
on tecs.shifts for select
using (tecs.is_facility_member(facility_id));

create policy "schedulers can manage shifts"
on tecs.shifts for all
using (tecs.has_facility_role(facility_id, array['facility_admin','scheduler','compliance_officer']::tecs.member_role[]))
with check (tecs.has_facility_role(facility_id, array['facility_admin','scheduler','compliance_officer']::tecs.member_role[]));

-- Incident reports
create policy "clinical and compliance roles can read incident metadata"
on tecs.incident_reports for select
using (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer','clinical_reviewer','read_only_auditor']::tecs.member_role[]));

create policy "clinical and compliance roles can create incident reports"
on tecs.incident_reports for insert
with check (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer','clinical_reviewer']::tecs.member_role[]));

create policy "compliance officers can update legal hold metadata only"
on tecs.incident_reports for update
using (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]))
with check (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer']::tecs.member_role[]));

-- Incident addenda
create policy "incident readers can read addenda"
on tecs.incident_addenda for select
using (
  exists (
    select 1
    from tecs.incident_reports ir
    where ir.id = incident_addenda.incident_id
      and tecs.has_facility_role(ir.facility_id, array['facility_admin','compliance_officer','clinical_reviewer','read_only_auditor']::tecs.member_role[])
  )
);

create policy "clinical and compliance roles can append addenda"
on tecs.incident_addenda for insert
with check (
  exists (
    select 1
    from tecs.incident_reports ir
    where ir.id = incident_addenda.incident_id
      and tecs.has_facility_role(ir.facility_id, array['facility_admin','compliance_officer','clinical_reviewer']::tecs.member_role[])
  )
);

-- Audit events
create policy "facility auditors can read audit events"
on tecs.audit_events for select
using (
  tecs.is_platform_admin()
  or (facility_id is not null and tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer','read_only_auditor']::tecs.member_role[]))
);

-- audit_events inserts are generated by database triggers and service_role jobs.
-- No direct authenticated insert/update/delete policy is intentionally provided.

-- ---------------------------------------------------------------------------
-- 12. HARD-STOP PROCEDURE FOR CREDENTIAL FAILURES
-- ---------------------------------------------------------------------------

create or replace function tecs.apply_credential_hard_stop(
  p_nurse_id uuid,
  p_reason text,
  p_verification_id uuid default null
)
returns void
language plpgsql
security definer
set search_path = tecs, public
as $$
declare
  v_shift record;
begin
  update tecs.nurses
  set compliance_status = 'red',
      license_status = case when license_status = 'active' then 'under_review' else license_status end,
      updated_at = now()
  where id = p_nurse_id;

  for v_shift in
    select id, facility_id
    from tecs.shifts
    where nurse_id = p_nurse_id
      and shift_start > now()
      and status in ('assigned', 'locked')
  loop
    update tecs.shifts
    set nurse_id = null,
        status = 'released',
        locked_at = null,
        released_reason = p_reason,
        updated_at = now()
    where id = v_shift.id;

    insert into tecs.audit_events(facility_id, nurse_id, actor_kind, action, entity_table, entity_id, reason, metadata)
    values (
      v_shift.facility_id,
      p_nurse_id,
      'system',
      'shift_released',
      'shifts',
      v_shift.id,
      p_reason,
      jsonb_build_object('verification_id', p_verification_id)
    );
  end loop;

  insert into tecs.audit_events(nurse_id, actor_kind, action, entity_table, entity_id, reason, metadata)
  values (
    p_nurse_id,
    'system',
    'hard_stop_applied',
    'nurses',
    p_nurse_id,
    p_reason,
    jsonb_build_object('verification_id', p_verification_id)
  );
end;
$$;

-- ---------------------------------------------------------------------------
-- 13. OPERATIONAL VIEWS
-- ---------------------------------------------------------------------------

create or replace view tecs.v_facility_shift_roster as
select
  s.id as shift_id,
  s.facility_id,
  f.name as facility_name,
  s.unit_type,
  s.shift_start,
  s.shift_end,
  s.status,
  n.id as nurse_id,
  n.legal_name as nurse_name,
  n.license_number,
  n.license_status,
  n.compliance_status,
  n.last_verified_at
from tecs.shifts s
join tecs.facilities f on f.id = s.facility_id
left join tecs.nurses n on n.id = s.nurse_id;

-- RLS on underlying tables governs this view. In Supabase, prefer security_invoker
-- views where supported by the platform version:
-- alter view tecs.v_facility_shift_roster set (security_invoker = true);

-- ---------------------------------------------------------------------------
-- 14. RECOMMENDED GRANTS
-- ---------------------------------------------------------------------------

-- Adapt grants to the schemas exposed through Supabase API settings.
grant usage on schema tecs to authenticated;
grant select, insert, update, delete on all tables in schema tecs to authenticated;
grant usage, select on all sequences in schema tecs to authenticated;

-- Do not grant anon access to TECS clinical tables unless a public endpoint is
-- explicitly designed and independently reviewed.

-- ============================================================================
-- END OF TECS REVISED DATABASE SCHEMA
-- ============================================================================
