bazzar/crates/stock_manager/migrations/202204131841_init.sql
2022-11-29 15:18:31 +01:00

62 lines
2.3 KiB
SQL

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TYPE "QuantityUnit" AS ENUM (
'g',
'dkg',
'kg',
'piece'
);
CREATE TABLE photos
(
id serial NOT NULL PRIMARY KEY,
local_path character varying NOT NULL,
file_name character varying NOT NULL,
unique_name text DEFAULT (gen_random_uuid())::text NOT NULL
);
CREATE TABLE products
(
id serial NOT NULL PRIMARY KEY,
"name" character varying NOT NULL,
category character varying,
deliver_days_flag integer DEFAULT 127 NOT NULL
);
CREATE TABLE product_variants
(
id serial NOT NULL PRIMARY KEY,
product_id integer REFERENCES products (id) ON DELETE CASCADE NOT NULL,
"name" character varying NOT NULL,
short_description character varying NOT NULL,
long_description character varying NOT NULL,
price integer NOT NULL,
quantity_unit "QuantityUnit" NOT NULL,
CONSTRAINT non_negative CHECK ((price >= 0))
);
CREATE TABLE stocks
(
id serial NOT NULL PRIMARY KEY,
product_variant_id integer REFERENCES product_variants (id) ON DELETE CASCADE NOT NULL,
quantity integer DEFAULT 0 NOT NULL,
quantity_unit "QuantityUnit" NOT NULL,
CONSTRAINT positive_quantity CHECK ((quantity >= 0))
);
CREATE TABLE product_photos
(
id serial NOT NULL PRIMARY KEY,
product_variant_id integer REFERENCES product_variants (id) ON DELETE CASCADE NOT NULL,
photo_id integer REFERENCES photos (id) NOT NULL
);
CREATE TABLE categories
(
id serial NOT NULL PRIMARY KEY,
parent_id int references categories (id) ON DELETE CASCADE,
"name" varchar not null,
"key" varchar not null,
"svg" varchar not null
);