setup-database.sql

-- ========================================
-- SISTEMA DE RESERVA DE LABORATÓRIOS
-- Script de Inicialização do Banco
-- ========================================

-- 1. CRIAR BANCO DE DADOS
DROP DATABASE IF EXISTS laboratory_reservation;
CREATE DATABASE laboratory_reservation
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE laboratory_reservation;

-- 2. CRIAR TABELAS

-- Tabela de usuários
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('student', 'teacher', 'admin') DEFAULT 'student',
  registration_number VARCHAR(20) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_registration (registration_number),
  INDEX idx_role (role)
);

-- Tabela de laboratórios
CREATE TABLE laboratories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  capacity INT NOT NULL DEFAULT 1,
  location VARCHAR(100) NOT NULL,
  status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_name (name),
  INDEX idx_status (status),
  INDEX idx_location (location)
);

-- Tabela de equipamentos
CREATE TABLE equipment (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  laboratory_id INT NOT NULL,
  status ENUM('available', 'unavailable', 'maintenance') DEFAULT 'available',
  serial_number VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (laboratory_id) REFERENCES laboratories(id) ON DELETE CASCADE,
  INDEX idx_laboratory (laboratory_id),
  INDEX idx_status (status),
  INDEX idx_serial (serial_number)
);

-- Tabela de reservas
CREATE TABLE reservations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  laboratory_id INT NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  purpose TEXT NOT NULL,
  status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (laboratory_id) REFERENCES laboratories(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_laboratory (laboratory_id),
  INDEX idx_time (start_time, end_time),
  INDEX idx_status (status),
  CONSTRAINT chk_time CHECK (end_time > start_time)
);

-- Tabela de relação entre reservas e equipamentos
CREATE TABLE reservation_equipment (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reservation_id INT NOT NULL,
  equipment_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
  FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE,
  UNIQUE KEY unique_reservation_equipment (reservation_id, equipment_id),
  INDEX idx_reservation (reservation_id),
  INDEX idx_equipment (equipment_id)
);

-- 3. INSERIR DADOS INICIAIS

-- Usuário administrador padrão (senha: password)
INSERT INTO users (name, email, password, role, registration_number) VALUES
('Administrador do Sistema', 'admin@faculdade.edu', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'ADM001'),
('Professor João Silva', 'joao.silva@faculdade.edu', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'teacher', 'PROF001'),
('Maria Santos', 'maria.santos@faculdade.edu', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'student', 'EST001');

-- Laboratórios de exemplo
INSERT INTO laboratories (name, description, capacity, location, status) VALUES
('Laboratório de Informática 1', 'Laboratório com 30 computadores Dell e projetor multimídia', 30, 'Bloco A - Sala 101', 'active'),
('Laboratório de Química Geral', 'Laboratório para experimentos químicos básicos e analíticos', 20, 'Bloco B - Sala 205', 'active'),
('Laboratório de Física Experimental', 'Laboratório de física com equipamentos de medição', 25, 'Bloco C - Sala 301', 'active'),
('Laboratório de Eletrônica', 'Laboratório com bancadas para desenvolvimento de circuitos', 15, 'Bloco A - Sala 203', 'active'),
('Laboratório de Biologia', 'Laboratório com microscópios e equipamentos de biologia', 20, 'Bloco D - Sala 102', 'maintenance');

-- Equipamentos de exemplo
INSERT INTO equipment (name, description, laboratory_id, status, serial_number) VALUES
-- Lab Informática 1 (id: 1)
('Computador Dell OptiPlex 7090', 'Computador desktop Intel i5 para desenvolvimento', 1, 'available', 'DELL001'),
('Computador Dell OptiPlex 7090', 'Computador desktop Intel i5 para desenvolvimento', 1, 'available', 'DELL002'),
('Projetor Epson PowerLite', 'Projetor multimídia 3000 lumens para apresentações', 1, 'available', 'EPSON001'),
('Switch de Rede 24 portas', 'Switch gerenciável para conexão de rede local', 1, 'available', 'SW001'),

-- Lab Química (id: 2)
('Balança Analítica Shimadzu', 'Balança de precisão 0.1mg para experimentos', 2, 'available', 'BAL001'),
('Capela de Exaustão', 'Sistema de ventilação para segurança química', 2, 'available', 'CAP001'),
('Aquecedor Elétrico', 'Aquecedor com controle de temperatura para reações', 2, 'available', 'AQ001'),
('Espectrofotômetro UV-Vis', 'Equipamento para análise espectroscópica', 2, 'available', 'ESP001'),

-- Lab Física (id: 3)
('Osciloscópio Digital Tektronix', 'Equipamento para medição de sinais elétricos', 3, 'available', 'OSC001'),
('Gerador de Função', 'Gerador de sinais para experimentos elétricos', 3, 'available', 'GER001'),
('Multímetro Digital Fluke', 'Instrumento de medição elétrica de precisão', 3, 'available', 'MUL001'),
('Paquímetro Digital', 'Instrumento de medição linear de alta precisão', 3, 'available', 'PAQ001'),

-- Lab Eletrônica (id: 4)
('Fonte de Alimentação Regulável', 'Fonte 0-30V/5A para alimentação de circuitos', 4, 'available', 'FONT001'),
('Ferro de Solda com Estação', 'Ferro de solda com controle de temperatura', 4, 'available', 'SOL001'),
('Bancada de Eletrônica', 'Mesa com instrumentos integrados para eletrônica', 4, 'available', 'BANC001'),
('Protoboard 1660 pontos', 'Placa para montagem de protótipos', 4, 'available', 'PROTO001');

-- Reservas de exemplo (algumas no passado, algumas futuras)
INSERT INTO reservations (user_id, laboratory_id, start_time, end_time, purpose, status) VALUES
(2, 1, '2025-05-28 08:00:00', '2025-05-28 10:00:00', 'Aula de Programação Web - Desenvolvimento de APIs', 'active'),
(3, 2, '2025-05-28 14:00:00', '2025-05-28 16:00:00', 'Experimento de Titulação Ácido-Base', 'active'),
(2, 3, '2025-05-29 09:00:00', '2025-05-29 11:00:00', 'Aula de Circuitos Elétricos - Medições com Osciloscópio', 'active'),
(3, 1, '2025-05-30 13:00:00', '2025-05-30 15:00:00', 'Desenvolvimento de Projeto Final TCC', 'active');

-- Relacionar reservas com equipamentos específicos
INSERT INTO reservation_equipment (reservation_id, equipment_id) VALUES
-- Reserva 1 (Lab Informática): usar computadores e projetor
(1, 1), (1, 2), (1, 3),
-- Reserva 2 (Lab Química): usar balança e aquecedor
(2, 5), (2, 7),
-- Reserva 3 (Lab Física): usar osciloscópio e gerador
(3, 9), (3, 10),
-- Reserva 4 (Lab Informática): usar um computador
(4, 1);

-- 4. CRIAR VIEWS PARA RELATÓRIOS

-- View de reservas ativas com informações completas
CREATE VIEW v_active_reservations AS
SELECT 
  r.id,
  r.start_time,
  r.end_time,
  r.purpose,
  r.status,
  u.name as user_name,
  u.email as user_email,
  u.registration_number,
  u.role as user_role,
  l.name as laboratory_name,
  l.location as laboratory_location,
  l.capacity as laboratory_capacity,
  TIMESTAMPDIFF(HOUR, r.start_time, r.end_time) as duration_hours
FROM reservations r
JOIN users u ON r.user_id = u.id
JOIN laboratories l ON r.laboratory_id = l.id
WHERE r.status = 'active' AND r.end_time > NOW()
ORDER BY r.start_time;

-- View de equipamentos com informações do laboratório
CREATE VIEW v_equipment_details AS
SELECT 
  e.id,
  e.name,
  e.description,
  e.status,
  e.serial_number,
  e.created_at,
  l.name as laboratory_name,
  l.location as laboratory_location,
  l.id as laboratory_id
FROM equipment e
JOIN laboratories l ON e.laboratory_id = l.id
ORDER BY l.name, e.name;

-- View de estatísticas de uso por laboratório
CREATE VIEW v_laboratory_usage_stats AS
SELECT 
  l.id,
  l.name,
  l.location,
  l.capacity,
  l.status,
  COUNT(r.id) as total_reservations,
  COUNT(CASE WHEN r.status = 'active' AND r.end_time > NOW() THEN 1 END) as active_reservations,
  COUNT(CASE WHEN r.status = 'completed' THEN 1 END) as completed_reservations,
  COUNT(CASE WHEN r.status = 'cancelled' THEN 1 END) as cancelled_reservations,
  COALESCE(SUM(TIMESTAMPDIFF(HOUR, r.start_time, r.end_time)), 0) as total_hours_reserved
FROM laboratories l
LEFT JOIN reservations r ON l.id = r.laboratory_id
GROUP BY l.id, l.name, l.location, l.capacity, l.status
ORDER BY total_reservations DESC;

-- 5. VERIFICAR CRIAÇÃO

SELECT 'Database setup completed successfully!' as message;

-- Mostrar estatísticas dos dados inseridos
SELECT 
  (SELECT COUNT(*) FROM users) as total_users,
  (SELECT COUNT(*) FROM laboratories) as total_laboratories,
  (SELECT COUNT(*) FROM equipment) as total_equipment,
  (SELECT COUNT(*) FROM reservations) as total_reservations;

-- Mostrar algumas reservas ativas
SELECT * FROM v_active_reservations LIMIT 5;