---

-- Table: Stores
CREATE TABLE Stores (
    store_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200)
);

-- Table: Consultants
CREATE TABLE Consultants (
    consultant_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    store_id INT,
    FOREIGN KEY (store_id) REFERENCES Stores(store_id)
);

-- Table: Products
CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    type VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Table: Sales
CREATE TABLE Sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    product_id INT,
    consultant_id INT,
    store_id INT,
    final_price DECIMAL(10,2),
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (consultant_id) REFERENCES Consultants(consultant_id),
    FOREIGN KEY (store_id) REFERENCES Stores(store_id)
);
INSERT INTO Stores (name, address) VALUES
('Central', '10 Lenin Ave'),
('North', '25 Gagarin St'),
('South', '7 Stroitely St');

INSERT INTO Consultants (full_name, store_id) VALUES
('Ivanova A.A.', 1),
('Petrov B.V.', 1),
('Sidorova V.G.', 2),
('Smirnov D.E.', 3),
('Kuznetsova E.F.', 2),
('Popov G.H.', 1),
('Sokolova I.J.', 3),
('Mikhailov K.L.', 2),
('Fedorova M.N.', 3),
('Vasiliev O.P.', 1);

INSERT INTO Products (name, type, price, stock_quantity) VALUES
('iPhone 15', 'smartphone', 79999.00, 12),
('Samsung Galaxy S24', 'smartphone', 69999.00, 8),
('iPad Air', 'tablet', 54999.00, 5),
('Xiaomi 13T', 'smartphone', 39999.00, 10),
('Google Pixel 8', 'smartphone', 64999.00, 4),
('Huawei MatePad', 'tablet', 45999.00, 6),
('iPhone 14', 'smartphone', 59999.00, 7),
('Samsung Tab S9', 'tablet', 71999.00, 3),
('Nothing Phone 2', 'smartphone', 49999.00, 9),
('Realme Pad 2', 'tablet', 24999.00, 11);

INSERT INTO Sales (sale_date, product_id, consultant_id, store_id, final_price) VALUES
('2026-05-01', 1, 1, 1, 79999.00),
('2026-05-02', 2, 2, 1, 69999.00),
('2026-05-03', 3, 3, 2, 54999.00),
('2026-05-04', 4, 4, 3, 39999.00),
('2026-05-05', 5, 5, 2, 64999.00),
('2026-05-06', 6, 6, 1, 45999.00),
('2026-05-07', 7, 7, 3, 59999.00),
('2026-05-08', 8, 8, 2, 71999.00),
('2026-05-09', 9, 9, 3, 49999.00),
('2026-05-10', 10, 10, 1, 24999.00);