-- ============================================
-- SISTEMA DE OPERADORES GPS
-- Controle de Frotas - Pinheiro Empresas
-- ============================================

-- 1. CRIAR TABELA DE OPERADORES GPS
CREATE TABLE IF NOT EXISTS operadores_gps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    usuario VARCHAR(50) UNIQUE NOT NULL,
    senha VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    telefone VARCHAR(20),
    status ENUM('ativo', 'inativo') DEFAULT 'ativo',
    nivel_acesso ENUM('operador', 'supervisor', 'admin') DEFAULT 'operador',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_usuario (usuario),
    INDEX idx_status (status),
    INDEX idx_nivel_acesso (nivel_acesso)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. ADICIONAR CAMPOS NA TABELA VIAGENS PARA REGISTRAR OPERADOR
ALTER TABLE viagens 
ADD COLUMN operador_gps_id INT NULL AFTER tecnico_id,
ADD COLUMN operador_gps_nome VARCHAR(100) NULL AFTER operador_gps_id,
ADD COLUMN data_liberacao_partida TIMESTAMP NULL AFTER data_inicio,
ADD COLUMN observacoes_operador TEXT NULL AFTER observacoes,
ADD COLUMN ip_liberacao VARCHAR(45) NULL AFTER observacoes_operador,
ADD COLUMN user_agent_liberacao TEXT NULL AFTER ip_liberacao,
ADD FOREIGN KEY (operador_gps_id) REFERENCES operadores_gps(id) ON DELETE SET NULL,
ADD INDEX idx_operador_gps (operador_gps_id),
ADD INDEX idx_data_liberacao (data_liberacao_partida);

-- 3. ADICIONAR CAMPOS NA TABELA SOLICITACOES_ROTAS TAMBÉM
ALTER TABLE solicitacoes_rotas 
ADD COLUMN operador_gps_id INT NULL AFTER gestor_id,
ADD COLUMN operador_gps_nome VARCHAR(100) NULL AFTER operador_gps_id,
ADD COLUMN data_liberacao_partida TIMESTAMP NULL AFTER created_at,
ADD COLUMN observacoes_operador TEXT NULL AFTER observacoes,
ADD COLUMN ip_liberacao VARCHAR(45) NULL AFTER observacoes_operador,
ADD COLUMN user_agent_liberacao TEXT NULL AFTER ip_liberacao,
ADD FOREIGN KEY (operador_gps_id) REFERENCES operadores_gps(id) ON DELETE SET NULL,
ADD INDEX idx_operador_gps (operador_gps_id),
ADD INDEX idx_data_liberacao (data_liberacao_partida);

-- 4. INSERIR OPERADORES DE EXEMPLO
INSERT INTO operadores_gps (nome, usuario, senha, email, telefone, nivel_acesso) VALUES
('João Silva', 'joao.gps', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'joao.gps@pinheiroempresas.com.br', '(11) 99999-1111', 'operador'),
('Maria Santos', 'maria.gps', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'maria.gps@pinheiroempresas.com.br', '(11) 99999-2222', 'operador'),
('Pedro Costa', 'pedro.gps', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'pedro.gps@pinheiroempresas.com.br', '(11) 99999-3333', 'supervisor'),
('Ana Oliveira', 'ana.gps', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ana.gps@pinheiroempresas.com.br', '(11) 99999-4444', 'admin')
ON DUPLICATE KEY UPDATE 
    nome = VALUES(nome),
    email = VALUES(email),
    telefone = VALUES(telefone);

-- 5. CRIAR TABELA DE LOGS DE LIBERAÇÃO
CREATE TABLE IF NOT EXISTS logs_liberacao_partida (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    operador_gps_id INT NOT NULL,
    operador_gps_nome VARCHAR(100) NOT NULL,
    tipo_liberacao ENUM('viagem', 'rota') NOT NULL,
    registro_id BIGINT NOT NULL COMMENT 'ID da viagem ou rota',
    veiculo_id BIGINT,
    veiculo_modelo VARCHAR(100),
    veiculo_placa VARCHAR(20),
    tecnico_id VARCHAR(50),
    tecnico_nome VARCHAR(100),
    cliente VARCHAR(200),
    motivo TEXT,
    ip_liberacao VARCHAR(45),
    user_agent_liberacao TEXT,
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (operador_gps_id) REFERENCES operadores_gps(id) ON DELETE CASCADE,
    INDEX idx_operador (operador_gps_id),
    INDEX idx_tipo_registro (tipo_liberacao, registro_id),
    INDEX idx_veiculo (veiculo_id),
    INDEX idx_tecnico (tecnico_id),
    INDEX idx_data (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. CRIAR VIEW PARA RELATÓRIOS
CREATE OR REPLACE VIEW vw_liberacoes_partida AS
SELECT 
    'viagem' as tipo,
    v.id as registro_id,
    v.operador_gps_id,
    v.operador_gps_nome,
    v.data_liberacao_partida,
    v.veiculo_id,
    ve.modelo as veiculo_modelo,
    ve.placa as veiculo_placa,
    v.tecnico_id,
    m.nome as tecnico_nome,
    v.cliente,
    v.motivo,
    v.observacoes_operador,
    v.ip_liberacao,
    v.user_agent_liberacao
FROM viagens v
LEFT JOIN veiculos ve ON v.veiculo_id = ve.id
LEFT JOIN motoristas m ON (v.tecnico_id = m.id OR v.tecnico_id = m.nome)
WHERE v.operador_gps_id IS NOT NULL

UNION ALL

SELECT 
    'rota' as tipo,
    sr.id as registro_id,
    sr.operador_gps_id,
    sr.operador_gps_nome,
    sr.data_liberacao_partida,
    sr.veiculo_id,
    ve.modelo as veiculo_modelo,
    ve.placa as veiculo_placa,
    sr.motorista_id as tecnico_id,
    m.nome as tecnico_nome,
    sr.titulo as cliente,
    sr.descricao as motivo,
    sr.observacoes_operador,
    sr.ip_liberacao,
    sr.user_agent_liberacao
FROM solicitacoes_rotas sr
LEFT JOIN veiculos ve ON sr.veiculo_id = ve.id
LEFT JOIN motoristas m ON sr.motorista_id = m.id
WHERE sr.operador_gps_id IS NOT NULL;

-- 7. MOSTRAR RESUMO DAS ALTERAÇÕES
SELECT 'TABELAS CRIADAS/MODIFICADAS' as acao, 'operadores_gps' as tabela
UNION ALL
SELECT 'CAMPOS ADICIONADOS', 'viagens (operador_gps_id, operador_gps_nome, data_liberacao_partida, etc.)'
UNION ALL
SELECT 'CAMPOS ADICIONADOS', 'solicitacoes_rotas (operador_gps_id, operador_gps_nome, data_liberacao_partida, etc.)'
UNION ALL
SELECT 'TABELA CRIADA', 'logs_liberacao_partida'
UNION ALL
SELECT 'VIEW CRIADA', 'vw_liberacoes_partida';

-- 8. MOSTRAR OPERADORES INSERIDOS
SELECT 
    id, nome, usuario, email, telefone, nivel_acesso, status
FROM operadores_gps 
ORDER BY id;
