EHR & Patient Management Data Specifications

CREATE UNIQUE INDEX idx_patients_mrn ON patients(mrn);
CREATE INDEX idx_patients_name_en ON patients(last_name_en, first_name_en);
CREATE INDEX idx_patients_name_ar ON patients(last_name_ar, first_name_ar);
CREATE INDEX idx_patients_dob ON patients(date_of_birth);
CREATE INDEX idx_patients_active ON patients(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_patients_merged ON patients(merged_into_patient_id) WHERE merged_into_patient_id IS NOT NULL;

CREATE INDEX idx_patient_demo_patient ON patient_demographics(patient_id);
CREATE UNIQUE INDEX idx_patient_demo_current ON patient_demographics(patient_id) WHERE is_current = TRUE;
CREATE INDEX idx_patient_demo_emirate ON patient_demographics(emirate_code);
CREATE INDEX idx_patient_demo_mobile ON patient_demographics(mobile_phone);
CREATE INDEX idx_patient_demo_email ON patient_demographics(email);

CREATE INDEX idx_patient_idents_patient ON patient_identifiers(patient_id);
CREATE INDEX idx_patient_idents_type_value ON patient_identifiers(identifier_type, identifier_value);
CREATE UNIQUE INDEX idx_patient_idents_eid ON patient_identifiers(identifier_value) WHERE identifier_type = 'EMIRATES_ID' AND is_primary = TRUE;
CREATE INDEX idx_patient_idents_expiry ON patient_identifiers(expiry_date) WHERE expiry_date IS NOT NULL;

ALTER TABLE patient_identifiers
  ADD CONSTRAINT fk_ident_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT chk_eid_format CHECK (
    identifier_type != 'EMIRATES_ID' OR identifier_value ~ '^784-[0-9]{4}-[0-9]{7}-[0-9]$'
  );

CREATE INDEX idx_patient_contacts_patient ON patient_contacts(patient_id);
CREATE INDEX idx_patient_contacts_emergency ON patient_contacts(patient_id, is_emergency_contact) WHERE is_emergency_contact = TRUE;

CREATE INDEX idx_allergies_patient ON patient_allergies(patient_id);
CREATE INDEX idx_allergies_status ON patient_allergies(patient_id, status) WHERE status = 'active';
CREATE INDEX idx_allergies_allergen ON patient_allergies(allergen_code, allergen_system);
CREATE INDEX idx_allergies_encounter ON patient_allergies(encounter_id);

ALTER TABLE patient_allergies
  ADD CONSTRAINT fk_allergy_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_allergy_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  ADD CONSTRAINT fk_allergy_entered_by FOREIGN KEY (entered_by) REFERENCES users(user_id),
  ADD CONSTRAINT chk_nka_no_allergen CHECK (
    status NOT IN ('nka','nkda') OR (allergen_code IS NULL AND allergen_description = status)
  );

CREATE INDEX idx_problems_patient ON patient_problems(patient_id);
CREATE INDEX idx_problems_active ON patient_problems(patient_id, status) WHERE status = 'active';
CREATE INDEX idx_problems_icd10 ON patient_problems(icd10_code);
CREATE INDEX idx_problems_snomed ON patient_problems(snomed_code);
CREATE INDEX idx_problems_encounter ON patient_problems(encounter_id);
CREATE INDEX idx_problems_chronic ON patient_problems(patient_id, is_chronic) WHERE is_chronic = TRUE;

CREATE INDEX idx_consents_patient ON patient_consents(patient_id);
CREATE INDEX idx_consents_type ON patient_consents(patient_id, consent_type_code);
CREATE INDEX idx_consents_status ON patient_consents(status);
CREATE INDEX idx_consents_expiry ON patient_consents(expiry_datetime) WHERE expiry_datetime IS NOT NULL AND status = 'granted';

ALTER TABLE patient_consents
  ADD CONSTRAINT fk_consent_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT fk_consent_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  ADD CONSTRAINT chk_granted_datetime CHECK (status != 'granted' OR granted_datetime IS NOT NULL),
  ADD CONSTRAINT chk_withdrawn_datetime CHECK (status != 'withdrawn' OR withdrawn_datetime IS NOT NULL);

CREATE INDEX idx_documents_patient ON patient_documents(patient_id);
CREATE INDEX idx_documents_encounter ON patient_documents(encounter_id);
CREATE INDEX idx_documents_type ON patient_documents(document_type_code);
CREATE INDEX idx_documents_status ON patient_documents(status);
CREATE INDEX idx_documents_retention ON patient_documents(retention_expiry) WHERE retention_expiry IS NOT NULL;

CREATE INDEX idx_notes_patient ON clinical_notes(patient_id);
CREATE INDEX idx_notes_encounter ON clinical_notes(encounter_id);
CREATE INDEX idx_notes_author ON clinical_notes(author_provider_id);
CREATE INDEX idx_notes_status ON clinical_notes(status);
CREATE INDEX idx_notes_type ON clinical_notes(note_type);
CREATE INDEX idx_notes_pending ON clinical_notes(status, authored_datetime) WHERE status IN ('draft', 'pending_cosign');
CREATE INDEX idx_notes_parent ON clinical_notes(parent_note_id) WHERE parent_note_id IS NOT NULL;

CREATE INDEX idx_dupes_patient1 ON duplicate_suspects(patient_id_1);
CREATE INDEX idx_dupes_patient2 ON duplicate_suspects(patient_id_2);
CREATE INDEX idx_dupes_status ON duplicate_suspects(status);
CREATE UNIQUE INDEX idx_dupes_pair ON duplicate_suspects(
  LEAST(patient_id_1, patient_id_2), GREATEST(patient_id_1, patient_id_2)
) WHERE status NOT IN ('resolved','rejected');
CREATE INDEX idx_dupes_score ON duplicate_suspects(match_score DESC);

CREATE UNIQUE INDEX idx_providers_staff ON providers(staff_number);
CREATE INDEX idx_providers_name ON providers(last_name_en, first_name_en);
CREATE INDEX idx_providers_specialty ON providers(specialty_code);
CREATE INDEX idx_providers_license ON providers(license_number, licensing_authority);
CREATE INDEX idx_providers_facility ON providers(facility_id);
CREATE INDEX idx_providers_department ON providers(department_id);
CREATE INDEX idx_providers_active ON providers(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_providers_license_expiry ON providers(license_expiry_date);

CREATE INDEX idx_credentials_provider ON provider_credentials(provider_id);
CREATE INDEX idx_credentials_type ON provider_credentials(credential_type);
CREATE INDEX idx_credentials_expiry ON provider_credentials(expiry_date) WHERE status = 'active';

CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_provider ON users(provider_id) WHERE provider_id IS NOT NULL;
CREATE INDEX idx_users_patient ON users(patient_id) WHERE patient_id IS NOT NULL;
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_users_type ON users(user_type);

ALTER TABLE users
  ADD CONSTRAINT fk_user_provider FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  ADD CONSTRAINT fk_user_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  ADD CONSTRAINT chk_user_link CHECK (
    (user_type = 'portal_patient' AND patient_id IS NOT NULL) OR
    (user_type IN ('clinical','portal_provider') AND provider_id IS NOT NULL) OR
    (user_type IN ('administrative','system','integration'))
  );

CREATE UNIQUE INDEX idx_roles_code ON roles(role_code);
CREATE INDEX idx_roles_category ON roles(role_category);
CREATE INDEX idx_roles_active ON roles(is_active) WHERE is_active = TRUE;

CREATE UNIQUE INDEX idx_permissions_code ON permissions(permission_code);
CREATE INDEX idx_permissions_module ON permissions(module);
CREATE UNIQUE INDEX idx_permissions_unique ON permissions(module, resource, action);

CREATE INDEX idx_role_perms_role ON role_permissions(role_id);
CREATE INDEX idx_role_perms_perm ON role_permissions(permission_id);

CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);
CREATE INDEX idx_user_roles_facility ON user_roles(facility_id);
CREATE INDEX idx_user_roles_active ON user_roles(user_id, is_active) WHERE is_active = TRUE;

CREATE UNIQUE INDEX idx_facilities_code ON facilities(facility_code);
CREATE INDEX idx_facilities_emirate ON facilities(emirate_code);
CREATE INDEX idx_facilities_type ON facilities(facility_type);
CREATE INDEX idx_facilities_authority ON facilities(licensing_authority);
CREATE INDEX idx_facilities_active ON facilities(is_active) WHERE is_active = TRUE;

CREATE UNIQUE INDEX idx_depts_code ON departments(facility_id, department_code);
CREATE INDEX idx_depts_facility ON departments(facility_id);
CREATE INDEX idx_depts_parent ON departments(parent_department_id);
CREATE INDEX idx_depts_type ON departments(department_type);

CREATE UNIQUE INDEX idx_locations_code ON locations(facility_id, location_code);
CREATE INDEX idx_locations_dept ON locations(department_id);
CREATE INDEX idx_locations_facility ON locations(facility_id);
CREATE INDEX idx_locations_type ON locations(location_type);
CREATE INDEX idx_locations_available ON locations(location_type, is_active, is_occupied) WHERE location_type = 'bed' AND is_active = TRUE AND is_occupied = FALSE;

CREATE INDEX idx_audit_user ON audit_log(user_id);
CREATE INDEX idx_audit_patient ON audit_log(patient_id);
CREATE INDEX idx_audit_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_timestamp ON audit_log(event_timestamp);
CREATE INDEX idx_audit_action ON audit_log(action);
CREATE INDEX idx_audit_btg ON audit_log(is_btg, event_timestamp) WHERE is_btg = TRUE;
CREATE INDEX idx_audit_module ON audit_log(module, event_timestamp);
CREATE INDEX idx_audit_outcome ON audit_log(outcome) WHERE outcome != 'success';

-- Partition by month for query performance and archival
CREATE TABLE audit_log (
  ...
) PARTITION BY RANGE (EXTRACT(YEAR_MONTH FROM event_timestamp));

-- Example partitions
ALTER TABLE audit_log ADD PARTITION (
  PARTITION p202601 VALUES LESS THAN (202602),
  PARTITION p202602 VALUES LESS THAN (202603)
);

CREATE INDEX idx_intmsg_patient ON integration_message_log(patient_id);
CREATE INDEX idx_intmsg_status ON integration_message_log(status);
CREATE INDEX idx_intmsg_type ON integration_message_log(message_type);
CREATE INDEX idx_intmsg_target ON integration_message_log(target_system);
CREATE INDEX idx_intmsg_retry ON integration_message_log(next_retry_at) WHERE status IN ('queued','error') AND retry_count < max_retries;
CREATE INDEX idx_intmsg_deadletter ON integration_message_log(status, created_at) WHERE status IN ('failed','rejected');
CREATE INDEX idx_intmsg_control ON integration_message_log(message_control_id);

-- ============================================================
-- EHR & Patient Management Module — Core Tables DDL
-- Target: PostgreSQL 15+ (syntax adaptable to MySQL 8+)
-- ============================================================

-- -------------------------------------------------------
-- 1. patients (Shared Entity — canonical definition)
-- -------------------------------------------------------
CREATE TABLE patients (
  patient_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  mrn VARCHAR(20) NOT NULL UNIQUE,
  first_name_en VARCHAR(100) NOT NULL,
  middle_name_en VARCHAR(100),
  last_name_en VARCHAR(100) NOT NULL,
  first_name_ar VARCHAR(100),
  middle_name_ar VARCHAR(100),
  last_name_ar VARCHAR(100),
  date_of_birth DATE NOT NULL,
  gender VARCHAR(10) NOT NULL,
  nationality_code VARCHAR(3),
  is_vip BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_deceased BOOLEAN NOT NULL DEFAULT FALSE,
  deceased_datetime TIMESTAMP,
  merged_into_patient_id BIGINT,
  blood_group VARCHAR(5),
  preferred_language VARCHAR(5) DEFAULT 'en',
  photo_url VARCHAR(500),
  created_by BIGINT NOT NULL,
  updated_by BIGINT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_patient_merged FOREIGN KEY (merged_into_patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_gender CHECK (gender IN ('male', 'female', 'unknown')),
  CONSTRAINT chk_dob CHECK (date_of_birth <= CURRENT_DATE),
  CONSTRAINT chk_deceased CHECK (is_deceased = FALSE OR deceased_datetime IS NOT NULL),
  CONSTRAINT chk_blood_group CHECK (blood_group IS NULL OR blood_group IN ('A+','A-','B+','B-','AB+','AB-','O+','O-'))
);

-- -------------------------------------------------------
-- 2. patient_demographics (versioned — SCD Type 2)
-- -------------------------------------------------------
CREATE TABLE patient_demographics (
  demographic_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  is_current BOOLEAN NOT NULL DEFAULT TRUE,
  effective_from TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_to TIMESTAMP,
  address_line1_en VARCHAR(200),
  address_line2_en VARCHAR(200),
  address_line1_ar VARCHAR(200),
  po_box VARCHAR(20),
  emirate_code VARCHAR(3),
  city VARCHAR(100),
  country_code VARCHAR(3) DEFAULT 'ARE',
  mobile_phone VARCHAR(20),
  home_phone VARCHAR(20),
  work_phone VARCHAR(20),
  email VARCHAR(200),
  marital_status VARCHAR(20),
  occupation VARCHAR(100),
  employer_name VARCHAR(200),
  religion VARCHAR(50),
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_demo_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_marital CHECK (marital_status IS NULL OR marital_status IN ('single','married','divorced','widowed','separated','unknown'))
);

CREATE UNIQUE INDEX idx_patient_demo_current ON patient_demographics(patient_id) WHERE is_current = TRUE;

-- -------------------------------------------------------
-- 3. patient_identifiers
-- -------------------------------------------------------
CREATE TABLE patient_identifiers (
  identifier_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  identifier_type VARCHAR(30) NOT NULL,
  identifier_value VARCHAR(100) NOT NULL,
  identifier_system VARCHAR(200),
  is_primary BOOLEAN NOT NULL DEFAULT TRUE,
  verification_status VARCHAR(20) NOT NULL DEFAULT 'unverified',
  issue_date DATE,
  expiry_date DATE,
  issuing_authority VARCHAR(100),
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_ident_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_ident_type CHECK (identifier_type IN ('MRN','EMIRATES_ID','PASSPORT','VISA','INSURANCE_MEMBER_ID','OLD_MRN','NATIONAL_ID','OTHER')),
  CONSTRAINT chk_ident_status CHECK (verification_status IN ('verified','unverified','expired','invalid')),
  CONSTRAINT chk_eid_format CHECK (identifier_type != 'EMIRATES_ID' OR identifier_value ~ '^784-[0-9]{4}-[0-9]{7}-[0-9]$')
);

-- -------------------------------------------------------
-- 4. facilities (Shared Entity — canonical definition)
-- -------------------------------------------------------
CREATE TABLE facilities (
  facility_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  facility_code VARCHAR(20) NOT NULL UNIQUE,
  facility_name_en VARCHAR(200) NOT NULL,
  facility_name_ar VARCHAR(200),
  facility_type VARCHAR(30) NOT NULL,
  licensing_authority VARCHAR(10) NOT NULL,
  license_number VARCHAR(50) NOT NULL,
  license_expiry_date DATE NOT NULL,
  emirate_code VARCHAR(3) NOT NULL,
  city VARCHAR(100),
  address_en VARCHAR(300),
  address_ar VARCHAR(300),
  phone VARCHAR(20),
  nabidh_facility_id VARCHAR(30),
  malaffi_facility_id VARCHAR(30),
  mrn_prefix VARCHAR(10) UNIQUE,
  timezone VARCHAR(50) NOT NULL DEFAULT 'Asia/Dubai',
  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_facility_type CHECK (facility_type IN ('hospital','clinic','day_surgery','pharmacy','lab','diagnostic_center','rehabilitation','home_health')),
  CONSTRAINT chk_licensing_auth CHECK (licensing_authority IN ('DOH','DHA','MOH')),
  CONSTRAINT chk_emirate CHECK (emirate_code IN ('DXB','AUH','SHJ','AJM','RAK','UAQ','FUJ'))
);

-- -------------------------------------------------------
-- 5. departments
-- -------------------------------------------------------
CREATE TABLE departments (
  department_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  facility_id BIGINT NOT NULL,
  department_code VARCHAR(20) NOT NULL,
  department_name_en VARCHAR(200) NOT NULL,
  department_name_ar VARCHAR(200),
  department_type VARCHAR(30) NOT NULL,
  parent_department_id BIGINT,
  cost_center_code VARCHAR(20),
  phone_extension VARCHAR(10),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_dept_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT fk_dept_parent FOREIGN KEY (parent_department_id) REFERENCES departments(department_id),
  CONSTRAINT chk_dept_type CHECK (department_type IN ('clinical','administrative','support','ancillary')),
  CONSTRAINT uq_dept_code UNIQUE (facility_id, department_code)
);

-- -------------------------------------------------------
-- 6. locations
-- -------------------------------------------------------
CREATE TABLE locations (
  location_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department_id BIGINT NOT NULL,
  facility_id BIGINT NOT NULL,
  location_code VARCHAR(20) NOT NULL,
  location_name VARCHAR(100) NOT NULL,
  location_type VARCHAR(30) NOT NULL,
  floor VARCHAR(10),
  wing VARCHAR(20),
  bed_type VARCHAR(20),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_occupied BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_loc_dept FOREIGN KEY (department_id) REFERENCES departments(department_id),
  CONSTRAINT fk_loc_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT chk_loc_type CHECK (location_type IN ('room','bed','station','bay','or_suite','exam_room','waiting_area','nursing_station','pharmacy','lab','other')),
  CONSTRAINT uq_loc_code UNIQUE (facility_id, location_code)
);

-- -------------------------------------------------------
-- 7. providers (Shared Entity — canonical definition)
-- -------------------------------------------------------
CREATE TABLE providers (
  provider_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  staff_number VARCHAR(20) NOT NULL UNIQUE,
  first_name_en VARCHAR(100) NOT NULL,
  middle_name_en VARCHAR(100),
  last_name_en VARCHAR(100) NOT NULL,
  first_name_ar VARCHAR(100),
  last_name_ar VARCHAR(100),
  provider_type VARCHAR(30) NOT NULL,
  specialty_code VARCHAR(20),
  subspecialty_code VARCHAR(20),
  license_number VARCHAR(50) NOT NULL,
  licensing_authority VARCHAR(10) NOT NULL,
  license_expiry_date DATE NOT NULL,
  national_id VARCHAR(20),
  email VARCHAR(200),
  mobile_phone VARCHAR(20),
  department_id BIGINT,
  facility_id BIGINT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  doh_provider_id VARCHAR(30),
  dha_provider_id VARCHAR(30),
  can_prescribe BOOLEAN NOT NULL DEFAULT FALSE,
  can_prescribe_controlled BOOLEAN NOT NULL DEFAULT FALSE,
  digital_signature_cert VARCHAR(500),
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_prov_dept FOREIGN KEY (department_id) REFERENCES departments(department_id),
  CONSTRAINT fk_prov_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT chk_prov_type CHECK (provider_type IN ('physician','nurse','pharmacist','technician','therapist','admin','other')),
  CONSTRAINT chk_prov_auth CHECK (licensing_authority IN ('DOH','DHA','MOH'))
);

-- -------------------------------------------------------
-- 8. users (Shared Entity — canonical definition)
-- -------------------------------------------------------
CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(200) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  provider_id BIGINT,
  patient_id BIGINT,
  user_type VARCHAR(20) NOT NULL,
  display_name VARCHAR(200) NOT NULL,
  preferred_language VARCHAR(5) DEFAULT 'en',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_locked BOOLEAN NOT NULL DEFAULT FALSE,
  locked_at TIMESTAMP,
  last_login_at TIMESTAMP,
  last_login_ip VARCHAR(45),
  failed_login_count INT NOT NULL DEFAULT 0,
  password_changed_at TIMESTAMP,
  mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  mfa_secret VARCHAR(255),
  created_by BIGINT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_user_provider FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_user_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_user_type CHECK (user_type IN ('clinical','administrative','portal_patient','portal_provider','system','integration'))
);

-- -------------------------------------------------------
-- 9. roles
-- -------------------------------------------------------
CREATE TABLE roles (
  role_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  role_code VARCHAR(50) NOT NULL UNIQUE,
  role_name_en VARCHAR(100) NOT NULL,
  role_name_ar VARCHAR(100),
  description TEXT,
  role_category VARCHAR(30) NOT NULL,
  is_system_role BOOLEAN NOT NULL DEFAULT FALSE,
  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,

  CONSTRAINT chk_role_cat CHECK (role_category IN ('clinical','administrative','portal','system','integration'))
);

-- -------------------------------------------------------
-- 10. permissions
-- -------------------------------------------------------
CREATE TABLE permissions (
  permission_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  permission_code VARCHAR(80) NOT NULL UNIQUE,
  module VARCHAR(30) NOT NULL,
  resource VARCHAR(50) NOT NULL,
  action VARCHAR(20) NOT NULL,
  description TEXT,
  is_sensitive BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT chk_perm_action CHECK (action IN ('create','read','update','delete','sign','approve','export','print')),
  CONSTRAINT uq_perm UNIQUE (module, resource, action)
);

-- -------------------------------------------------------
-- 11. role_permissions (junction)
-- -------------------------------------------------------
CREATE TABLE role_permissions (
  role_id BIGINT NOT NULL,
  permission_id BIGINT NOT NULL,
  granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  granted_by BIGINT NOT NULL,

  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(role_id),
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
);

-- -------------------------------------------------------
-- 12. user_roles (junction, facility-scoped)
-- -------------------------------------------------------
CREATE TABLE user_roles (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  facility_id BIGINT,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
  effective_to DATE,
  assigned_by BIGINT NOT NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(user_id),
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(role_id),
  CONSTRAINT fk_ur_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT chk_ur_dates CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

-- -------------------------------------------------------
-- 13. audit_log (append-only, partitioned)
-- -------------------------------------------------------
CREATE TABLE audit_log (
  audit_id BIGINT GENERATED ALWAYS AS IDENTITY,
  user_id BIGINT NOT NULL,
  session_id VARCHAR(64),
  action VARCHAR(30) NOT NULL,
  resource_type VARCHAR(50) NOT NULL,
  resource_id BIGINT,
  patient_id BIGINT,
  encounter_id BIGINT,
  module VARCHAR(30),
  event_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),
  old_values JSONB,
  new_values JSONB,
  is_btg BOOLEAN NOT NULL DEFAULT FALSE,
  btg_reason_code VARCHAR(30),
  btg_justification TEXT,
  is_phi_access BOOLEAN NOT NULL DEFAULT FALSE,
  outcome VARCHAR(20) NOT NULL DEFAULT 'success',
  error_message TEXT,

  PRIMARY KEY (audit_id, event_timestamp),
  CONSTRAINT chk_outcome CHECK (outcome IN ('success','failure','denied','error')),
  CONSTRAINT chk_btg CHECK (is_btg = FALSE OR btg_reason_code IS NOT NULL)
) PARTITION BY RANGE (event_timestamp);

-- Create initial partitions
CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE audit_log_2026_02 PARTITION OF audit_log
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE audit_log_2026_03 PARTITION OF audit_log
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Prevent UPDATE/DELETE on audit_log
CREATE OR REPLACE FUNCTION prevent_audit_modification()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Audit log records are immutable. UPDATE and DELETE operations are not permitted.';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_no_update
  BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();

-- -------------------------------------------------------
-- 14. patient_allergies
-- -------------------------------------------------------
CREATE TABLE patient_allergies (
  allergy_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT,
  allergen_type VARCHAR(20) NOT NULL,
  allergen_description VARCHAR(300) NOT NULL,
  allergen_code VARCHAR(20),
  allergen_system VARCHAR(50),
  reaction_description VARCHAR(500),
  reaction_code VARCHAR(20),
  severity VARCHAR(20) NOT NULL DEFAULT 'moderate',
  criticality VARCHAR(10),
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  onset_date DATE,
  verification_status VARCHAR(20) NOT NULL DEFAULT 'unconfirmed',
  entered_by BIGINT NOT NULL,
  verified_by BIGINT,
  verified_at TIMESTAMP,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_allergy_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_allergy_entered FOREIGN KEY (entered_by) REFERENCES users(user_id),
  CONSTRAINT chk_allergen_type CHECK (allergen_type IN ('medication','food','environmental','biologic','other')),
  CONSTRAINT chk_allergy_severity CHECK (severity IN ('mild','moderate','severe','life-threatening')),
  CONSTRAINT chk_allergy_status CHECK (status IN ('active','inactive','resolved','entered-in-error','nka','nkda')),
  CONSTRAINT chk_allergy_verif CHECK (verification_status IN ('unconfirmed','confirmed','refuted','entered-in-error'))
);

-- -------------------------------------------------------
-- 15. patient_problems
-- -------------------------------------------------------
CREATE TABLE patient_problems (
  problem_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT,
  problem_description VARCHAR(500) NOT NULL,
  icd10_code VARCHAR(10),
  icd10_description VARCHAR(300),
  snomed_code VARCHAR(20),
  snomed_description VARCHAR(300),
  problem_type VARCHAR(20) NOT NULL DEFAULT 'problem',
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  severity VARCHAR(20),
  onset_date DATE,
  resolved_date DATE,
  is_chronic BOOLEAN NOT NULL DEFAULT FALSE,
  is_principal BOOLEAN NOT NULL DEFAULT FALSE,
  entered_by BIGINT NOT NULL,
  verified_by BIGINT,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_problem_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_problem_entered FOREIGN KEY (entered_by) REFERENCES users(user_id),
  CONSTRAINT chk_problem_type CHECK (problem_type IN ('problem','diagnosis','symptom','finding','complaint')),
  CONSTRAINT chk_problem_status CHECK (status IN ('active','resolved','inactive','recurrence','remission')),
  CONSTRAINT chk_resolved_date CHECK (resolved_date IS NULL OR resolved_date >= onset_date)
);

-- -------------------------------------------------------
-- 16. patient_consents
-- -------------------------------------------------------
CREATE TABLE patient_consents (
  consent_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT,
  consent_type_code VARCHAR(50) NOT NULL,
  consent_template_id BIGINT,
  status VARCHAR(20) NOT NULL,
  processing_legal_basis VARCHAR(30) NOT NULL,
  granted_datetime TIMESTAMP,
  withdrawn_datetime TIMESTAMP,
  expiry_datetime TIMESTAMP,
  signature_method VARCHAR(30),
  signature_data TEXT,
  document_id BIGINT,
  witnessed_by BIGINT,
  collected_by BIGINT NOT NULL,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_consent_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_consent_status CHECK (status IN ('granted','declined','withdrawn','expired')),
  CONSTRAINT chk_consent_basis CHECK (processing_legal_basis IN ('explicit_consent','treatment_exemption','public_health','legal_obligation','vital_interest','contractual')),
  CONSTRAINT chk_granted CHECK (status != 'granted' OR granted_datetime IS NOT NULL),
  CONSTRAINT chk_withdrawn CHECK (status != 'withdrawn' OR withdrawn_datetime IS NOT NULL)
);

-- -------------------------------------------------------
-- 17. patient_documents
-- -------------------------------------------------------
CREATE TABLE patient_documents (
  document_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT,
  document_type_code VARCHAR(50) NOT NULL,
  document_title VARCHAR(300) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size_bytes BIGINT,
  storage_path VARCHAR(500) NOT NULL,
  storage_system VARCHAR(30) NOT NULL DEFAULT 'dms',
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  uploaded_by BIGINT NOT NULL,
  upload_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  retention_expiry DATE,
  checksum_sha256 VARCHAR(64),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_doc_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_doc_uploaded FOREIGN KEY (uploaded_by) REFERENCES users(user_id),
  CONSTRAINT chk_doc_status CHECK (status IN ('active','archived','deleted','pending_scan'))
);

-- -------------------------------------------------------
-- 18. clinical_notes
-- -------------------------------------------------------
CREATE TABLE clinical_notes (
  note_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT NOT NULL,
  note_type VARCHAR(30) NOT NULL,
  template_id BIGINT,
  note_title VARCHAR(300) NOT NULL,
  note_body TEXT NOT NULL,
  structured_data JSONB,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  author_provider_id BIGINT NOT NULL,
  authored_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_by BIGINT,
  signed_datetime TIMESTAMP,
  cosigner_provider_id BIGINT,
  cosigned_datetime TIMESTAMP,
  parent_note_id BIGINT,
  specialty_code VARCHAR(20),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_note_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_note_author FOREIGN KEY (author_provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_note_signer FOREIGN KEY (signed_by) REFERENCES providers(provider_id),
  CONSTRAINT fk_note_cosigner FOREIGN KEY (cosigner_provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_note_parent FOREIGN KEY (parent_note_id) REFERENCES clinical_notes(note_id),
  CONSTRAINT chk_note_type CHECK (note_type IN ('H_AND_P','PROGRESS','DISCHARGE','CONSULT','NURSING_ASSESSMENT','PROCEDURE','OPERATIVE','ED','ADDENDUM')),
  CONSTRAINT chk_note_status CHECK (status IN ('draft','pending_cosign','signed','amended','entered_in_error'))
);

-- -------------------------------------------------------
-- 19. duplicate_suspects
-- -------------------------------------------------------
CREATE TABLE duplicate_suspects (
  suspect_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id_1 BIGINT NOT NULL,
  patient_id_2 BIGINT NOT NULL,
  match_score DECIMAL(5,2) NOT NULL,
  match_method VARCHAR(30) NOT NULL,
  matching_fields JSONB,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  resolution VARCHAR(20),
  primary_patient_id BIGINT,
  flagged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  flagged_by BIGINT,
  reviewed_by BIGINT,
  reviewed_at TIMESTAMP,
  approved_by BIGINT,
  approved_at TIMESTAMP,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_dupe_p1 FOREIGN KEY (patient_id_1) REFERENCES patients(patient_id),
  CONSTRAINT fk_dupe_p2 FOREIGN KEY (patient_id_2) REFERENCES patients(patient_id),
  CONSTRAINT chk_dupe_diff CHECK (patient_id_1 != patient_id_2),
  CONSTRAINT chk_dupe_status CHECK (status IN ('new','in_review','pending_approval','resolved','rejected','auto_resolved')),
  CONSTRAINT chk_dupe_method CHECK (match_method IN ('deterministic','probabilistic','manual','registration_flag')),
  CONSTRAINT chk_dupe_score CHECK (match_score BETWEEN 0.00 AND 100.00)
);

-- -------------------------------------------------------
-- 20. provider_credentials
-- -------------------------------------------------------
CREATE TABLE provider_credentials (
  credential_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  provider_id BIGINT NOT NULL,
  credential_type VARCHAR(30) NOT NULL,
  credential_name VARCHAR(200) NOT NULL,
  credential_number VARCHAR(100),
  issuing_authority VARCHAR(200) NOT NULL,
  issue_date DATE,
  expiry_date DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  document_id BIGINT,
  verified_by BIGINT,
  verified_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_cred_provider FOREIGN KEY (provider_id) REFERENCES providers(provider_id),
  CONSTRAINT chk_cred_type CHECK (credential_type IN ('license','board_cert','degree','privilege','cpr_cert','malpractice_ins','other')),
  CONSTRAINT chk_cred_status CHECK (status IN ('active','expired','revoked','pending_renewal','suspended')),
  CONSTRAINT chk_cred_dates CHECK (expiry_date IS NULL OR expiry_date >= issue_date)
);

-- -------------------------------------------------------
-- 21. patient_contacts
-- -------------------------------------------------------
CREATE TABLE patient_contacts (
  contact_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id BIGINT NOT NULL,
  relationship VARCHAR(30) NOT NULL,
  contact_name_en VARCHAR(200) NOT NULL,
  contact_name_ar VARCHAR(200),
  mobile_phone VARCHAR(20) NOT NULL,
  home_phone VARCHAR(20),
  email VARCHAR(200),
  is_emergency_contact BOOLEAN NOT NULL DEFAULT TRUE,
  is_next_of_kin BOOLEAN NOT NULL DEFAULT FALSE,
  priority INT NOT NULL DEFAULT 1,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_contact_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_contact_rel CHECK (relationship IN ('spouse','parent','child','sibling','guardian','friend','employer','other')),
  CONSTRAINT chk_contact_priority CHECK (priority >= 1)
);

-- -------------------------------------------------------
-- 22. integration_message_log
-- -------------------------------------------------------
CREATE TABLE integration_message_log (
  message_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message_control_id VARCHAR(50),
  direction VARCHAR(10) NOT NULL,
  message_type VARCHAR(30) NOT NULL,
  target_system VARCHAR(50) NOT NULL,
  integration_endpoint VARCHAR(200),
  patient_id BIGINT,
  status VARCHAR(20) NOT NULL DEFAULT 'queued',
  payload TEXT,
  response_payload TEXT,
  ack_code VARCHAR(5),
  retry_count INT NOT NULL DEFAULT 0,
  max_retries INT NOT NULL DEFAULT 10,
  next_retry_at TIMESTAMP,
  error_message TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at TIMESTAMP,
  completed_at TIMESTAMP,

  CONSTRAINT fk_intmsg_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT chk_intmsg_dir CHECK (direction IN ('outbound','inbound')),
  CONSTRAINT chk_intmsg_status CHECK (status IN ('queued','sent','pending','acknowledged','error','failed','rejected'))
);