-- ============================================================================
-- TECS MOBILE EXTENSION SCHEMA
-- Texas Executive Clinical Systems — GPS Geofencing, Document Vault, Clock-In
-- Purpose: Extends the revised TECS schema with mobile-specific tables for
--          geofenced clock-in/out, credential document vault, and nurse
--          self-service signup workflow.
-- Author: Manus AI
-- Date: 2026-05-20
-- ============================================================================

-- ---------------------------------------------------------------------------
-- M1. ADDITIONAL CONTROLLED VOCABULARIES
-- ---------------------------------------------------------------------------

do $$ begin
  create type tecs.clock_event_type as enum (
    'clock_in',
    'clock_out',
    'break_start',
    'break_end'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.geofence_result as enum (
    'within_radius',
    'outside_radius',
    'gps_unavailable',
    'permission_denied'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.signup_status as enum (
    'pending_verification',
    'documents_required',
    'under_review',
    'approved',
    'rejected'
  );
exception when duplicate_object then null;
end $$;

do $$ begin
  create type tecs.document_vault_status as enum (
    'uploaded',
    'verified',
    'expired',
    'rejected',
    'pending_review'
  );
exception when duplicate_object then null;
end $$;

-- ---------------------------------------------------------------------------
-- M2. FACILITY GEOFENCE CONFIGURATION
-- ---------------------------------------------------------------------------

create table if not exists tecs.facility_geofences (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references tecs.facilities(id) on delete cascade,
  gps_latitude numeric(9,6) not null,
  gps_longitude numeric(9,6) not null,
  radius_meters numeric(8,2) not null default 200.0
    check (radius_meters between 50.0 and 2000.0),
  label text not null default 'Primary Entrance',
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_facility_geofences_facility
  on tecs.facility_geofences(facility_id)
  where is_active = true;

comment on table tecs.facility_geofences is
  'Defines GPS geofence boundaries for each facility. Multiple geofences per facility are supported (e.g., main entrance, parking structure, satellite building).';

-- ---------------------------------------------------------------------------
-- M3. CLOCK-IN/OUT EVENT LOG (GPS-VERIFIED)
-- ---------------------------------------------------------------------------

create table if not exists tecs.clock_events (
  id uuid primary key default gen_random_uuid(),
  shift_id uuid not null references tecs.shifts(id) on delete restrict,
  nurse_id uuid not null references tecs.nurses(id) on delete restrict,
  facility_id uuid not null references tecs.facilities(id) on delete restrict,
  event_type tecs.clock_event_type not null,
  -- GPS capture at moment of event
  device_latitude numeric(9,6) not null,
  device_longitude numeric(9,6) not null,
  device_accuracy_meters numeric(8,2),
  device_altitude_meters numeric(8,2),
  -- Geofence validation result
  geofence_id uuid references tecs.facility_geofences(id) on delete set null,
  geofence_result tecs.geofence_result not null,
  distance_to_facility_meters numeric(8,2) not null,
  -- Device metadata for audit
  device_id text,
  device_os text,
  app_version text,
  -- Timestamps
  event_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);

create index if not exists idx_clock_events_shift
  on tecs.clock_events(shift_id, event_at);

create index if not exists idx_clock_events_nurse_date
  on tecs.clock_events(nurse_id, event_at desc);

comment on table tecs.clock_events is
  'Immutable log of GPS-verified clock-in/out events. Each event records the device position and the geofence validation outcome. Records cannot be modified after creation.';

-- ---------------------------------------------------------------------------
-- M4. CREDENTIAL DOCUMENT VAULT (CAMERA-DRIVEN UPLOADS)
-- ---------------------------------------------------------------------------

create table if not exists tecs.document_vault (
  id uuid primary key default gen_random_uuid(),
  nurse_id uuid not null references tecs.nurses(id) on delete cascade,
  document_type text not null,
  -- Supported types: 'nursing_license', 'bls_card', 'tb_test', 'government_id',
  --                  'cpr_card', 'specialty_cert', 'covid_vaccine', 'other'
  display_name text not null,
  -- Storage reference (Supabase Storage bucket)
  storage_bucket text not null default 'credential-vault',
  storage_path text not null,
  file_mime_type text not null,
  file_size_bytes bigint not null check (file_size_bytes > 0),
  file_sha256 text not null,
  -- Capture metadata
  capture_method text not null default 'camera',
  -- 'camera', 'gallery', 'file_picker', 'scanner'
  device_id text,
  -- Document validity
  issued_date date,
  expiration_date date,
  -- Review workflow
  vault_status tecs.document_vault_status not null default 'uploaded',
  reviewed_by uuid references auth.users(id) on delete set null,
  reviewed_at timestamptz,
  rejection_reason text,
  -- OCR/extraction metadata
  extracted_data jsonb not null default '{}'::jsonb,
  ocr_confidence_score numeric(5,4) check (ocr_confidence_score between 0 and 1),
  -- Timestamps
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (storage_bucket, storage_path)
);

create index if not exists idx_document_vault_nurse_type
  on tecs.document_vault(nurse_id, document_type);

create index if not exists idx_document_vault_expiration
  on tecs.document_vault(expiration_date)
  where vault_status = 'verified';

comment on table tecs.document_vault is
  'Stores metadata for credential documents uploaded via mobile camera or file picker. Actual files reside in Supabase Storage; this table holds references, hashes, and review state.';

-- ---------------------------------------------------------------------------
-- M5. NURSE SELF-SERVICE SIGNUP REQUESTS
-- ---------------------------------------------------------------------------

create table if not exists tecs.nurse_signup_requests (
  id uuid primary key default gen_random_uuid(),
  -- Auth reference (may be null if pre-registration)
  user_id uuid references auth.users(id) on delete set null,
  -- Submitted profile data
  full_name text not null,
  email text not null,
  phone text,
  license_number text not null,
  license_jurisdiction text not null default 'TX',
  license_type text not null default 'RN',
  bls_expiration date not null,
  tb_expiration date not null,
  -- Signup workflow state
  signup_status tecs.signup_status not null default 'pending_verification',
  -- Automated verification results
  license_verification_id uuid references tecs.credential_verifications(id) on delete set null,
  license_verified_at timestamptz,
  -- Required documents checklist
  documents_required jsonb not null default '["nursing_license","bls_card","tb_test","government_id"]'::jsonb,
  documents_submitted jsonb not null default '[]'::jsonb,
  -- Review
  reviewed_by uuid references auth.users(id) on delete set null,
  reviewed_at timestamptz,
  rejection_reason text,
  -- If approved, link to created nurse record
  approved_nurse_id uuid references tecs.nurses(id) on delete set null,
  -- Timestamps
  submitted_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_nurse_signup_requests_status
  on tecs.nurse_signup_requests(signup_status, submitted_at desc);

create index if not exists idx_nurse_signup_requests_license
  on tecs.nurse_signup_requests(license_jurisdiction, license_number);

comment on table tecs.nurse_signup_requests is
  'Tracks nurse self-service registration requests from the mobile app. Each request goes through automated license verification and document upload before compliance review.';

-- ---------------------------------------------------------------------------
-- M6. IMMUTABILITY CONTROLS FOR CLOCK EVENTS
-- ---------------------------------------------------------------------------

create or replace function tecs.block_clock_event_mutation()
returns trigger
language plpgsql
as $$
begin
  if tg_op = 'DELETE' then
    raise exception 'Audit violation: clock events are immutable and cannot be deleted.';
  end if;
  if tg_op = 'UPDATE' then
    raise exception 'Audit violation: clock events are immutable and cannot be updated.';
  end if;
  return null;
end;
$$;

create trigger trg_block_clock_event_update
before update on tecs.clock_events
for each row execute function tecs.block_clock_event_mutation();

create trigger trg_block_clock_event_delete
before delete on tecs.clock_events
for each row execute function tecs.block_clock_event_mutation();

-- ---------------------------------------------------------------------------
-- M7. GEOFENCE VALIDATION FUNCTION (HAVERSINE)
-- ---------------------------------------------------------------------------

create or replace function tecs.calculate_haversine_distance(
  lat1 numeric, lon1 numeric,
  lat2 numeric, lon2 numeric
)
returns numeric
language plpgsql
immutable
as $$
declare
  earth_radius_meters constant numeric := 6371000.0;
  d_lat numeric;
  d_lon numeric;
  a numeric;
  c numeric;
begin
  d_lat := radians(lat2 - lat1);
  d_lon := radians(lon2 - lon1);
  a := sin(d_lat / 2) ^ 2 + cos(radians(lat1)) * cos(radians(lat2)) * sin(d_lon / 2) ^ 2;
  c := 2 * atan2(sqrt(a), sqrt(1 - a));
  return earth_radius_meters * c;
end;
$$;

comment on function tecs.calculate_haversine_distance is
  'Calculates the great-circle distance in meters between two GPS coordinates using the Haversine formula. Used for geofence radius validation.';

-- ---------------------------------------------------------------------------
-- M8. CLOCK-IN PROCEDURE WITH GEOFENCE ENFORCEMENT
-- ---------------------------------------------------------------------------

create or replace function tecs.execute_geofenced_clock_in(
  p_shift_id uuid,
  p_nurse_id uuid,
  p_device_latitude numeric,
  p_device_longitude numeric,
  p_device_accuracy_meters numeric default null,
  p_device_id text default null,
  p_device_os text default null,
  p_app_version text default null
)
returns jsonb
language plpgsql
security definer
set search_path = tecs, public
as $$
declare
  v_shift record;
  v_geofence record;
  v_distance numeric;
  v_result tecs.geofence_result;
  v_clock_event_id uuid;
begin
  -- Validate shift exists and is assigned to this nurse
  select s.*, f.name as facility_name
  into v_shift
  from tecs.shifts s
  join tecs.facilities f on f.id = s.facility_id
  where s.id = p_shift_id
    and s.nurse_id = p_nurse_id
    and s.status in ('assigned', 'locked');

  if v_shift is null then
    return jsonb_build_object(
      'success', false,
      'error', 'CLOCK_IN_DENIED',
      'message', 'No valid assigned shift found for this nurse.'
    );
  end if;

  -- Find the nearest active geofence for the facility
  select g.*,
         tecs.calculate_haversine_distance(p_device_latitude, p_device_longitude, g.gps_latitude, g.gps_longitude) as calc_distance
  into v_geofence
  from tecs.facility_geofences g
  where g.facility_id = v_shift.facility_id
    and g.is_active = true
  order by tecs.calculate_haversine_distance(p_device_latitude, p_device_longitude, g.gps_latitude, g.gps_longitude) asc
  limit 1;

  if v_geofence is null then
    return jsonb_build_object(
      'success', false,
      'error', 'CONFIGURATION_ERROR',
      'message', 'No geofence configured for this facility. Contact administrator.'
    );
  end if;

  v_distance := v_geofence.calc_distance;

  -- Enforce geofence radius
  if v_distance > v_geofence.radius_meters then
    v_result := 'outside_radius';

    -- Log the failed attempt
    insert into tecs.clock_events(
      shift_id, nurse_id, facility_id, event_type,
      device_latitude, device_longitude, device_accuracy_meters,
      geofence_id, geofence_result, distance_to_facility_meters,
      device_id, device_os, app_version
    ) values (
      p_shift_id, p_nurse_id, v_shift.facility_id, 'clock_in',
      p_device_latitude, p_device_longitude, p_device_accuracy_meters,
      v_geofence.id, v_result, v_distance,
      p_device_id, p_device_os, p_app_version
    );

    return jsonb_build_object(
      'success', false,
      'error', 'CLOCK_IN_DENIED',
      'message', format('You are %.1f meters from %s. Must be within %.0f meters to clock in.',
                        v_distance, v_shift.facility_name, v_geofence.radius_meters),
      'distance_meters', round(v_distance, 1),
      'max_radius_meters', v_geofence.radius_meters
    );
  end if;

  -- Geofence passed
  v_result := 'within_radius';

  -- Record the clock-in event
  insert into tecs.clock_events(
    shift_id, nurse_id, facility_id, event_type,
    device_latitude, device_longitude, device_accuracy_meters,
    geofence_id, geofence_result, distance_to_facility_meters,
    device_id, device_os, app_version
  ) values (
    p_shift_id, p_nurse_id, v_shift.facility_id, 'clock_in',
    p_device_latitude, p_device_longitude, p_device_accuracy_meters,
    v_geofence.id, v_result, v_distance,
    p_device_id, p_device_os, p_app_version
  ) returning id into v_clock_event_id;

  -- Lock the shift
  update tecs.shifts
  set status = 'locked',
      locked_at = now(),
      updated_at = now()
  where id = p_shift_id;

  -- Audit log
  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, 'mobile_app', 'insert', 'clock_events', v_clock_event_id,
    'Nurse clocked in via GPS-verified mobile app.',
    jsonb_build_object(
      'distance_meters', round(v_distance, 1),
      'geofence_radius', v_geofence.radius_meters,
      'device_id', p_device_id,
      'shift_id', p_shift_id
    )
  );

  return jsonb_build_object(
    'success', true,
    'message', format('Clock-in successful at %s. Distance: %.1f meters.', v_shift.facility_name, v_distance),
    'clock_event_id', v_clock_event_id,
    'distance_meters', round(v_distance, 1),
    'shift_locked', true
  );
end;
$$;

comment on function tecs.execute_geofenced_clock_in is
  'Validates nurse GPS position against facility geofence, records the clock-in event, and locks the shift. Returns structured JSON with success/failure details.';

-- ---------------------------------------------------------------------------
-- M9. RLS POLICIES FOR MOBILE TABLES
-- ---------------------------------------------------------------------------

alter table tecs.facility_geofences enable row level security;
alter table tecs.clock_events enable row level security;
alter table tecs.document_vault enable row level security;
alter table tecs.nurse_signup_requests enable row level security;

-- Facility geofences: readable by facility members, managed by admins
create policy "facility members can read geofences"
on tecs.facility_geofences for select
using (tecs.is_facility_member(facility_id));

create policy "facility admins can manage geofences"
on tecs.facility_geofences 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[]));

-- Clock events: readable by facility compliance, insertable via RPC only
create policy "facility compliance can read clock events"
on tecs.clock_events for select
using (tecs.has_facility_role(facility_id, array['facility_admin','compliance_officer','scheduler','read_only_auditor']::tecs.member_role[]));

-- Direct inserts to clock_events are done via the execute_geofenced_clock_in function
-- which runs as security definer. No direct insert policy for authenticated users.

-- Document vault: nurses can read their own, compliance can read facility nurses
create policy "nurses can read own vault documents"
on tecs.document_vault for select
using (
  exists (
    select 1 from tecs.nurses n
    join auth.users u on u.id = auth.uid()
    join tecs.nurse_signup_requests nsr on nsr.user_id = auth.uid()
    where n.id = document_vault.nurse_id
      and nsr.approved_nurse_id = n.id
  )
  or tecs.can_access_nurse(nurse_id)
  or tecs.is_platform_admin()
);

create policy "compliance can manage vault documents"
on tecs.document_vault for all
using (tecs.is_platform_admin() or tecs.can_access_nurse(nurse_id))
with check (tecs.is_platform_admin() or tecs.can_access_nurse(nurse_id));

-- Signup requests: users can read own, compliance can read all
create policy "users can read own signup request"
on tecs.nurse_signup_requests for select
using (user_id = auth.uid() or tecs.is_platform_admin());

create policy "users can create signup request"
on tecs.nurse_signup_requests for insert
with check (user_id = auth.uid());

create policy "compliance can manage signup requests"
on tecs.nurse_signup_requests for all
using (tecs.is_platform_admin())
with check (tecs.is_platform_admin());

-- ---------------------------------------------------------------------------
-- M10. UPDATED_AT TRIGGERS FOR NEW TABLES
-- ---------------------------------------------------------------------------

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

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

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

-- ============================================================================
-- END OF TECS MOBILE EXTENSION SCHEMA
-- ============================================================================
