---

-- Включаем поддержку внешних ключей
PRAGMA foreign_keys = ON;

-- 1. Таблица клиентов (clients)
CREATE TABLE clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    grade TEXT,
    created_at TEXT
);

-- 2. Таблица заказов (orders)
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    price REAL,
    client_id INTEGER,
    created_at TEXT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
);

-- 3. Таблица продуктов (products)
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    price REAL,
    what_inside TEXT
);

-- 4. Связующая таблица заказов и продуктов (order_products)
-- Составной первичный ключ (order_id + product_id)
CREATE TABLE order_products (
    order_id INTEGER,
    product_id INTEGER,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);