CREATE TABLE `employees` (
	`ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`FirstName` VARCHAR(30) NOT NULL,
	`LastName` VARCHAR(5) NOT NULL,
	`PESEL` INT(11) NOT NULL
);

CREATE TABLE `regions` (
	`ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`Name` VARCHAR(30)
	);

CREATE TABLE `agency` (
	`ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`Name` VARCHAR(50) NOT NULL,
	`RegionId` INT(11) NOT NULL,
	 Provision DECIMAL(3,2) NOT NULL,
	 FOREIGN KEY (RegionId) REFERENCES regions(ID)
);

CREATE TABLE `agents` (
	`ID` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`EmployeeId` INT(11) NOT NULL,
	`AgencyId` INT(11) NOT NULL,
	`Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (EmployeeId) REFERENCES employees(ID),
	FOREIGN KEY (AgencyId) REFERENCES agency(ID)
	);

CREATE TABLE `products` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`Name` VARCHAR(255) NOT NULL,
	`Price` DECIMAL NOT NULL
);

CREATE  TABLE `clients` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`Name` VARCHAR(50) NOT NULL
);

CREATE TABLE `transactions` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`ProductId` INT(11) NOT NULL,
	`ClientId` INT(11) NOT NULL,
	`AgentId` INT(11) NOT NULL,
	`Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (ProductId) REFERENCES products(ID),
	FOREIGN KEY (AgentId) REFERENCES agents(ID),
	FOREIGN KEY (ClientId) REFERENCES clients(ID)
);




INSERT INTO `regions` (`Name`) VALUES
	('pomorskie'),
	('kujawsko-pomorskie'),
	('lubelskie'),
	('lubuskie'),
	('warmińsko-mazurskie');


INSERT INTO `agency` (`Name`, `RegionId`, `Provision`) VALUES
	('Ubezpieka', 1, 20.00),
	('Bezpieczne ubezpieczenie', 3, 15.00),
	('NoRisk', 4, 30.00),
	('Ub3zpiecz0ny', 2, 25.50),
	('Ubyzpieczyciele', 5, 27.50);

INSERT INTO `employees` (`FirstName`, `LastName`, `PESEL`) VALUES
	(`Marian`, `Pazdzioch`, `53121042957`),
	(`Ferdynand`, `Kiepski`, `54111542957`),
	(`Halina`, `Kiepska`, `581210420679),
	(`Waldemar`, `Kiepski`, `78050691334`),
	(`Arnold`, `Boczek`, `65072412354`);

INSERT INTO `agents` (`EmployeeId`, `AgencyId`) VALUES
	(1, 3),
	(2,5),
	(3,1),
	(4,2),
	(5,4);

INSERT INTO `products` (`Name`, `Price`) VALUES
	(`Ubezpieczenie OC`, 850.00),
	(`Ubezpieczenie AC`, 1850.00),
	(`Ubezpieczenie OC + AC`, 2500.00),
	(`Ubezpieczenie NNW`, 100.00),
	(`Ubezpieczenie ASS`, 250.00);

INSERT INTO `clients` (`Name`) VALUES
	('Mariola Testowa'),
	('Genowefa Pigwa'),
	('Teodor Motor'),
	('Mariusz Kajetanowicz'),
	('Oliwia Oliwska');

INSERT INTO `transactions` (`ProductId`, `ClientId`, `AgentId`) VALUES
	(1, 3, 5),
	(2, 1, 2),
	(3, 2, 1),
	(3, 1, 3),
	(4, 4, 4),
	(5, 4, 2),
	(5, 2, 5),
	(2, 3, 4);




DROP TABLE IF EXISTS `regions`;
DROP TABLE IF EXISTS `agency`;
DROP TABLE IF EXISTS `employees`;
DROP TABLE IF EXISTS `agents`;
DROP TABLE IF EXISTS `clients`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `transactions`;


SELECT * FROM `transactions` WHERE `Client` != 2;
SELECT * FROM `products` WHERE `Price` > 800.00;

SELECT count(*) FROM `transactions` GROUP BY `AgentId`;
SELECT SUM(*) FROM `transactions`;

SELECT * FROM `employees` ORDER BY `LastName` ASC;
SELECT * FROM `transactions` ORDER BY `ID` DESC;

SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
	FROM `transactions`
	LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
	LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
	LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;

CREATE VIEW `widoczek` AS SELECT `transactions`.`ID` as idKlienta, `transactions`.`AgentId` as idAgenta, `products`.`Prize` as cena, `agency`.`Provision` as prowizja
	FROM `transactions`
	LEFT JOIN `products` ON `products`.`ID` = `transactions`.`ProductId`
	LEFT JOIN `agents` ON `agents`.`ID` = `transactions`.`AgentId`
	LEFT JOIN `agency` ON `agency`.`ID` = `agents`.`AgencyId`;