Test Data Generation

DELETE FROM provider_ingredient WHERE True;
DELETE FROM provider            WHERE True;
DELETE FROM order_item          WHERE True;
DELETE FROM pizza_ingredient    WHERE True;
DELETE FROM pizza               WHERE True;
DELETE FROM ingredient_stock    WHERE True;
DELETE FROM ingredient          WHERE True;
DELETE FROM "order"             WHERE True;
DELETE FROM customer            WHERE True;
-------------------
INSERT INTO customer
SELECT
    '8' || CAST(FLOOR(random() * 100) + 900 AS TEXT) || CAST(FLOOR(random() * 9000000) + 1000000 AS TEXT)
        AS phone_number,
    MIN(ABS(FLOOR((random() + random() + random() + random() + random() + random() + random() + random() + random() + random()) * 2 - 10))) AS discount
FROM (SELECT generate_series(1, 10000)) AS t1
GROUP BY phone_number;
-------------------
INSERT INTO ingredient("name")
SELECT UNNEST(ARRAY['cheese', 'dough', 'tomato', 'chicken', 'bacon', 'red sauce', 'white sauce', 'mushroom', 'onion', 'ketchup', 'mayonnaise', 'salt', 'pepper', 'chili', 'pineapple', 'sour cream', 'sausage', 'parmesan', 'blue cheese', 'ham', 'pepperoni', 'sugar', 'water', 'egg', 'corn']);
-------------------
INSERT INTO ingredient_stock("ingredient_id", "weight")
SELECT "id", floor((random() + random() * 2 + random() * 2 + random() * 3 + random() * 3 + random() * 4)  * 9000 + 10000)
FROM ingredient;
-------------------
INSERT INTO "order" ("date", "customer", "address", "status")
SELECT
    "date" + make_interval(
        hours:=FLOOR(random() * 12 + 10)::INT,
        mins :=FLOOR(random() * 60)::INT
    ) AS "date",
    "customer",
    (ARRAY['Дерибасовская', '221B, Baker Street', 'Лубянка', 'Литейный, 4', 'Кантемировская, 2', 'Какой-то адрес', 'Ещё один адрес', 'У чёрта на куличках', 'в Караганду', 'тому, кто это прочитал'])[FLOOR(1 + random() * 10)]  AS address,
    CASE
        WHEN CURRENT_DATE > "date" + INTERVAL '2 DAY'
            THEN 'выполнен'
        ELSE (enum_range(NULL::order_status))[1 + FLOOR(random() * 4)]
    END AS status
FROM (
    SELECT
        "date",
        UNNEST((SELECT (ARRAY_AGG(phone_number))[1:FLOOR(rand * 100 + 100)] FROM customer ORDER BY random())) AS customer
    FROM (SELECT CURRENT_DATE - generate_series(1, 500) AS "date", random() AS rand) AS t1
) AS t2;
-------------------
INSERT INTO pizza ("name", "price", "weight")
SELECT
    UNNEST(ARRAY['Margarita', 'Hawaii', 'Pepperoni', '4 cheeses', 'Neapolitana', 'Sea', 'Calzone', 'Mushroom']) AS "name",
    UNNEST(price)  AS price,
    UNNEST(weight) AS weight
FROM (
    SELECT
        ARRAY_AGG(FLOOR(random() * 10) * 20 + 450) AS price,
        ARRAY_AGG(FLOOR(random() * 300) + 500)     AS weight
    FROM generate_series(1, 8)
) AS t1;
-------------------
WITH ingrs AS (
    SELECT
        pizza,
        weight,
        (SELECT (ARRAY_AGG("id"))[1:num_ingredients] FROM ingredient ORDER BY random()) AS ingredient_ids,
        (SELECT ARRAY_AGG(random()) AS weights FROM generate_series(1, num_ingredients)) AS weights
    FROM (
        SELECT
            "name" AS pizza,
            FLOOR(random() * 5 + 8)::INT AS num_ingredients,
            weight
        FROM pizza
    ) AS t1
)

INSERT INTO pizza_ingredient("pizza", "ingredient_id", "weight")
SELECT
    pizza,
    UNNEST(ingredient_ids) AS ingredient_id,
    1 + (UNNEST(weights) / (SELECT SUM(w) FROM UNNEST(weights) AS w) * weight)::INT AS weight
FROM ingrs;
-------------------
WITH num_pizzas_total AS (
    SELECT
        "id" AS order_id,
        1 + FLOOR(ABS(random() + random() + random() + random() + random() + random() + random() + random() + random() - 4.5)) AS num_pizzas
    FROM "order"
),

num_each_pizzas AS (
    SELECT
        order_id,
        UNNEST((SELECT ARRAY_AGG("name") FROM (SELECT "name" FROM pizza ORDER BY random() LIMIT num_pizzas) AS t2)) AS pizza,
        UNNEST((SELECT ARRAY_AGG(FLOOR(1 / POWER(random(), 0.25))) FROM generate_series(1, num_pizzas::INT))) AS "count"
    FROM num_pizzas_total
)

INSERT INTO order_item
SELECT
    order_id,
    pizza,
    CASE
        WHEN random() < 0.001
            THEN -FLOOR(random() * 1000)
        ELSE "count"
    END AS "count"
FROM num_each_pizzas;
-------------------
INSERT INTO provider("org_name", "phone_number")
SELECT
    UNNEST((ARRAY['ООО "Моя оборона"', 'ООО "Зеленоглазое такси"', 'ООО "ИЯИЁ Батарейка"', 'ООО "Нли Ю"', 'ООО "Паньки"', 'ООО "Какие люди"', 'ООО "Восьмиклассница"', 'ООО "Пошла родимая"', 'ООО "Паньки"'])) AS org_name,
    UNNEST((
        SELECT ARRAY_AGG(
            '8'
            ||
            CAST(FLOOR(random() * 100) + 900 AS TEXT)
            ||
            CAST(FLOOR(random() * 9000000) + 1000000 AS TEXT))
        FROM generate_series(1, 9)
    )) AS phone_number;
-------------------
WITH ingredient_quantity AS (
    SELECT
        "id" AS ingredient_id,
        FLOOR(random() * 5 + 2)::INT AS quantity
    FROM ingredient
),

ingredient_with_provider AS (
    SELECT
        ingredient_id,
        UNNEST((
            SELECT ARRAY_AGG("id")
            FROM (
                SELECT "id"
                FROM provider
                ORDER BY random()
                LIMIT quantity
            ) AS t1
        )) AS provider_id,
        FLOOR(random() * 100 + 50) AS mean_price,
        FLOOR(random() * 25) AS var_price
    FROM ingredient_quantity
),
ingredient_with_price_and_provider AS (
    SELECT
        provider_id,
        ingredient_id,
        FLOOR(random() * var_price + mean_price) AS price
    FROM ingredient_with_provider
)

INSERT INTO provider_ingredient
SELECT *
FROM ingredient_with_price_and_provider