init.sql

CREATE TABLE IF NOT EXISTS catalog_brands (
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS catalog_types (
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS catalog_items (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    description text NOT NULL DEFAULT '',
    price numeric(18,2) NOT NULL CHECK (price >= 0),
    picture_file_name text NOT NULL DEFAULT '',
    catalog_type_id integer NOT NULL REFERENCES catalog_types(id),
    catalog_brand_id integer NOT NULL REFERENCES catalog_brands(id),
    available_stock integer NOT NULL CHECK (available_stock >= 0),
    restock_threshold integer NOT NULL CHECK (restock_threshold >= 0),
    max_stock_threshold integer NOT NULL CHECK (max_stock_threshold >= 0),
    on_reorder boolean NOT NULL DEFAULT false
);

CREATE INDEX IF NOT EXISTS ix_catalog_items_name ON catalog_items(name);
CREATE INDEX IF NOT EXISTS ix_catalog_items_type ON catalog_items(catalog_type_id);
CREATE INDEX IF NOT EXISTS ix_catalog_items_brand ON catalog_items(catalog_brand_id);

CREATE TABLE IF NOT EXISTS baskets (
    buyer_id text PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS basket_items (
    basket_buyer_id text NOT NULL REFERENCES baskets(buyer_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES catalog_items(id),
    product_name text NOT NULL,
    unit_price numeric(18,2) NOT NULL CHECK (unit_price >= 0),
    quantity integer NOT NULL CHECK (quantity > 0),
    picture_file_name text NOT NULL DEFAULT '',
    PRIMARY KEY (basket_buyer_id, product_id)
);

CREATE INDEX IF NOT EXISTS ix_basket_items_product ON basket_items(product_id);

CREATE TABLE IF NOT EXISTS orders (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    buyer_id text NOT NULL,
    order_date timestamptz NOT NULL,
    status text NOT NULL CHECK (status IN ('StockConfirmed', 'Shipped', 'Cancelled')),
    description text NOT NULL DEFAULT '',
    street text NOT NULL,
    city text NOT NULL,
    state text NOT NULL,
    country text NOT NULL,
    zip_code text NOT NULL
);

CREATE INDEX IF NOT EXISTS ix_orders_buyer ON orders(buyer_id, id);

CREATE TABLE IF NOT EXISTS order_items (
    order_id integer NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES catalog_items(id),
    product_name text NOT NULL,
    unit_price numeric(18,2) NOT NULL CHECK (unit_price >= 0),
    units integer NOT NULL CHECK (units > 0),
    picture_file_name text NOT NULL DEFAULT '',
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX IF NOT EXISTS ix_order_items_product ON order_items(product_id);

INSERT INTO catalog_brands (id, name) VALUES
    (1, 'Daybird'),
    (2, 'Gravitator'),
    (3, 'WildRunner'),
    (4, 'Quester'),
    (5, 'B&R'),
    (6, 'Raptor Elite'),
    (7, 'Solstix'),
    (8, 'Grolltex')
ON CONFLICT DO NOTHING;

INSERT INTO catalog_types (id, name) VALUES
    (1, 'Footwear'),
    (2, 'Climbing'),
    (3, 'Ski/boarding'),
    (4, 'Bags'),
    (5, 'Trekking'),
    (6, 'Jackets')
ON CONFLICT DO NOTHING;

INSERT INTO catalog_items
    (id, name, description, price, picture_file_name, catalog_type_id, catalog_brand_id,
     available_stock, restock_threshold, max_stock_threshold, on_reorder)
VALUES
    (1, 'Wanderer Black Hiking Boots', 'Daybird''s Wanderer Hiking Boots in sleek black are perfect for all your outdoor adventures. These boots are made with a waterproof leather upper and a durable rubber sole for superior traction. With their cushioned insole and padded collar, these boots will keep you comfortable all day long.', 109.99, '1.png', 1, 1, 100, 10, 200, false),
    (2, 'Summit Pro Harness', 'Conquer new heights with the Summit Pro Harness by Gravitator. This lightweight and durable climbing harness features adjustable leg loops and waist belt for a customized fit.', 89.99, '2.png', 2, 2, 50, 10, 200, false),
    (3, 'Alpine Fusion Goggles', 'Enhance your skiing experience with the Alpine Fusion Goggles from WildRunner. These goggles offer full UV protection and anti-fog lenses.', 79.99, '3.png', 3, 3, 40, 10, 200, false),
    (4, 'Expedition Backpack', 'The Expedition Backpack by Quester is a durable and spacious companion for outdoor adventures.', 129.99, '4.png', 4, 4, 30, 10, 200, false),
    (5, 'Blizzard Rider Snowboard', 'The Blizzard Rider Snowboard by B&R is a versatile board for riders of all levels.', 299.99, '5.png', 3, 5, 12, 10, 200, false),
    (6, 'Carbon Fiber Trekking Poles', 'Lightweight carbon fiber trekking poles by Raptor Elite.', 69.99, '6.png', 5, 6, 0, 10, 200, true),
    (7, 'Explorer 45L Backpack', 'A waterproof and tear-resistant 45L backpack by Solstix.', 149.99, '7.png', 4, 7, 20, 10, 200, false),
    (8, 'Frostbite Insulated Jacket', 'A water-resistant insulated jacket by Grolltex.', 179.99, '8.png', 6, 8, 15, 10, 200, false)
ON CONFLICT DO NOTHING;

SELECT setval(
    pg_get_serial_sequence('catalog_items', 'id'),
    COALESCE((SELECT MAX(id) FROM catalog_items), 1),
    true
);