CREATE DATABASE amcdb;
\c amcdb
CREATE TABLE Stores (
    store_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_no VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    fax_number VARCHAR(20) NOT NULL,
    location_id INT NOT NULL,
    FOREIGN KEY (location_id) REFERENCES locations (location_id)
);

CREATE TABLE Location (
    location_id SERIAL PRIMARY KEY,
    street_number VARCHAR(50) NOT NULL,
    suburb VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    postcode VARCHAR(20) NOT NULL
);

CREATE TABLE Departments (
    department_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    phone_no VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    store_id INT REFERENCES Stores(store_id)
);

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_number VARCHAR (20) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    address VARCHAR(150) NOT NULL,
    email VARCHAR(100) NOT NULL,
    tfn VARCHAR(20) NOT NULL,
    salary FLOAT NOT NULL,
    joining_date DATE NOT NULL,
    employee_type VARCHAR(50) NOT NULL,
    employee_position VARCHAR(50) NOT NULL,
    store_id INT REFERENCES Stores(store_id),
    department_id INT REFERENCES Departments(department_id)
);

CREATE TABLE Payslip (
    payslip_id SERIAL PRIMARY KEY,
    no_of_worked_hours FLOAT NOT NULL,
    gross_pay VARCHAR(70) NOT NULL,
    week_end DATE NOT NULL,
    store_id INT REFERENCES Stores(store_id),
    employee_id INT REFERENCES Employees(employee_id)
);

CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    product_no VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    brand VARCHAR(100) NOT NULL,
    description VARCHAR(150) NOT NULL,
    price FLOAT NOT NULL
);

CREATE TABLE Store_Inventory (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES Products(product_id),
    quantity FLOAT NOT NULL,
    ordered FLOAT NOT NULL,
    store_id INT REFERENCES Stores(store_id)
);

CREATE TABLE Suppliers (
    supplier_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_no VARCHAR(20) NOT NULL,
    address VARCHAR(150) NOT NULL
);

CREATE TABLE Suppliers_to_Product_Mapping (
    supplier_id INT REFERENCES Suppliers(supplier_id),
    product_id INT REFERENCES Products(product_id),
    store_id INT REFERENCES Stores(store_id),
    PRIMARY KEY (supplier_id, product_id, store_id)
);

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    postcode VARCHAR(20) NOT NULL,
 store_id INT REFERENCES Stores(store_id),
 customer_id INT REFERENCES Patrons(customer_id)
);

CREATE TABLE Patrons (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_no VARCHAR(20) NOT NULL,
    address VARCHAR(150) NOT NULL
);

CREATE TABLE Patron_Ordered_Details (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES Orders(order_id),
    product_id INT REFERENCES Products(product_id),
    quantity INT NOT NULL
);

-- Inserting values into Stores table
INSERT INTO Stores (name, phone_no, email, fax_number, location_id) VALUES
('Store A', '0496807277', 'storea@example.com', '123-456-7890', 1),
('Store B', '0492877277', 'storeb@example.com', '987-654-3210', 2),
('Store C', '0494417277', 'storec@example.com', '111-222-3333', 3),
('Store D', '0498817277', 'stored@example.com', '444-555-6666', 4),
('Store E', '0499817277', 'storee@example.com', '555-666-7777', 5),
('Store F', '0492217277', 'storef@example.com', '666-777-8888', 6),
('Store G', '0492117277', 'storeg@example.com', '888-555-6666', 7),
('Store H', '0492817217', 'storeh@example.com', '555-999-7777', 8),
('Store I', '0492817277', 'storeI@example.com', '222-777-4444', 9),
('Store J', '0405817277', 'storeJ@example.com', '111-777-4444', 10);

-- Inserting values into Location table
INSERT INTO Location (street_number, suburb, state, postcode) VALUES
('12 Darley St', 'Suburbia', 'NSW', '2000'),
('1 Regent St', 'Metropolis', 'VIC', '3006'),
('2 Oxford St', 'Townsville', 'QLD', '4050'),
('6 GEORGE St', 'Rivertown', 'SA', '5000'),
('1 Bently St', 'Beachside', 'WA', '6040'),
('16 Allawa St', 'Outback', 'NT', '7000'),
('50 Charlote st', 'Ashfield', 'NSW', '2148'),
('3 Harney St', 'Stanmore', 'VIC', '2324'),
('9 Pine St', 'Rockdale', 'NSW', '7001'),
('10 Robert St', 'Campsie', 'WA', '2204');

-- Inserting values into Departments table
INSERT INTO Departments (title, phone_no, email, store_id) VALUES
('HR', '0496807277', 'hr@store.com', 1),
('Finance', '0492877277', 'finance@store.com', 2),
('Sales', '0494417277', 'sales@store.com', 1),
('Marketing', '0498817277', 'marketing@store.com', 2),
('Operations', '0499817277', 'operations@store.com', 1),
('IT', '0492217277', 'it@store.com', 2),
('Research', '0492117277', 'research@store.com', 1),
('Account', '0492817217', 'account@store.com', 2),
('Testing', '0492817277', 'testing@store.com', 1),
('Sales', '0405817277', 'sales@store.com', 2);

-- Inserting values into Employees table
INSERT INTO Employees (emloyee_number, first_name, last_name, address, email, tfn, salary, joining_date, employee_type, employee_position, store_id, department_id) VALUES
('emp01', 'John', 'Doe', '123 Street St', 'john@example.com', '123456789', 50000.00, '2023-01-15', 'Fulltime', 'HR', 1, 1),
('emp02', 'Jane', 'Smith', '456 Avenue Ave', 'jane@example.com', '987654321', 60000.00, '2023-02-20', 'Fulltime', 'Finance', 2, 2),
('emp03', 'Alice', 'Johnson', '789 Boulevard Blvd', 'alice@example.com', '456789123', 55000.00, '2023-03-25', 'Casual', 'Sales', 1, 3),
('emp04', 'Tom', 'Hanks', '789 First St', 'tom@example.com', '987654321', 48000.00, '2023-05-15', 'Fulltime', 'Marketing', 2, 4),
('emp05', 'Emma', 'Watson', '890 Second Ave', 'emma@example.com', '123456789', 52000.00, '2023-06-20', 'Fulltime', 'Operations', 1, 5),
('emp06', 'Chris', 'Hemsworth', '901 Third St', 'chris@example.com', '456789123', 50000.00, '2023-07-25', 'Fulltime', 'IT', 2, 6),
('emp07', 'Emma', 'Stone', '103 Fourth St', 'emma.stone@example.com', '123123123', 45000.00, '2023-10-15', 'Casual', 'Research', 1, 7),
('emp08', 'Robert', 'Downey', '204 Fifth Ave', 'robert.downey@example.com', '456456456', 48000.00, '2023-11-20', 'Fulltime', 'Development', 2, 8),
('emp09', 'Jennifer', 'Lopez', '305 Sixth St', 'jennifer.lopez@example.com', '789789789', 50000.00, '2023-12-25', 'Casual', 'Testing', 1, 9),
('emp10', 'Brad', 'Pitt', '406 Seventh Ave', 'brad.pitt@example.com', '101010101', 52000.00, '2024-01-01', 'Fulltime', 'Sales', 2, 10);


-- Inserting values into Payslip table
INSERT INTO Payslip (payslip_id, no_of_worked_hours, gross_pay, week_end, store_id, employee_id) VALUES
(1001, 40.5, '2000.00', '2023-04-05', 1, 1),
(1002, 45.0, '2400.00', '2023-04-05', 2, 2),
(1003, 38.0, '1900.00', '2023-04-05', 1, 3),
(1004, 37.0, '1800.00', '2023-04-05', 2, 4),
(1005, 39.5, '2000.00', '2023-04-05', 1, 5),
(1006, 36.5, '1900.00', '2023-04-05', 2, 6),
(1007, 37.0, '1800.00', '2023-04-05', 1, 7),
(1008, 39.5, '2000.00', '2023-04-05', 2, 8),
(1009, 36.5, '1900.00', '2023-04-05', 1, 9),
(1010, 38.0, '1950.00', '2023-04-05', 2, 10);


-- Inserting values into Products table
INSERT INTO Products (product_id, product_no, name, brand, description, price) VALUES
(201, 'PRD01', 'Shirt', 'HM', 'Cotton Shirt', 8.99),
(202, 'PRD02', 'Laptop', 'Dell', 'High-performance Laptop', 1200.00),
(203, 'PRD03', 'iPhone', 'Apple', 'Smartphone', 800.00),
(204, 'PRD04', 'Jeans', 'Tommy', 'Denim Jeans', 35.00),
(205, 'PRD05', 'Headphones', 'Apple', 'Wireless Headphones', 80.00),
(206, 'PRD06', 'Watch', 'Samsung', 'Luxury Watch', 300.00),
(207, 'PRD07', 'Skirt', 'HM', 'Soft Cotton Skirt', 49.00),
(208, 'PRD08', 'Mobile', 'Samsung', 'Luxury Phone', 800.00),
(209, 'PRD09', 'Airpods', 'Apple', 'Wireless Earbuds', 219.00),
(210, 'PRD10', 'Playstation', 'Sony', 'Gaming Console', 900.00);


-- Inserting values into Store_Inventory table
INSERT INTO Store_Inventory (product_id, quantity, ordered, store_id) VALUES
(1, 50, 10, 1),
(2, 4, 5, 2),
(3, 30, 8, 1),
(4, 20, 10, 2),
(5, 15, 5, 1),
(6, 10, 3, 2),
(7, 25, 7, 1),
(8, 30, 5, 2),
(9, 20, 10, 1),
(10, 15, 8, 2);


-- Inserting values into Suppliers table
INSERT INTO Suppliers (name, phone_no, address) VALUES
('Supplier 1', '123-456-7890', 'Supplier Address 1'),
('Supplier 2', '987-654-3210', 'Supplier Address 2'),
('Supplier 3', '111-222-3333', 'Supplier Address 3'),
('Supplier 4', '123-456-7890', 'Supplier Address 4'),
('Supplier 5', '987-654-3210', 'Supplier Address 5'),
('Supplier 6', '111-222-3333', 'Supplier Address 6'),
('Supplier 7', '333-444-5555', 'Supplier Address 7'),
('Supplier 8', '444-555-6666', 'Supplier Address 8'),
('Supplier 9', '555-666-7777', 'Supplier Address 9'),
('Supplier 10', '666-777-8888', 'Supplier Address 10');


-- Inserting values into Orders table
INSERT INTO Orders (order_id, customer_id, store_id, order_date, postcode) VALUES
(1, 101, 1, '2023-04-01', '2000'),
(2, 102, 2, '2023-04-02', '3000'),
(3, 103, 1, '2017-07-07', '4000'),
(4, 104, 2, '2018-04-04', '5000'),
(5, 105, 1, '2017-04-05', '6000'),
(6, 106, 2, '2018-08-08', '7000'),
(7, 107, 1, '2018-09-08', '2204'),
(8, 108, 2, '2018-10-03', '7001'),
(9, 109, 1, '2018-05-08', '7008'),
(10, 110, 2, '2017-06-08', '1243');

-- Inserting values into Patrons table
INSERT INTO Patrons (customer_id, first_name, last_name, phone_no, address) VALUES
(101, 'Michael', 'Brown', '0492617313', '1 King St'),
(102, 'Sarah', 'Davis', '0492217313', '2 Queen St'),
(103, 'Chris', 'Wilson', '0400880003', '3 George St'),
(104, 'Sophia', 'Johnson', '0492891313', '12 Hill St'),
(105, 'Ethan', 'Williams', '0492891314', '14 Oak Ave'),
(106, 'Ava', 'Jones', '0492891315', '16 Elm St'),
(107, 'William', 'Garcia', '0492891316', '18 Willow St'),
(108, 'Mia', 'Davis', '0492891317', '20 Elm Ave'),
(109, 'Olivia', 'Miller', '0492891318', '22 Oak St'),
(110, 'Noah', 'Wilson', '0492891319', '24 Maple Ave');

-- Inserting values into Patron_Ordered_Details table
INSERT INTO Patron_Ordered_Details (order_id, product_id, quantity) VALUES
(1, 201, 2),
(2, 202, 3),
(3, 203, 10),
(4, 204, 4),
(5, 205, 2),
(6, 206, 13),
(7, 207, 16),
(8, 208, 11),
(9, 209, 8),
(10, 210, 6);