-- ============================================
-- SCHEMA PARA SISTEMA DE ROTAS
-- Sistema de Solicitação de Autorização de Rotas com Múltiplos Clientes
-- Compatível com a estrutura atual do banco
-- ============================================

-- Tabela principal para solicitações de rotas
CREATE TABLE IF NOT EXISTS solicitacoes_rotas (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    motorista_id BIGINT NOT NULL COMMENT 'ID do motorista que solicitou a rota',
    veiculo_id BIGINT NOT NULL COMMENT 'ID do veículo a ser usado',
    titulo VARCHAR(200) NOT NULL COMMENT 'Título da rota (ex: "Atendimento Região Norte")',
    descricao TEXT NULL COMMENT 'Descrição detalhada da rota',
    data_prevista DATE NOT NULL COMMENT 'Data prevista para execução da rota',
    hora_inicio_prevista TIME NOT NULL COMMENT 'Horário previsto para início',
    duracao_estimada INT NULL COMMENT 'Duração estimada em minutos',
    km_estimado INT NULL COMMENT 'Quilometragem estimada total',
    prioridade ENUM('baixa', 'normal', 'alta', 'urgente') DEFAULT 'normal',
    status ENUM('pendente', 'aprovada', 'negada', 'em_execucao', 'concluida', 'cancelada') DEFAULT 'pendente',
    
    -- Dados do gestor que aprovou/negou
    gestor_id BIGINT NULL COMMENT 'ID do gestor que respondeu (tabela motoristas com tipo gestor)',
    data_resposta TIMESTAMP NULL,
    justificativa_gestor TEXT NULL,
    
    -- Dados de execução
    data_inicio_real TIMESTAMP NULL,
    data_fim_real TIMESTAMP NULL,
    km_real INT NULL COMMENT 'Quilometragem real executada',
    diferenca_km_calculada INT NULL COMMENT 'Diferença entre KM real e estimado',
    justificativa_km TEXT NULL COMMENT 'Justificativa para diferença de quilometragem',
    observacoes_finais TEXT NULL,
    
    -- Controle
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (motorista_id) REFERENCES motoristas(id) ON UPDATE CASCADE,
    FOREIGN KEY (veiculo_id) REFERENCES veiculos(id) ON UPDATE CASCADE,
    FOREIGN KEY (gestor_id) REFERENCES motoristas(id) ON UPDATE CASCADE,
    
    INDEX idx_motorista (motorista_id),
    INDEX idx_veiculo (veiculo_id),
    INDEX idx_status (status),
    INDEX idx_data_prevista (data_prevista),
    INDEX idx_gestor (gestor_id)
);

-- Tabela para clientes da rota (relacionamento 1:N)
CREATE TABLE IF NOT EXISTS rota_clientes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    solicitacao_rota_id BIGINT NOT NULL,
    cliente_id INT NULL COMMENT 'ID do cliente na tabela clientes (se existir)',
    nome_cliente VARCHAR(200) NOT NULL COMMENT 'Nome do cliente',
    endereco TEXT NULL COMMENT 'Endereço do cliente',
    telefone VARCHAR(20) NULL,
    motivo_atendimento TEXT NOT NULL COMMENT 'Motivo da visita',
    ordem_sequencia INT NOT NULL COMMENT 'Ordem de atendimento (1, 2, 3...)',
    tempo_estimado INT NULL COMMENT 'Tempo estimado em minutos para este cliente',
    
    -- Dados de execução
    hora_chegada TIMESTAMP NULL,
    hora_saida TIMESTAMP NULL,
    status_atendimento ENUM('pendente', 'em_atendimento', 'concluido', 'cancelado') DEFAULT 'pendente',
    observacoes TEXT NULL,
    
    -- Geolocalização
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (solicitacao_rota_id) REFERENCES solicitacoes_rotas(id) ON DELETE CASCADE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    
    INDEX idx_solicitacao (solicitacao_rota_id),
    INDEX idx_cliente (cliente_id),
    INDEX idx_ordem (ordem_sequencia),
    INDEX idx_status (status_atendimento)
);

-- Tabela para viagens geradas automaticamente após aprovação
CREATE TABLE rota_viagens (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    solicitacao_rota_id BIGINT NOT NULL,
    viagem_id BIGINT NOT NULL COMMENT 'ID da viagem na tabela viagens',
    rota_cliente_id BIGINT NOT NULL COMMENT 'Cliente específico desta viagem',
    ordem_execucao INT NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (solicitacao_rota_id) REFERENCES solicitacoes_rotas(id) ON DELETE CASCADE,
    FOREIGN KEY (viagem_id) REFERENCES viagens(id) ON DELETE CASCADE,
    FOREIGN KEY (rota_cliente_id) REFERENCES rota_clientes(id) ON DELETE CASCADE,
    
    INDEX idx_solicitacao (solicitacao_rota_id),
    INDEX idx_viagem (viagem_id),
    INDEX idx_cliente (rota_cliente_id)
);

-- View para consultas completas de rotas
CREATE VIEW v_rotas_completas AS
SELECT 
    sr.*,
    m.nome as motorista_nome,
    v.modelo as veiculo_modelo,
    v.placa as veiculo_placa,
    g.nome as gestor_nome,
    COUNT(rc.id) as total_clientes,
    SUM(rc.tempo_estimado) as tempo_total_estimado,
    CASE 
        WHEN sr.status = 'pendente' THEN 'Aguardando Aprovação'
        WHEN sr.status = 'aprovada' THEN 'Aprovada'
        WHEN sr.status = 'negada' THEN 'Negada'
        WHEN sr.status = 'em_execucao' THEN 'Em Execução'
        WHEN sr.status = 'concluida' THEN 'Concluída'
        WHEN sr.status = 'cancelada' THEN 'Cancelada'
    END as status_descricao
FROM solicitacoes_rotas sr
LEFT JOIN motoristas m ON sr.motorista_id = m.id
LEFT JOIN veiculos v ON sr.veiculo_id = v.id
LEFT JOIN motoristas g ON sr.gestor_id = g.id
LEFT JOIN rota_clientes rc ON sr.id = rc.solicitacao_rota_id
GROUP BY sr.id;

-- Dados de exemplo (só descomente depois de ter os dados necessários)
-- INSERT INTO solicitacoes_rotas 
-- (motorista_id, veiculo_id, titulo, descricao, data_prevista, hora_inicio_prevista, prioridade) 
-- VALUES 
-- (1, 1, 'Rota Norte - Manutenções Preventivas', 'Atendimento de manutenções preventivas na região norte da cidade', CURDATE() + INTERVAL 1 DAY, '08:00:00', 'normal');

-- INSERT INTO rota_clientes 
-- (solicitacao_rota_id, nome_cliente, endereco, motivo_atendimento, ordem_sequencia, tempo_estimado) 
-- VALUES 
-- (1, 'Empresa ABC Ltda', 'Rua das Flores, 123 - Centro', 'Manutenção preventiva em ar condicionado', 1, 90),
-- (1, 'Loja XYZ', 'Av. Principal, 456 - Bairro Norte', 'Reparo em sistema elétrico', 2, 120),
-- (1, 'Indústria DEF', 'Rua Industrial, 789 - Distrito Industrial', 'Manutenção em equipamentos', 3, 180);
