---

CREATE DATABASE building_materials;

USE building_materials;

CREATE TABLE roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE manufacturers (
    manufacturer_id INT PRIMARY KEY AUTO_INCREMENT,
    manufacturer_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    supplier_id INT NOT NULL,
    manufacturer_id INT NOT NULL,
    category_id INT NOT NULL,
    discount INT DEFAULT 0,
    stock_quantity INT NOT NULL,
    description TEXT,
    image_path VARCHAR(255),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(manufacturer_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    delivery_date DATE NOT NULL,
    pickup_address VARCHAR(255) NOT NULL,
    client_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    status VARCHAR(50) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES users(user_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    item_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO roles (role_name) VALUES
('Гость'),
('Клиент'),
('Менеджер'),
('Администратор');

INSERT INTO users (full_name, login, password, role_id) VALUES
('Иван Петров', 'client', '123', 2),
('Мария Смирнова', 'manager', '123', 3),
('Алексей Иванов', 'admin', '123', 4);

INSERT INTO categories (category_name) VALUES
('Пиломатериалы'),
('Крепеж'),
('Лакокрасочные'),
('Сантехника'),
('Электрика');

INSERT INTO suppliers (supplier_name) VALUES
('ООО "СтройМаркет"'),
('ИП "ПрофиМатериалы"'),
('ЗАО "СтройРесурс"'),
('ТД "ТехноСтрой"');

INSERT INTO manufacturers (manufacturer_name) VALUES
('Knauf'),
('Ceresit'),
('Tikkurila'),
('Roca'),
('Schneider Electric');

INSERT INTO products (product_name, category_id, description, manufacturer_id, supplier_id, price, unit, discount, stock_quantity, image_path) VALUES
('Гипсокартон влагостойкий', 1, 'Лист 2500х1200х12.5 мм, влагостойкий', 1, 1, 650.00, 'лист', 5, 50, ''),
('Дюбель-гвоздь 6х40', 2, 'Упаковка 100 шт', 2, 2, 250.00, 'уп.', 0, 200, ''),
('Краска интерьерная белая', 3, 'Ведро 10 л, матовая', 3, 3, 2800.00, 'шт', 10, 30, ''),
('Унитаз компакт', 4, 'Белый, с бачком', 4, 4, 5500.00, 'комплект', 0, 15, ''),
('Розетка двойная', 5, 'Белая, с заземлением', 5, 1, 350.00, 'шт', 5, 100, '');

INSERT INTO orders (order_date, delivery_date, pickup_address, client_id, code, status) VALUES
('2025-05-20', '2025-05-25', 'ул. Строителей, 15', 1, 'ORD001', 'Доставлен'),
('2025-05-22', '2025-05-28', 'пр. Мира, 8', 1, 'ORD002', 'В обработке');

INSERT INTO order_items (order_id, product_id, quantity, item_price) VALUES
(1, 1, 10, 650.00),
(1, 2, 5, 250.00),
(2, 3, 2, 2800.00);