CREATE TABLE medical_records (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
  family_history JSONB DEFAULT '[]',
  personal_history JSONB DEFAULT '[]',
  surgical_history JSONB DEFAULT '[]',
  current_medications JSONB DEFAULT '[]',
  immunizations JSONB DEFAULT '[]',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(patient_id)
);

CREATE TABLE consultation_notes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
  doctor_id UUID NOT NULL REFERENCES users(id),
  appointment_id UUID REFERENCES appointments(id),
  subjective TEXT,
  objective TEXT,
  assessment TEXT,
  plan TEXT,
  vital_signs JSONB,
  diagnoses JSONB DEFAULT '[]',
  is_signed BOOLEAN NOT NULL DEFAULT FALSE,
  signed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_consultations_patient ON consultation_notes(patient_id, created_at DESC);
CREATE INDEX idx_consultations_doctor ON consultation_notes(doctor_id, created_at DESC);

CREATE TABLE prescriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  consultation_id UUID NOT NULL REFERENCES consultation_notes(id) ON DELETE CASCADE,
  patient_id UUID NOT NULL REFERENCES patients(id),
  doctor_id UUID NOT NULL REFERENCES users(id),
  items JSONB NOT NULL DEFAULT '[]',
  pharmacy_notes TEXT,
  pdf_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE medical_files (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
  consultation_id UUID REFERENCES consultation_notes(id),
  file_name TEXT NOT NULL,
  file_type TEXT NOT NULL DEFAULT 'other' CHECK (file_type IN ('lab_result','imaging','referral','other')),
  storage_path TEXT NOT NULL,
  uploaded_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE medical_records ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic records" ON medical_records FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic records" ON medical_records FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());
CREATE POLICY "Update clinic records" ON medical_records FOR UPDATE USING (clinic_id = auth.clinic_id());

ALTER TABLE consultation_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic notes" ON consultation_notes FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic notes" ON consultation_notes FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());
CREATE POLICY "Update own notes" ON consultation_notes FOR UPDATE USING (clinic_id = auth.clinic_id() AND doctor_id = auth.uid());

ALTER TABLE prescriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic prescriptions" ON prescriptions FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic prescriptions" ON prescriptions FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());

ALTER TABLE medical_files ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic files" ON medical_files FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic files" ON medical_files FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());