-- ============================================================================
-- MASTER EXTENSION: ROLE-BASED ACCESS CONTROL (RBAC) & MONETIZATION STORAGE
-- ============================================================================

-- Create User Roles Tiers
CREATE TYPE public.user_role AS ENUM ('tecs_admin', 'facility_partner', 'field_nurse');

-- Main Application Users Profile 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()
);

-- Extend the Facility Table to capture Financial/Monetization Rules
ALTER TABLE public.facilities 
ADD COLUMN monthly_saas_fee NUMERIC(8,2) DEFAULT 0.00,
ADD COLUMN vms_admin_percentage NUMERIC(4,2) DEFAULT 3.00; -- Default 3% markup

-- Extend the Shifts Table to automate Profit Ledger calculations
ALTER TABLE public.shifts 
ADD COLUMN bill_rate NUMERIC(6,2) NOT NULL DEFAULT 0.00,
ADD COLUMN pay_rate NUMERIC(6,2) NOT NULL DEFAULT 0.00,
ADD COLUMN calculated_gross_profit NUMERIC(6,2) GENERATED ALWAYS AS (bill_rate - pay_rate) STORED;

-- ============================================================================
-- ADVANCED SECURITY POLICY MESH (ROW-LEVEL SECURITY)
-- ============================================================================
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;

-- 1. Global Rule for System Administrators (TECS Core Team)
-- Permits full visibility over all clinical and corporate tables if role matches admin status
CREATE POLICY admin_omni_access ON public.user_profiles 
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM public.user_profiles 
            WHERE id = auth.uid() AND associated_role = 'tecs_admin'
        )
    );

-- 2. Client Isolation Policy Framework
-- Restricts hospital users from seeing any external records outside their facility identity
CREATE POLICY client_secure_shift_access ON public.shifts
    FOR ALL USING (
        facility_id = (
            SELECT facility_id FROM public.user_profiles WHERE id = auth.uid()
        )
    );

-- 3. Field Clinician Profile Isolation
-- Guarantees a nurse can only read, update, or check into their individual shift structures
CREATE POLICY nurse_self_access ON public.nurses
    FOR SELECT USING (
        id = (
            SELECT nurse_id FROM public.user_profiles WHERE id = auth.uid()
        )
    );
