CREATE DATABASE IF NOT EXISTS extremadesk CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE extremadesk;

ALTER TABLE chamados
  MODIFY status enum('Aberto','Aguardando TI','Em andamento','Aguardando usuario','Resolvido','Cancelado','Reaberto') DEFAULT 'Aberto';

SET @db := DATABASE();
SET @has_updated_at := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'chamados' AND COLUMN_NAME = 'updated_at'
);
SET @stmt := IF(@has_updated_at = 0,
    'ALTER TABLE chamados ADD COLUMN updated_at timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER created_at',
    'SELECT 1'
);
PREPARE s FROM @stmt;
EXECUTE s;
DEALLOCATE PREPARE s;

UPDATE chamados SET updated_at = COALESCE(updated_at, tempo_resolucao, created_at);

CREATE TABLE IF NOT EXISTS chamados_historico (
    id int(11) NOT NULL AUTO_INCREMENT,
    chamado_id int(11) NOT NULL,
    usuario_id int(11) DEFAULT NULL,
    acao varchar(100) NOT NULL,
    descricao text DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (id),
    KEY chamado_id (chamado_id),
    KEY usuario_id (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
