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

CREATE TABLE Administrators (
    mail          TEXT PRIMARY KEY,
    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,
    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 VALUES ('1', 'john_wiliams@gmail.com', 'Джон Таунер Уильямс', 'john_wil');
INSERT INTO Client VALUES ('pechkin@awake.su', 'Петр Сергеевич Печкин', 'pechka_petr');
INSERT INTO Client VALUES ('joke@gmail.com', 'Джокер', 'joke');
INSERT INTO Client VALUES ('alex@gmail.com', 'Алексей Ложкин', 'alex');
INSERT INTO Client VALUES ('new@gmail.com', 'Игорь', 'joke');
INSERT INTO Client VALUES ('abc@gmail.com', 'Майкл', 'simple');
INSERT INTO Client VALUES ('stepan@gmail.com', 'Степан Спенович Степыч', 'stepych');

INSERT INTO Administrators VALUES ('wayne@gmail.com', 'Брюс Уейн', 'bat');
INSERT INTO Administrators 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, id_group, name, ingredients, mass) VALUES ('1', 'Пицца Мексиканская', '1', 'Тесто, соус чили, курица, помидоры', '260');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Пицца Сырная', '1', 'Тесто, сыр, курица', '340');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Пицца Пепперони', '1', 'Тесто, сыр, соус чили, колбаса', '320');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Coca Cola', '2', 'Вода, сахар, ароматизаторы', '100');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Sprite', '2', 'Вода, сахар, ароматизаторы', '100');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Ролл Цезарь', '4', 'Лаваш, курица, салат', '');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Ролл Острый', '4', 'Лаваш, курица, соус чили', '');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Морс', '2', 'Вода, сахар, ягоды', '150');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Салат Крабовый', '3', 'Салат, овощи, крабовые палочки', '180');
INSERT INTO Products (id_group, name, ingredients, mass) VALUES ('Салат Цезарь', '3', 'Салат, курица, сухари, соус цезарь', '230');

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 (id_product, id_order) VALUES ('1', '2');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('2', '4');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('3', '4');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('3', '2');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('4', '2');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('4', '6');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('4', '3');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('5', '6');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('5', '2');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('5', '4');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('6', '4');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('6', '2');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('7', '1');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('8', '1');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('8', '4');
INSERT INTO Product_In_Order (id_product, id_order) VALUES ('8', '8');



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