DFNBuYmE

1DROP TABLE IF EXISTS invoices;
2DROP TABLE IF EXISTS repairsstate;
3DROP TABLE IF EXISTS repairs;
4DROP TABLE IF EXISTS cars;
5DROP TABLE IF EXISTS carmodels;
6DROP TABLE IF EXISTS carmarks;
7DROP TABLE IF EXISTS users;
8DROP TABLE IF EXISTS states;
9DROP TABLE IF EXISTS roles;
10DROP TABLE IF EXISTS Address;
11
12CREATE TABLE IF NOT EXISTS Address (
13	addressId INTEGER PRIMARY KEY AUTO_INCREMENT,
14	street varchar(30),
15	homeNumber INT,
16	postCode INT,
17	city VARCHAR(30)
18);
19
20CREATE TABLE IF NOT EXISTS Roles (
21	roleId INTEGER PRIMARY KEY AUTO_INCREMENT,
22	roleName VARCHAR(40)
23);
24
25CREATE TABLE IF NOT EXISTS Users (
26	userId INTEGER PRIMARY KEY AUTO_INCREMENT,
27	roleId INT,
28	name VARCHAR(30),
29	surname VARCHAR(30),
30	addressId INT,
31	login VARCHAR(30),
32	password VARCHAR(30),
33		FOREIGN KEY (addressId) REFERENCES Address(addressId),
34		FOREIGN KEY (roleId) REFERENCES Roles(roleId)
35);
36
37
38CREATE TABLE IF NOT EXISTS CarMarks (
39	carMarkId INTEGER PRIMARY KEY AUTO_INCREMENT,
40	markName VARCHAR(40)
41);
42
43CREATE TABLE IF NOT EXISTS CarModels (
44	carModelId INTEGER PRIMARY KEY AUTO_INCREMENT,
45	modelName VARCHAR(40),
46	carMarkId INT,
47		FOREIGN KEY(carMarkId) REFERENCES CarMarks(carMarkId)
48);
49
50CREATE TABLE IF NOT EXISTS Cars (
51	carId INTEGER PRIMARY KEY AUTO_INCREMENT,
52	carModelId INT,
53	course VARCHAR(30),
54	userId INT,
55		FOREIGN KEY (userId) REFERENCES Users(userId),
56		FOREIGN KEY (carModelId) REFERENCES CarModels(carModelId)
57);
58
59CREATE TABLE IF NOT EXISTS Repairs (
60	repairId INTEGER PRIMARY KEY AUTO_INCREMENT,
61	repairCauses VARCHAR(100),
62	carId INT,
63	price INT,
64		FOREIGN KEY (carId) REFERENCES Cars(carId)
65);
66
67CREATE TABLE IF NOT EXISTS States (
68	stateId INTEGER PRIMARY KEY AUTO_INCREMENT,
69	name VARCHAR(30)
70);
71
72CREATE TABLE IF NOT EXISTS RepairsState (
73	repairStateId INTEGER PRIMARY KEY AUTO_INCREMENT,
74	repairId INT,
75	dateOfStart DATE,
76	dateOfEnd DATE,
77	stateId INT,
78    userId INT,
79		FOREIGN KEY (stateId) REFERENCES States(stateId),
80		FOREIGN KEY (repairId) REFERENCES Repairs(repairId)
81);
82
83CREATE TABLE IF NOT EXISTS Invoices (
84	invoiceId INTEGER PRIMARY KEY AUTO_INCREMENT,
85	repairId INT,
86	invoiceNumber VARCHAR(30),
87	dateOfMake DATE,
88	description VARCHAR(100),
89		FOREIGN KEY (repairId) REFERENCES Repairs(repairId)
90);
91
92
93---------------------------
94INSERT INTO Roles (roleName) VALUES ("Klient");
95INSERT INTO Roles (roleName) VALUES ("Pracownik");
96INSERT INTO Roles (roleName) VALUES ("Administrator");
97
98
99INSERT INTO Address (street, homeNumber, postCode, city) VALUES ("aaa", 12, 1234, "xxx");
100INSERT INTO Address (street, homeNumber, postCode, city) VALUES ("bbbb", 123, 123321, "ssd");
101INSERT INTO Address (street, homeNumber, postCode, city) VALUES ("asddsadsa", 122, 1234, "aaaa");
102INSERT INTO Address (street, homeNumber, postCode, city) VALUES ("qqqqq", 112, 1233, "xass");
103
104INSERT INTO Users (roleId, `name`, surname, addressId, login, `password`) VALUES (1, "Marcin", "R", 1, "mr", "mr123");
105INSERT INTO Users (roleId, `name`, surname, addressId, login, `password`) VALUES (1, "Jakub", "K", 2, "jk", "jk123");
106INSERT INTO Users (roleId, `name`, surname, addressId, login, `password`) VALUES (2, "Pawel", "Ra", 3, "pra", "pra123");
107INSERT INTO Users (roleId, `name`, surname, addressId, login, `password`) VALUES (3, "Mariusz", "Rz", 4, "mrz", "mrz123");
108
109INSERT INTO CarMarks (markName) VALUES ('Mazda');
110INSERT INTO CarMarks (markName) VALUES ('Fiat');
111INSERT INTO CarMarks (markName) VALUES ('Lamborghini');
112INSERT INTO CarMarks (markName) VALUES ('Skoda');
113
114INSERT INTO CarModels (modelName, carMarkId) VALUES ('6', 1);
115INSERT INTO CarModels (modelName, carMarkId) VALUES ('Punto', 2);
116INSERT INTO CarModels (modelName, carMarkId) VALUES ('Gallardo', 3);
117INSERT INTO CarModels (modelName, carMarkId) VALUES ('Fabia', 4);
118
119INSERT INTO Cars (carModelId, course, userId) VALUES (1, 123000, 1);
120INSERT INTO Cars (carModelId, course, userId) VALUES (3, 3000, 2);
121
122INSERT INTO Repairs (repairCauses, carId, price) VALUES ("Wymiana oleju", 1, 150);
123INSERT INTO Repairs (repairCauses, carId, price) VALUES ("Sprzeglo", 2, 2500);
124
125INSERT INTO States (`name`) VALUES ('Nierozpoczete');
126INSERT INTO States (`name`) VALUES ('W trakcie');
127INSERT INTO States (`name`) VALUES ('Zakonczone');
128
129INSERT INTO RepairsState (repairId, dateOfStart, DateOfEnd, stateId, userId) VALUES (1, "04.04.2019", "05.04.2019", 3, 1);
130INSERT INTO RepairsState (repairId, dateOfStart, DateOfEnd, stateId, userId) VALUES (1, "04.04.2019", NULL, 2, 1);
131
132SELECT * FROM Invoices;
133
134INSERT INTO Invoices (repairId, invoiceNumber, dateOfMake, `description`) VALUES (1, "1", "05.04.2019", "Faktura Vat - wymiana sprzegla");
135
136SELECT * FROM users