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

-- Drop tables in reverse order of dependencies
DROP TABLE IF EXISTS `Vaccination`;
DROP TABLE IF EXISTS `Vaccine`;
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(32) NOT NULL,
  PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `Vaccine`
--
CREATE TABLE `Vaccine` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `owner` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Vaccine_Owner` (`owner`),
  CONSTRAINT `FK_Vaccine_Owner_login` FOREIGN KEY (`owner`) REFERENCES `User` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Vaccination`
--
CREATE TABLE `Vaccination` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `owner` varchar(100) NOT NULL,
  `petId` int(11) NOT NULL,
  `vaccineId` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Vaccination_Pet` (`petId`),
  KEY `FK_Vaccination_Vaccine` (`vaccineId`),
  CONSTRAINT `FK_Vaccination_Pet_id` FOREIGN KEY (`petId`) REFERENCES `Pet` (`id`),
  CONSTRAINT `FK_Vaccination_Vaccine_id` FOREIGN KEY (`vaccineId`) REFERENCES `Vaccine` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- User creation for test DB
--
CREATE USER IF NOT EXISTS xcs@localhost IDENTIFIED BY 'xcs';
GRANT ALL PRIVILEGES ON xcs_test.* TO xcs@localhost;
FLUSH PRIVILEGES;