---

DROP TABLE IF EXISTS "ПОСТАВКА" CASCADE;
DROP TABLE IF EXISTS "ЗАКАЗ" CASCADE;
DROP TABLE IF EXISTS "КУЛИНАРНОЕ_ИЗДЕЛИЕ" CASCADE;
DROP TABLE IF EXISTS "СОТРУДНИК" CASCADE;
DROP TABLE IF EXISTS "КЛИЕНТ" CASCADE;
DROP TABLE IF EXISTS "ПРОДУКТ" CASCADE;
DROP TABLE IF EXISTS "ПОСТАВЩИК" CASCADE;
drop table if exists "СОСТАВ_ИЗДЕЛИЯ" cascade;

CREATE TABLE "ПОСТАВЩИК" (
    "id_поставщика" INT generated by default AS IDENTITY PRIMARY KEY,
    "наименование" VARCHAR(100) NOT NULL,
    "ИНН" VARCHAR(12) NOT NULL UNIQUE,
    "телефон" VARCHAR(15) UNIQUE
);

CREATE TABLE "ПРОДУКТ" (
    "id_продукта" INT generated by default AS IDENTITY PRIMARY KEY,
    "наименование" VARCHAR(100) NOT NULL,
    "единица_измерения" VARCHAR(20) NOT NULL
);

CREATE TABLE "КЛИЕНТ" (
    "id_клиента" INT generated by default AS IDENTITY PRIMARY KEY,
    "ФИО" VARCHAR(100) NOT NULL,
    "телефон" VARCHAR(15) UNIQUE
);


CREATE TABLE "СОТРУДНИК" (
    "id_сотрудника" INT generated by default AS IDENTITY PRIMARY KEY,
    "ФИО" VARCHAR(100) NOT NULL,
    "телефон" VARCHAR(15) UNIQUE,
    "должность" VARCHAR(100) NOT NULL
);



CREATE TABLE "КУЛИНАРНОЕ_ИЗДЕЛИЕ" (
    "id_изделия" INT generated by default AS IDENTITY PRIMARY KEY,
    "наименование" VARCHAR(100) NOT NULL,
    "цена" DECIMAL(10,2) NOT NULL CHECK ("цена" >= 0),
    "id_сотрудника" INT NOT NULL,
    CONSTRAINT "fk_изделие_сотрудник"
        FOREIGN KEY ("id_сотрудника")
        REFERENCES "СОТРУДНИК" ("id_сотрудника")
        ON DELETE RESTRICT ON UPDATE CASCADE
);

create table "СОСТАВ_ИЗДЕЛИЯ"(
"id_состава" INT generated by default AS IDENTITY PRIMARY key,
"id_продукта" int not null,
"количество" decimal(10, 2) not null CHECK ("количество" > 0),
"id_изделия" int not null,
 constraint "fk_состав_продукт"
 foreign key ("id_продукта")
 references "ПРОДУКТ"("id_продукта")
  ON DELETE RESTRICT ON UPDATE cascade,
  constraint "fk_состав_изделие"
  foreign key ("id_изделия")
  references "КУЛИНАРНОЕ_ИЗДЕЛИЕ"("id_изделия")
    ON DELETE RESTRICT ON UPDATE cascade
);

CREATE TABLE "ЗАКАЗ" (
    "id_заказа" INT generated by default AS IDENTITY PRIMARY KEY,
    "количество" INT NOT NULL CHECK ("количество" > 0),
    "цена" DECIMAL(10,2) NOT NULL CHECK ("цена" >= 0),
    "сумма" DECIMAL(10,2) NOT NULL CHECK ("сумма" >= 0),
    "id_клиента" INT NOT NULL,
    "id_изделия" INT NOT NULL,
    CONSTRAINT "fk_заказ_клиент"
        FOREIGN KEY ("id_клиента")
        REFERENCES "КЛИЕНТ" ("id_клиента")
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "fk_заказ_изделие"
        FOREIGN KEY ("id_изделия")
        REFERENCES "КУЛИНАРНОЕ_ИЗДЕЛИЕ" ("id_изделия")
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE "ПОСТАВКА" (
    "id_поставки" INT generated by default AS IDENTITY PRIMARY KEY,


    "id_поставщика" INT NOT NULL,
    "id_продукта" INT NOT NULL,
    "дата_поставки" DATE NOT NULL DEFAULT CURRENT_DATE,
    "количество" INT NOT NULL CHECK ("количество" > 0),
    CONSTRAINT "fk_поставка_поставщик"
        FOREIGN KEY ("id_поставщика")
        REFERENCES "ПОСТАВЩИК" ("id_поставщика")
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "fk_поставка_продукт" 
    FOREIGN KEY ("id_продукта")
        REFERENCES "ПРОДУКТ" ("id_продукта")
        ON DELETE RESTRICT ON UPDATE CASCADE
);