-- PATCH.SQL (apply to your existing safety_schema)
-- Adds: resolution_description, attachment file_path, and incident audit log table

-- 1) Incidents: add resolution description (mandatory when status=completed)
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'incidents'
    AND COLUMN_NAME = 'resolution_description'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE incidents ADD COLUMN resolution_description TEXT NULL AFTER resolution_date',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2) Attachments: store relative uploads path (uploads/<stored_name>)
SET @col2_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'incident_attachments'
    AND COLUMN_NAME = 'file_path'
);
SET @sql2 := IF(@col2_exists = 0,
  'ALTER TABLE incident_attachments ADD COLUMN file_path VARCHAR(255) NULL AFTER stored_name',
  'SELECT 1'
);
PREPARE stmt2 FROM @sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

-- Backfill existing rows (safe if already filled)
UPDATE incident_attachments
   SET file_path = CONCAT('uploads/', stored_name)
 WHERE (file_path IS NULL OR file_path = '');

-- 3) Audit log table (tracks all changes)
CREATE TABLE IF NOT EXISTS incident_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  action VARCHAR(30) NOT NULL,              -- CREATE / UPDATE / ATTACH_ADD / ATTACH_DELETE
  field_name VARCHAR(64) NULL,              -- column name or logical field
  old_value TEXT NULL,
  new_value TEXT NULL,
  changed_by INT NULL,
  meta_json JSON NULL,
  changed_at DATETIME NOT NULL,
  INDEX idx_audit_incident (incident_id),
  INDEX idx_audit_changed_at (changed_at),
  CONSTRAINT fk_audit_incident FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) Units dropdown must come from table (config_units)
CREATE TABLE IF NOT EXISTS config_units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO config_units (id, name, created_at) VALUES
  (1, 'ERODE', NOW()),
  (2, 'TVL-1', NOW()),
  (3, 'TVL-2', NOW());
