-- ============================================================================
-- 1. BASE SYSTEM STRUCTURAL CORE & RELATIONAL EXTENSIONS
-- ============================================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Avoid schema drift by cleanly dropping conflicts on rebuilds
DROP TABLE IF EXISTS public.user_profiles CASCADE;
DROP TABLE IF EXISTS public.incident_reports CASCADE;
DROP TABLE IF EXISTS public.shifts CASCADE;
DROP TABLE IF EXISTS public.facilities CASCADE;
DROP TABLE IF EXISTS public.nurses CASCADE;
DROP TYPE IF EXISTS public.user_role CASCADE;

-- Establish Explicit Multi-Portal Access Enums
CREATE TYPE public.user_role AS ENUM ('tecs_admin', 'facility_partner', 'field_nurse');

-- Clinician Compliance Infrastructure Table
CREATE TABLE public.nurses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    license_number TEXT UNIQUE NOT NULL,
    license_status TEXT NOT NULL DEFAULT 'Active', -- Active, Suspended, Under_Review
    compliance_status TEXT NOT NULL DEFAULT 'Green', -- Green, Yellow, Red
    bls_expiration DATE NOT NULL,
    tb_expiration DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Multi-Tenant Facility Profile Hubs (Hospitals, Prisons, LTACs)
CREATE TABLE public.facilities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    region TEXT NOT NULL DEFAULT 'DFW',
    gps_latitude NUMERIC(9,6) NOT NULL,
    gps_longitude NUMERIC(9,6) NOT NULL,
    monthly_saas_fee NUMERIC(8,2) DEFAULT 0.00,
    vms_admin_percentage NUMERIC(4,2) DEFAULT 3.00, -- Default 3% Managed Admin Markup
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Central Identity Security Workspace Profiles Table
CREATE TABLE public.user_profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    associated_role public.user_role NOT NULL DEFAULT 'field_nurse',
    facility_id UUID REFERENCES public.facilities(id) ON DELETE SET NULL,
    nurse_id UUID REFERENCES public.nurses(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Scheduling Engine with Automated Float Profit Accounting
CREATE TABLE public.shifts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    facility_id UUID NOT NULL REFERENCES public.facilities(id) ON DELETE CASCADE,
    nurse_id UUID REFERENCES public.nurses(id) ON DELETE SET NULL,
    unit_type TEXT NOT NULL, -- ICU, ER, Med-Surg, L&D
    shift_start TIMESTAMPTZ NOT NULL,
    shift_end TIMESTAMPTZ NOT NULL,
    is_locked BOOLEAN NOT NULL DEFAULT FALSE,
    bill_rate NUMERIC(6,2) NOT NULL DEFAULT 0.00,
    pay_rate NUMERIC(6,2) NOT NULL DEFAULT 0.00,
    calculated_gross_profit NUMERIC(6,2) GENERATED ALWAYS AS (bill_rate - pay_rate) STORED,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Immutable Incident Logs (The Guardian GRC Defense Layer)
CREATE TABLE public.incident_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nurse_id UUID NOT NULL REFERENCES public.nurses(id) ON DELETE RESTRICT,
    facility_id UUID NOT NULL REFERENCES public.facilities(id) ON DELETE RESTRICT,
    severity_tier TEXT NOT NULL, -- Tier_1_Critical, Tier_2_Admin
    incident_type TEXT NOT NULL, -- Medication_Error, Patient_Fall, Abuse_Neglect
    narrative TEXT NOT NULL, -- Secure AES-256 Encrypted Clinical Notes
    is_legal_hold BOOLEAN NOT NULL DEFAULT FALSE,
    gps_latitude NUMERIC(9,6),
    gps_longitude NUMERIC(9,6),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- ============================================================================
-- 2. "AUDITOR-GRADE" DELETION INTERLOCKS & VERSION CONTROLS
-- ============================================================================

-- Disables standard DELETE paths completely on critical clinical logs
CREATE RULE protect_tecs_incidents AS 
ON DELETE TO public.incident_reports 
DO INSTEAD NOTHING;

-- Enforces historical version lock parameters on filed reports during active audits
CREATE OR REPLACE FUNCTION public.lock_immutable_incidents()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_legal_hold = TRUE AND NEW.is_legal_hold = FALSE THEN
        RAISE EXCEPTION 'Audit Violation: Active legal holds cannot be stripped without senior compliance override keys.';
    END IF;
    IF OLD.narrative IS DISTINCT FROM NEW.narrative THEN
        RAISE EXCEPTION 'Audit Violation: Modifications to existing clinical incident narratives are strictly forbidden.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_lock_incidents
BEFORE UPDATE ON public.incident_reports
FOR EACH ROW EXECUTE FUNCTION public.lock_immutable_incidents();

-- ============================================================================
-- 3. GOVERNANCE AND SECURITY SECURITY (ROW-LEVEL SECURITY MESH)
-- ============================================================================
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.nurses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.facilities ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.incident_reports ENABLE ROW LEVEL SECURITY;

-- Rule A: System Admins hold unrestricted read/write authority across the database
CREATE POLICY admin_omni_access ON public.user_profiles 
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM public.user_profiles 
            WHERE user_profiles.id = auth.uid() AND associated_role = 'tecs_admin'
        )
    );

-- Rule B: Multi-Tenant Tenant Isolation: Facility partners see only their specific shifts/records
CREATE POLICY client_secure_shift_access ON public.shifts
    FOR ALL USING (
        facility_id = (
            SELECT facility_id FROM public.user_profiles WHERE user_profiles.id = auth.uid()
        )
    );

CREATE POLICY client_secure_incident_access ON public.incident_reports
    FOR ALL USING (
        facility_id = (
            SELECT facility_id FROM public.user_profiles WHERE user_profiles.id = auth.uid()
        )
    );

-- Rule C: Field Clinicians isolated completely to viewing their personal nurse data vectors
CREATE POLICY nurse_self_access ON public.nurses
    FOR SELECT USING (
        id = (
            SELECT nurse_id FROM public.user_profiles WHERE user_profiles.id = auth.uid()
        )
    );