---

-- Создание базы данных (если нужно)
CREATE DATABASE IF NOT EXISTS shop_db;
USE shop_db;

-- Таблица ролей пользователей
CREATE TABLE Roles (
    RoleID INT PRIMARY KEY AUTO_INCREMENT,
    RoleName VARCHAR(50) NOT NULL UNIQUE,
    Description TEXT
);

-- Таблица пользователей
CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(100) NOT NULL UNIQUE,
    Email VARCHAR(255) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    RegistrationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    RoleID INT,
    FOREIGN KEY (RoleID) REFERENCES Roles(RoleID) ON DELETE SET NULL
);

-- Таблица статусов заказов
CREATE TABLE OrderStatuses (
    StatusID INT PRIMARY KEY AUTO_INCREMENT,
    StatusName VARCHAR(50) NOT NULL UNIQUE,
    Description TEXT
);

-- Таблица заказов
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    StatusID INT NOT NULL,
    ShippingAddress TEXT,
    Comments TEXT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE,
    FOREIGN KEY (StatusID) REFERENCES OrderStatuses(StatusID) ON DELETE RESTRICT
);

-- Таблица производителей
CREATE TABLE Manufacturers (
    ManufacturerID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL UNIQUE,
    Country VARCHAR(100),
    ContactInfo TEXT
);

-- Таблица категорий товаров
CREATE TABLE ProductCategories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName VARCHAR(255) NOT NULL UNIQUE,
    ParentCategoryID INT,
    Description TEXT,
    FOREIGN KEY (ParentCategoryID) REFERENCES ProductCategories(CategoryID) ON DELETE SET NULL
);

-- Таблица товаров
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT NOT NULL DEFAULT 0,
    CategoryID INT NOT NULL,
    ManufacturerID INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    IsActive BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (CategoryID) REFERENCES ProductCategories(CategoryID) ON DELETE RESTRICT,
    FOREIGN KEY (ManufacturerID) REFERENCES Manufacturers(ManufacturerID) ON DELETE RESTRICT
);

-- Дополнительная таблица для связи «многие‑ко‑многим»: товары в заказах
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL DEFAULT 1,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE RESTRICT
);

-- Создание индексов для ускорения поиска
CREATE INDEX idx_users_username ON Users(Username);
CREATE INDEX idx_orders_user_id ON Orders(UserID);
CREATE INDEX idx_orders_status_id ON Orders(StatusID);
CREATE INDEX idx_products_category_id ON Products(CategoryID);
CREATE INDEX idx_products_manufacturer_id ON Products(ManufacturerID);
CREATE INDEX idx_order_items_order_id ON OrderItems(OrderID);