CREATE TABLE Client (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    mail          TEXT,
    name          TEXT,
    password      TEXT,
    UNIQUE (mail)
);

CREATE TABLE Administrators (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    mail          TEXT,
    name          TEXT,
    password      TEXT,
    UNIQUE (mail)
);

CREATE TABLE Group_Products (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT
);


CREATE TABLE Orders (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    DATE        INTEGER                  DEFAULT [strftime('%s', 'now')],
    client_mail TEXT,
    STATUS      TEXT,
    adres       TEXT,
    FOREIGN KEY (client_mail)            REFERENCES Client (mail)
);

CREATE TABLE Products (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    id_group    INTEGER,
    name        TEXT,
    ingredients TEXT,
    mass        INTEGER,
    price       INTEGER,
    FOREIGN KEY (id_group)              REFERENCES Group_Products (id)
);

CREATE TABLE Product_In_Order (
    id_product  INTEGER,
    id_order    INTEGER,
    PRIMARY KEY (id_product, id_order)
    FOREIGN KEY (id_product)            REFERENCES Produts (id),
    FOREIGN KEY (id_order)              REFERENCES Orders (id)
);


CREATE INDEX idx_Clients ON Client (
    mail
);

CREATE INDEX idx_Orders ON Orders (
    DATE,
    client_mail
);


INSERT INTO Client (id, mail, name, password) VALUES ('1', 'john_wiliams@gmail.com', 'Джон Таунер Уильямс', 'john_wil');
INSERT INTO Client (mail, name, password) VALUES ('pechkin@awake.su', 'Петр Сергеевич Печкин', 'pechka_petr');
INSERT INTO Client (mail, name, password) VALUES ('joke@gmail.com', 'Джокер', 'joke');
INSERT INTO Client (mail, name, password) VALUES ('alex@gmail.com', 'Алексей Ложкин', 'alex');
INSERT INTO Client (mail, name, password) VALUES ('new@gmail.com', 'Игорь', 'joke');
INSERT INTO Client (mail, name, password) VALUES ('abc@gmail.com', 'Майкл', 'simple');
INSERT INTO Client (mail, name, password) VALUES ('stepan@gmail.com', 'Степан Спенович Степыч', 'stepych');

INSERT INTO Administrators (id, mail, name, password) VALUES ('1', 'wayne@gmail.com', 'Брюс Уейн', 'bat');
INSERT INTO Administrators (mail, name, password) VALUES ('admin@awake.su', 'Awakens', 'star');

INSERT INTO Group_Products (id, name) VALUES ('1', 'Пиццы');
INSERT INTO Group_Products (name) VALUES ('Напитки');
INSERT INTO Group_Products (name) VALUES ('Салаты');
INSERT INTO Group_Products (name) VALUES ('Суши и роллы');

INSERT INTO Products (id, name, id_group, ingredients, mass, price) VALUES ('1', 'Пицца Мексиканская', '1', 'Тесто, соус чили, курица, помидоры', '260', '560');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Пицца Сырная', '1', 'Тесто, сыр, курица', '340', '320');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Пицца Пепперони', '1', 'Тесто, сыр, соус чили, колбаса', '320', '730');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Coca Cola', '2', 'Вода, сахар, ароматизаторы', '100', '650');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Sprite', '2', 'Вода, сахар, ароматизаторы', '100', '640');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Ролл Цезарь', '4', 'Лаваш, курица, салат', '120', '350');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Ролл Острый', '4', 'Лаваш, курица, соус чили', '130', '770');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Морс', '2', 'Вода, сахар, ягоды', '150', '540');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Салат Крабовый', '3', 'Салат, овощи, крабовые палочки', '180', '650');
INSERT INTO Products (name, id_group, ingredients, mass, price) VALUES ('Салат Цезарь', '3', 'Салат, курица, сухари, соус цезарь', '230', '530');

INSERT INTO Orders (id, DATE, client_mail, STATUS, adres) VALUES ('1', strftime('%s', 'now'), 'alex@gmail.com', 'Обрабатывается', 'Владивосток');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'alex@gmail.com', 'Доставлено', 'Артём');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'new@gmail.com', 'Обрабатывается', 'Уссурийск');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'new@gmail.com', 'Обрабатывается', 'Владивосток');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'joke@gmail.com1', 'Обрабатывается', 'Артём');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'john_wiliams@gmail.com', 'Обрабатывается', 'Владивосток');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'john_wiliams@gmail.com', 'Доставлено', 'Владивосток');
INSERT INTO Orders (DATE, client_mail, STATUS, adres) VALUES (strftime('%s', 'now'), 'pechkin@awake.su', 'Доставлено', 'Владивосток');

INSERT INTO Product_In_Order VALUES ('1', '2');
INSERT INTO Product_In_Order VALUES ('2', '4');
INSERT INTO Product_In_Order VALUES ('3', '4');
INSERT INTO Product_In_Order VALUES ('3', '2');
INSERT INTO Product_In_Order VALUES ('4', '2');
INSERT INTO Product_In_Order VALUES ('4', '6');
INSERT INTO Product_In_Order VALUES ('4', '3');
INSERT INTO Product_In_Order VALUES ('5', '6');
INSERT INTO Product_In_Order VALUES ('5', '2');
INSERT INTO Product_In_Order VALUES ('5', '4');
INSERT INTO Product_In_Order VALUES ('6', '4');
INSERT INTO Product_In_Order VALUES ('6', '2');
INSERT INTO Product_In_Order VALUES ('7', '1');
INSERT INTO Product_In_Order VALUES ('8', '1');
INSERT INTO Product_In_Order VALUES ('8', '4');
INSERT INTO Product_In_Order VALUES ('8', '8');



/*
* Удаление всех таблиц
DROP TABLE Product_In_Order;
DROP TABLE Products;
DROP TABLE Group_Products;
DROP TABLE Orders;
DROP TABLE Client;
DROP TABLE Administrators;
*/

--
SELECT cl_name AS 'Имя клиента', Group_Products.name AS 'Группа товаров', cl_count AS 'Количество заказов', price_sum AS 'Общая сумма заказа'
FROM
  (
    SELECT *, name AS cl_name
    FROM
       (
          (
            SELECT  client_mail, id_group, COUNT(client_mail) AS cl_count, SUM(price) AS price_sum
            FROM(
                   ( Products JOIN Product_In_Order ON  Products.id = Product_In_Order.id_product)
                   JOIN Orders
                   ON id_order = Orders.id
                )
            GROUP BY client_mail, id_group
          )
          JOIN
          Client
          ON Client.mail = client_mail
      )
  )
JOIN
Group_Products
ON Group_Products.id = id_group