-- ============================================
-- SCHEMA DO BANCO DE DADOS - CONTROLE DE FROTAS
-- Sistema de retaguarda em PHP
-- ============================================

CREATE DATABASE IF NOT EXISTS controle_frotas 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE controle_frotas;

-- ============================================
-- TABELA DE USUÁRIOS/TÉCNICOS
-- ============================================
CREATE TABLE usuarios (
    id VARCHAR(50) PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    senha VARCHAR(255) NOT NULL,
    cargo ENUM('Técnico', 'Administrador', 'Supervisor') DEFAULT 'Técnico',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ultimo_login TIMESTAMP NULL,
    token_fcm VARCHAR(255) NULL COMMENT 'Token para notificações push',
    
    INDEX idx_email (email),
    INDEX idx_ativo (ativo),
    INDEX idx_cargo (cargo)
);

-- ============================================
-- TABELA DE MOTORISTAS
-- ============================================
CREATE TABLE motoristas (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    matricula VARCHAR(50) NOT NULL,
    cpf VARCHAR(14) UNIQUE NULL,
    telefone VARCHAR(20) NULL,
    cnh_numero VARCHAR(20) NULL,
    cnh_categoria VARCHAR(10) NULL,
    data_nascimento DATE NULL,
    foto_uri VARCHAR(500) NULL,
    usuario VARCHAR(50) NOT NULL,
    senha VARCHAR(255) NOT NULL,
    status ENUM('ativo', 'afastado', 'desligado') DEFAULT 'ativo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_matricula (matricula),
    INDEX idx_cpf (cpf),
    INDEX idx_status (status),
    INDEX idx_usuario (usuario)
);

-- ============================================
-- TABELA DE VEÍCULOS
-- ============================================
CREATE TABLE veiculos (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    modelo VARCHAR(100) NOT NULL,
    placa VARCHAR(10) NOT NULL,
    marca VARCHAR(50) NULL,
    ano_fabricacao YEAR NULL,
    tipo ENUM('carro', 'moto', 'caminhao', 'van', 'onibus', 'outros') NULL,
    cor VARCHAR(30) NULL,
    odometro_atual INT NULL COMMENT 'Quilometragem atual',
    status ENUM('ativo', 'em_uso', 'manutencao', 'desativado') DEFAULT 'ativo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_placa (placa),
    INDEX idx_status (status),
    INDEX idx_tipo (tipo),
    INDEX idx_modelo (modelo)
);

-- ============================================
-- TABELA DE VIAGENS
-- ============================================
CREATE TABLE viagens (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tecnico_id VARCHAR(50) NOT NULL,
    veiculo_id BIGINT NOT NULL,
    cliente VARCHAR(200) NOT NULL,
    motivo TEXT NOT NULL,
    km_inicial INT NOT NULL,
    km_final INT NULL,
    km_gps_final INT NULL COMMENT 'KM calculado via GPS',
    data_inicio TIMESTAMP NOT NULL,
    data_fim TIMESTAMP NULL,
    latitude_inicial DECIMAL(10, 8) NULL,
    longitude_inicial DECIMAL(11, 8) NULL,
    latitude_final DECIMAL(10, 8) NULL,
    longitude_final DECIMAL(11, 8) NULL,
    observacoes TEXT NULL,
    justificativa_divergencia TEXT NULL COMMENT 'Justificativa para divergência entre KM manual e GPS',
    sincronizado BOOLEAN DEFAULT FALSE,
    trajeto JSON NULL COMMENT 'Pontos GPS do trajeto como JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tecnico_id) REFERENCES usuarios(id) ON UPDATE CASCADE,
    FOREIGN KEY (veiculo_id) REFERENCES veiculos(id) ON UPDATE CASCADE,
    
    INDEX idx_tecnico (tecnico_id),
    INDEX idx_veiculo (veiculo_id),
    INDEX idx_data_inicio (data_inicio),
    INDEX idx_data_fim (data_fim),
    INDEX idx_sincronizado (sincronizado),
    INDEX idx_cliente (cliente),
    INDEX idx_status (data_fim) COMMENT 'Para identificar viagens em andamento'
);

-- ============================================
-- TABELA DE PONTOS DE TRAJETO (DETALHADA)
-- ============================================
CREATE TABLE trajeto_pontos (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    viagem_id BIGINT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    timestamp_ponto TIMESTAMP NOT NULL,
    velocidade DECIMAL(5, 2) NULL COMMENT 'Velocidade em km/h',
    precisao DECIMAL(5, 2) NULL COMMENT 'Precisão do GPS em metros',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (viagem_id) REFERENCES viagens(id) ON DELETE CASCADE,
    
    INDEX idx_viagem (viagem_id),
    INDEX idx_timestamp (timestamp_ponto),
    INDEX idx_coords (latitude, longitude)
);

-- ============================================
-- TABELA DE LOGS DE SISTEMA
-- ============================================
CREATE TABLE system_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('debug', 'info', 'warning', 'error', 'critical') NOT NULL,
    message TEXT NOT NULL,
    context JSON NULL COMMENT 'Dados adicionais em JSON',
    user_id VARCHAR(50) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    endpoint VARCHAR(200) NULL,
    method VARCHAR(10) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    INDEX idx_level (level),
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id),
    INDEX idx_endpoint (endpoint)
);

-- ============================================
-- TABELA DE CONFIGURAÇÕES DO SISTEMA
-- ============================================
CREATE TABLE configuracoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chave VARCHAR(100) UNIQUE NOT NULL,
    valor TEXT NULL,
    descricao TEXT NULL,
    tipo ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    categoria VARCHAR(50) DEFAULT 'geral',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_chave (chave),
    INDEX idx_categoria (categoria)
);

-- ============================================
-- TABELA DE SESSÕES JWT
-- ============================================
CREATE TABLE jwt_sessions (
    id VARCHAR(64) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_token_hash (token_hash)
);

-- ============================================
-- TABELA DE NOTIFICAÇÕES
-- ============================================
CREATE TABLE notificacoes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    mensagem TEXT NOT NULL,
    tipo ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    lida BOOLEAN DEFAULT FALSE,
    data_envio TIMESTAMP NULL,
    fcm_message_id VARCHAR(200) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    
    INDEX idx_user_id (user_id),
    INDEX idx_lida (lida),
    INDEX idx_tipo (tipo),
    INDEX idx_created_at (created_at)
);

-- ============================================
-- DADOS INICIAIS
-- ============================================

-- Usuário administrador padrão (senha: admin123)
INSERT INTO usuarios (id, nome, email, senha, cargo) VALUES 
('admin', 'Administrador', 'admin@controledefrotas.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrador');

-- Configurações iniciais
INSERT INTO configuracoes (chave, valor, descricao, tipo, categoria) VALUES
('app_name', 'Sistema Controle de Frotas', 'Nome da aplicação', 'string', 'geral'),
('max_upload_size', '10485760', 'Tamanho máximo de upload em bytes', 'integer', 'uploads'),
('google_maps_enabled', 'true', 'Habilitar integração com Google Maps', 'boolean', 'mapas'),
('backup_enabled', 'true', 'Habilitar backups automáticos', 'boolean', 'backup'),
('notification_enabled', 'true', 'Habilitar notificações push', 'boolean', 'notificacoes');

-- Alguns veículos de exemplo
INSERT INTO veiculos (modelo, placa, marca, ano_fabricacao, tipo, cor, status) VALUES
('Uno', 'ABC1234', 'Fiat', 2020, 'carro', 'Branco', 'ativo'),
('Strada', 'DEF5678', 'Fiat', 2021, 'caminhao', 'Prata', 'ativo'),
('Civic', 'GHI9012', 'Honda', 2022, 'carro', 'Preto', 'ativo');

-- Alguns motoristas de exemplo
INSERT INTO motoristas (nome, matricula, cpf, telefone, usuario, senha, status) VALUES
('João Silva', 'MOT001', '12345678900', '(11) 99999-9999', 'joao.silva', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ativo'),
('Maria Santos', 'MOT002', '98765432100', '(11) 88888-8888', 'maria.santos', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ativo');

-- ============================================
-- VIEWS ÚTEIS
-- ============================================

-- View para viagens com informações completas
CREATE VIEW v_viagens_completas AS
SELECT 
    v.*,
    u.nome as tecnico_nome,
    u.email as tecnico_email,
    ve.modelo as veiculo_modelo,
    ve.placa as veiculo_placa,
    ve.marca as veiculo_marca,
    TIMESTAMPDIFF(MINUTE, v.data_inicio, v.data_fim) as duracao_minutos,
    (v.km_final - v.km_inicial) as km_rodados,
    CASE 
        WHEN v.data_fim IS NULL THEN 'Em Andamento'
        ELSE 'Finalizada'
    END as status_viagem
FROM viagens v
LEFT JOIN usuarios u ON v.tecnico_id = u.id
LEFT JOIN veiculos ve ON v.veiculo_id = ve.id;

-- View para estatísticas rápidas
CREATE VIEW v_dashboard_stats AS
SELECT 
    (SELECT COUNT(*) FROM viagens WHERE data_fim IS NULL) as viagens_andamento,
    (SELECT COUNT(*) FROM viagens WHERE DATE(data_inicio) = CURDATE()) as viagens_hoje,
    (SELECT COUNT(*) FROM viagens WHERE WEEK(data_inicio) = WEEK(CURDATE())) as viagens_semana,
    (SELECT COUNT(*) FROM viagens WHERE MONTH(data_inicio) = MONTH(CURDATE())) as viagens_mes,
    (SELECT COUNT(*) FROM veiculos WHERE status = 'ativo') as veiculos_disponiveis,
    (SELECT COUNT(*) FROM veiculos WHERE status = 'em_uso') as veiculos_em_uso,
    (SELECT COUNT(*) FROM usuarios WHERE ativo = 1) as usuarios_ativos;

-- ============================================
-- PROCEDURES ÚTEIS
-- ============================================

DELIMITER //

-- Procedure para finalizar viagem
CREATE PROCEDURE sp_finalizar_viagem(
    IN p_viagem_id BIGINT,
    IN p_km_final INT,
    IN p_latitude_final DECIMAL(10, 8),
    IN p_longitude_final DECIMAL(11, 8),
    IN p_observacoes TEXT
)
BEGIN
    DECLARE v_veiculo_id BIGINT;
    
    -- Atualizar a viagem
    UPDATE viagens SET 
        data_fim = NOW(),
        km_final = p_km_final,
        latitude_final = p_latitude_final,
        longitude_final = p_longitude_final,
        observacoes = p_observacoes
    WHERE id = p_viagem_id;
    
    -- Liberar o veículo
    SELECT veiculo_id INTO v_veiculo_id FROM viagens WHERE id = p_viagem_id;
    UPDATE veiculos SET status = 'ativo' WHERE id = v_veiculo_id;
    
END //

-- Procedure para cleanup de logs antigos
CREATE PROCEDURE sp_cleanup_logs()
BEGIN
    DELETE FROM system_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    DELETE FROM jwt_sessions WHERE expires_at < NOW();
END //

DELIMITER ;

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger para atualizar status do veículo ao iniciar viagem
DELIMITER //
CREATE TRIGGER tr_viagem_insert 
AFTER INSERT ON viagens
FOR EACH ROW
BEGIN
    UPDATE veiculos SET status = 'em_uso' WHERE id = NEW.veiculo_id;
END //

-- Trigger para log de alterações em viagens
CREATE TRIGGER tr_viagem_update
AFTER UPDATE ON viagens
FOR EACH ROW
BEGIN
    INSERT INTO system_logs (level, message, context) VALUES (
        'info',
        'Viagem atualizada',
        JSON_OBJECT(
            'viagem_id', NEW.id,
            'tecnico_id', NEW.tecnico_id,
            'action', 'update'
        )
    );
END //

DELIMITER ;

-- ============================================
-- ÍNDICES ADICIONAIS PARA PERFORMANCE
-- ============================================

-- Índices compostos para consultas frequentes
CREATE INDEX idx_viagens_periodo_tecnico ON viagens(tecnico_id, data_inicio, data_fim);
CREATE INDEX idx_viagens_status_data ON viagens(data_fim, data_inicio);
CREATE INDEX idx_logs_cleanup ON system_logs(created_at, level);

-- ============================================
-- COMENTÁRIOS FINAIS
-- ============================================

-- Este schema foi projetado para:
-- 1. Compatibilidade total com o app Android
-- 2. Performance otimizada para consultas frequentes
-- 3. Integridade referencial
-- 4. Logs e auditoria completos
-- 5. Escalabilidade horizontal
-- 6. Backup e recovery eficientes

-- Versão do Schema: 1.0
-- Data de criação: 2025-06-12
-- Compatível com: MySQL 8.0+ 