CREATE DATABASE `daaexample`;
USE `daaexample`;

CREATE TABLE `daaexample`.`people` (
	`id` int NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	`surname` varchar(100) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `daaexample`.`users` (
	`login` varchar(100) NOT NULL,
	`password` varchar(64) NOT NULL,
	PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `daaexample`.`pet` (
	`id` int NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	`owner` int NOT NULL,
	`type` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

GRANT ALL ON `daaexample`.* TO 'daa'@'localhost' IDENTIFIED BY 'daa';


INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (1,'Antón','Pérez');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (2,'Manuel','Martínez');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (3,'Laura','Reboredo');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (4,'Perico','Palotes');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (5,'Ana','María');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (6,'María','Nuevo');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (7,'Alba','Fernández');
INSERT INTO `daaexample`.`people` (`id`,`name`,`surname`) VALUES (8,'Asunción','Jiménez');

INSERT INTO `daaexample`.`pet` (`id`,`name`,`owner`,`type`) VALUES (1,'Bobby', 1, "Perro");
INSERT INTO `daaexample`.`pet` (`id`,`name`,`owner`,`type`) VALUES (2,'Cookie', 1, "Gato");
INSERT INTO `daaexample`.`pet` (`id`,`name`,`owner`,`type`) VALUES (3,'Poseidon', 3, "Perro");
INSERT INTO `daaexample`.`pet` (`id`,`name`,`owner`,`type`) VALUES (4,'Athenea', 3, "Gallina");


-- The password for each user is its login suffixed with "pass". For example, user "admin" has the password "adminpass".
INSERT INTO `daaexample`.`users` (`login`,`password`) VALUES ('admin', '43f413b773f7d0cfad0e8e6529ec1249ce71e8697919eab30d82d800a3986b70');
INSERT INTO `daaexample`.`users` (`login`,`password`) VALUES ('normal', '688f21dd2d65970f174e2c9d35159250a8a23e27585452683db8c5d10b586336');

ALTER TABLE `pet`
  ADD CONSTRAINT `fkowner` FOREIGN KEY (`owner`) REFERENCES `people` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;