Scheduling & Bed/OR Management Data Specifications

CREATE TABLE encounters (
    encounter_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    encounter_type         VARCHAR(30) NOT NULL,
    encounter_class        VARCHAR(10) NOT NULL,
    encounter_status       VARCHAR(20) NOT NULL DEFAULT 'planned',
    admission_datetime     TIMESTAMP NULL,
    discharge_datetime     TIMESTAMP NULL,
    facility_id            BIGINT NOT NULL,
    department_id          BIGINT NULL,
    location_id            BIGINT NULL,
    attending_provider_id  BIGINT NULL,
    admitting_provider_id  BIGINT NULL,
    referring_provider_id  BIGINT NULL,
    admission_source       VARCHAR(20) NULL,
    discharge_disposition  VARCHAR(20) NULL,
    payer_id               BIGINT NULL,
    insurance_plan_id      BIGINT NULL,
    expected_los           NUMERIC(5,2) NULL,
    actual_los             NUMERIC(5,2) NULL,
    chief_complaint        VARCHAR(500) NULL,
    chief_complaint_snomed VARCHAR(20) NULL,
    uae_emirate_code       VARCHAR(2) NULL,
    created_by             BIGINT NOT NULL,
    updated_by             BIGINT NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_encounter_class
        CHECK (encounter_class IN ('AMB','EMER','IMP','OBS','VR','HH')),
    CONSTRAINT chk_encounter_status
        CHECK (encounter_status IN ('planned','in-progress','onleave','finished','cancelled')),
    CONSTRAINT chk_encounter_dates
        CHECK (discharge_datetime IS NULL OR admission_datetime IS NULL
               OR discharge_datetime >= admission_datetime),
    CONSTRAINT chk_los_non_negative
        CHECK ((expected_los IS NULL OR expected_los >= 0)
           AND (actual_los IS NULL OR actual_los >= 0)),
    CONSTRAINT chk_uae_emirate_code
        CHECK (uae_emirate_code IS NULL OR uae_emirate_code IN ('AD','DU','SH','AJ','RA','FU','UA'))
);

-- FKs
ALTER TABLE encounters
  ADD CONSTRAINT fk_enc_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_location
    FOREIGN KEY (location_id) REFERENCES locations(location_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_attending
    FOREIGN KEY (attending_provider_id) REFERENCES providers(provider_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_admitting
    FOREIGN KEY (admitting_provider_id) REFERENCES providers(provider_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_referring
    FOREIGN KEY (referring_provider_id) REFERENCES providers(provider_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_payer
    FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_plan
    FOREIGN KEY (insurance_plan_id) REFERENCES insurance_plans(insurance_plan_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_enc_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id),
  ADD CONSTRAINT fk_enc_updated_by
    FOREIGN KEY (updated_by) REFERENCES users(user_id);

-- Indexes
CREATE INDEX idx_encounters_patient ON encounters(patient_id);
CREATE INDEX idx_encounters_facility_status ON encounters(facility_id, encounter_status);
CREATE INDEX idx_encounters_admission_dt ON encounters(admission_datetime);
CREATE INDEX idx_encounters_discharge_dt ON encounters(discharge_datetime);
CREATE INDEX idx_encounters_payer ON encounters(payer_id);
CREATE INDEX idx_encounters_attending ON encounters(attending_provider_id);
CREATE INDEX idx_encounters_class_status ON encounters(encounter_class, encounter_status);
CREATE INDEX idx_encounters_active_ip
    ON encounters(facility_id, department_id, encounter_status)
    WHERE encounter_class IN ('IMP','EMER') AND encounter_status IN ('in-progress','onleave');

CREATE TABLE encounter_details (
    detail_id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    encounter_id               BIGINT NOT NULL,
    admission_diagnosis_icd10  VARCHAR(10) NULL,
    principal_diagnosis_icd10  VARCHAR(10) NULL,
    drg_code                   VARCHAR(10) NULL,
    case_weight                NUMERIC(6,3) NULL,
    financial_class            VARCHAR(20) NULL,
    pre_auth_number            VARCHAR(50) NULL,
    bed_id                     BIGINT NULL,
    room_type_requested        VARCHAR(20) NULL,
    isolation_required         BOOLEAN NOT NULL DEFAULT FALSE,
    isolation_reason_snomed    VARCHAR(20) NULL,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_encdet_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON DELETE CASCADE,
    CONSTRAINT fk_encdet_bed
        FOREIGN KEY (bed_id) REFERENCES beds(bed_id) ON UPDATE CASCADE,
    CONSTRAINT chk_encdet_case_weight
        CHECK (case_weight IS NULL OR case_weight >= 0),
    CONSTRAINT chk_encdet_room_type
        CHECK (room_type_requested IS NULL OR room_type_requested IN ('PRIVATE','SEMI','WARD','VIP'))
);

CREATE UNIQUE INDEX ux_encounter_details_encounter
    ON encounter_details(encounter_id);

CREATE INDEX idx_encdet_principal_dx ON encounter_details(principal_diagnosis_icd10);
CREATE INDEX idx_encdet_drg ON encounter_details(drg_code);

CREATE TABLE appointments (
    appointment_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id               BIGINT NOT NULL,
    encounter_id             BIGINT NULL,
    provider_id              BIGINT NOT NULL,
    department_id            BIGINT NOT NULL,
    facility_id              BIGINT NOT NULL,
    appointment_type_id      BIGINT NOT NULL,
    slot_id                  BIGINT NULL,
    appointment_datetime     TIMESTAMP NOT NULL,
    duration_minutes         INT NOT NULL DEFAULT 15,
    status                   VARCHAR(20) NOT NULL DEFAULT 'booked',
    check_in_time            TIMESTAMP NULL,
    reason_for_visit         VARCHAR(500) NULL,
    reason_for_visit_snomed  VARCHAR(20) NULL,
    cancellation_reason      VARCHAR(50) NULL,
    rescheduled_from         BIGINT NULL,
    waitlist_entry_id        BIGINT NULL,
    created_by               BIGINT NOT NULL,
    updated_by               BIGINT NULL,
    created_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_appointment_status
        CHECK (status IN ('booked','checked_in','completed','cancelled','no_show')),
    CONSTRAINT chk_appointment_duration
        CHECK (duration_minutes > 0)
);

ALTER TABLE appointments
  ADD CONSTRAINT fk_appt_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_appt_encounter
    FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_appt_provider
    FOREIGN KEY (provider_id) REFERENCES providers(provider_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_appt_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_appt_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE,
  ADD CONSTRAINT fk_appt_type
    FOREIGN KEY (appointment_type_id) REFERENCES appointment_types(type_id),
  ADD CONSTRAINT fk_appt_slot
    FOREIGN KEY (slot_id) REFERENCES appointment_slots(slot_id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_appt_rescheduled_from
    FOREIGN KEY (rescheduled_from) REFERENCES appointments(appointment_id),
  ADD CONSTRAINT fk_appt_waitlist
    FOREIGN KEY (waitlist_entry_id) REFERENCES waitlist_entries(entry_id),
  ADD CONSTRAINT fk_appt_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id),
  ADD CONSTRAINT fk_appt_updated_by
    FOREIGN KEY (updated_by) REFERENCES users(user_id);

CREATE INDEX idx_appt_patient ON appointments(patient_id, appointment_datetime);
CREATE INDEX idx_appt_provider_date ON appointments(provider_id, appointment_datetime);
CREATE INDEX idx_appt_facility_dept_date ON appointments(facility_id, department_id, appointment_datetime);
CREATE INDEX idx_appt_status ON appointments(status);
CREATE INDEX idx_appt_waitlist ON appointments(waitlist_entry_id);
CREATE INDEX idx_appt_encounter ON appointments(encounter_id);

CREATE TABLE appointment_slots (
    slot_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    schedule_template_id BIGINT NULL,
    provider_id         BIGINT NOT NULL,
    department_id       BIGINT NOT NULL,
    facility_id         BIGINT NOT NULL,
    slot_date           DATE NOT NULL,
    start_time          TIME NOT NULL,
    end_time            TIME NOT NULL,
    slot_type           VARCHAR(20) NOT NULL DEFAULT 'standard',
    max_overbooking     INT NOT NULL DEFAULT 0,
    current_bookings    INT NOT NULL DEFAULT 0,
    is_blocked          BOOLEAN NOT NULL DEFAULT FALSE,
    block_reason        VARCHAR(200) NULL,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_slot_time
        CHECK (end_time > start_time),
    CONSTRAINT chk_slot_overbooking
        CHECK (max_overbooking >= 0 AND current_bookings >= 0)
);

ALTER TABLE appointment_slots
  ADD CONSTRAINT fk_slot_template
    FOREIGN KEY (schedule_template_id) REFERENCES scheduling_templates(template_id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_slot_provider
    FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_slot_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
  ADD CONSTRAINT fk_slot_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id);

CREATE INDEX idx_slots_provider_date
    ON appointment_slots(provider_id, slot_date, start_time);
CREATE INDEX idx_slots_facility_dept_date
    ON appointment_slots(facility_id, department_id, slot_date);
CREATE INDEX idx_slots_availability
    ON appointment_slots(provider_id, slot_date)
    WHERE is_blocked = FALSE;

CREATE TABLE appointment_types (
    type_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    type_name_en       VARCHAR(100) NOT NULL,
    type_name_ar       VARCHAR(100) NOT NULL,
    default_duration   INT NOT NULL DEFAULT 15,
    prep_time          INT NOT NULL DEFAULT 0,
    post_time          INT NOT NULL DEFAULT 0,
    color_code         VARCHAR(7) NULL,
    requires_referral  BOOLEAN NOT NULL DEFAULT FALSE,
    requires_pre_auth  BOOLEAN NOT NULL DEFAULT FALSE,
    is_active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_appttype_duration
        CHECK (default_duration > 0 AND prep_time >= 0 AND post_time >= 0),
    CONSTRAINT chk_appttype_color
        CHECK (color_code IS NULL OR color_code ~ '^#[0-9A-Fa-f]{6}$')
);

CREATE UNIQUE INDEX ux_appttype_name_en ON appointment_types(type_name_en);

CREATE TABLE beds (
    bed_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location_id        BIGINT NOT NULL,
    facility_id        BIGINT NOT NULL,
    department_id      BIGINT NOT NULL,
    ward_type          VARCHAR(30) NOT NULL,
    bed_number         VARCHAR(20) NOT NULL,
    bed_status         VARCHAR(20) NOT NULL DEFAULT 'available',
    is_isolation       BOOLEAN NOT NULL DEFAULT FALSE,
    gender_restriction VARCHAR(10) NULL,
    is_active          BOOLEAN NOT NULL DEFAULT TRUE,
    cleaning_status    VARCHAR(20) NOT NULL DEFAULT 'ready',
    last_cleaned_at    TIMESTAMP NULL,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_bed_status
        CHECK (bed_status IN ('available','occupied','reserved','out_of_service')),
    CONSTRAINT chk_bed_cleaning_status
        CHECK (cleaning_status IN ('ready','needs_cleaning','in_progress')),
    CONSTRAINT chk_bed_gender
        CHECK (gender_restriction IS NULL OR gender_restriction IN ('M','F','MIXED'))
);

ALTER TABLE beds
  ADD CONSTRAINT fk_bed_location
    FOREIGN KEY (location_id) REFERENCES locations(location_id),
  ADD CONSTRAINT fk_bed_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  ADD CONSTRAINT fk_bed_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

CREATE UNIQUE INDEX ux_bed_facility_number
    ON beds(facility_id, bed_number);

CREATE INDEX idx_beds_status
    ON beds(facility_id, department_id, bed_status, cleaning_status);

CREATE TABLE bed_assignments (
    assignment_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    bed_id             BIGINT NOT NULL,
    patient_id         BIGINT NOT NULL,
    encounter_id       BIGINT NOT NULL,
    assigned_datetime  TIMESTAMP NOT NULL,
    released_datetime  TIMESTAMP NULL,
    assignment_reason  VARCHAR(100) NULL,
    is_current         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_bedassign_dates
        CHECK (released_datetime IS NULL OR released_datetime >= assigned_datetime)
);

ALTER TABLE bed_assignments
  ADD CONSTRAINT fk_bedassign_bed
    FOREIGN KEY (bed_id) REFERENCES beds(bed_id),
  ADD CONSTRAINT fk_bedassign_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_bedassign_encounter
    FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON DELETE CASCADE;

CREATE INDEX idx_bedassign_bed_current
    ON bed_assignments(bed_id)
    WHERE is_current = TRUE;

CREATE INDEX idx_bedassign_encounter
    ON bed_assignments(encounter_id, is_current);

CREATE TABLE bed_transfers (
    transfer_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    encounter_id       BIGINT NOT NULL,
    patient_id         BIGINT NOT NULL,
    from_bed_id        BIGINT NULL,
    to_bed_id          BIGINT NOT NULL,
    transfer_reason    VARCHAR(200) NOT NULL,
    requested_by       BIGINT NOT NULL,
    approved_by        BIGINT NULL,
    transfer_datetime  TIMESTAMP NOT NULL,
    adt_message_id     BIGINT NULL,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE bed_transfers
  ADD CONSTRAINT fk_bedtr_encounter
    FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON DELETE CASCADE,
  ADD CONSTRAINT fk_bedtr_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_bedtr_from_bed
    FOREIGN KEY (from_bed_id) REFERENCES beds(bed_id),
  ADD CONSTRAINT fk_bedtr_to_bed
    FOREIGN KEY (to_bed_id) REFERENCES beds(bed_id),
  ADD CONSTRAINT fk_bedtr_requested_by
    FOREIGN KEY (requested_by) REFERENCES users(user_id),
  ADD CONSTRAINT fk_bedtr_approved_by
    FOREIGN KEY (approved_by) REFERENCES users(user_id);

CREATE INDEX idx_bedtr_encounter ON bed_transfers(encounter_id, transfer_datetime);
CREATE INDEX idx_bedtr_patient ON bed_transfers(patient_id, transfer_datetime);

CREATE TABLE or_rooms (
    room_id                     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    facility_id                 BIGINT NOT NULL,
    room_name                   VARCHAR(50) NOT NULL,
    room_type                   VARCHAR(20) NOT NULL,
    equipment_list              TEXT NULL,
    is_active                   BOOLEAN NOT NULL DEFAULT TRUE,
    max_daily_hours             INT NOT NULL DEFAULT 24,
    cleaning_turnaround_minutes INT NOT NULL DEFAULT 30,
    created_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_orroom_hours
        CHECK (max_daily_hours BETWEEN 0 AND 24),
    CONSTRAINT chk_orroom_turnaround
        CHECK (cleaning_turnaround_minutes >= 0)
);

ALTER TABLE or_rooms
  ADD CONSTRAINT fk_orroom_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id);

CREATE UNIQUE INDEX ux_orroom_facility_name
    ON or_rooms(facility_id, room_name);

CREATE TABLE or_schedules (
    schedule_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    or_room_id          BIGINT NOT NULL,
    schedule_date       DATE NOT NULL,
    block_start         TIME NOT NULL,
    block_end           TIME NOT NULL,
    surgeon_id          BIGINT NULL,
    anesthesiologist_id BIGINT NULL,
    status              VARCHAR(20) NOT NULL DEFAULT 'open',
    block_type          VARCHAR(20) NOT NULL,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_orsched_time
        CHECK (block_end > block_start),
    CONSTRAINT chk_orsched_status
        CHECK (status IN ('open','released','cancelled')),
    CONSTRAINT chk_orsched_block_type
        CHECK (block_type IN ('BLOCKED','OPEN','EMERGENCY'))
);

ALTER TABLE or_schedules
  ADD CONSTRAINT fk_orsched_room
    FOREIGN KEY (or_room_id) REFERENCES or_rooms(room_id),
  ADD CONSTRAINT fk_orsched_surgeon
    FOREIGN KEY (surgeon_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_orsched_anesth
    FOREIGN KEY (anesthesiologist_id) REFERENCES providers(provider_id);

CREATE INDEX idx_orsched_room_date
    ON or_schedules(or_room_id, schedule_date);

CREATE TABLE or_cases (
    case_id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    schedule_id                BIGINT NOT NULL,
    patient_id                 BIGINT NOT NULL,
    encounter_id               BIGINT NOT NULL,
    surgeon_id                 BIGINT NOT NULL,
    anesthesiologist_id        BIGINT NULL,
    procedure_code_cpt         VARCHAR(10) NOT NULL,
    procedure_description      VARCHAR(255) NOT NULL,
    estimated_duration         INT NOT NULL,
    actual_start               TIMESTAMP NULL,
    actual_end                 TIMESTAMP NULL,
    case_status                VARCHAR(20) NOT NULL DEFAULT 'scheduled',
    pre_op_checklist_complete  BOOLEAN NOT NULL DEFAULT FALSE,
    consent_verified           BOOLEAN NOT NULL DEFAULT FALSE,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_orcase_duration
        CHECK (estimated_duration > 0),
    CONSTRAINT chk_orcase_times
        CHECK (actual_end IS NULL OR actual_start IS NULL OR actual_end >= actual_start),
    CONSTRAINT chk_orcase_status
        CHECK (case_status IN ('scheduled','in_progress','completed','cancelled','postponed'))
);

ALTER TABLE or_cases
  ADD CONSTRAINT fk_orcase_schedule
    FOREIGN KEY (schedule_id) REFERENCES or_schedules(schedule_id) ON DELETE CASCADE,
  ADD CONSTRAINT fk_orcase_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_orcase_encounter
    FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  ADD CONSTRAINT fk_orcase_surgeon
    FOREIGN KEY (surgeon_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_orcase_anesth
    FOREIGN KEY (anesthesiologist_id) REFERENCES providers(provider_id);

CREATE INDEX idx_orcase_schedule ON or_cases(schedule_id);
CREATE INDEX idx_orcase_patient ON or_cases(patient_id);
CREATE INDEX idx_orcase_status ON or_cases(case_status);

CREATE TABLE waitlist_entries (
    entry_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id          BIGINT NOT NULL,
    waitlist_type       VARCHAR(20) NOT NULL,
    requested_service   VARCHAR(100) NOT NULL,
    preferred_provider_id BIGINT NULL,
    priority            VARCHAR(20) NOT NULL DEFAULT 'routine',
    added_datetime      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    target_date_from    DATE NULL,
    target_date_to      DATE NULL,
    status              VARCHAR(20) NOT NULL DEFAULT 'active',
    offered_count       INT NOT NULL DEFAULT 0,
    resolved_datetime   TIMESTAMP NULL,
    resolution_type     VARCHAR(20) NULL,
    notes               VARCHAR(500) NULL,
    created_by          BIGINT NOT NULL,

    CONSTRAINT chk_waitlist_type
        CHECK (waitlist_type IN ('APPOINTMENT','BED','OR')),
    CONSTRAINT chk_waitlist_priority
        CHECK (priority IN ('urgent','routine')),
    CONSTRAINT chk_waitlist_status
        CHECK (status IN ('active','offered','resolved','cancelled')),
    CONSTRAINT chk_waitlist_offered
        CHECK (offered_count >= 0)
);

ALTER TABLE waitlist_entries
  ADD CONSTRAINT fk_waitlist_patient
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_waitlist_provider
    FOREIGN KEY (preferred_provider_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_waitlist_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id);

CREATE INDEX idx_waitlist_type_status
    ON waitlist_entries(waitlist_type, status, priority, added_datetime);
CREATE INDEX idx_waitlist_patient
    ON waitlist_entries(patient_id, status);

CREATE TABLE provider_schedules (
    schedule_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id    BIGINT NOT NULL,
    facility_id    BIGINT NOT NULL,
    department_id  BIGINT NOT NULL,
    day_of_week    INT NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL,
    session_type   VARCHAR(20) NOT NULL DEFAULT 'clinic',
    effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
    effective_to   DATE NULL,
    is_recurring   BOOLEAN NOT NULL DEFAULT TRUE,
    max_patients   INT NOT NULL DEFAULT 0,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_provsched_day
        CHECK (day_of_week BETWEEN 1 AND 7),
    CONSTRAINT chk_provsched_time
        CHECK (end_time > start_time),
    CONSTRAINT chk_provsched_dates
        CHECK (effective_to IS NULL OR effective_to >= effective_from),
    CONSTRAINT chk_provsched_capacity
        CHECK (max_patients >= 0)
);

ALTER TABLE provider_schedules
  ADD CONSTRAINT fk_provsched_provider
    FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_provsched_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  ADD CONSTRAINT fk_provsched_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

CREATE INDEX idx_provsched_provider
    ON provider_schedules(provider_id, day_of_week, effective_from);

CREATE TABLE scheduling_rules (
    rule_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rule_type            VARCHAR(50) NOT NULL,
    rule_name            VARCHAR(100) NOT NULL,
    rule_parameters_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    facility_id          BIGINT NULL,
    department_id        BIGINT NULL,
    appointment_type_id  BIGINT NULL,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_by           BIGINT NOT NULL,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE scheduling_rules
  ADD CONSTRAINT fk_schrule_facility
    FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  ADD CONSTRAINT fk_schrule_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
  ADD CONSTRAINT fk_schrule_appttype
    FOREIGN KEY (appointment_type_id) REFERENCES appointment_types(type_id),
  ADD CONSTRAINT fk_schrule_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id);

CREATE INDEX idx_schrule_scope
    ON scheduling_rules(facility_id, department_id, appointment_type_id, is_active);
CREATE INDEX idx_schrule_type
    ON scheduling_rules(rule_type);

CREATE TABLE scheduling_templates (
    template_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    template_name    VARCHAR(100) NOT NULL,
    provider_id      BIGINT NULL,
    department_id    BIGINT NULL,
    effective_period DATERANGE NOT NULL,
    slots_json       JSONB NOT NULL,
    is_active        BOOLEAN NOT NULL DEFAULT TRUE,
    created_by       BIGINT NOT NULL,
    created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE scheduling_templates
  ADD CONSTRAINT fk_schtemplate_provider
    FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_schtemplate_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
  ADD CONSTRAINT fk_schtemplate_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id);

CREATE INDEX idx_schtemplate_active
    ON scheduling_templates(is_active);