---

 CREATE DATABASE IF NOT EXISTS bicycle_store_db
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bicycle_store_db;

DROP TABLE IF EXISTS Order_Product;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Roles;
DROP TABLE IF EXISTS Orders_Status;
DROP TABLE IF EXISTS Pickup_Point;
DROP TABLE IF EXISTS Category;
DROP TABLE IF EXISTS Manufacturer;
DROP TABLE IF EXISTS Provider;

CREATE TABLE Roles
(
    Roles_ID INT AUTO_INCREMENT PRIMARY KEY,
    Roles_Name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Users
(
    Users_ID INT AUTO_INCREMENT PRIMARY KEY,
    Users_Surename VARCHAR(100) NOT NULL,
    Users_Name VARCHAR(100) NOT NULL,
    Users_Login VARCHAR(100) NOT NULL UNIQUE,
    Users_Pasword VARCHAR(255) NOT NULL,
    Roles_ID INT NOT NULL,
    CONSTRAINT FK_Users_Roles FOREIGN KEY (Roles_ID) REFERENCES Roles (Roles_ID) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Category
(
    Category_ID INT AUTO_INCREMENT PRIMARY KEY,
    Category_Name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Manufacturer
(
    Manufacturer_ID INT AUTO_INCREMENT PRIMARY KEY,
    Manufacturer_Name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Provider
(
    Provider_ID INT AUTO_INCREMENT PRIMARY KEY,
    Provider_Name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Product
(
    Product_ID INT AUTO_INCREMENT PRIMARY KEY,
    Product_Article VARCHAR(50) NULL UNIQUE,
    Product_Name VARCHAR(200) NOT NULL,
    Product_Description TEXT NULL,
    Category_ID INT NOT NULL,
    Manufacturer_ID INT NOT NULL,
    Provider_ID INT NOT NULL,
    Product_Cost DECIMAL(10, 2) NOT NULL,
    Product_Unit VARCHAR(20) NOT NULL,
    Product_Discount_Amount DECIMAL(5, 2) NOT NULL,
    Product_Quantity_In_Stock INT NOT NULL,
    Product_Photo_Path VARCHAR(255) NULL,
    CONSTRAINT FK_Product_Category FOREIGN KEY (Category_ID) REFERENCES Category (Category_ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT FK_Product_Manufacturer FOREIGN KEY (Manufacturer_ID) REFERENCES Manufacturer (Manufacturer_ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT FK_Product_Provider FOREIGN KEY (Provider_ID) REFERENCES Provider (Provider_ID) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Orders_Status
(
    Status_ID INT AUTO_INCREMENT PRIMARY KEY,
    Status_Name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Pickup_Point
(
    Pickup_Point_ID INT AUTO_INCREMENT PRIMARY KEY,
    Addres VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Orders
(
    Orders_ID INT AUTO_INCREMENT PRIMARY KEY,
    Orders_Article VARCHAR(50) NOT NULL UNIQUE,
    Orders_Date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Orders_Delivery_Date DATETIME NULL,
    Pickup_Point_ID INT NOT NULL,
    Status_ID INT NOT NULL,
    Users_ID INT NULL,
    CONSTRAINT FK_Orders_Pickup_Point FOREIGN KEY (Pickup_Point_ID) REFERENCES Pickup_Point (Pickup_Point_ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT FK_Orders_Status FOREIGN KEY (Status_ID) REFERENCES Orders_Status (Status_ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT FK_Orders_Users FOREIGN KEY (Users_ID) REFERENCES Users (Users_ID) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Orders_Product
(
    Orders_ID INT NOT NULL,
    Product_ID INT NOT NULL,
    Count INT NOT NULL,
    PRIMARY KEY (Orders_ID, Product_ID),
    CONSTRAINT FK_Orders_Product_Orders FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_Order_Product_Product FOREIGN KEY (Product_ID) REFERENCES Product (Product_ID) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;