-- Drop old tables if they exist with integer ID columns
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incidents' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incidents CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_health_checks' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_health_checks CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_medicals' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_medicals CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_files' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_files CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_root_causes' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_root_causes CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_custom_fields' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_custom_fields CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_actions' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_actions CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_logs' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_logs CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_field_incidents' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_field_incidents CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_assignment_rules' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_assignment_rules CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_hazard_types' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_hazard_types CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_types' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_types CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_statuses' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_statuses CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_severities' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_severities CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_hazards' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_hazards CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_risk_reviews' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_risk_reviews CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_risk_matrices' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_risk_matrices CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_inspections' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_inspections CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_inspection_items' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_inspection_items CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_inspection_audits' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_inspection_audits CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_inspection_types' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_inspection_types CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_inspection_templates' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_inspection_templates CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_emergency_drills' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_emergency_drills CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_emergency_contacts' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_emergency_contacts CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_training_requirements' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_training_requirements CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_regulations' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_regulations CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_compliance_tasks' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_compliance_tasks CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_compliance_logs' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_compliance_logs CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsas' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsas CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_steps' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_steps CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_signoffs' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_signoffs CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_reviewers' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_reviewers CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_step_files' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_step_files CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_training' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_training CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_jsa_versions' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_jsa_versions CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_action_signoffs' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_action_signoffs CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_incident_signoffs' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_incident_signoffs CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_stakeholders' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_stakeholders CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_needs_expectations' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_needs_expectations CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_issues' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_issues CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_fault_trees' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_fault_trees CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ohs_five_whys' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS ohs_five_whys CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workcom_inspections' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS workcom_inspections CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'safety_incidents' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS safety_incidents CASCADE;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'incident_alert_rules' AND column_name = 'id') THEN
        DROP TABLE IF EXISTS incident_alert_rules CASCADE;
    END IF;
END $$;

-- Table: ohs_incidents
CREATE TABLE ohs_incidents (
  ohs_incident_id     uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  date                date DEFAULT NULL,
  time                varchar(255) DEFAULT NULL,
  branch_id           uuid DEFAULT NULL,
  nature              varchar(255) DEFAULT NULL,
  short_description   varchar(255) DEFAULT NULL,
  classification      VARCHAR(100) NOT NULL DEFAULT 'injury', -- injury, property_damage, environmental, near_miss, first_aid, medical_treatment, lti, fatality
  severity            VARCHAR(100) DEFAULT NULL,
  investigator_id     uuid DEFAULT NULL,
  reviewer_id         uuid DEFAULT NULL,
  status              varchar(50) DEFAULT 'reported',
  submitted_by        INTEGER DEFAULT NULL,
  form_data           jsonb DEFAULT NULL,
  org_id              uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  isDeleted           BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt           timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_health_checks
CREATE TABLE ohs_health_checks (
  ohs_health_check_id       uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  employee_id               uuid NOT NULL,
  check_type                varchar(100) NOT NULL,
  result                    text DEFAULT NULL,
  check_date                date NOT NULL,
  notes                     text DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  pendingSync               BOOLEAN DEFAULT FALSE,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_medicals
CREATE TABLE ohs_medicals (
  ohs_medical_id          uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id             uuid NOT NULL,
  health_check_id         uuid DEFAULT NULL,
  surname                 varchar(100) DEFAULT NULL,
  first_name              varchar(100) DEFAULT NULL,
  other_initials          varchar(100) DEFAULT NULL,
  sex                     varchar(10) DEFAULT NULL,
  address                 varchar(255) DEFAULT NULL,
  dob                     date DEFAULT NULL,
  job_title               varchar(100) DEFAULT NULL,
  occ_years               INTEGER DEFAULT NULL,
  occ_months              INTEGER DEFAULT NULL,
  occ_days                INTEGER DEFAULT NULL,
  employment_status       varchar(100) DEFAULT NULL,
  incident_date           date DEFAULT NULL,
  incident_time           time DEFAULT NULL,
  incident_type           varchar(255) DEFAULT NULL,
  agency                  varchar(255) DEFAULT NULL,
  incident_description    text DEFAULT NULL,
  damage_location         text DEFAULT NULL,
  corrective_action       varchar(255) DEFAULT NULL,
  action_date             date DEFAULT NULL,
  medical_treatment       varchar(255) DEFAULT NULL,
  medical_practitioner    varchar(255) DEFAULT NULL,
  date_notified           date DEFAULT NULL,
  incapacity_years        INTEGER DEFAULT NULL,
  incapacity_months       INTEGER DEFAULT NULL,
  incapacity_days         INTEGER DEFAULT NULL,
  employer_signature      varchar(255) DEFAULT NULL,
  employer_date           date DEFAULT NULL,
  employee_signature      varchar(255) DEFAULT NULL,
  employee_date           date DEFAULT NULL,
  org_id                  uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id               uuid DEFAULT NULL,
  pendingSync             BOOLEAN DEFAULT FALSE,
  isDeleted               BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt               timestamptz NOT NULL DEFAULT now()
);


-- Table: ohs_incident_files
CREATE TABLE ohs_incident_files (
  ohs_incident_file_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id             uuid NOT NULL,
  file_path               varchar(255) DEFAULT NULL,
  uploaded_by             INTEGER DEFAULT NULL,
  org_id                  uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id               uuid DEFAULT NULL,
  isDeleted               BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt               timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_root_causes
CREATE TABLE ohs_root_causes (
  ohs_root_cause_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id          uuid NOT NULL,
  method               varchar(100) DEFAULT NULL,
  analysis_notes       text DEFAULT NULL,
  checklist            text DEFAULT NULL,
  five_whys_id         uuid DEFAULT NULL,
  fishbone_file_id     uuid DEFAULT NULL,
  fault_tree_id        uuid DEFAULT NULL,
  created_by           INTEGER DEFAULT NULL,
  org_id               uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id            uuid DEFAULT NULL,
  isDeleted            BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt            timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_incident_custom_fields
CREATE TABLE ohs_incident_custom_fields (
  ohs_incident_custom_field_id  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  field_name                    varchar(100) NOT NULL,
  field_type                    varchar(50) NOT NULL,
  is_required                   BOOLEAN NOT NULL DEFAULT FALSE,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_actions
CREATE TABLE ohs_actions (
  ohs_action_id          uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id            uuid NOT NULL,
  description            text DEFAULT NULL,
  responsible_user_id    uuid DEFAULT NULL,
  target_date date       DEFAULT NULL,
  status                 varchar(50) DEFAULT NULL,
  completed_at           timestamp DEFAULT NULL,
  org_id                 uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id              uuid DEFAULT NULL,
  isDeleted              BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt              timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_incident_logs
CREATE TABLE ohs_incident_logs (
  ohs_incident_log_id   uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id           uuid NOT NULL,
  action                varchar(50) NOT NULL,
  details               text DEFAULT NULL,
  user_id               uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  logged_at             timestamp NOT NULL DEFAULT current_timestamp,
  org_id                uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id             uuid DEFAULT NULL,
  isDeleted             BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt             timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_incident_logs_incident_idx ON ohs_incident_logs(incident_id);

-- Table: ohs_field_incidents
CREATE TABLE ohs_field_incidents (
  ohs_field_incident_id     uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_type             varchar(100) DEFAULT NULL,
  description               text DEFAULT NULL,
  document_ids              varchar(255) DEFAULT NULL,
  gps                       varchar(255) DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_assignment_rules
CREATE TABLE ohs_assignment_rules (
  ohs_assignment_rule_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  type                      varchar(50) DEFAULT NULL,
  severity                  varchar(50) DEFAULT NULL,
  investigator_id           uuid NOT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_hazard_types
CREATE TABLE ohs_hazard_types (
  ohs_hazard_type_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name                      varchar(100) NOT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);


-- Table: ohs_incident_types
CREATE TABLE ohs_incident_types (
  ohs_incident_type_id      uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name                      varchar(100) NOT NULL,
  form_id                   uuid DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_incident_statuses
CREATE TABLE ohs_incident_statuses (
  ohs_incident_status_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name                      varchar(100) NOT NULL,
  purpose                   text DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);


-- Table: ohs_severities
CREATE TABLE ohs_severities (
  ohs_severity_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name                      varchar(100) NOT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_hazards
CREATE TABLE ohs_hazards (
  ohs_hazard_id             uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  description               text DEFAULT NULL,
  hazard_type_id            uuid DEFAULT NULL,
  severity                  INTEGER DEFAULT NULL,
  probability               INTEGER DEFAULT NULL,
  risk_level                INTEGER DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_risk_reviews
CREATE TABLE ohs_risk_reviews (
  ohs_risk_review_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  hazard_id                 uuid NOT NULL,
  reviewer_id               uuid DEFAULT NULL,
  review_date               date DEFAULT NULL,
  residual_risk             INTEGER DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_risk_matrices
CREATE TABLE ohs_risk_matrices (
  ohs_risk_matrix_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  severity                  INTEGER NOT NULL,
  probability               INTEGER NOT NULL,
  risk_value                INTEGER NOT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_inspections
CREATE TABLE ohs_inspections (
  ohs_inspection_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  date                      date NOT NULL,
  short_description         varchar(255) DEFAULT NULL,
  inspector_id              uuid DEFAULT NULL,
  inspection_type_id        uuid DEFAULT NULL,
  form_data                 jsonb DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_inspection_items
CREATE TABLE ohs_inspection_items (
  ohs_inspection_item_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  inspection_id             uuid NOT NULL,
  question                  varchar(255) NOT NULL,
  response                  varchar(100) DEFAULT NULL,
  notes                     text DEFAULT NULL,
  corrective_action         text DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_inspection_audits
CREATE TABLE ohs_inspection_audits (
  ohs_inspection_audit_id   uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  inspection_id             uuid NOT NULL,
  action                    varchar(20) NOT NULL,
  changed_fields            text DEFAULT NULL,
  user_id                   uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  performed_at              timestamp DEFAULT current_timestamp,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_inspection_types
CREATE TABLE ohs_inspection_types (
  ohs_inspection_type_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name                      varchar(100) NOT NULL,
  form_id                   uuid DEFAULT NULL,
  org_id                    uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                 uuid DEFAULT NULL,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_inspection_templates
CREATE TABLE ohs_inspection_templates (
  ohs_inspection_template_id  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  template_name               varchar(255) DEFAULT NULL,
  name                        varchar(100) DEFAULT NULL,
  form_id                     uuid DEFAULT NULL,
  description                 text DEFAULT NULL,
  org_id                      uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                   uuid DEFAULT NULL,
  isDeleted                   BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                   timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_emergency_drills
CREATE TABLE ohs_emergency_drills (
  ohs_emergency_drill_id      uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  drill_type                  varchar(100) NOT NULL,
  scheduled_for               timestamp NOT NULL,
  conducted_by                INTEGER DEFAULT NULL,
  notes                       text DEFAULT NULL,
  org_id                      uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                   uuid DEFAULT NULL,
  reminder_sent               BOOLEAN DEFAULT FALSE,
  pendingSync                 BOOLEAN DEFAULT FALSE,
  isDeleted                   BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                   timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_emergency_contacts
CREATE TABLE ohs_emergency_contacts (
  ohs_emergency_contact_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  employee_id                 uuid NOT NULL,
  contact_name                varchar(255) NOT NULL,
  phone                       varchar(50) NOT NULL,
  relation                    varchar(100) DEFAULT NULL,
  org_id                      uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                   uuid DEFAULT NULL,
  pendingSync                 BOOLEAN DEFAULT FALSE,
  isDeleted                   BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                   timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_training_requirements
CREATE TABLE ohs_training_requirements (
  ohs_training_requirement_id   uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  role_id                       uuid DEFAULT NULL,
  project_id                    uuid DEFAULT NULL,
  course_id                     uuid NOT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_training_requirements_role_idx ON ohs_training_requirements(role_id);
CREATE INDEX ohs_training_requirements_project_idx ON ohs_training_requirements(project_id);
CREATE INDEX ohs_training_requirements_course_idx ON ohs_training_requirements(course_id);

-- Table: ohs_regulations
CREATE TABLE ohs_regulations (
  ohs_regulation_id             uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  title                         varchar(255) NOT NULL,
  description                   text DEFAULT NULL,
  effective_date                date DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_compliance_tasks
CREATE TABLE ohs_compliance_tasks (
  ohs_compliance_task_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  regulation_id                 uuid DEFAULT NULL,
  title                         varchar(255) NOT NULL,
  due_date                      timestamp DEFAULT NULL,
  assigned_to                   INTEGER DEFAULT NULL,
  status                        VARCHAR(100) DEFAULT 'pending',
  reminder_sent                 BOOLEAN DEFAULT FALSE,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_compliance_logs
CREATE TABLE ohs_compliance_logs (
  ohs_compliance_log_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  task_id                       uuid NOT NULL,
  log_date                      timestamp NOT NULL DEFAULT current_timestamp,
  notes                         text DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_jsas
CREATE TABLE ohs_jsas (
  ohs_jsa_id                    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  title                         varchar(255) NOT NULL,
  description                   text DEFAULT NULL,
  doc_path                      varchar(255) DEFAULT NULL,
  department_id                 uuid DEFAULT NULL,
  analysis_date                 date DEFAULT NULL,
  review_date                   timestamp DEFAULT NULL,
  prepared_by                   INTEGER DEFAULT NULL,
  team_member_ids               json DEFAULT NULL,
  permit_requirements           json DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: ohs_jsa_steps
CREATE TABLE ohs_jsa_steps (
  ohs_jsa_step_id               uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_id                        uuid NOT NULL,
  step_order                    INTEGER DEFAULT NULL,
  step                          text DEFAULT NULL,
  hazard_id                     uuid DEFAULT NULL,
  responsible_user_id           uuid DEFAULT NULL,
  likelihood                    INTEGER DEFAULT NULL,
  severity                      INTEGER DEFAULT NULL,
  risk_before                   INTEGER DEFAULT NULL,
  risk_after                    INTEGER DEFAULT NULL,
  controls                      text DEFAULT NULL,
  control_type                  VARCHAR(20) DEFAULT NULL,
  ppe_required                  json DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_steps_jsa_idx ON ohs_jsa_steps (jsa_id);

-- Table: ohs_jsa_signoffs
CREATE TABLE ohs_jsa_signoffs (
  ohs_jsa_signoff_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_id                        uuid NOT NULL,
  user_id                       uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  signed_at                     timestamp DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_signoffs_jsa_idx ON ohs_jsa_signoffs (jsa_id);

-- Table: ohs_jsa_reviewers
CREATE TABLE ohs_jsa_reviewers (
  ohs_jsa_reviewer_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_id                        uuid NOT NULL,
  user_id                       uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  reviewed_at                   timestamp DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_reviewers_jsa_idx ON ohs_jsa_reviewers (jsa_id);

-- Table: ohs_jsa_step_files
CREATE TABLE ohs_jsa_step_files (
  ohs_jsa_step_file_id          uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_step_id                   uuid NOT NULL,
  document_id                   uuid NOT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_step_files_step_idx ON ohs_jsa_step_files (jsa_step_id);

-- Table: ohs_jsa_training
CREATE TABLE ohs_jsa_training (
  ohs_jsa_training_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_id                        uuid NOT NULL,
  employee_id                   uuid NOT NULL,
  trained_at                    timestamp DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_training_jsa_idx ON ohs_jsa_training (jsa_id);

-- Table: ohs_jsa_versions
CREATE TABLE ohs_jsa_versions (
  ohs_jsa_version_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  jsa_id                        uuid NOT NULL,
  version_no                    INTEGER NOT NULL,
  data_json                     json NOT NULL,
  updated_by                    INTEGER DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_jsa_versions_jsa_idx ON ohs_jsa_versions (jsa_id);

-- Table: ohs_action_signoffs
CREATE TABLE ohs_action_signoffs (
  ohs_action_signoff_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  item_id                       uuid NOT NULL,
  item_type                     varchar(20) NOT NULL,
  user_id                       uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  signature                     text DEFAULT NULL,
  document_id                   uuid DEFAULT NULL,
  signed_at                     timestamp DEFAULT CURRENT_TIMESTAMP,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_action_signoffs_item_idx ON ohs_action_signoffs (item_id);

-- Table: ohs_incident_signoffs
CREATE TABLE ohs_incident_signoffs (
  ohs_incident_signoff_id       uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id                   uuid NOT NULL,
  user_id                       uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  signature                     text DEFAULT NULL,
  document_id                   uuid DEFAULT NULL,
  signed_at                     timestamp DEFAULT CURRENT_TIMESTAMP,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_incident_signoffs_idx ON ohs_incident_signoffs (incident_id);

-- Table: ohs_stakeholders
CREATE TABLE ohs_stakeholders (
  ohs_stakeholder_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  stakeholder_name              varchar(255) NOT NULL,
  category                      varchar(100) DEFAULT NULL,
  role_influence                text DEFAULT NULL,
  needs_expectations            text DEFAULT NULL,
  impact                        text DEFAULT NULL,
  communication_method          varchar(100) DEFAULT NULL,
  review_date                   date DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_stakeholders_org_idx ON ohs_stakeholders (org_id);
CREATE INDEX ohs_stakeholders_branch_idx ON ohs_stakeholders (branch_id);

-- Table: ohs_needs_expectations
CREATE TABLE ohs_needs_expectations (
  ohs_needs_expectation_id      uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  interested_party              varchar(255) NOT NULL,
  needs                         text DEFAULT NULL,
  expectations                  text DEFAULT NULL,
  compliance_required           BOOLEAN DEFAULT FALSE,
  source                        varchar(255) DEFAULT NULL,
  status                        varchar(50) DEFAULT NULL,
  notes                         text DEFAULT NULL,
  review_date                   date DEFAULT NULL,
  review_by                     INTEGER DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_needs_expectations_org_idx ON ohs_needs_expectations (org_id);
CREATE INDEX ohs_needs_expectations_branch_idx ON ohs_needs_expectations (branch_id);

-- Table: ohs_issues
CREATE TABLE ohs_issues (
  ohs_issue_id                  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  description                   text DEFAULT NULL,
  type                          varchar(100) DEFAULT NULL,
  category                      varchar(100) DEFAULT NULL,
  impact                        varchar(255) DEFAULT NULL,
  risk_or_opportunity           varchar(255) DEFAULT NULL,
  action_taken                  text DEFAULT NULL,
  owner                         varchar(100) DEFAULT NULL,
  review_date                   date DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_issues_org_idx ON ohs_issues (org_id);
CREATE INDEX ohs_issues_branch_idx ON ohs_issues (branch_id);

-- Table: ohs_fault_trees
CREATE TABLE ohs_fault_trees (
  ohs_fault_tree_id             uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id                   uuid NOT NULL,
  tree_data                     jsonb DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ohs_fault_trees_org_idx ON ohs_fault_trees (org_id);
CREATE INDEX ohs_fault_trees_branch_idx ON ohs_fault_trees (branch_id);

CREATE TABLE ohs_five_whys (
  ohs_five_why_id               uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_id                   uuid DEFAULT NULL,
  whys                          jsonb NOT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: workcom_inspections
CREATE TABLE workcom_inspections (
  workcom_inspection_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  date                          date NOT NULL,
  location                      varchar(255) DEFAULT NULL,
  short_description             varchar(255) DEFAULT NULL,
  inspector_id                  uuid DEFAULT NULL,
  inspection_type_id            uuid DEFAULT NULL,
  workplace_id                  uuid DEFAULT NULL,
  inspection_request_id         uuid DEFAULT NULL,
  form_data                     jsonb DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: safety_incidents
CREATE TABLE safety_incidents (
  safety_incident_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_type                 varchar(100) DEFAULT NULL,
  location                      varchar(255) DEFAULT NULL,
  description                   text DEFAULT NULL,
  gps                           varchar(255) DEFAULT NULL,
  document_ids                  varchar(255) DEFAULT NULL,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);

-- Table: incident_alert_rules
CREATE TABLE incident_alert_rules (
  incident_alert_rule_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  incident_type_id              uuid DEFAULT NULL,
  severity                      varchar(50) DEFAULT NULL,
  send_alert_to                 INTEGER DEFAULT NULL,
  email                         varchar(255) DEFAULT NULL,
  send_email                    BOOLEAN DEFAULT FALSE,
  phone                         varchar(50) DEFAULT NULL,
  send_sms                      BOOLEAN DEFAULT FALSE,
  send_push                     BOOLEAN DEFAULT FALSE,
  org_id                        uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
  branch_id                     uuid DEFAULT NULL,
  isDeleted                     BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                     timestamptz NOT NULL DEFAULT now()
);
