-- =====================================================================
-- Caresoft Imaging — migration 005
-- P7: equipment management — breakdown and service log, PM schedule,
--     regulatory dates, connectivity monitoring.
-- Safe to run on a database created from schema.sql + migrations 002-004.
-- =====================================================================
USE `caresoft_imaging`;

ALTER TABLE img_modality
  ADD COLUMN room_location    VARCHAR(120) NULL AFTER station_name,
  ADD COLUMN service_status   ENUM('in_service','down','maintenance','retired')
             NOT NULL DEFAULT 'in_service' AFTER is_active,
  ADD COLUMN last_echo_error  VARCHAR(255) NULL AFTER last_echo_ok,
  ADD COLUMN echo_fail_count  INT NOT NULL DEFAULT 0 AFTER last_echo_error,
  ADD COLUMN warranty_expiry  DATE NULL AFTER amc_expiry,
  ADD COLUMN aerb_reg_no      VARCHAR(80) NULL AFTER warranty_expiry,
  ADD COLUMN aerb_expiry      DATE NULL AFTER aerb_reg_no,
  ADD COLUMN calibration_due  DATE NULL AFTER last_pm_date,
  ADD COLUMN silent_alert_hours INT NULL COMMENT 'warn if no study arrives for this many hours'
             AFTER calibration_due,
  ADD KEY ix_mod_status (tenant_id, service_status);

-- Everything that happens to a machine, on one timeline.
CREATE TABLE IF NOT EXISTS img_equipment_event (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  modality_id   INT UNSIGNED NOT NULL,
  event_type    ENUM('breakdown','service','pm','calibration','installation','qa','note')
                NOT NULL DEFAULT 'breakdown',
  severity      ENUM('minor','major','total') NOT NULL DEFAULT 'major'
                COMMENT 'total = the machine cannot scan at all',
  title         VARCHAR(200) NOT NULL,
  description   TEXT NULL,
  action_taken  TEXT NULL,
  vendor        VARCHAR(140) NULL,
  ticket_ref    VARCHAR(80)  NULL,
  parts_replaced VARCHAR(300) NULL,
  cost          DECIMAL(12,2) NULL,
  started_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at   DATETIME NULL,
  counts_downtime TINYINT(1) NOT NULL DEFAULT 1
                COMMENT '0 for planned work done outside operating hours',
  next_due_date DATE NULL COMMENT 'for pm and calibration, when the next one falls',
  raised_by     INT UNSIGNED NULL COMMENT 'null when the monitor raised it',
  closed_by     INT UNSIGNED NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ee (tenant_id, modality_id, started_at),
  KEY ix_ee_open (tenant_id, resolved_at),
  CONSTRAINT fk_ee_tenant FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE,
  CONSTRAINT fk_ee_mod FOREIGN KEY (modality_id) REFERENCES img_modality(id) ON DELETE CASCADE
) ENGINE=InnoDB;