monacemta bazebi proeqti

--factory table
CREATE TABLE Factory(
f_id INT PRIMARY KEY,
f_name VARCHAR (255),
f_location VARCHAR (255)
);

--branch table
CREATE TABLE Branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(255),
branch_factory_id int,
FOREIGN KEY (branch_factory_id) REFERENCES Factory (f_id)
);

--supplier table
CREATE TABLE Supplier(
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(255)
);

--water table
--price of 1L of water - 0.3
CREATE TABLE Water(
water_id VARCHAR(5) PRIMARY KEY,
water_source VARCHAR(255) NOT NULL,
amount_obtained INT NOT NULL,
water_price DECIMAL(4,2) DEFAULT 0.3,
date_obtained DATE NOT NULL
);

--bottle table
CREATE TABLE Bottle(
bottle_id VARCHAR(6) PRIMARY KEY,
supplier_id INT,
bottle_type VARCHAR(10) CHECK(bottle_type IN ('glass', 'plastic')),
bottle_price DECIMAL(4,2) NOT NULL,
bottle_amount INT NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES Supplier ( supplier_id)
);

--update to the bottle table to set the price accordingly
--if plastic -0.3, if glass - 1.5, else 0
UPDATE Bottle
SET bottle_price = CASE
    WHEN bottle_type = 'glass' THEN 1.5
    WHEN bottle_type = 'plastic' THEN 0.3
    ELSE 0
END;

--Product ( bottled water ) table
CREATE TABLE Product(
product_id VARCHAR(9) PRIMARY KEY,
product_type VARCHAR(10) CHECK(product_type IN ('glass', 'plastic')),
bottle_id VARCHAR(6),
water_id VARCHAR(5),
product_price DECIMAL(6,2),
product_amount INT NOT NULL,
date_produced DATE NOT NULL,
date_shipped DATE,
FOREIGN KEY (bottle_id) REFERENCES Bottle (bottle_id),
FOREIGN KEY (water_id) REFERENCES Water (water_id)
);

-- Update the price values using the calculated formula.
--price is equal to the sum of bottle and water price, plus 45%
UPDATE Product
SET product_price =
(SELECT (Bottle.bottle_price + Water.water_price) * 1.45
FROM Bottle, Water WHERE Bottle.bottle_id = Product.bottle_id AND Water.water_id = Product.water_id);

--person table, will be used for the manager and employee table
CREATE TABLE Person(
ssn VARCHAR(11) PRIMARY KEY,
);

--manager table
CREATE TABLE Manager(
manager_id VARCHAR(10) PRIMARY KEY,
manager_ssn VARCHAR(11) NOT NULL,
manager_name VARCHAR(255) NOT NULL,
manager_lname VARCHAR(255) NOT NULL,
factory_id INT NOT NULL,
branch_id INT NOT NULL,
FOREIGN KEY (manager_ssn) REFERENCES Person(ssn),
FOREIGN KEY (factory_id) REFERENCES Factory(f_id),
FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
);

--employee table
CREATE TABLE Employee(
employee_ssn VARCHAR(11),
employee_name VARCHAR(255) NOT NULL,
employee_lname VARCHAR(255) NOT NULL,
employee_factory INT NOT NULL,
employee_branch INT NOT NULL,
FOREIGN KEY (employee_ssn) REFERENCES Person(ssn),
FOREIGN KEY (employee_factory) REFERENCES Factory(f_id),
FOREIGN KEY (employee_branch) REFERENCES Branch(branch_id)
);

--store table
CREATE TABLE Store(
store_id INT PRIMARY KEY,
store_name VARCHAR(255) NOT NULL,
product_id VARCHAR(9),
product_price DECIMAL(6,2),
product_amount_received INT,
date_received DATE,
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

--update the Store table to calculate the revenue received
UPDATE STORE
SET product_price = (SELECT Product.product_price * Store.product_amount_received
                     FROM Product, Store
                     WHERE Product.product_id = Store.product_id);


--sales table
CREATE TABLE Sales(
store_id int,
revenue_date DATE NOT NULL,
revenue DECIMAL(7,2),
FOREIGN KEY (store_id) REFERENCES Store(store_id)
);

UPDATE Sales
SET revenue_date = (SELECT Store.date_received
                    FROM Store);
UPDATE Sales
SET revenue = (SELECT Store.product_price
                FROM Store);

CREATE TABLE Salaries(
worker_ssn VARCHAR(11),
worker_branch_id INT,
worker_salary DECIMAL(7,2),
FOREIGN KEY (worker_ssn) REFERENCES Person(ssn),
FOREIGN KEY (worker_branch_id) REFERENCES Branch(branch_id)
);


--update to salaries table, each branch has its own salary
UPDATE salaries
SET worker_salary =
    CASE
        WHEN worker_branch_id = 1 AND worker_ssn IN (SELECT manager_ssn FROM Manager) THEN 2500
        WHEN worker_branch_id = 1 AND worker_ssn IN (SELECT employee_ssn FROM Employee) THEN 1500
        WHEN worker_branch_id = 2 AND worker_ssn IN (SELECT manager_ssn FROM Manager) THEN 5500
        WHEN worker_branch_id = 2 AND worker_ssn IN (SELECT employee_ssn FROM Employee) THEN 3500
        WHEN worker_branch_id = 3 AND worker_ssn IN (SELECT manager_ssn FROM Manager) THEN 1700
        WHEN worker_branch_id = 3 AND worker_ssn IN (SELECT employee_ssn FROM Employee) THEN 900
    END;