CREATE TABLE customer (
    customer_id int  NOT NULL,
    name varchar(20)  NOT NULL,
    email varchar(30)  NOT NULL,
    CONSTRAINT customer_pk PRIMARY KEY (customer_id),
    CONSTRAINT customer_id_check CHECK (customer_id > 0)
);

CREATE TABLE dev_group (
    leader_id int  NOT NULL,
    group_id int  NOT NULL,
    name char(20)  NOT NULL,
    room int  NOT NULL,
    CONSTRAINT dev_group_pk PRIMARY KEY (group_id),
    CONSTRAINT leader_id_check CHECK (leader_id > 0)
);


CREATE TABLE worker (
    name varchar(20)  NOT NULL,
    worker_id int  NOT NULL,
    CONSTRAINT worker_id PRIMARY KEY (worker_id)
);

CREATE TABLE worker_updates (
    worker_id int  NOT NULL,
    group_id int  NOT NULL,
    new_salary int  NOT NULL,
    new_status char(20)  NOT NULL,
    time_start date  NOT NULL,
    time_end date,
    CONSTRAINT worker_updates_pk PRIMARY KEY (worker_id,time_start),
    CONSTRAINT worker_updates_dev_group FOREIGN KEY (group_id) REFERENCES dev_group (group_id),
    CONSTRAINT worker_updates_worker FOREIGN KEY (worker_id) REFERENCES worker (worker_id),
    CONSTRAINT time_check CHECK (time_end >= time_start)
);


CREATE TABLE product (
    product_id int  NOT NULL,
    product_name varchar(20)  NOT NULL,
    price int  NOT NULL,
    group_id int  NOT NULL,
    CONSTRAINT product_pk PRIMARY KEY (product_id),
    CONSTRAINT product_dev_group FOREIGN KEY (group_id) REFERENCES dev_group (group_id),
    CONSTRAINT price_check CHECK (price > 0)

);


CREATE TABLE orders (
    order_id int  NOT NULL,
    customer_id int  NOT NULL,
    product_id int  NOT NULL,
    time timestamp  NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (order_id),
    CONSTRAINT customer_order FOREIGN KEY (customer_id) REFERENCES customer (customer_id),
    CONSTRAINT product_order FOREIGN KEY (product_id) REFERENCES product (product_id)
);


CREATE TABLE updates (
    product_id int  NOT NULL,
    changelog text  NOT NULL,
    time date  NOT NULL,
    update_id int  NOT NULL,
    CONSTRAINT updates_pk PRIMARY KEY (update_id),
    CONSTRAINT product_updates FOREIGN KEY (product_id) REFERENCES product (product_id),
    CONSTRAINT update_id_check CHECK (update_id > 0)
);



INSERT INTO worker (name, worker_id) VALUES ('Ivanov', 1);
INSERT INTO worker (name, worker_id) VALUES ('Petrov', 2);
INSERT INTO worker (name, worker_id) VALUES ('Sidorov', 3);
INSERT INTO worker (name, worker_id) VALUES ('Nikolaev', 4);
INSERT INTO worker (name, worker_id) VALUES ('Nonamov', 5);


INSERT INTO customer (customer_id, name, email) VALUES (1, 'Petya', 'petya@mail.ru');
INSERT INTO customer (customer_id, name, email) VALUES (2, 'Vanya', 'Vanya@mail.ru');
INSERT INTO customer (customer_id, name, email) VALUES (3, 'Kolya', 'Kolya@mail.ru');
INSERT INTO customer (customer_id, name, email) VALUES (4, 'Sanya', 'Sanya@mail.ru');
INSERT INTO customer (customer_id, name, email) VALUES (5, 'Noname', 'Noname@mail.ru');


INSERT INTO dev_group VALUES (1, 1, 'first-group', 1);
INSERT INTO dev_group VALUES (4, 2, 'second-group', 2);

INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (1, 1, 100000, 'good-worker', '2019/01/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (2, 1, 80000, 'good-worker', '2019/01/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (3, 1, 70000, 'good-worker', '2019/01/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (4, 2, 100000, 'good-worker', '2019/01/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (5, 2, 90000, 'good-worker', '2019/01/01');

UPDATE worker_updates SET time_end = '2019/02/01' where worker_id = 1;
UPDATE worker_updates SET time_end = '2019/02/01' where worker_id = 2;
UPDATE worker_updates SET time_end = '2019/02/01' where worker_id = 3;
UPDATE worker_updates SET time_end = '2019/02/01' where worker_id = 4;
UPDATE worker_updates SET time_end = '2019/02/01' where worker_id = 5;

INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (1, 1, 110000, 'good-worker', '2019/02/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (2, 1, 60000, 'medium-worker', '2019/02/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (3, 1, 50000, 'medium-worker', '2019/02/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (4, 2, 110000, 'good-worker', '2019/02/01');
INSERT INTO worker_updates (worker_id, group_id, new_salary, new_status, time_start) values (5, 2, 70000, 'medium-worker', '2019/02/01');



INSERT into product values (1, 'photoshop', 1000, 1);
INSERT into product values (2, 'illustrator', 1500, 1);
INSERT into product values (3, 'aftereffects', 2500, 1);
INSERT into product values (4, 'premier', 1500, 2);
INSERT into product values (5, 'acrobat', 1100, 2);


INSERT into updates values (1, 'mvp', '2019/01/15', 1);
INSERT into updates values (2, 'mvp', '2019/01/15', 2);
INSERT into updates values (3, 'mvp', '2019/01/15', 3);
INSERT into updates values (4, 'mvp', '2019/01/15', 4);
INSERT into updates values (5, 'mvp', '2019/01/15', 5);
INSERT into updates values (5, 'mvp2', '2019/01/15', 6);

INSERT into orders values (1, 1, 1, '2019/01/16');
INSERT into orders values (2, 2, 2, '2019/01/17');
INSERT into orders values (3, 3, 3, '2019/01/18');
INSERT into orders values (4, 4, 4, '2019/01/19');
INSERT into orders values (5, 5, 5, '2019/01/20');
INSERT into orders values (6, 4, 5, '2019/01/20');

/* общая прибыль за месяц*/
select sum(price)
from orders inner join product on orders.product_id = product.product_id;

/* общие затраты на зарплату на текущий момент */
select sum(new_salary)
from worker_updates
where time_end is null;

/* количество покупок */
create view amount_of_orders as
  select count(order_id)
    from orders;
select * from amount_of_orders;


/* сумма покупок по каждому клиенту */
create view sum_for_customer as
select sum(price), customer_id
from orders inner join product on orders.product_id = product.product_id
group by customer_id;
select * from sum_for_customer;


/* количество покупок по каждому клиенту */
select product_id, count(update_id)
from updates
group by product_id;