-- Módulo Colaboradores + atribuição de rotas
USE verci;

CREATE TABLE IF NOT EXISTS colaboradores (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL,
    cpf             CHAR(11) NULL,
    telefone        VARCHAR(20) NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    senha_hash      VARCHAR(255) NOT NULL,
    funcao          VARCHAR(100) NULL COMMENT 'Função na equipe de campo',
    ativo           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,
    INDEX idx_colaborador_ativo (ativo)
) ENGINE=InnoDB;

ALTER TABLE rotas
    ADD COLUMN IF NOT EXISTS colaborador_id INT UNSIGNED NULL AFTER responsavel_id;

-- MySQL 8.0.12 might not support IF NOT EXISTS on ADD COLUMN - use procedure or ignore error
-- For compatibility, migration run via PHP handles this

-- FK added only if column was new (run via PHP migration script)