---

-- Таблица категорий (Один ко многим)
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

-- Таблица товаров с внешним ключом
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    image_path TEXT DEFAULT 'placeholder.png', -- Заглушка по умолчанию
    FOREIGN KEY (category_id) REFERENCES categories(id) 
        ON DELETE SET NULL
);

-- Таблица позиций (сочетание Модель + Размер)
CREATE TABLE IF NOT EXISTS stock (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    size INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE(product_id, size) -- Чтобы не было дублей одного размера
);

INSERT INTO products (category_id, name, price) VALUES (?, ?, ?);

SELECT 
    p.id, 
    p.name, 
    p.price,
    -- Если ID товара НЕТ в списке заказанных за прошлый месяц, делаем скидку
    CASE 
        WHEN p.id NOT IN (
            SELECT DISTINCT product_id 
            FROM orders 
            WHERE strftime('%Y-%m', order_date) = strftime('%Y-%m', 'now', '-1 month')
        ) THEN p.price * 0.75
        ELSE p.price
    END AS final_price
FROM products p;

SELECT 
    p.id,
    p.name,
    s.size,
    s.quantity,
    CASE 
        WHEN s.quantity > 5 THEN 'много'
        ELSE 'мало'
    END AS stock_status,
    CASE 
        WHEN s.quantity <= 3 THEN 1  -- Флаг для подсветки цветом
        ELSE 0
    END AS highlight_flag
FROM products p
JOIN stock s ON p.id = s.product_id;