ShopBase / localhost

CREATE Database ShopBase;
USE ShopBase
 
--Tabeli loomine brands
Create table brands(
brand_id int primary key identity(1,1),
brand_name varchar(20)
);
INSERT INTO brands (brand_name) 
VALUES ('Zara'),
('Nike'),
('Adidas'),
('Gucci'),
('Chanel');
SELECT * FROM brands

--Tabeli loomine categories
Create table categories(
category_id int primary key identity(1,1),
category_name varchar(20)
);
INSERT INTO categories (category_name)
VALUES ('Ülerõivad'),
('Talje riided'),
('Aluspesu'),
('Mütsid'),
('Kingad');
SELECT * FROM categories

--Tabeli loomine customers
CREATE TABLE customers(
customer_id int primary key identity(1,1),
first_name varchar (30),
last_name varchar (30),
phone varchar (30),
email varchar (30),
street varchar (30),
city varchar (30),
state varchar (15),
zip_code varchar(10)
);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state, zip_code)
VALUES ('Sofiia', 'Halchenko', '54346644', 'sofiagalcenko3@gmail.com', 'Viljandi mnt 16', 'Tallinn', 'Eesti', '25544'),
('Zenja', 'Svirilin', '58877544', 'zenja323@gmail.com', 'Majaka 9', 'Tallinn', 'Eesti', '25334'),
('Sasha', 'Semjonova', '58756454', 'semjonova2@gmail.com', 'Meistri 5', 'Tallinn', 'Eesti', '33774'),
('Bogdan', 'Sergatsov', '58334563', 'bogdan.sergatsov233@gmail.com', 'Väike-Õismäe 15', 'Tallinn', 'Eesti', '58761'),
('Martin', 'Sild', '58996845', 'martinsild@gmail.com', 'Peterburi tee 2', 'Tallinn', 'Eesti', '58364');
SELECT * FROM customers

--Tabeli loomine products
CREATE TABLE products(
product_id int not null primary key identity(1,1),
product_name varchar(20),
brand_id int,
category_id int,
model_year int,
list_price decimal(7, 2),
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
INSERT INTO products
(product_name, brand_id, category_id,model_year,list_price)
VALUES ('särk', 1, 2, 2020, 25.20),
('jope', 2, 2, 2021, 25.15),
('seelik', 3, 2, 2022, 26.10),
('tossud', 5, 5, 2023, 22.25),
('ujumistrikoo', 4, 3, 2024, 20.5);
select * from products

--Tabeli loomine stores
Create table stores(
store_id int primary key identity(1,1),
store_name varchar(20),
phone varchar(20),
email varchar(20),
street varchar(20),
city varchar(20),
state varchar(20),
zip_code varchar (10)
);
INSERT INTO stores
(store_name, phone, email, street, city, state, zip_code) 
VALUES ('Zara','55565545', 'zara323@gmail.com', 'Narva mnt 22', 'Tallinn', 'Eesti', '25644'),
('Skyler','58755469', 'skylerl42@gmail.com', 'Majaka 16', 'Tallinn', 'Eesti', '33234'),
('AOIFE', '58732463', 'aoife233@gmail.com', 'Väike-Õismäe 15', 'Tallinn', 'Eesti', '58871'),
('Fashions', '58334445', 'fashions23@gmail.com', 'Männiku tee 109a', 'Tallinn', 'Eesti', '53324'),
('Butik', '5822345', 'butik23@gmail.com', 'Männiku tee 135a', 'Tallinn', 'Eesti', '51234');
SELECT * FROM stores

--Tabeli loomine staffs
CREATE TABLE staffs(
staff_id int primary key identity(1,1),
first_name varchar (25),
last_name varchar (25),
email varchar (30),
phone varchar (20),
active bit,
store_id int,
manager_id int,
FOREIGN KEY(manager_id) REFERENCES staffs(staff_id),
FOREIGN KEY(store_id) REFERENCES stores(store_id)
);
INSERT INTO staffs
(first_name, last_name, email, phone, active, store_id, manager_id) 
VALUES ('Daria', 'Halchenko', 'daragalcenko3@gmail.com', '58933227', 1, 2, 1),
('Kirill', 'Saats', 'kirill123@gmail.com', '58764027', 0, 1, 2),
('Valeria', 'Allik', 'valeria.allik@gmail.com', '58746852', 1, 4, 3),
('Lev', 'Jegorov', 'lev@gmail.com', '58568797', 0, 3, 4),
('Maksim', 'Svirilin', 'maksim134@gmail.com', '5833852', 1, 5, 5);
select * from staffs

--Tabeli loomine orders
CREATE TABLE orders(
order_id int not null primary key identity(1,1),
customer_id int,
order_status varchar(20),
order_date date,
required_date date,
sgipped_date date,
store_id int,
staff_id int,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (staff_id) REFERENCES staffs(staff_id)
);
INSERT INTO orders
(customer_id, order_status, order_date, required_date, sgipped_date, store_id, staff_id) 
VALUES (1, 'Saadetud', '2024-08-15', '2024-04-26', '2024-08-05', 1, 2),
(2, 'ravi', '2024-05-13', '2024-03-21', '2024-06-27', 3, 5),
(3, 'ootus', '2024-10-25', '2024-01-22', '2024-03-24', 4, 1),
(1, 'Saadetud', '2024-03-07', '2024-09-23', '2024-10-24', 5, 4),
(1, 'ravi', '2024-11-27', '2024-05-19', '2024-08-24', 2, 3);
select * from orders

--Tabeli loomine stocks
CREATE TABLE stocks(
store_id int not null,
product_id int,
quantity int,
PRIMARY KEY (store_id, product_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
FOREIGN KEY(store_id) REFERENCES stores(store_id)
);
INSERT INTO stocks
(store_id, product_id, quantity) 
VALUES (1, 9, 20),
(2, 10, 50),
(3, 11, 45),
(4, 12, 25),
(5, 13, 55);
select * from stocks

--Tabeli loomine order_items
CREATE TABLE order_items(
order_id int not null,
item_id int not null,
product_id int,
quantity int,
list_price decimal (7, 2),
discount decimal (7, 2),
PRIMARY KEY (order_id, item_id),
FOREIGN KEY(product_id) REFERENCES products(product_id)
);
INSERT INTO order_items
(order_id, item_id, product_id, quantity, list_price, discount) 
VALUES (1, 1, 9, 25, 23.33, 10),
(2, 2, 10, 15, 25.45, 5),
(3, 3, 11, 25, 215.45, 15),
(4, 4, 12, 45, 533.45, 20),
(5, 5, 13, 5, 25.45, 5);
select * from order_items

--просматривать (STAFF)
GRANT SELECT ON customers TO staff;
GRANT SELECT ON orders TO staff;
GRANT SELECT ON staffs TO staff;
GRANT SELECT ON stores TO staff;
GRANT SELECT ON order_items TO staff;
--Staff имеет право добавлять заказы (orders, order_item)
GRANT INSERT ON order_items TO staff;
GRANT INSERT ON orders TO staff;
--keelduda DELETE ja UPDATE (STAFF)
DENY DELETE to staff;
DENY UPDATE to staff;

--Manager не может добавлять новые магазины (stores), ему доступно полное управление товарами, покупателями и заказами. 
GRANT INSERT, SELECT, UPDATE, DELETE ON order_items TO manager;
GRANT SELECT, UPDATE ON stores TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON staffs TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON orders TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON customers TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON categories TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON products TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON stocks TO manager;
GRANT INSERT, SELECT, UPDATE, DELETE ON brands TO manager;
 
GRANT SELECT to manager;

INSERT INTO orders
(customer_id, order_status, order_date, required_date, sgipped_date, store_id, staff_id) 
VALUES (2, 'Uuendatud', '2024-10-25', '2024-07-22', '2024-07-05', 1, 3);
SELECT * FROM orders;

INSERT INTO order_items
(order_id, item_id, product_id, quantity, list_price, discount) 
VALUES (1, 3, 15, 25, 143.33, 15);
SELECT * FROM order_items;

--kontrollime UPDATE
UPDATE order_items
set quantity=45 where order_id=1;

--kontrollime DELETE
 
DELETE from orders

SELECT * FROM customers;
SELECT * FROM orders;
SELECT * FROM staffs;
SELECT * FROM stores;
SELECT * FROM order_items;
SELECT * FROM categories;
SELECT * FROM products;
SELECT * FROM stocks;
SELECT * FROM brands;

UPDATE order_items
set quantity=45 where order_id=1;
SELECT * FROM order_items;

INSERT INTO stores
(store_name, phone, email, street, city, state, zip_code) 
VALUES ('H&M','58885545', 'hm35675323@gmail.com', 'Narva mnt 22', 'Tallinn', 'Eesti', '25644');

create procedure insertToProducts(
@prod_name varchar(50), 
@brand int, 
@category int)
as
INSERT INTO products(
product_name, brand_id, category_id)
Values(@prod_name, @brand, @category);
EXEC insertToProducts
@prod_name=automasin,@brand=1, @category=2;
SELECT * from products

CREATE PROCEDURE categories_kustutamine
@deleteID int
AS
BEGIN
SELECT * FROM categories;
DELETE FROM categories
WHERE category_id = @deleteID;
SELECT * FROM categories;
END; 
-- kontroll
EXEC  categories_kustutamine 1;