CREATE DATABASE ShopBase ;
USE ShopBase ;

Create table brands(
brand_id int primary key identity(1,1),
brand_name varchar(20)
);
INSERT INTO brands
(brand_name) 
VALUES ('zara'),
('h&m'),
('dior'),
('nike'),
('chanel');
select * from brands

Create table categories(
category_id int primary key identity(1,1),
category_name varchar(20)
);
INSERT INTO categories
(category_name) 
VALUES ('ülerõivad'),
('ujumisriided'),
('kombinesoonid'),
('pesu'),
('pidžaama');
select * from categories

Create table customers(
customer_id int primary key identity(1,1),
first_name varchar(20),
last_name varchar(20),
phone varchar(20),
email varchar(20),
street varchar(20),
city varchar(20),
state varchar(20),
zip_code char(5)
);
INSERT INTO customers
(first_name,last_name,phone,email,street,city,state,zip_code) 
VALUES ('Valeria','Allik','55516475','lera@gmail.com','Narva mnt','Tallinn','Estonia',52345),
('Daria','Halchenko','55596734','d.h@gmail.com','Peterbuuri tee','Tallinn','Estonia',54321),
('Alexandra','Semjonova','55539674','sasha@gmail.com','Telliskivi tee','Tallinn','Estonia',13245),
('David','Myrseth','55503752','david@gmail.com','Paemurru tee','Narva','Estonia',65432),
('Maria','Gorbunova','55504829','maria@gmail.com','Viljandi mnt','Tallinn','Estonia',94037);
select * from customers

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, 1, 2020, 25.20),
('t-särk', 2, 2, 2023, 10.99),
('teksad', 3, 3, 2021, 30.50),
('püksid', 4, 4, 2024, 15.20),
('ujumispüksid', 5, 5, 2022, 9.99);
select * from products

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 char(5)
);
INSERT INTO stores
(store_name, phone, email,street,city,state,zip_code) 
VALUES ('Blive', '55583951', 'blive@gmail.com', 'Narva mnt', 'Tallin','Estonia',47382),
('Hibe', '55503728', 'hibe@gmail.com', 'Viljandi mnt', 'Tallin','Estonia',58361),
('Illit', '55529571', 'illit@gmail.com', 'Telliskivi tee', 'Tallin','Estonia',94017),
('TXT', '55530681', 'txt@gmail.com', 'Paemurru tee', 'Narva','Estonia',10603),
('SKZ', '55559306', 'skz@gmail.com', 'Peterbuuri tee', 'Tallin','Estonia',50381);
select * from stores

CREATE TABLE staffs(
staff_id int not null primary key identity(1,1),
first_name varchar(20),
last_name varchar(20),
email varchar(20),
phone varchar(20),
active bit,
store_id int,
manager_id int,
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (manager_id) REFERENCES staffs(staff_id)
);
INSERT INTO staffs
(first_name, last_name, email,phone,active,store_id,manager_id) 
VALUES ('Kirill', 'Saats', 'kirill@gmail.com', '55594027', 1, 1, 1),
('David', 'Lennuk', 'lennuk@gmail.com', '55573950', 0, 2, 2),
('Martin', 'Sild', 'martin@gmail.com', '55525852', 1, 3, 3),
('Lev', 'Jegorov', 'lev@gmail.com', '55568397', 0, 4, 4),
('Timur', 'Bashirov', 'timur@gmail.com', '55528401', 1, 5, 5);
select * from staffs

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,
shipped_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, shipped_date,store_id,staff_id) 
VALUES (1, 'Shipped', '2024-08-15', '2024-08-16', '2024-07-14', 1, 1),
(2, 'Shipped', '2024-10-25', '2024-04-30', '2024-09-17', 2, 2),
(3, 'Shipped', '2024-10-15', '2024-03-02', '2024-09-24', 3, 3),
(4, 'Shipped', '2023-12-16', '2024-07-13', '2024-11-20', 4, 4),
(5, 'Shipped', '2024-01-19', '2024-05-26', '2024-12-30', 5, 5);
select * from orders

CREATE TABLE order_items(
order_id int not null,
item_id int,
product_id int,
quantity int,
list_price decimal(7, 2),
discount varchar(10),
PRIMARY KEY (order_id, item_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
FOREIGN KEY(order_id) REFERENCES orders(order_id),
FOREIGN KEY(item_id) REFERENCES orders(order_id)
);
INSERT INTO order_items
(order_id, item_id, product_id, quantity,list_price,discount) 
VALUES (1, 1, 1, 10, 110.50, '5%'),
(2, 2, 2, 30, 30.99, '10%'),
(3, 3, 3, 42, 43.80, '40%'),
(4, 4, 4, 56, 12.99, '25%'),
(5, 5, 5, 5, 58.35, '12%');
select * from order_items

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, 1, 30),
(2, 2, 90),
(3, 3, 76),
(4, 4, 81),
(5, 5, 48);
select * from stocks

grant SELECT on customers to staff; 
grant SELECT on staffs to staff;
grant SELECT on orders to staff;
grant SELECT on order_items to staff;
grant SELECT on stores to staff;

grant insert on order_items to staff;
grant insert on orders to staff;

DENY DELETE on order_items to staff;
DENY DELETE on orders to staff;

DENY update on order_items to staff;
DENY update on orders to staff;

GRANT SELECT to manager;

DENY insert on stores to manager;