TeamSystemResources

base.sql

-- Create the "drivers" table to store information about the gig economy drivers
CREATE TABLE public.drivers (
    driver_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the "chocolates" table to store information about the available Ferrero chocolates
CREATE TABLE public.chocolates (
    chocolate_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the "orders" table to store information about customer orders
CREATE TABLE public.orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_phone_number VARCHAR(20) NOT NULL,
    customer_address TEXT NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivery_date TIMESTAMP,
    driver_id INT REFERENCES public.drivers(driver_id),
    delivered BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the "order_items" table to store the details of each chocolate item in an order
CREATE TABLE public.order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES public.orders(order_id),
    chocolate_id INT REFERENCES public.chocolates(chocolate_id),
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

-- Create the "depots" table to store information about warehouse depots
CREATE TABLE public.depots (
    depot_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the "inventory" table to track the chocolate inventory in depots
CREATE TABLE public.inventory (
    inventory_id SERIAL PRIMARY KEY,
    depot_id INT REFERENCES public.depots(depot_id),
    chocolate_id INT REFERENCES public.chocolates(chocolate_id),
    quantity INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the "delivery" table to track the delivery of orders
CREATE TABLE public.delivery (
    delivery_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES public.orders(order_id),
    driver_id INT REFERENCES public.drivers(driver_id),
    depot_id INT REFERENCES public.depots(depot_id),
    delivery_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert 10 different types of chocolate named after Swiss mountains into the "chocolates" table
INSERT INTO public.chocolates (name, description, price)
VALUES
    ('Matterhorn', 'Dark chocolate with hazelnuts', 5.99),
    ('Eiger', 'Milk chocolate with almonds', 4.99),
    ('Jungfrau', 'White chocolate with strawberries', 4.99),
    ('Monte Rosa', 'Dark chocolate with caramel', 5.99),
    ('Titlis', 'Milk chocolate with pistachios', 4.99),
    ('Pilatus', 'Dark chocolate with orange zest', 5.99),
    ('Rigi', 'Milk chocolate with caramel and sea salt', 4.99),
    ('Weisshorn', 'White chocolate with raspberries', 4.99),
    ('Dufourspitze', 'Dark chocolate with espresso', 5.99),
    ('Mönch', 'Milk chocolate with hazelnuts and nougat', 4.99);

-- Insert three depots in Milan, Italy into the "depots" table
INSERT INTO public.depots (name, location)
VALUES
    ('Depot 1', 'Milan, Italy'),
    ('Depot 2', 'Milan, Italy'),
    ('Depot 3', 'Milan, Italy');

-- Set the initial inventory for each depot and chocolate in the "inventory" table
-- Depot 1
INSERT INTO public.inventory (depot_id, chocolate_id, quantity)
VALUES
    (1, 1, 50),
    (1, 2, 50),
    (1, 3, 50),
    (1, 4, 50),
    (1, 5, 50),
    (1, 6, 50),
    (1, 7, 50),
    (1, 8, 50),
    (1, 9, 50),
    (1, 10, 50);

-- Depot 2
INSERT INTO public.inventory (depot_id, chocolate_id, quantity)
VALUES
    (2, 1, 75),
    (2, 2, 75),
    (2, 3, 75),
    (2, 4, 75),
    (2, 5, 75),
    (2, 6, 75),
    (2, 7, 75),
    (2, 8, 75),
    (2, 9, 75),
    (2, 10, 75);

-- Depot 3
INSERT INTO public.inventory (depot_id, chocolate_id, quantity)
VALUES
    (3, 1, 100),
    (3, 2, 100),
    (3, 3, 100),
    (3, 4, 100),
    (3, 5, 100),
    (3, 6, 100),
    (3, 7, 100),
    (3, 8, 100),
    (3, 9, 100),
    (3, 10, 100);