--factory table
CREATE TABLE Factory(
factory_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 (factory_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,
);

--director table
CREATE TABLE Director(
director_ssn VARCHAR(11),
factory_id INT,
FOREIGN KEY (director_ssn) REFERENCES Person(ssn),
FOREIGN KEY (factory_id) REFERENCES Factory(factory_id)
);
--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(factory_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(factory_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 to Sales, revenue date is the same as the day the store received the products
UPDATE Sales
SET revenue_date = (SELECT Store.date_received
                    FROM Store);

----update to Sales, revenue is the same as the price paid by the store
UPDATE Sales
SET revenue = (SELECT Store.product_price
                FROM Store);

--salaries table
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;



--inserting info into the tables

--inserting into the Factory table
INSERT INTO Factory
VALUES
    (1, 'kumisis wyaro', 'Kumisi'),
    (2, 'rustavis wyali', 'Rustavi');

--inserting into the branch table
INSERT INTO Branch (branch_id, branch_name, branch_factory_id)
SELECT 1, 'Water obtaining', factory_id FROM Factory WHERE factory_id = 1
UNION ALL
SELECT 2, 'Water filtration', factory_id FROM Factory WHERE factory_id = 1
UNION ALL
SELECT 3, 'water mineralisation', factory_id FROM Factory WHERE factory_id = 1
UNION ALL
SELECT 4, 'Bottling water', factory_id FROM Factory WHERE factory_id = 2
UNION ALL
SELECT 5, 'Warehouse ', factory_id FROM Factory WHERE factory_id = 2;

--inserting into the supplier table
INSERT INTO Supplier
Values
    (1, 'Suramis gandzi'),
    (2, 'Pol-Ethyl');

--inserting into the water table
INSERT INTO Water
VALUES
    ('W0001','Kumisi creek N1',7000,0.3,'2023-06-01'),
    ('W0002','Kumisi creek N2',8500,0.3,'2023-06-01'),
    ('W0003','Kumisi creek N3',2000,0.3,'2023-06-02'),
    ('W0004','Kumisi creek N1',3500,0.3,'2023-06-03'),
    ('W0005','Kumisi creek N2',6000,0.3,'2023-06-06');

--inserting into the bottle table
INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0001', supplier_id, 'glass', 1.50, 100
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0001', supplier_id, 'plastic', 0.3, 2300
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0002', supplier_id, 'glass', 1.50, 1550
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0002', supplier_id, 'plastic', 0.3, 650
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0003', supplier_id, 'plastic', 0.3, 950
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0004', supplier_id, 'plastic', 0.3, 650
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0005', supplier_id, 'plastic', 0.3, 750
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0006', supplier_id, 'plastic', 0.3, 9000
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BP0007', supplier_id, 'plastic', 0.3, 7000
FROM Supplier
WHERE supplier_name = 'Pol-Ethyl';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0003', supplier_id, 'glass', 1.50, 550
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0004', supplier_id, 'glass', 1.50, 1700
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0005', supplier_id, 'glass', 1.50, 120
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0006', supplier_id, 'glass', 1.50, 1200
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

INSERT INTO Bottle (bottle_id, supplier_id, bottle_type, bottle_price, bottle_amount)
SELECT 'BG0007', supplier_id, 'glass', 1.50, 1500
FROM Supplier
WHERE supplier_name = 'Suramis gandzi';

--inserting into the product table
INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000001', 'glass', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BG0001'), (SELECT water_id FROM Water WHERE water_id = 'W0001'), 2.50, 100, '2023-06-01', '2023-06-05');

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000002', 'plastic', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BP0002'), (SELECT water_id FROM Water WHERE water_id = 'W0002'), 1.00, 200, '2023-06-02', NULL);

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000003', 'glass', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BG0002'), (SELECT water_id FROM Water WHERE water_id = 'W0003'), 2.50, 150, '2023-06-03', '2023-06-06');

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000004', 'plastic', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BP0001'), (SELECT water_id FROM Water WHERE water_id = 'W0002'), 1.00, 300, '2023-06-04', NULL);

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000005', 'plastic', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BP0001'), (SELECT water_id FROM Water WHERE water_id = 'W0002'), 1.00, 300, '2023-06-04', NULL);

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000006', 'plastic', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BP0002'), (SELECT water_id FROM Water WHERE water_id = 'W0002'), 1.00, 300, '2023-06-04', NULL);

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000007', 'plastic', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BP0003'), (SELECT water_id FROM Water WHERE water_id = 'W0004'), 1.00, 300, '2023-06-04', NULL);

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000008', 'glass', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BG0001'), (SELECT water_id FROM Water WHERE water_id = 'W0003'), 2.50, 100, '2023-06-01', '2023-06-05');

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000009', 'glass', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BG0002'), (SELECT water_id FROM Water WHERE water_id = 'W0002'), 2.50, 100, '2023-06-01', '2023-06-05');

INSERT INTO Product (product_id, product_type, bottle_id, water_id, product_price, product_amount, date_produced, date_shipped)
VALUES ('P00000010', 'glass', (SELECT bottle_id FROM Bottle WHERE bottle_id = 'BG0003'), (SELECT water_id FROM Water WHERE water_id = 'W0001'), 2.50, 100, '2023-06-01', '2023-06-05');

--inserting into the person table

INSERT INTO Person
VALUES
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001'),
    ('00000000001');