SET NAMES utf8mb4;

ALTER TABLE system_incidents ADD COLUMN IF NOT EXISTS response_sla_breached_at DATETIME NULL;
ALTER TABLE system_incidents ADD COLUMN IF NOT EXISTS resolution_sla_breached_at DATETIME NULL;
ALTER TABLE system_incidents ADD COLUMN IF NOT EXISTS escalated_at DATETIME NULL;

CREATE TABLE IF NOT EXISTS incident_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  incident_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  note TEXT NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_incident_history_incident(incident_id,created_at),
  CONSTRAINT fk_ih_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ih_incident FOREIGN KEY(incident_id) REFERENCES system_incidents(id) ON DELETE CASCADE,
  CONSTRAINT fk_ih_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sla_policies (
  workspace_id BIGINT UNSIGNED PRIMARY KEY,
  critical_response_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  error_response_minutes INT UNSIGNED NOT NULL DEFAULT 60,
  warning_response_minutes INT UNSIGNED NOT NULL DEFAULT 240,
  critical_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 120,
  error_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 480,
  warning_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 1440,
  auto_escalate TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sla_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_windows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  source VARCHAR(100) NOT NULL DEFAULT 'all',
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  notes TEXT NULL,
  status ENUM('active','cancelled','completed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mw_workspace_time(workspace_id,starts_at,ends_at),
  CONSTRAINT fk_mw_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
