-- Migração 001: Criação das tabelas principais
-- Versão: 1.0
-- Data: 2024-01-15

-- Departamentos (catálogo de setores)
CREATE TABLE IF NOT EXISTS departamentos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL COMMENT 'Nome do departamento',
    descricao TEXT COMMENT 'Descrição do departamento',
    setor_vex VARCHAR(100) NOT NULL COMMENT 'Mapeamento com usuarios.SETOR',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_setor_vex (setor_vex),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Status dos tickets
CREATE TABLE IF NOT EXISTS ticket_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL COMMENT 'Nome do status',
    cor VARCHAR(7) DEFAULT '#6c757d' COMMENT 'Cor hexadecimal para exibição',
    ordem INT DEFAULT 0 COMMENT 'Ordem de exibição',
    ativo BOOLEAN DEFAULT TRUE,
    permite_solicitante BOOLEAN DEFAULT FALSE COMMENT 'Se solicitante pode definir este status',
    permite_agente BOOLEAN DEFAULT TRUE COMMENT 'Se agente pode definir este status',
    pausa_sla BOOLEAN DEFAULT FALSE COMMENT 'Se este status pausa o SLA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nome (nome),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prioridades dos tickets
CREATE TABLE IF NOT EXISTS ticket_priorities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL COMMENT 'Nome da prioridade',
    nivel INT NOT NULL COMMENT 'Nível numérico (1=baixa, 4=crítica)',
    cor VARCHAR(7) DEFAULT '#6c757d' COMMENT 'Cor hexadecimal para exibição',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nome (nome),
    UNIQUE KEY uk_nivel (nivel),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Calendários de negócio
CREATE TABLE IF NOT EXISTS business_calendars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL COMMENT 'Nome do calendário',
    timezone VARCHAR(50) DEFAULT 'America/Sao_Paulo' COMMENT 'Fuso horário',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Horários de funcionamento
CREATE TABLE IF NOT EXISTS business_calendar_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    calendar_id INT NOT NULL,
    dia_semana TINYINT NOT NULL COMMENT 'Dia da semana (1=segunda, 7=domingo)',
    hora_inicio TIME NOT NULL,
    hora_fim TIME NOT NULL,
    ativo BOOLEAN DEFAULT TRUE,
    FOREIGN KEY fk_hours_calendar (calendar_id) REFERENCES business_calendars(id) ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_calendar_dia (calendar_id, dia_semana)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feriados
CREATE TABLE IF NOT EXISTS business_calendar_holidays (
    id INT AUTO_INCREMENT PRIMARY KEY,
    calendar_id INT NOT NULL,
    data DATE NOT NULL,
    nome VARCHAR(100) NOT NULL COMMENT 'Nome do feriado',
    recorrente BOOLEAN DEFAULT FALSE COMMENT 'Se repete anualmente',
    FOREIGN KEY fk_holidays_calendar (calendar_id) REFERENCES business_calendars(id) ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_calendar_data (calendar_id, data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Políticas de SLA
CREATE TABLE IF NOT EXISTS sla_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL COMMENT 'Nome da política',
    departamento_id INT NOT NULL,
    priority_id INT NOT NULL,
    calendar_id INT NOT NULL,
    primeira_resposta_horas INT NOT NULL COMMENT 'Prazo para primeira resposta em horas',
    resolucao_horas INT NOT NULL COMMENT 'Prazo para resolução em horas',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_sla_departamento (departamento_id) REFERENCES departamentos(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_sla_priority (priority_id) REFERENCES ticket_priorities(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_sla_calendar (calendar_id) REFERENCES business_calendars(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    UNIQUE KEY uk_dept_priority (departamento_id, priority_id),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tickets principais
CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(20) NOT NULL COMMENT 'Número único do ticket (ex: TK-2024-001)',
    titulo VARCHAR(255) NOT NULL COMMENT 'Título/assunto do ticket',
    descricao TEXT COMMENT 'Descrição inicial do problema',
    requester_id INT NOT NULL COMMENT 'ID do solicitante (usuarios.id)',
    assignee_id INT NULL COMMENT 'ID do agente responsável (usuarios.id)',
    departamento_id INT NOT NULL COMMENT 'Departamento responsável',
    status_id INT NOT NULL COMMENT 'Status atual',
    priority_id INT NOT NULL COMMENT 'Prioridade',
    sla_due_at TIMESTAMP NULL COMMENT 'Prazo SLA para resolução',
    first_response_due_at TIMESTAMP NULL COMMENT 'Prazo SLA para primeira resposta',
    first_response_at TIMESTAMP NULL COMMENT 'Data da primeira resposta',
    resolved_at TIMESTAMP NULL COMMENT 'Data de resolução',
    closed_at TIMESTAMP NULL COMMENT 'Data de fechamento',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_numero (numero),
    FOREIGN KEY fk_tickets_departamento (departamento_id) REFERENCES departamentos(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY fk_tickets_status (status_id) REFERENCES ticket_statuses(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY fk_tickets_priority (priority_id) REFERENCES ticket_priorities(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX idx_requester (requester_id),
    INDEX idx_assignee (assignee_id),
    INDEX idx_departamento_status_priority (departamento_id, status_id, priority_id),
    INDEX idx_sla_due (sla_due_at),
    INDEX idx_created_at (created_at),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mensagens dos tickets
CREATE TABLE IF NOT EXISTS ticket_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL COMMENT 'ID do ticket',
    author_id INT NOT NULL COMMENT 'Autor da mensagem (usuarios.id)',
    conteudo TEXT NOT NULL COMMENT 'Conteúdo da mensagem',
    is_internal BOOLEAN DEFAULT FALSE COMMENT 'Se é mensagem interna (não visível ao solicitante)',
    is_first_response BOOLEAN DEFAULT FALSE COMMENT 'Se é a primeira resposta do agente',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY fk_messages_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_ticket_created (ticket_id, created_at),
    INDEX idx_author (author_id),
    INDEX idx_is_internal (is_internal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Anexos
CREATE TABLE IF NOT EXISTS ticket_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NULL COMMENT 'ID do ticket (opcional)',
    message_id INT NULL COMMENT 'ID da mensagem (opcional)',
    filename VARCHAR(255) NOT NULL COMMENT 'Nome original do arquivo',
    stored_filename VARCHAR(255) NOT NULL COMMENT 'Nome do arquivo no sistema',
    file_path VARCHAR(500) NOT NULL COMMENT 'Caminho completo do arquivo',
    file_size INT NOT NULL COMMENT 'Tamanho em bytes',
    mime_type VARCHAR(100) NOT NULL COMMENT 'Tipo MIME',
    uploaded_by INT NOT NULL COMMENT 'Usuário que fez upload (usuarios.id)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_attachments_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_attachments_message (message_id) REFERENCES ticket_messages(id) ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_ticket (ticket_id),
    INDEX idx_message (message_id),
    INDEX idx_uploaded_by (uploaded_by),
    CONSTRAINT chk_attachment_reference CHECK (ticket_id IS NOT NULL OR message_id IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Observadores de tickets
CREATE TABLE IF NOT EXISTS ticket_watchers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL COMMENT 'ID do ticket',
    user_id INT NOT NULL COMMENT 'ID do usuário observador (usuarios.id)',
    added_by INT NOT NULL COMMENT 'Quem adicionou o observador (usuarios.id)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_watchers_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE KEY uk_ticket_user (ticket_id, user_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tags
CREATE TABLE IF NOT EXISTS ticket_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL COMMENT 'Nome da tag',
    cor VARCHAR(7) DEFAULT '#6c757d' COMMENT 'Cor hexadecimal',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mapeamento de tags
CREATE TABLE IF NOT EXISTS ticket_tag_map (
    ticket_id INT NOT NULL,
    tag_id INT NOT NULL,
    added_by INT NOT NULL COMMENT 'Quem adicionou a tag (usuarios.id)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ticket_id, tag_id),
    FOREIGN KEY fk_tag_map_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_tag_map_tag (tag_id) REFERENCES ticket_tags(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auditoria
CREATE TABLE IF NOT EXISTS ticket_audits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL COMMENT 'ID do ticket',
    actor_id INT NOT NULL COMMENT 'Usuário que executou a ação (usuarios.id)',
    acao VARCHAR(100) NOT NULL COMMENT 'Tipo de ação executada',
    campo VARCHAR(100) NULL COMMENT 'Campo alterado (se aplicável)',
    valor_anterior TEXT NULL COMMENT 'Valor anterior',
    valor_novo TEXT NULL COMMENT 'Novo valor',
    detalhes JSON NULL COMMENT 'Detalhes adicionais da ação',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY fk_audits_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_ticket_created (ticket_id, created_at),
    INDEX idx_actor (actor_id),
    INDEX idx_acao (acao)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campos customizados
CREATE TABLE IF NOT EXISTS custom_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL COMMENT 'Nome do campo',
    label VARCHAR(100) NOT NULL COMMENT 'Rótulo para exibição',
    tipo ENUM('text', 'textarea', 'select', 'checkbox', 'date', 'number') NOT NULL,
    opcoes JSON NULL COMMENT 'Opções para campos select',
    obrigatorio BOOLEAN DEFAULT FALSE,
    ativo BOOLEAN DEFAULT TRUE,
    ordem INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nome (nome),
    INDEX idx_ativo_ordem (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valores dos campos customizados
CREATE TABLE IF NOT EXISTS ticket_custom_field_values (
    ticket_id INT NOT NULL,
    field_id INT NOT NULL,
    valor TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (ticket_id, field_id),
    FOREIGN KEY fk_custom_values_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_custom_values_field (field_id) REFERENCES custom_fields(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Timers de SLA
CREATE TABLE IF NOT EXISTS sla_timers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    sla_policy_id INT NOT NULL,
    primeira_resposta_due TIMESTAMP NULL,
    primeira_resposta_met BOOLEAN DEFAULT FALSE,
    resolucao_due TIMESTAMP NULL,
    resolucao_met BOOLEAN DEFAULT FALSE,
    tempo_pausado INT DEFAULT 0 COMMENT 'Tempo pausado em minutos',
    pausado_em TIMESTAMP NULL COMMENT 'Quando foi pausado',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY fk_timers_ticket (ticket_id) REFERENCES tickets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY fk_timers_policy (sla_policy_id) REFERENCES sla_policies(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    UNIQUE KEY uk_ticket (ticket_id),
    INDEX idx_primeira_resposta_due (primeira_resposta_due),
    INDEX idx_resolucao_due (resolucao_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fila de notificações
CREATE TABLE IF NOT EXISTS notifications_outbox (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('email', 'whatsapp', 'webhook') NOT NULL,
    destinatario VARCHAR(255) NOT NULL COMMENT 'Email, telefone ou URL',
    assunto VARCHAR(255) NULL COMMENT 'Assunto (para email)',
    conteudo TEXT NOT NULL COMMENT 'Conteúdo da mensagem',
    payload JSON NULL COMMENT 'Dados adicionais',
    tentativas INT DEFAULT 0,
    max_tentativas INT DEFAULT 3,
    status ENUM('pendente', 'enviado', 'erro', 'cancelado') DEFAULT 'pendente',
    erro_msg TEXT NULL,
    enviado_em TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status_created (status, created_at),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Log de execução de jobs
CREATE TABLE IF NOT EXISTS scheduler_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_name VARCHAR(100) NOT NULL COMMENT 'Nome do job',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    finished_at TIMESTAMP NULL,
    status ENUM('running', 'success', 'error') DEFAULT 'running',
    output TEXT NULL COMMENT 'Saída do job',
    error_msg TEXT NULL COMMENT 'Mensagem de erro',
    INDEX idx_job_started (job_name, started_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de controle de migrações
CREATE TABLE IF NOT EXISTS migration_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(50) NOT NULL,
    description VARCHAR(255) NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_version (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Registrar esta migração
INSERT IGNORE INTO migration_log (version, description) VALUES
('001', 'Criação das tabelas principais do sistema Help Desk');

