CREATE DATABASE IF NOT EXISTS `xcs`;
USE `xcs`;

DROP TABLE IF EXISTS `pet_vet`;
DROP TABLE IF EXISTS `vaccination`;
DROP TABLE IF EXISTS `vaccine`;
DROP TABLE IF EXISTS `identifier`;
DROP TABLE IF EXISTS `pet`;
DROP TABLE IF EXISTS `user`;

--
-- Table structure for table `User`
--
CREATE TABLE `user` (
  `role` varchar(5) NOT NULL,
  `login` varchar(100) NOT NULL,
  `password` varchar(128) NOT NULL,
  PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Pet`
--
CREATE TABLE `pet` (
  `id` int NOT NULL AUTO_INCREMENT,
  `animal` varchar(4) NOT NULL,
  `birth` datetime NOT NULL,
  `name` varchar(100) NOT NULL,
  `owner` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Pet_Owner` (`owner`),
  CONSTRAINT `FK_Pet_Owner_login` FOREIGN KEY (`owner`) REFERENCES `user` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `pet_vet`
--
CREATE TABLE `pet_vet` (
  `pet_id` int NOT NULL,
  `vet_login` varchar(100) NOT NULL,
  PRIMARY KEY (`pet_id`,`vet_login`),
  CONSTRAINT `FK_PetVet_Pet` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`),
  CONSTRAINT `FK_PetVet_Vet` FOREIGN KEY (`vet_login`) REFERENCES `user` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `identifier`
--
CREATE TABLE `identifier` (
  `value` varchar(32) NOT NULL,
  `type` varchar(4) NOT NULL,
  `pet_id` int DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `FK_Identifier_Pet` (`pet_id`),
  CONSTRAINT `FK_Identifier_Pet` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `vaccine`
--
CREATE TABLE `vaccine` (
  `VACCINE_TYPE` varchar(31) NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `doses` int DEFAULT NULL,
  `periode` int DEFAULT NULL,
  `periodicType` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `vaccination`
--
CREATE TABLE `vaccination` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `pet_id` int DEFAULT NULL,
  `vaccine_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Vacc_Pet` (`pet_id`),
  KEY `FK_Vacc_Vaccine` (`vaccine_id`),
  CONSTRAINT `FK_Vacc_Pet` FOREIGN KEY (`pet_id`) REFERENCES `pet` (`id`),
  CONSTRAINT `FK_Vacc_Vaccine` FOREIGN KEY (`vaccine_id`) REFERENCES `vaccine` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Users
--
INSERT INTO `user` (role, login, password) VALUES
('ADMIN','jose','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner1','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner2','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner3','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner4','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner5','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner6','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner7','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner8','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner9','a3f6f4b40b24e2fd61f08923ed452f34'),
('OWNER','owner10','a3f6f4b40b24e2fd61f08923ed452f34D'),
('VET','vet1','a3f6f4b40b24e2fd61f08923ed452f34'),
('VET','vet2','a3f6f4b40b24e2fd61f08923ed452f34'),
('VET','vet3','a3f6f4b40b24e2fd61f08923ed452f34');

--
-- Pets
--
INSERT INTO `pet` (animal,birth,name,owner) VALUES
('DOG','2020-01-01 12:00:00','Bobby','owner1'),
('CAT','2020-02-01 12:00:00','Mimi','owner1'),
('BIRD','2020-03-01 12:00:00','Paco','owner1'),
('DOG','2020-04-01 12:00:00','Rocky','owner2'),
('CAT','2020-05-01 12:00:00','Luna','owner2'),
('BIRD','2020-06-01 12:00:00','Kiwi','owner2'),
('DOG','2020-07-01 12:00:00','Max','owner3'),
('CAT','2020-08-01 12:00:00','Nala','owner3'),
('BIRD','2020-09-01 12:00:00','Sunny','owner3'),
('DOG','2020-10-01 12:00:00','Leo','owner4'),
('CAT','2020-11-01 12:00:00','Lola','owner4'),
('BIRD','2020-12-01 12:00:00','Tweety','owner4'),
('DOG','2021-01-01 12:00:00','Toby','owner5'),
('CAT','2021-02-01 12:00:00','Bella','owner5'),
('BIRD','2021-03-01 12:00:00','Angel','owner5');

--
-- Identifiers
--
INSERT INTO `identifier` (value,type,pet_id) VALUES
('ID00001','TAG',1),
('ID00002','CHIP',1),
('ID00003','TAG',2),
('ID00004','CHIP',2),
('ID00005','TAG',3),
('ID00006','CHIP',3),
('ID00007','TAG',4),
('ID00008','CHIP',4),
('ID00009','TAG',5),
('ID00010','CHIP',5),
('ID00011','TAG',6),
('ID00012','CHIP',6),
('ID00013','TAG',7),
('ID00014','CHIP',7),
('ID00015','TAG',8),
('ID00016','CHIP',8),
('ID00017','TAG',9),
('ID00018','CHIP',9);


--
-- Pet-Vet relations
--
INSERT INTO `pet_vet` (pet_id,vet_login) VALUES
(1,'vet1'),(2,'vet1'),(3,'vet1'),
(4,'vet2'),(5,'vet2'),(6,'vet2'),
(7,'vet3'),(8,'vet3'),(9,'vet3');

--
-- Vaccines
--
INSERT INTO `vaccine` (VACCINE_TYPE,name,doses,periode,periodicType) VALUES
('MONODOSE','VacunaUno',NULL,NULL,NULL),
('MULTIDOSE','VacunaPeriodica',20,NULL,NULL),
('PERIODIC','Periodica',NULL,7,'DAYS');

--
-- Vaccinations
--
INSERT INTO `vaccination` (date,pet_id,vaccine_id) VALUES
('2025-10-01',1,1),
('2025-10-02',2,2),
('2025-10-03',3,3),
('2025-10-04',4,1),
('2025-10-05',5,2),
('2025-10-06',6,3),
('2025-10-07',7,1),
('2025-10-08',8,2),
('2025-10-09',9,3);