-- ===========================================================================
-- Migration: Biosecurity
-- Date: 2026-07-29
--
-- A standalone farm module covering four biosecurity records:
--   * visitor & vehicle register (who came onto the farm, from where)
--   * disinfection / foot-bath log
--   * checklists — configurable templates run as pass/fail inspections
--   * incidents — breaches logged and tracked to resolution
-- Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS biosecurity_visitors (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  visitor_name  VARCHAR(120) NOT NULL,
  company       VARCHAR(120) NULL,
  purpose       VARCHAR(160) NULL,
  phone         VARCHAR(40)  NULL,
  from_location VARCHAR(160) NULL,          -- last farm/place visited (disease risk)
  vehicle_reg   VARCHAR(40)  NULL,
  visited_on    DATE         NOT NULL,
  time_in       VARCHAR(5)   NULL,          -- HH:MM
  time_out      VARCHAR(5)   NULL,
  disinfected   TINYINT(1)   NOT NULL DEFAULT 0,
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bvis_farm (farm_id, visited_on),
  CONSTRAINT fk_bvis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_disinfections (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  location      VARCHAR(120) NOT NULL,      -- gate, house entrance, foot bath…
  chemical      VARCHAR(120) NULL,
  concentration VARCHAR(60)  NULL,
  done_by       VARCHAR(120) NULL,
  done_on       DATE         NOT NULL,
  time          VARCHAR(5)   NULL,
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bdis_farm (farm_id, done_on),
  CONSTRAINT fk_bdis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_incidents (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  incident_type VARCHAR(120) NOT NULL,
  severity      ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  occurred_on   DATE         NOT NULL,
  description   VARCHAR(500) NULL,
  action_taken  VARCHAR(500) NULL,
  status        ENUM('open','resolved') NOT NULL DEFAULT 'open',
  resolved_on   DATE         NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_binc_farm (farm_id, status),
  CONSTRAINT fk_binc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- checklist templates: a named, reusable set of items
CREATE TABLE IF NOT EXISTS biosecurity_checklists (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id    INT UNSIGNED NOT NULL,
  name       VARCHAR(120) NOT NULL,
  active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_bchk_farm (farm_id),
  CONSTRAINT fk_bchk_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_checklist_items (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  template_id INT UNSIGNED NOT NULL,
  farm_id     INT UNSIGNED NOT NULL,
  label       VARCHAR(200) NOT NULL,
  sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_bchki_tpl (template_id),
  CONSTRAINT fk_bchki_tpl  FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE CASCADE,
  CONSTRAINT fk_bchki_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)                  ON DELETE CASCADE
) ENGINE=InnoDB;

-- inspections: one run of a template, scored pass/fail/na
CREATE TABLE IF NOT EXISTS biosecurity_inspections (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED NOT NULL,
  template_id   INT UNSIGNED NULL,          -- SET NULL if the template is later deleted
  template_name VARCHAR(120) NULL,          -- snapshot of the template's name
  inspected_on  DATE         NOT NULL,
  inspector     VARCHAR(120) NULL,
  passed        INT UNSIGNED NOT NULL DEFAULT 0,
  failed        INT UNSIGNED NOT NULL DEFAULT 0,
  na            INT UNSIGNED NOT NULL DEFAULT 0,
  score_pct     DECIMAL(5,1) NULL,          -- passed / (passed+failed) * 100
  note          VARCHAR(255) NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bins_farm (farm_id, inspected_on),
  CONSTRAINT fk_bins_farm FOREIGN KEY (farm_id)     REFERENCES farms(id)                  ON DELETE CASCADE,
  CONSTRAINT fk_bins_tpl  FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS biosecurity_inspection_results (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inspection_id INT UNSIGNED NOT NULL,
  farm_id       INT UNSIGNED NOT NULL,
  label         VARCHAR(200) NOT NULL,      -- snapshot of the item label
  result        ENUM('pass','fail','na') NOT NULL DEFAULT 'pass',
  note          VARCHAR(255) NULL,
  KEY idx_binsr_ins (inspection_id),
  CONSTRAINT fk_binsr_ins  FOREIGN KEY (inspection_id) REFERENCES biosecurity_inspections(id) ON DELETE CASCADE,
  CONSTRAINT fk_binsr_farm FOREIGN KEY (farm_id)       REFERENCES farms(id)                   ON DELETE CASCADE
) ENGINE=InnoDB;