-- Patients table (PHI)
CREATE TABLE patients (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by  UUID REFERENCES auth.users(id),
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    date_of_birth DATE,
    mrn         TEXT UNIQUE,  -- Medical Record Number
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Care team memberships — controls who can see which patients
CREATE TABLE care_team (
    patient_id   UUID REFERENCES patients(id) ON DELETE CASCADE,
    provider_id  UUID REFERENCES auth.users(id),
    role         TEXT NOT NULL CHECK (role IN ('attending', 'nurse', 'specialist', 'admin')),
    PRIMARY KEY (patient_id, provider_id)
);

-- Enable Row Level Security
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE care_team ENABLE ROW LEVEL SECURITY;

-- Providers can only see patients where they are on the care team
CREATE POLICY "providers_see_their_patients"
    ON patients
    FOR SELECT
    USING (
        id IN (
            SELECT patient_id
            FROM care_team
            WHERE provider_id = auth.uid()
        )
    );

-- Providers can only insert patients they are assigned to
CREATE POLICY "providers_insert_patients"
    ON patients
    FOR INSERT
    WITH CHECK (created_by = auth.uid());

-- Care team: providers see their own entries
CREATE POLICY "care_team_select"
    ON care_team
    FOR SELECT
    USING (provider_id = auth.uid());

-- Admins can see all patients (using custom claim)
CREATE POLICY "admins_see_all"
    ON patients
    FOR ALL
    USING (
        (auth.jwt() ->> 'role') = 'admin'
    );

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Sign up a new provider
const { data, error } = await supabase.auth.signUp({
  email:    'dr.smith@clinic.com',
  password: 'SecurePassword123!',
  options: {
    data: {
      full_name: 'Dr. Smith',
      role:      'attending'
    }
  }
})

// Sign in
const { data: session } = await supabase.auth.signInWithPassword({
  email:    'dr.smith@clinic.com',
  password: 'SecurePassword123!'
})

// Get current user
const { data: { user } } = await supabase.auth.getUser()

// Fetch patients — RLS automatically filters to care team only
const { data: patients, error } = await supabase
  .from('patients')
  .select('id, first_name, last_name, mrn, date_of_birth')
  .order('last_name')

// Create a patient (must also add to care team)
const { data: patient } = await supabase
  .from('patients')
  .insert({
    first_name:    'Erik',
    last_name:     'Hansen',
    date_of_birth: '1985-03-15',
    mrn:           'MRN-2026-001'
  })
  .select()
  .single()

// Assign to care team
await supabase
  .from('care_team')
  .insert({
    patient_id:  patient.id,
    provider_id: user.id,
    role:        'attending'
  })

// Supabase dashboard: create a private bucket called "medical-files"
// Enable RLS on the bucket

// Storage policy: providers can only access files for their patients

-- Storage policy (in SQL editor)
CREATE POLICY "providers_access_patient_files"
    ON storage.objects
    FOR ALL
    USING (
        bucket_id = 'medical-files'
        AND (storage.foldername(name))[1] IN (
            SELECT patient_id::TEXT
            FROM care_team
            WHERE provider_id = auth.uid()
        )
    );

// Upload a medical document
const filePath = `${patient.id}/lab-results-2026-04-17.pdf`

const { error } = await supabase.storage
  .from('medical-files')
  .upload(filePath, fileBlob, {
    contentType: 'application/pdf',
    upsert:      false
  })

// Get a signed URL (expires in 1 hour)
const { data: signedUrl } = await supabase.storage
  .from('medical-files')
  .createSignedUrl(filePath, 3600)

// Listen for new messages in a patient's care channel
const channel = supabase
  .channel(`patient-${patientId}`)
  .on(
    'postgres_changes',
    {
      event:  'INSERT',
      schema: 'public',
      table:  'care_messages',
      filter: `patient_id=eq.${patientId}`
    },
    (payload) => {
      const message = payload.new
      setCareMessages(prev => [...prev, message])
    }
  )
  .subscribe()

// Send a care message
await supabase.from('care_messages').insert({
  patient_id: patientId,
  sender_id:  user.id,
  message:    'Patient vitals reviewed. Continue current medication.',
  priority:   'normal'
})

// Cleanup
return () => supabase.removeChannel(channel)

-- Audit log table
CREATE TABLE audit_log (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID REFERENCES auth.users(id),
    action      TEXT NOT NULL,
    table_name  TEXT NOT NULL,
    record_id   UUID,
    old_data    JSONB,
    new_data    JSONB,
    ip_address  TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger to log all PHI access
CREATE OR REPLACE FUNCTION log_phi_access()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (user_id, action, table_name, record_id, old_data, new_data)
    VALUES (
        auth.uid(),
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD)::jsonb ELSE NULL END,
        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::jsonb ELSE NULL END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER audit_patients
    AFTER INSERT OR UPDATE OR DELETE ON patients
    FOR EACH ROW EXECUTE FUNCTION log_phi_access();