---

DROP DATABASE IF EXISTS bicycle_store;
CREATE DATABASE bicycle_store
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bicycle_store;

-- ============================================
-- РОЛИ
-- ============================================

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

-- ============================================
-- ПОЛЬЗОВАТЕЛИ
-- ============================================

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    login VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    patronymic VARCHAR(50),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id)
        REFERENCES roles(role_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ============================================
-- КАТЕГОРИИ
-- ============================================

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

-- ============================================
-- ПРОИЗВОДИТЕЛИ
-- ============================================

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

-- ============================================
-- ПОСТАВЩИКИ
-- ============================================

CREATE TABLE suppliers (
    supplier_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_name VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ============================================
-- ЕДИНИЦЫ ИЗМЕРЕНИЯ
-- ============================================

CREATE TABLE units (
    unit_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    unit_name VARCHAR(30) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ============================================
-- ТОВАРЫ
-- ============================================

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    description TEXT,
    manufacturer_id INT UNSIGNED NOT NULL,
    supplier_id INT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    unit_id INT UNSIGNED NOT NULL,
    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    image_path VARCHAR(255),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_product_price
        CHECK (price >= 0),

    CONSTRAINT chk_product_discount
        CHECK (discount_percent >= 0 AND discount_percent <= 100),

    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_products_manufacturer
        FOREIGN KEY (manufacturer_id)
        REFERENCES manufacturers(manufacturer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_products_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_products_unit
        FOREIGN KEY (unit_id)
        REFERENCES units(unit_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ============================================
-- СТАТУСЫ ЗАКАЗОВ
-- ============================================

CREATE TABLE order_statuses (
    status_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ============================================
-- ЗАКАЗЫ
-- ============================================

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article VARCHAR(30) NOT NULL UNIQUE,
    status_id INT UNSIGNED NOT NULL,
    pickup_address VARCHAR(255) NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_status
        FOREIGN KEY (status_id)
        REFERENCES order_statuses(status_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ============================================
-- СОСТАВ ЗАКАЗА
-- ============================================

CREATE TABLE order_items (
    order_item_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,

    CONSTRAINT chk_order_item_quantity
        CHECK (quantity > 0),

    CONSTRAINT chk_order_item_price
        CHECK (unit_price >= 0),

    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    UNIQUE KEY uq_order_product (order_id, product_id)
) ENGINE=InnoDB;

-- ============================================
-- ИНДЕКСЫ
-- ============================================

CREATE INDEX idx_products_name
    ON products(product_name);

CREATE INDEX idx_products_category
    ON products(category_id);

CREATE INDEX idx_products_price
    ON products(price);

CREATE INDEX idx_products_stock
    ON products(stock_quantity);

CREATE INDEX idx_products_discount
    ON products(discount_percent);

CREATE INDEX idx_orders_date
    ON orders(order_date);

CREATE INDEX idx_order_items_product
    ON order_items(product_id);

-- ============================================
-- РОЛИ
-- ============================================

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

-- ============================================
-- ПОЛЬЗОВАТЕЛИ
-- ============================================

INSERT INTO users
(role_id, login, password_hash, last_name, first_name, patronymic)
VALUES
(1, 'guest', 'guest123', 'Гость', 'Гость', NULL),
(2, 'client', 'client123', 'Иванов', 'Иван', 'Иванович'),
(3, 'manager', 'manager123', 'Петров', 'Пётр', 'Петрович'),
(4, 'admin', 'admin123', 'Сидоров', 'Алексей', 'Алексеевич');

-- ============================================
-- КАТЕГОРИИ
-- ============================================

INSERT INTO categories (category_name) VALUES
('Горные велосипеды'),
('Шоссейные велосипеды'),
('Городские велосипеды'),
('Детские велосипеды'),
('Электровелосипеды'),
('Аксессуары');

-- ============================================
-- ПРОИЗВОДИТЕЛИ
-- ============================================

INSERT INTO manufacturers (manufacturer_name) VALUES
('Forward'),
('Stels'),
('Merida'),
('Cube'),
('Author'),
('Giant');

-- ============================================
-- ПОСТАВЩИКИ
-- ============================================

INSERT INTO suppliers (supplier_name) VALUES
('ООО Веломир'),
('ООО СпортСнаб'),
('ООО Велоторг'),
('ООО Велосипедный мир');

-- ============================================
-- ЕДИНИЦЫ ИЗМЕРЕНИЯ
-- ============================================

INSERT INTO units (unit_name) VALUES
('шт.'),
('компл.'),
('упак.');

-- ============================================
-- СТАТУСЫ ЗАКАЗОВ
-- ============================================

INSERT INTO order_statuses (status_name) VALUES
('Новый'),
('В обработке'),
('Готов к выдаче'),
('Выдан'),
('Отменён');

-- ============================================
-- ТОВАРЫ
-- ============================================

INSERT INTO products
(
    article,
    product_name,
    category_id,
    description,
    manufacturer_id,
    supplier_id,
    price,
    unit_id,
    stock_quantity,
    discount_percent,
    image_path
)
VALUES

(
    'BIK-0001',
    'Forward Apache 27',
    1,
    'Горный велосипед с колёсами 27 дюймов.',
    1,
    1,
    45990.00,
    1,
    12,
    0.00,
    'images/BIK-0001.jpg'
),

(
    'BIK-0002',
    'Stels Navigator 500',
    1,
    'Горный велосипед для прогулок и активного катания.',
    2,
    2,
    38990.00,
    1,
    8,
    10.00,
    'images/BIK-0002.jpg'
),

(
    'BIK-0003',
    'Merida Scultura 100',
    2,
    'Шоссейный велосипед для тренировок и длительных поездок.',
    3,
    3,
    79990.00,
    1,
    5,
    5.00,
    'images/BIK-0003.jpg'
),

(
    'BIK-0004',
    'Cube Attain',
    2,
    'Шоссейный велосипед с алюминиевой рамой.',
    4,
    4,
    109990.00,
    1,
    3,
    0.00,
    'images/BIK-0004.jpg'
),

(
    'BIK-0005',
    'Author Compact',
    3,
    'Городской велосипед для ежедневных поездок.',
    5,
    1,
    52990.00,
    1,
    10,
    15.00,
    'images/BIK-0005.jpg'
),

(
    'BIK-0006',
    'Giant Escape 3',
    3,
    'Универсальный городской велосипед.',
    6,
    2,
    61990.00,
    1,
    7,
    0.00,
    'images/BIK-0006.jpg'
),

(
    'BIK-0007',
    'Stels Pilot 20',
    4,
    'Детский велосипед с колёсами 20 дюймов.',
    2,
    3,
    23990.00,
    1,
    14,
    7.00,
    'images/BIK-0007.jpg'
),

(
    'BIK-0008',
    'Forward Twister 24',
    4,
    'Детский велосипед с колёсами 24 дюйма.',
    1,
    1,
    27990.00,
    1,
    0,
    0.00,
    'images/BIK-0008.jpg'
),

(
    'BIK-0009',
    'Cube Reaction Hybrid',
    5,
    'Электровелосипед с электродвигателем и аккумулятором.',
    4,
    4,
    219990.00,
    1,
    4,
    20.00,
    'images/BIK-0009.jpg'
),

(
    'BIK-0010',
    'Giant Explore E+',
    5,
    'Городской электровелосипед.',
    6,
    2,
    189990.00,
    1,
    6,
    12.00,
    'images/BIK-0010.jpg'
),

(
    'ACC-0001',
    'Велосипедный шлем',
    6,
    'Защитный велосипедный шлем.',
    5,
    1,
    4990.00,
    1,
    25,
    0.00,
    'images/ACC-0001.jpg'
),

(
    'ACC-0002',
    'Велосипедный замок',
    6,
    'Надёжный замок для защиты велосипеда.',
    3,
    3,
    2990.00,
    1,
    30,
    0.00,
    'images/ACC-0002.jpg'
),

(
    'ACC-0003',
    'Фонарь передний',
    6,
    'Передний светодиодный фонарь.',
    2,
    2,
    1990.00,
    1,
    0,
    5.00,
    'images/ACC-0003.jpg'
),

(
    'ACC-0004',
    'Насос велосипедный',
    6,
    'Компактный ручной насос.',
    1,
    1,
    2490.00,
    1,
    18,
    0.00,
    'images/ACC-0004.jpg'
);

-- ============================================
-- ЗАКАЗЫ
-- ============================================

INSERT INTO orders
(article, status_id, pickup_address, order_date)
VALUES
(
    'ORD-000001',
    4,
    'г. Москва, ул. Ленина, д. 10',
    '2026-09-20 10:30:00'
),
(
    'ORD-000002',
    3,
    'г. Москва, ул. Пушкина, д. 25',
    '2026-09-22 14:15:00'
),
(
    'ORD-000003',
    2,
    'г. Москва, ул. Гагарина, д. 5',
    '2026-09-24 09:45:00'
),
(
    'ORD-000004',
    1,
    'г. Москва, ул. Центральная, д. 12',
    '2026-09-27 16:20:00'
);

-- ============================================
-- СОСТАВ ЗАКАЗОВ
-- ============================================

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 45990.00),
(1, 11, 1, 4990.00),
(2, 5, 1, 52990.00),
(2, 12, 2, 2990.00),
(3, 9, 1, 219990.00),
(4, 7, 1, 23990.00),
(4, 13, 2, 1990.00);

-- ============================================
-- ПРЕДСТАВЛЕНИЕ ТОВАРОВ
-- ============================================

CREATE VIEW v_products AS
SELECT
    p.product_id,
    p.article,
    p.product_name,
    c.category_name AS category,
    p.description,
    m.manufacturer_name AS manufacturer,
    s.supplier_name AS supplier,
    p.price,
    u.unit_name AS unit,
    p.stock_quantity,
    p.discount_percent,

    CASE
        WHEN p.discount_percent > 0
        THEN ROUND(
            p.price * (1 - p.discount_percent / 100),
            2
        )
        ELSE p.price
    END AS final_price,

    p.image_path

FROM products p

JOIN categories c
    ON c.category_id = p.category_id

JOIN manufacturers m
    ON m.manufacturer_id = p.manufacturer_id

JOIN suppliers s
    ON s.supplier_id = p.supplier_id

JOIN units u
    ON u.unit_id = p.unit_id;

-- ============================================
-- ПРЕДСТАВЛЕНИЕ ЗАКАЗОВ
-- ============================================

CREATE VIEW v_orders AS
SELECT
    o.order_id,
    o.article AS order_article,
    os.status_name AS status,
    o.pickup_address,
    o.order_date,

    COALESCE(
        SUM(oi.quantity),
        0
    ) AS total_items,

    COALESCE(
        SUM(oi.quantity * oi.unit_price),
        0
    ) AS total_amount

FROM orders o

JOIN order_statuses os
    ON os.status_id = o.status_id

LEFT JOIN order_items oi
    ON oi.order_id = o.order_id

GROUP BY
    o.order_id,
    o.article,
    os.status_name,
    o.pickup_address,
    o.order_date;

-- ============================================
-- ПРОВЕРКА БАЗЫ
-- ============================================

SELECT * FROM v_products;

SELECT * FROM v_orders;

-- Товары, которые есть на складе
SELECT *
FROM v_products
WHERE stock_quantity > 0;

-- Товары, которых нет на складе
SELECT *
FROM v_products
WHERE stock_quantity = 0;

-- Товары со скидкой от 12% до 18.99%
SELECT *
FROM v_products
WHERE discount_percent >= 12
  AND discount_percent < 19;

-- Товары со скидкой 19% и более
SELECT *
FROM v_products
WHERE discount_percent >= 19;

-- Поиск товара
SELECT *
FROM v_products
WHERE product_name LIKE '%велосипед%'
   OR description LIKE '%велосипед%'
   OR manufacturer LIKE '%Forward%';

-- Сортировка по цене
SELECT *
FROM v_products
ORDER BY price ASC;

-- Сортировка по количеству на складе
SELECT *
FROM v_products
ORDER BY stock_quantity ASC;