USE extremadesk;

SET @db := DATABASE();

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

SET @has_chamados_deleted_by := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'chamados' AND COLUMN_NAME = 'deleted_by'
);
SET @stmt := IF(@has_chamados_deleted_by = 0,
    'ALTER TABLE chamados ADD COLUMN deleted_by int(11) DEFAULT NULL AFTER deleted_at',
    'SELECT 1'
);
PREPARE s FROM @stmt;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @has_usuarios_deleted_at := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'deleted_at'
);
SET @stmt := IF(@has_usuarios_deleted_at = 0,
    'ALTER TABLE usuarios ADD COLUMN deleted_at timestamp NULL DEFAULT NULL AFTER created_at',
    'SELECT 1'
);
PREPARE s FROM @stmt;
EXECUTE s;
DEALLOCATE PREPARE s;

SET @has_usuarios_deleted_by := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'deleted_by'
);
SET @stmt := IF(@has_usuarios_deleted_by = 0,
    'ALTER TABLE usuarios ADD COLUMN deleted_by int(11) DEFAULT NULL AFTER deleted_at',
    'SELECT 1'
);
PREPARE s FROM @stmt;
EXECUTE s;
DEALLOCATE PREPARE s;

CREATE TABLE IF NOT EXISTS configuracoes (
    chave varchar(100) NOT NULL,
    valor longtext DEFAULT NULL,
    updated_at timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (chave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO configuracoes (chave, valor)
SELECT 'secretarias', 'SECRETARIA DE SAUDE
SECRETARIA DE EDUCACAO
SECRETARIA DE CULTURA
SECRETARIA DE DESENVOLVIMENTO ECONOMICO
SECRETARIA DE ESPORTES
SECRETARIA DE MEIO AMBIENTE
SECRETARIA DE PLANEJAMENTO & FINANCAS
CONTABILIDADE
CONTROLADORIA
PROCURADORIA
SECRETARIA DE TURISMO
SECRETARIA DE ASSISTENCIA SOCIAL
SECRETARIA DE OBRAS & URBANISMO
RECURSOS HUMANOS
GOVERNO'
WHERE NOT EXISTS (SELECT 1 FROM configuracoes WHERE chave = 'secretarias');

INSERT INTO configuracoes (chave, valor)
SELECT 'topicos', 'vector|VECTOR
email|E-MAIL
hardware|HARDWARE
imp_comum|IMPRESSORA
sistemas|SISTEMAS
wifi|WI-FI
rede|REDE DE INTERNET
material|MATERIAL
telefonia|TELEFONIA'
WHERE NOT EXISTS (SELECT 1 FROM configuracoes WHERE chave = 'topicos');
