db-creation-queries.sql

BEGIN;


CREATE TABLE IF NOT EXISTS public.addresses
(
    address_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    address_line1 character varying(255) COLLATE pg_catalog."default" NOT NULL,
    address_line2 character varying(255) COLLATE pg_catalog."default",
    city character varying(255) COLLATE pg_catalog."default" NOT NULL,
    country character varying(255) COLLATE pg_catalog."default" NOT NULL,
    pincode character varying(255) COLLATE pg_catalog."default" NOT NULL,
    state character varying(255) COLLATE pg_catalog."default" NOT NULL,
    user_id bigint,
    CONSTRAINT addresses_pkey PRIMARY KEY (address_id)
);

CREATE TABLE IF NOT EXISTS public.categories
(
    category_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    category_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT categories_pkey PRIMARY KEY (category_id)
);

CREATE TABLE IF NOT EXISTS public.configurations
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(255) COLLATE pg_catalog."default",
    value character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT configurations_pkey PRIMARY KEY (id),
    CONSTRAINT uk6yq89sc7r2wfj8p3l6d5eg8ce UNIQUE (name, value)
);

CREATE TABLE IF NOT EXISTS public.order_items
(
    order_item_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    price double precision,
    quantity integer,
    order_id bigint,
    product_id bigint,
    CONSTRAINT order_items_pkey PRIMARY KEY (order_item_id)
);

CREATE TABLE IF NOT EXISTS public.orders
(
    order_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    order_date date,
    order_status character varying(255) COLLATE pg_catalog."default",
    total_amount double precision,
    address_id bigint,
    payment_id bigint,
    user_id bigint,
    CONSTRAINT orders_pkey PRIMARY KEY (order_id),
    CONSTRAINT ukhaujdjk1ohmeixjhnhslchrp1 UNIQUE (payment_id)
);

CREATE TABLE IF NOT EXISTS public.payments
(
    payment_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    amount double precision,
    payment_method character varying(255) COLLATE pg_catalog."default",
    payment_status character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT payments_pkey PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS public.product_images
(
    image_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    image_url character varying(255) COLLATE pg_catalog."default",
    product_id bigint,
    data oid,
    file_name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT product_images_pkey PRIMARY KEY (image_id)
);

CREATE TABLE IF NOT EXISTS public.products
(
    product_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    description character varying(255) COLLATE pg_catalog."default",
    original_price double precision,
    product_name character varying(255) COLLATE pg_catalog."default",
    quantity integer,
    selling_price double precision,
    category_id bigint,
    CONSTRAINT products_pkey PRIMARY KEY (product_id)
);

CREATE TABLE IF NOT EXISTS public.products_configurations
(
    product_id bigint NOT NULL,
    product_configuration_id bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS public.reviews
(
    review_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    comment character varying(250) COLLATE pg_catalog."default",
    rating double precision,
    order_item_id bigint,
    user_id bigint,
    CONSTRAINT reviews_pkey PRIMARY KEY (review_id),
    CONSTRAINT uk96f6ovfc9wn4579incehx4gra UNIQUE (order_item_id)
);

CREATE TABLE IF NOT EXISTS public.roles
(
    role_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    app_role character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT roles_pkey PRIMARY KEY (role_id)
);

CREATE TABLE IF NOT EXISTS public.shopping_cart_items
(
    item_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    quantity integer,
    product_id bigint,
    shopping_cart_id bigint,
    CONSTRAINT shopping_cart_items_pkey PRIMARY KEY (item_id)
);

CREATE TABLE IF NOT EXISTS public.shopping_carts
(
    shopping_cart_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    user_id bigint,
    CONSTRAINT shopping_carts_pkey PRIMARY KEY (shopping_cart_id),
    CONSTRAINT ukt5ao4h91q3su6hi9d2haxdr2t UNIQUE (user_id)
);

CREATE TABLE IF NOT EXISTS public.users
(
    user_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    email character varying(255) COLLATE pg_catalog."default" NOT NULL,
    is_enabled boolean,
    password character varying(255) COLLATE pg_catalog."default" NOT NULL,
    username character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT users_pkey PRIMARY KEY (user_id),
    CONSTRAINT uk6dotkott2kjsp8vw4d0m25fb7 UNIQUE (email),
    CONSTRAINT ukr43af9ap4edm43mmtq01oddj6 UNIQUE (username)
);

CREATE TABLE IF NOT EXISTS public.users_roles
(
    user_id bigint NOT NULL,
    role_id bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS public.wishlist_items
(
    item_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    product_id bigint,
    wishlist_id bigint,
    CONSTRAINT wishlist_items_pkey PRIMARY KEY (item_id)
);

CREATE TABLE IF NOT EXISTS public.wishlists
(
    wishlist_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    user_id bigint,
    CONSTRAINT wishlists_pkey PRIMARY KEY (wishlist_id),
    CONSTRAINT ukobh8c909a28dx3aqh4cbdhh25 UNIQUE (user_id)
);

ALTER TABLE IF EXISTS public.addresses
    ADD CONSTRAINT fk1fa36y2oqhao3wgg2rw1pi459 FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.order_items
    ADD CONSTRAINT fkbioxgbv59vetrxe0ejfubep1w FOREIGN KEY (order_id)
    REFERENCES public.orders (order_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.order_items
    ADD CONSTRAINT fkocimc7dtr037rh4ls4l95nlfi FOREIGN KEY (product_id)
    REFERENCES public.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.orders
    ADD CONSTRAINT fk32ql8ubntj5uh44ph9659tiih FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.orders
    ADD CONSTRAINT fk8aol9f99s97mtyhij0tvfj41f FOREIGN KEY (payment_id)
    REFERENCES public.payments (payment_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS ukhaujdjk1ohmeixjhnhslchrp1
    ON public.orders(payment_id);


ALTER TABLE IF EXISTS public.orders
    ADD CONSTRAINT fkhlglkvf5i60dv6dn397ethgpt FOREIGN KEY (address_id)
    REFERENCES public.addresses (address_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.product_images
    ADD CONSTRAINT fkqnq71xsohugpqwf3c9gxmsuy FOREIGN KEY (product_id)
    REFERENCES public.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.products
    ADD CONSTRAINT fkog2rp4qthbtt2lfyhfo32lsw9 FOREIGN KEY (category_id)
    REFERENCES public.categories (category_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.products_configurations
    ADD CONSTRAINT fkdl38ct7831wxc87buu8tq7ivl FOREIGN KEY (product_configuration_id)
    REFERENCES public.configurations (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.products_configurations
    ADD CONSTRAINT fkis1ftt3p803lrbkk5j2nlm27g FOREIGN KEY (product_id)
    REFERENCES public.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.reviews
    ADD CONSTRAINT fk2x2x74lnliqmt91bc1w95ll8n FOREIGN KEY (order_item_id)
    REFERENCES public.order_items (order_item_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS uk96f6ovfc9wn4579incehx4gra
    ON public.reviews(order_item_id);


ALTER TABLE IF EXISTS public.reviews
    ADD CONSTRAINT fkcgy7qjc1r99dp117y9en6lxye FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.shopping_cart_items
    ADD CONSTRAINT fkbujx77cqv0w1v7v9cw6md956s FOREIGN KEY (shopping_cart_id)
    REFERENCES public.shopping_carts (shopping_cart_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.shopping_cart_items
    ADD CONSTRAINT fkgbo9dbc0drcea1m4u8112oxip FOREIGN KEY (product_id)
    REFERENCES public.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.shopping_carts
    ADD CONSTRAINT fk3iw2988ea60alsp0gnvvyt744 FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS ukt5ao4h91q3su6hi9d2haxdr2t
    ON public.shopping_carts(user_id);


ALTER TABLE IF EXISTS public.users_roles
    ADD CONSTRAINT fk2o0jvgh89lemvvo17cbqvdxaa FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.users_roles
    ADD CONSTRAINT fkj6m8fwv7oqv74fcehir1a9ffy FOREIGN KEY (role_id)
    REFERENCES public.roles (role_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.wishlist_items
    ADD CONSTRAINT fkkem9l8vd14pk3cc4elnpl0n00 FOREIGN KEY (wishlist_id)
    REFERENCES public.wishlists (wishlist_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.wishlist_items
    ADD CONSTRAINT fkqxj7lncd242b59fb78rqegyxj FOREIGN KEY (product_id)
    REFERENCES public.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.wishlists
    ADD CONSTRAINT fk330pyw2el06fn5g28ypyljt16 FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS ukobh8c909a28dx3aqh4cbdhh25
    ON public.wishlists(user_id);

END;