CREATE TABLE IF NOT EXISTS `raca` (
  `idraca` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(128) NOT NULL,
  `descricao` TEXT NULL,
  PRIMARY KEY (`idraca`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `adotante` (
  `idadotante` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(128) NOT NULL,
  `telefone` VARCHAR(20),
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`idadotante`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `pet` (
  `idpet` INT NOT NULL AUTO_INCREMENT,
  `foto` VARCHAR(256) NOT NULL,
  `data_nascimento` DATE NOT NULL,
  `data_cadastro` DATE NOT NULL DEFAULT (CURRENT_DATE),
  `bio` TEXT NOT NULL,
  `data_adocao` DATE NULL,
  `status_adocao` ENUM('adotado', 'em adoção', 'não adotado') NOT NULL DEFAULT 'não adotado',
  `raca_idraca` INT NOT NULL,
  `adotante_idadotante` INT NULL,
  `nome` VARCHAR(68) NOT NULL,
  PRIMARY KEY (`idpet`),
  CONSTRAINT `fk_pet_raca` FOREIGN KEY (`raca_idraca`) REFERENCES `raca` (`idraca`),
  CONSTRAINT `fk_pet_adotante1` FOREIGN KEY (`adotante_idadotante`) REFERENCES `adotante` (`idadotante`)
) ENGINE = InnoDB;

INSERT INTO raca (nome) VALUES ('Labrador'), ('Poodle'), ('Vira-lata'), ('Bulldog'), ('Beagle');

INSERT INTO adotante (nome, telefone, email) VALUES
('Carlos Silva', '71999990001', 'carlos.silva@email.com'),
('Mariana Souza', '71999990002', 'mariana.souza@email.com'),
('João Pereira', '71999990003', 'joao.pereira@email.com'),
('Ana Oliveira', '71999990004', 'ana.oliveira@email.com'),
('Pedro Santos', '71999990005', 'pedro.santos@email.com');

INSERT INTO pet (nome, foto, data_nascimento, bio, raca_idraca, status_adocao, data_adocao, adotante_idadotante) 
VALUES
('Lab', 'labrador1.jpg', '2022-03-10', 'Labrador muito brincalhão e amigável.', 1, 'adotado', '2026-03-01', 1),
('Po', 'poodle1.jpg', '2021-07-15', 'Poodle pequeno e muito carinhoso.', 2, 'não adotado', NULL, NULL),
('VO', 'viralata1.jpg', '2023-01-05', 'Vira-lata resgatado da rua, muito dócil.', 3, 'em adoção', '2026-03-07', 2),
('La', 'labrador2.jpg', '2020-11-20', 'Labrador adulto, muito calmo.', 1, 'adotado', '2026-03-01', 1),
('Vi', 'viralata2.jpg', '2022-08-30', 'Filhote de vira-lata muito energético.', 3, 'não adotado', NULL, NULL);


UPDATE pet SET nome = 'Dora' WHERE idpet = 1;
UPDATE pet SET nome = 'Plínio' WHERE idpet = 2;
UPDATE pet SET nome = 'Belinha' WHERE idpet = 3;
UPDATE pet SET nome = 'Kurt' WHERE idpet = 4;
UPDATE pet SET nome = 'Jack' WHERE idpet = 5;


SELECT COUNT(*) AS total_adotados_no_periodo 
FROM pet 
WHERE status_adocao = 'adotado' 
  AND data_adocao BETWEEN '2026-03-01' AND '2026-03-05';


SELECT 
    P.foto, 
    P.nome AS nome_pet, 
    TIMESTAMPDIFF(YEAR, P.data_nascimento, CURRENT_DATE) AS idade_aproximada_anos, 
    R.nome AS raca
FROM pet P
INNER JOIN raca R ON R.idraca = P.raca_idraca;


SELECT foto, nome, bio 
FROM pet 
WHERE idpet = 1;


SELECT status_adocao, COUNT(*) AS quantidade 
FROM pet 
GROUP BY status_adocao;


SELECT 
    P.nome AS nome_pet,
    R.nome AS raca,
    P.status_adocao,
    A.nome AS nome_adotante
FROM pet P
INNER JOIN raca R ON R.idraca = P.raca_idraca
LEFT JOIN adotante A ON A.idadotante = P.adotante_idadotante;