STKPfiBn

1DROP TABLE IF EXISTS order_status;
2DROP TABLE IF EXISTS ingredient_stock;
3DROP TABLE IF EXISTS provider_ingredient;
4DROP TABLE IF EXISTS pizza_ingredient;
5DROP TABLE IF EXISTS ingredient;
6DROP TABLE IF EXISTS provider;
7DROP TABLE IF EXISTS order_item;
8DROP TABLE IF EXISTS "order";
9DROP TABLE IF EXISTS customer;
10DROP TYPE  IF EXISTS order_status;
11DROP TABLE IF EXISTS pizza;
12
13CREATE TABLE pizza
14(
15    name   TEXT PRIMARY KEY,
16    price  NUMERIC NOT NULL CHECK ( price > 0 ),
17    weight NUMERIC NOT NULL CHECK ( weight > 0 )
18);
19CREATE TABLE ingredient
20(
21    id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
22    name TEXT NOT NULL UNIQUE
23);
24CREATE TABLE pizza_ingredient
25(
26    ingredient_id INT REFERENCES ingredient,
27    pizza         TEXT REFERENCES pizza,
28    weight        NUMERIC NOT NULL CHECK ( weight > 0 ),
29    PRIMARY KEY (ingredient_id, pizza)
30);
31CREATE TABLE ingredient_stock
32(
33    ingredient_id INT PRIMARY KEY REFERENCES ingredient,
34    weight        NUMERIC NOT NULL CHECK ( weight > 0 )
35);
36CREATE TABLE customer
37(
38    phone_number TEXT PRIMARY KEY CHECK ( phone_number ~ '^8[0-9]{10}$' ),
39    discount     NUMERIC
40);
41CREATE TYPE order_status AS ENUM ('принят', 'в обработке', 'отгружен', 'выполнен');
42CREATE TABLE "order"
43(
44    id   	 SERIAL 	  PRIMARY KEY,
45    date     timestamp    NOT NULL,
46    customer TEXT         NOT NULL REFERENCES customer,
47    address  TEXT         NOT NULL,
48    status   order_status NOT NULL
49);
50CREATE TABLE order_item
51(
52    order_id INT REFERENCES "order",
53    pizza        TEXT REFERENCES pizza,
54    count        INT NOT NULL,
55    PRIMARY KEY (order_id, pizza)
56);
57CREATE TABLE provider
58(
59    id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
60    org_name     TEXT NOT NULL ,
61    phone_number TEXT NOT NULL CHECK ( phone_number ~ '^8[0-9]{10}$' ),
62    UNIQUE (org_name, phone_number)
63);
64CREATE TABLE provider_ingredient(
65    provider_id INT REFERENCES provider,
66    ingredient_id INT REFERENCES ingredient,
67    price NUMERIC NOT NULL,
68    PRIMARY KEY (provider_id, ingredient_id)
69);