DROP TABLE IF EXISTS leave_requests;
CREATE TABLE leave_requests (
  leave_request_id                  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  user_id                          uuid DEFAULT NULL REFERENCES users(user_id) ON DELETE SET NULL,
  org_id                           uuid DEFAULT NULL REFERENCES orgs(org_id) ON DELETE SET NULL,
  leavetype                        varchar(50) DEFAULT NULL,
  startdate                        date DEFAULT NULL,
  enddate                          date DEFAULT NULL,
  reason                           text DEFAULT NULL,
  status                           varchar(20) NOT NULL DEFAULT 'Pending',
  approvedAt                       timestamptz DEFAULT NULL,
  approvedBy                       uuid DEFAULT NULL,
  bereavement_relationship         varchar(100) DEFAULT NULL,
  care_recipient_name              varchar(255) DEFAULT NULL,
  deceased_name                    varchar(255) DEFAULT NULL,
  expected_due_date                date DEFAULT NULL,
  familycare_relationship          varchar(100) DEFAULT NULL,
  maternity_medical_certificate    uuid DEFAULT NULL REFERENCES documents(document_id),
  medical_certificate              uuid DEFAULT NULL REFERENCES documents(document_id),
  number_of_births                 integer DEFAULT NULL,
  paternity_expected_birth_date    date DEFAULT NULL,
  paternity_medical_certificate    uuid DEFAULT NULL REFERENCES documents(document_id),
  paternity_number_of_births       integer DEFAULT NULL,
  isDeleted                        BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                        timestamptz NOT NULL DEFAULT now()
);