---

CREATE TABLE "user" (
    id_user integer PRIMARY KEY,
    fullname varchar(100) NOT NULL,
    email varchar(256) NOT NULL,
    password varchar(32) NOT NULL
);

CREATE TABLE project (
    id_project integer PRIMARY KEY,
    name varchar(120) NOT NULL,
    description varchar(1000) NOT NULL,
    startdate timestamp NOT NULL,
    enddate timestamp NOT NULL
);

CREATE TABLE project_role (
    id_role integer PRIMARY KEY,
    id_user integer NOT NULL,
    id_project integer NOT NULL,
    status varchar(20) NOT NULL
        CHECK (status IN ('Супер-Админ','Администратор','Менеджер','Исполнитель')),
    CONSTRAINT fk_user_project_role 
    	FOREIGN KEY (id_user) 
    	REFERENCES "user"(id_user),
    CONSTRAINT fk_idproject_project_role
    	FOREIGN KEY (id_project) 
    	REFERENCES project(id_project)
);

CREATE TABLE tasks (
    id_task integer PRIMARY KEY,
    id_project integer NOT NULL,
    id_user integer NOT NULL,
    name varchar(120) NOT NULL,
    description varchar(2500) NOT NULL,
    priority varchar(20) NOT NULL
        CHECK (priority IN ('Срочно','Высокий','Средний','Низкий')),
    status varchar(20) NOT NULL
        CHECK (status IN ('Новая','В работе','На проверке','Отменена')),
    CONSTRAINT fk_iduser_tasks 
    	FOREIGN KEY (id_user) 
    	REFERENCES "user"(id_user),
    CONSTRAINT fk_idproject_tasks 
    	FOREIGN KEY (id_project) 
    	REFERENCES project(id_project)
);

CREATE TABLE tags (
    id_tag integer PRIMARY KEY,
    name varchar(30) NOT NULL,
    description varchar(100) NOT NULL,
    color varchar(7) DEFAULT '#ffffff'
);

CREATE TABLE tag_task (
    id_task integer NOT NULL,
    id_tag integer NOT NULL,
    id_user integer NOT NULL,
    PRIMARY KEY (id_task, id_tag),
    CONSTRAINT fk_idtask_tag_task 
    	FOREIGN KEY (id_task)
    	REFERENCES tasks(id_task),
    CONSTRAINT fk_idtag_tag_task  
    	FOREIGN KEY (id_tag)  
    	REFERENCES tags(id_tag),
    CONSTRAINT fk_iduser_tag_task 
    	FOREIGN KEY (id_user) 
    	REFERENCES "user"(id_user)
);

CREATE TABLE comments (
    id_comments integer PRIMARY KEY,
    id_task integer NOT NULL,
    id_user integer NOT NULL,
    text varchar(1000) NOT NULL,
    creationdate timestamp NOT NULL,
    CONSTRAINT fk_idtask_comments 
    	FOREIGN KEY (id_task)
    	REFERENCES tasks(id_task),
    CONSTRAINT fk_iduser_comments 
    	FOREIGN KEY (id_user) 
    	REFERENCES "user"(id_user)
);

INSERT INTO "user" VALUES
(1,'Никита Павликов','nikitosTOP2281337@gmail.com','1212323'),
(2,'Павлик Никитов','pavlikLoh228@gmail.com','212323'),
(3,'Анна Смирнова','anna.smirnova@mail.ru','qwerty123'),
(4,'Дмитрий Козлов','dmitry.kozlov@gmail.com','pass4567'),
(5,'Елена Волкова','elena.volkova@yandex.ru','volkova99'),
(6,'Сергей Морозов','sergey.morozov@gmail.com','moroz777'),
(7,'Ольга Новикова','olga.novikova@mail.ru','novikova12'),
(8,'Артём Соколов','artem.sokolov@gmail.com','sokolov88'),
(9,'Мария Лебедева','maria.lebedeva@yandex.ru','lebedeva5'),
(10,'Иван Попов','ivan.popov@gmail.com','popov2026');

INSERT INTO project VALUES
(1,'Мобильное приложение','Разработка iOS/Android приложения','2026-10-01','2026-12-15'),
(2,'CRM-система','Внутренняя CRM для отдела продаж','2026-10-05','2027-01-20'),
(3,'Telegram-бот','Бот для трекинга привычек','2026-09-25','2026-11-01'),
(4,'Редизайн портала','Обновление UI/UX корпоративного портала','2026-11-01','2027-02-28'),
(5,'Маркетплейс','Платформа для продажи товаров','2026-08-15','2027-03-10'),
(6,'Аналитическая панель','BI-дашборд для аналитики','2026-09-01','2026-12-30');

INSERT INTO project_role VALUES
(1,1,1,'Супер-Админ'),
(2,2,1,'Менеджер'),
(3,3,1,'Исполнитель'),
(4,4,2,'Администратор'),
(5,5,2,'Менеджер'),
(6,6,2,'Исполнитель'),
(7,7,3,'Менеджер'),
(8,8,3,'Исполнитель'),
(9,9,4,'Администратор'),
(10,10,4,'Исполнитель'),
(11,1,5,'Супер-Админ'),
(12,3,5,'Менеджер'),
(13,6,6,'Менеджер'),
(14,8,6,'Исполнитель');

INSERT INTO tasks VALUES
(1,1,1,'Сделать тг-бота','Сделать тг бота трекера привычек','Средний','На проверке'),
(2,1,2,'Сделать SaaS анимацию сайта','Сделать SaaS анимацию сайту в After Effects','Срочно','В работе'),
(3,1,2,'Сделать баннер','Сделать красивый баннер для сайта на главную страницу в photoshop','Низкий','В работе'),
(4,1,2,'Подготовиться к паре','Сделать все дз и подготовиться к тесту','Высокий','Новая'),
(5,2,4,'Настроить авторизацию','Реализовать вход и регистрацию в CRM','Высокий','В работе'),
(6,2,5,'Сделать отчёт по продажам','Отчёт с графиками за месяц','Средний','Новая'),
(7,2,6,'Импорт клиентов из Excel','Загрузка базы клиентов из файла','Низкий','На проверке'),
(8,3,7,'Команда /start','Обработчик стартовой команды бота','Срочно','В работе'),
(9,3,8,'Напоминания','Пуш-уведомления о привычках','Высокий','Новая'),
(10,3,8,'Статистика привычек','График выполнения привычек','Средний','Отменена'),
(11,4,9,'Новый логотип','Разработать новый логотип портала','Средний','В работе'),
(12,4,10,'Адаптивная вёрстка','Сделать адаптив под мобильные','Высокий','Новая'),
(13,5,3,'Каталог товаров','Страница каталога с фильтрами','Высокий','В работе'),
(14,5,6,'Корзина','Реализовать корзину покупок','Срочно','На проверке'),
(15,5,8,'Оплата картой','Интеграция платёжного шлюза','Высокий','Новая'),
(16,6,6,'Дашборд продаж','Главный экран с метриками','Средний','В работе'),
(17,6,8,'Экспорт в PDF','Экспорт отчётов в PDF','Низкий','Новая'),
(18,6,3,'Фильтры по датам','Фильтрация данных по периоду','Средний','В работе');

INSERT INTO tags VALUES
(1,'Разработка','Задачи разработки','#635773'),
(2,'ДЗ','Домашка','#5ffa4b'),
(3,'Дизайн','Задачи по дизайну','#ff6b6b'),
(4,'Бэкенд','Серверная часть','#4ecdc4'),
(5,'Фронтенд','Клиентская часть','#ffe66d'),
(6,'Срочно','Срочные задачи','#ff0000'),
(7,'Баг','Исправление ошибок','#8b0000'),
(8,'Документация','Написание документации','#a8e6cf'),
(9,'Тестирование','QA и тесты','#dcedc1'),
(10,'Рефакторинг','Улучшение кода','#ffd3b6'),
(11,'API','Работа с API','#b5ead7'),
(12,'Мобильное','Мобильная разработка','#c7ceea');

INSERT INTO tag_task VALUES
(1,1,1),
(4,2,2),
(2,5,4),
(6,5,4),
(5,6,5),
(9,7,6),
(6,8,7),
(5,8,7),
(4,9,8),
(5,9,8),
(3,11,9),
(3,12,10);

INSERT INTO comments VALUES
(1,1,1,'Начал делать бота, каркас на aiogram готов','2026-09-21 09:15:00'),
(2,1,2,'Добавь меню с кнопками: «Привычки», «Статистика», «Настройки»','2026-09-21 09:40:00'),
(3,1,1,'Ок, меню добавил, теперь пишу логику трекинга','2026-09-21 10:05:00'),
(4,1,2,'Финальная проверка бота — всё работает','2026-09-22 16:20:00'),
(5,2,2,'Собрал сцены в After Effects, рендерю превью','2026-09-22 11:00:00'),
(6,2,1,'Добавь плавности на переходе между блоками','2026-09-22 11:25:00'),
(7,2,2,'Принял, к вечеру будет финальный рендер','2026-09-22 11:40:00'),
(8,3,2,'Баннер почти готов, осталось подобрать картинку','2026-09-23 15:00:00'),
(9,3,1,'Возьми стоковое фото с Unsplash','2026-09-23 15:20:00'),
(10,3,2,'Картинку подобрал, баннер готов','2026-09-23 17:10:00'),
(11,4,2,'Начал готовиться к паре, все задания по списку делаю','2026-09-24 18:00:00'),
(12,4,1,'Не забудь про тест в конце','2026-09-24 18:15:00'),
(13,4,2,'Тест сдал, можно закрывать задачу','2026-09-25 12:30:00'),
(14,5,4,'Авторизация через JWT, токен живёт 24 часа','2026-09-25 09:10:00'),
(15,5,5,'Добавь восстановление пароля по email','2026-09-25 09:45:00'),
(16,5,4,'Ок, сброс пароля сделал','2026-09-25 10:20:00'),
(17,6,5,'Отчёт нужен с разбивкой по менеджерам','2026-09-26 11:30:00'),
(18,6,4,'Добавь график за месяц','2026-09-26 12:00:00'),
(19,7,6,'Excel-файл большой, обрабатываю по частям','2026-09-27 14:15:00'),
(20,7,5,'Проверь, чтобы не дублировались записи','2026-09-27 14:40:00'),
(21,8,7,'Команда /start работает, тестирую на сервере','2026-09-28 08:50:00'),
(22,8,8,'Добавь кнопки быстрого доступа','2026-09-28 09:20:00'),
(23,9,8,'Напоминания будут через cron каждые 5 минут','2026-09-29 12:05:00'),
(24,10,8,'Задача отменена по решению заказчика','2026-09-29 16:30:00'),
(25,11,9,'Логотип в 3 вариантах, выбери лучший','2026-10-01 10:40:00'),
(26,11,10,'Второй вариант самый удачный','2026-10-01 11:15:00'),
(27,12,10,'Адаптив под 320px тоже нужен','2026-10-02 13:50:00'),
(28,13,3,'Фильтры по цене и категории готовы','2026-10-05 09:25:00'),
(29,13,6,'Добавь сортировку по популярности','2026-10-05 10:00:00'),
(30,14,6,'Корзина сохраняется в localStorage','2026-10-06 15:35:00'),
(31,14,8,'Проверь, чтобы не терялась при перезагрузке','2026-10-06 16:10:00'),
(32,15,8,'Подключаю ЮKassa, тестовый режим','2026-10-07 11:05:00'),
(33,16,6,'Дашборд грузится долго, надо оптимизировать','2026-10-10 09:35:00'),
(34,16,3,'Добавлю кэширование запросов','2026-10-10 10:10:00'),
(35,17,8,'PDF-экспорт через библиотеку','2026-10-11 14:25:00'),
(36,18,3,'Фильтр по датам — диапазон с календарём','2026-10-12 10:50:00'),
(37,18,6,'Сделай пресеты: неделя, месяц, год','2026-10-12 11:20:00');



-- 1 
-- Задачи со срочным приоритетом
select id_task, name, status
from tasks
where priority = 'Срочно';

-- Статусы, в которых больше 3 задач
select status, COUNT(*) AS cnt
from tasks
group 	by status
having COUNT(*) > 3;

-- Задачи, которые ещё не начаты
select id_task, name, priority
from tasks
where status = 'Новая';

-- Задачи, которые в работе или на проверке
select id_task, name, priority
from tasks as t
where t.status in ('В работе', 'На проверке');

-- Сколько задач в каждом статусе
select status, count(*)
from tasks as t
group by status;

-- Первые 3 пользователя
select u.id_user, u.fullname 
from "user" as u
limit 3;

-- Сортировка по имени
select fullname 
from "user" as u
order by fullname;

-- 2 
-- задачи, у которых приоритет не низкий
select t.id_task, t."name", t.priority 
from tasks as t
where t.priority not in ('Низкий')
order by id_task;

-- задачи пользователя с айди 2
select t.id_task , t."name" , t.status
from tasks as t 
where t.id_user = 2
order by "name";

-- топ-3 проекта с самым поздним дедлайном
select p.id_project , p."name", p.enddate
from project as p
order by enddate desc
limit 3;

-- получить все комменты по определенному айди задачи
select c.id_task, c.id_user , c."text" 
from "comments" as c
where c.id_task = 3
order by c.creationdate;

-- получить самый старый проект
select p."name", p.description , p.startdate 
from project as p
order by p.startdate
limit 1;


-- 3
-- Топ 3 пользователя по числу комментариев
select u.fullname , COUNT(c.id_comments) as comments_count
from "user" as u 
left join "comments" as c on c.id_user = u.id_user
group by u.fullname
order by comments_count desc
limit 3;

-- все проекты и их задачи
select p."name" as prname, t.id_task, t."name" as task
from project as p 
left join tasks as t on t.id_project = p.id_project
order by p."name" , t.id_task;

-- все задачи и id их теги
select t.id_task , t."name" , tt.id_tag
from tasks as t
left join tag_task as tt  on tt.id_task = t.id_task 
order by t.id_task;

-- 4
-- 1. список задач с названием проекта и именем исполнителя
select t.id_task, t."name", t.status, t.priority, p."name" as project, u.fullname as executor
from tasks as t
left join project as p on p.id_project = t.id_project
left join "user" as u on u.id_user = t.id_user
order by p."name", t.id_task;

-- 2. лента комментариев: кто, к какой задаче, в каком проекте
select c.id_comments, c.creationdate, u.fullname as author, t."name" as task, p."name" as project, c."text"
from comments as c
left join "user" as u on u.id_user = c.id_user
left join tasks as t on t.id_task = c.id_task
left join project as p on p.id_project = t.id_project
order by c.creationdate desc;

-- 3. задачи с тегами: проект, исполнитель, тег, кто поставил тег
select t.id_task, t."name", p."name" as project, u.fullname as executor, tg."name" as tag, tu.fullname as tag_added_by
from tasks as t
left join project as p on p.id_project = t.id_project
left join "user" as u on u.id_user = t.id_user
left join tag_task as tt on tt.id_task = t.id_task
left join tags as tg on tg.id_tag = tt.id_tag
left join "user" as tu on tu.id_user = tt.id_user
order by t.id_task, tg."name";

-- 5 Вложенные запросы

-- 1 Исполнители срочных задач.
select u.id_user, u.fullname
from ( select distinct id_user from tasks where priority = 'Срочно' )
as scrochno join "user" as u on u.id_user = scrochno.id_user;

-- 2 Задачи у которых больше двух комментов
select c.id_task, c.cnt 
from ( select id_task, count(*) as cnt from comments group by id_task )
as c where c.cnt > 2
order by c.cnt desc;

-- 3 Пользователи и сколько у них задач
select u.fullname, z.cnt as tasks_count
from ( select id_user, count(*) as cnt from tasks group by id_user )
as z join "user" as u on u.id_user = z.id_user 
order by z.cnt desc;

-- 4 проект и сколько в них задач
select p.name, z.cnt as tasks_count
from (select id_project, count(*) as cnt from tasks group by id_project)
as z join project as p on p.id_project = z.id_project 
order by z.cnt desc;