-- Kanban Enhancements: Stage Dates & History
USE compras_solicitacoes;

-- 1) Datas de entrada por etapa
ALTER TABLE requests
  ADD COLUMN stage_entered_cotacao DATETIME NULL AFTER stage,
  ADD COLUMN stage_entered_compra  DATETIME NULL AFTER stage_entered_cotacao,
  ADD COLUMN stage_entered_entrega DATETIME NULL AFTER stage_entered_compra;

-- 2) Histórico de mudança de etapa
CREATE TABLE IF NOT EXISTS request_stage_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  old_stage ENUM('COTACAO','COMPRA','ENTREGA') NULL,
  new_stage ENUM('COTACAO','COMPRA','ENTREGA') NOT NULL,
  changed_by INT NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (request_id),
  CONSTRAINT fk_rsh_req FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
);
