---

-- =====================================================
-- 1. Очистка старых таблиц (если они были)
-- =====================================================
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;

-- =====================================================
-- 2. Справочные таблицы
-- =====================================================
CREATE TABLE "ПОСТАВЩИК" (
    "id_поставщика" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "наименование" VARCHAR(100) NOT NULL,
    "ИНН" VARCHAR(12) NOT NULL UNIQUE,
    "телефон" VARCHAR(15) UNIQUE
);

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

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

-- =====================================================
-- 3. Основные таблицы
-- =====================================================
CREATE TABLE "СОТРУДНИК" (
    "id_сотрудника" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "ФИО" VARCHAR(100) NOT NULL,
    "телефон" VARCHAR(15) UNIQUE,
    "должность" VARCHAR(100) NOT NULL
);

CREATE TABLE "КУЛИНАРНОЕ_ИЗДЕЛИЕ" (
    "id_изделия" INT GENERATED ALWAYS 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
);

-- =====================================================
-- 4. Таблицы операций (Журналы)
-- =====================================================
CREATE TABLE "ЗАКАЗ" (
    "id_заказа" INT GENERATED ALWAYS 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 ALWAYS 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
);