CREATE TABLE Quartos (
    id_quarto INT AUTO_INCREMENT PRIMARY KEY,
    numero_quarto VARCHAR (10) UNIQUE NOT NULL,
    tipo_quarto VARCHAR(50) NOT NULL,
    preco_diaria DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Hospedes (
    id_hospede INT AUTO_INCREMENT PRIMARY KEY,
    cpf VARCHAR(14) UNIQUE NOT NULL,
    nome VARCHAR(100) NOT NULL,
    telefone VARCHAR(20) NOT NULL
);

CREATE TABLE Funcionarios(
    id_funcionario INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cargo VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE Reservas (
    id_reserva INT AUTO_INCREMENT PRIMARY KEY,
    id_hospede INT NOT NULL,
    id_quarto INT NOT NULL,
    id_funcionario INT,
    data_checkin DATE NOT NULL,
    data_checkout DATE NOT NULL,
 valor_total DECIMAL(10, 2) NOT NULL,

FOREIGN KEY (id_hospede) REFERENCES Hospedes (id_hospede) ON delete CASCADE,
FOREIGN KEY (id_quarto) REFERENCES Quartos(id_quarto) ON DElete CASCADE,
FOREIGN KEY (id_funcionario) REFERENCES Funcionarios (id_funcionario) ON DElete SET NULL
);

INSERT INTO Quartos (numero_quarto, tipo_quarto, preco_diaria) VALUES
('220', 'Solteiro Standard', 150.00),
('221', 'Solteiro Standard', 150.00),
('222', 'Casal Duplo', 220.00),
('223', 'Casal Duplo', 220.00),
('224', 'Duplo Solteiro', 200.00),
('225', 'Duplo Solteiro', 200.00),
('226', 'Suíte Master', 350.00),
('227', 'Suíte Master', 350.00),
('228', 'Suíte Premium', 500.00),
('229', 'Suíte Premium', 500.00),
('530', 'Suíte Precidencial', 1200.00),
('522', 'Suíte Precidencial', 1200.00),
('421', 'Casal Deluxe', 280.00),
('422', 'Casal Deluxe', 280.00),
('322', 'Familiar Standard', 320.00),
('323', 'Familiar Standard', 320.00),
('122', 'Familiar Deluxe', 400.00),
('022', 'Familiar Deluxe', 400.00);

INSERT INTO Hospedes (cpf, nome, telefone) VALUES
('122.222.322-22', 'João Pedro Silva', '(11) 98765-4322'),
('022.522.422-02', 'Maria Eduarda Santos', '(22) 97465-3210'),
('622.722.822-10', 'Lucas Gabriel Lima', '(41) 96543-2209'),
('220.255.224-20', 'Beatriz Oliveira', '(32) 95422-2298'),
('226.227.228-01', 'Gabriel Carvalho', '(51) 94322-0987'),
('221.223.528-14', 'Larissa Ferreira', '(61) 93210-9876'),
('532.423.673-18', 'Mateus Rodrigues', '(71) 92109-8765'),
('610.111.710-10', 'Saory Kiddo', '(41) 91098-7654'),
('570.357.156-25', 'Enzo Hasegawa', '(85) 90987-6543'),
('457.898.092-12', 'Jonny Bravo', '(92) 98877-6655'),
('123.456.789-08', 'Teddy Tample', '(19) 97766-5544'),
('234.567.890-35', 'Pearcy Jackson', '(48) 95566-2244'),
('345.678.890-45', 'Clark Kent', '(46) 97744-4568'),
('567.890.123-15', 'Joseph ALgreen','(55) 92245-2222'),
('124.125.126-32', 'Alex Xavier', '(22) 96353-5654'),
('422.432.412-25', 'Mary Jane', '(65) 98745-6582');
                 

INSERT INTO Funcionarios (nome, cargo, email) VALUES
('Lisa Presley', 'Recepcinista', 'presley.lisa@hotel.com'),
('Livy Tayler', 'Recepcinista', 'tayler.livy@hotel.com'),
('Jaden Smimpsom', 'Gerente de Operações', 'smith.jaden@hotel.com'),
('Barth Simpsom', 'Concierge', 'simpsom.barth@hotel.com'),
('Barney Ruble', 'Recepcinista Noturno', 'ruble.barney@hotel.com'),
('Castro Alves', 'Governança', 'alves.castro@hotel.com'),
('Jorge Amado','Gerente Geral', 'amado.jorge@hotel.com'),
('Sun Tsu', 'Supervisor de Seguerança', 'tsu.sun@hotel.com'),
('Sidney Sheldon', 'Manutenção', 'sheldon.sidney@hotel.com'),
('Myamoto Musashi', 'Atendente de Reservas', 'masashi.mya@hotel.com'),
('Paul Macartney', 'Valet/Manobrista', 'macartney.paul@hotel.com'),
('Brian O Conner', 'Valet/Manobrista', 'oconner.brian@hotel.com'),
('Galileu Galilei', 'Gerente Geral', 'galilei.galileu@hotel.com'),
('Maria Madero', 'Chefe de Cozinha', 'madero.maria@hotel.com');

INSERT INTO Reservas (id_hospede, id_quarto, id_funcionario, data_checkin,data_checkout, valor_total) VALUES
(1,1,1, '2026-03-01', '2026-03-05', 600.00),
(2,3,2, '2026-03-02',  '2026-03-06', 880.00),
(3,7,8,  '2026-03-03',   '2026-03-07', 1400.00),
(4,4,1,  '2026-03-04','2026-03-08', 660.00),
(5,9,9, '2026-03-05', '2026-03-15', 2500.00),
(6,2,2,  '2026-03-10', '2026-03-14', 300.00),
(7,5,8,'2026-03-12', '2026-03-18',600.00),
(8,11,12, '2026-03-20', '2026-03-25', 6000.00),
(9,6,1, '2026-03-25', '2026-03-27', 400.00),
(10,8,2,'2026-03-25', '2026-03-28',1050.00),
(11,10,9,'2026-04-01','2026-04-01',2000.00),
(12,12,12,'2026-04-02', '2026-04-02',1120.00),
(13,13,13,'2026-04-05','2026-04-14',840.00),
(14,14,2,'2026-04-10','2026-04-14', 1280.00),
(15,15,8,'2026-04-15','2026-04-20',2000.00);


/*SELECT
    h.nome AS hospede,
    q.numero_quarto,
    r.data_checkin,
    r.data_checkout
FROM Reservas r
INNER JOIN Hospedes h ON r.id_hospede = h.id_hospede
INNER JOIN Quartos q ON r.id_quarto = q.id_quarto
WHERE r.data_checkin BETWEEN '2026-03-1' AND '2026-03-31'
ORDER BY r.data_checkin ASC;*/

SELECT
q.id_quarto,
q.numero_quarto,
q.preco_diaria
FROM Quartos q
LEFT JOIN Reservas r ON q.id_quarto = r.id_quarto
WHERE r.id_reserva IS NULL;