---

-- ============================================
-- БАЗА ДАННЫХ: разработчик ПО (Вариант 14)
-- ============================================

-- 1. Удаляем старые таблицы, если есть
DROP DATABASE IF EXISTS software_developer_tracker;
CREATE DATABASE software_developer_tracker;
USE software_developer_tracker;

-- 2. Таблица: разработчики
CREATE TABLE developers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

-- 3. Таблица: проекты (для клиентов)
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(150) NOT NULL,
    client VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    deadline DATE,
    status VARCHAR(50) DEFAULT 'разработка'
);

-- 4. Таблица: ошибки (баги)
CREATE TABLE bugs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    description TEXT NOT NULL,
    severity VARCHAR(20) DEFAULT 'medium',
    status VARCHAR(20) DEFAULT 'open',
    reported_by INT NOT NULL,
    fixed_by INT,
    reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fixed_at TIMESTAMP NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (reported_by) REFERENCES developers(id),
    FOREIGN KEY (fixed_by) REFERENCES developers(id)
);

-- 5. Таблица: еженедельные отчёты
CREATE TABLE reports (
    id INT PRIMARY KEY AUTO_INCREMENT,
    developer_id INT NOT NULL,
    project_id INT NOT NULL,
    week_start DATE NOT NULL,
    work_done TEXT NOT NULL,
    problems TEXT,
    plan_next_week TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (developer_id) REFERENCES developers(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

-- 6. Заполнение таблицы developers
INSERT INTO developers (full_name, email, role, hire_date, is_active) VALUES
('Иван Петров', 'ivan.petrov@itcompany.ru', 'backend', '2022-03-10', TRUE),
('Мария Сидорова', 'maria.sidorova@itcompany.ru', 'frontend', '2022-06-15', TRUE),
('Алексей Смирнов', 'alexey.smirnov@itcompany.ru', 'fullstack', '2021-11-20', TRUE),
('Елена Козлова', 'elena.kozlova@itcompany.ru', 'backend', '2023-01-25', TRUE),
('Дмитрий Иванов', 'dmitry.ivanov@itcompany.ru', 'frontend', '2023-02-10', FALSE);

-- 7. Заполнение таблицы projects
INSERT INTO projects (project_name, client, start_date, deadline, status) VALUES
('CRM для банка', 'ПАО Сбербанк', '2024-01-10', '2024-06-30', 'разработка'),
('Мобильное приложение такси', 'Яндекс.Такси', '2024-02-01', '2024-07-15', 'тестирование'),
('Сайт интернет-магазина', 'ООО Вайлдберриз', '2024-01-20', '2024-05-01', 'разработка'),
('Личный кабинет клиента', 'АО Тинькофф', '2023-11-01', '2024-03-01', 'развёрнуто'),
('Система учёта склада', 'ООО Магнит', '2024-03-01', '2024-08-01', 'тестирование'),
('Telegram бот для доставки', 'Самокат', '2024-02-15', '2024-04-30', 'разработка');

-- 8. Заполнение таблицы bugs
INSERT INTO bugs (project_id, description, severity, status, reported_by, fixed_by, fixed_at) VALUES
(1, 'Не сохраняются данные клиента при редактировании', 'high', 'fixed', 1, 1, '2024-02-10 14:30:00'),
(1, 'Ошибка авторизации через соцсети', 'medium', 'in_progress', 2, 3, NULL),
(2, 'Вылетает приложение при открытии карты', 'critical', 'fixed', 3, 1, '2024-02-15 11:20:00'),
(2, 'Не приходит SMS с кодом', 'high', 'open', 1, NULL, NULL),
(3, 'Не загружаются изображения товаров', 'medium', 'in_progress', 4, 2, NULL),
(3, 'Кнопка "Купить" не работает на телефонах', 'high', 'open', 2, NULL, NULL),
(5, 'Неправильно считает остатки на складе', 'critical', 'fixed', 3, 4, '2024-03-05 09:15:00'),
(6, 'Бот не отвечает на команды', 'high', 'in_progress', 5, 1, NULL);

-- 9. Заполнение таблицы reports
INSERT INTO reports (developer_id, project_id, week_start, work_done, problems, plan_next_week) VALUES
(1, 1, '2024-02-26', 'Написал API для авторизации, починил баг с сохранением клиента', 'Проблема с CORS на фронте', 'Сделать документацию API'),
(2, 3, '2024-02-26', 'Сверстал карточки товаров, настроил адаптив', 'Изображения грузятся медленно', 'Оптимизировать загрузку картинок'),
(3, 2, '2024-02-26', 'Добавил геолокацию в приложение, починил вылет при открытии карты', 'Проблема с эмулятором Android', 'Настроить CI/CD'),
(4, 5, '2024-02-26', 'Написал скрипты для учёта остатков, починил расчёт', 'Нет', 'Подключить базу данных'),
(1, 6, '2024-02-26', 'Настроил webhook для бота, реализовал команды', 'Telegram API иногда тормозит', 'Добавить клавиатуру'),
(2, 1, '2024-03-04', 'Поправил стили, настроил CORS', 'Всё работает', 'Тестирование'),
(3, 2, '2024-03-04', 'Настроил сборку APK', 'Проблема с подписи приложения', 'Выкатить в тестовый магазин'),
(1, 1, '2024-03-04', 'Написал документацию по API', 'Нет', 'Помогать фронту');

-- 10. Проверочные запросы (показать всё)

-- Все разработчики
SELECT * FROM developers;

-- Все проекты
SELECT * FROM projects;

-- Все ошибки с названиями проектов и разработчиками
SELECT 
    bugs.id,
    bugs.description,
    bugs.severity,
    bugs.status,
    projects.project_name,
    rep.full_name AS reported_by_name,
    fix.full_name AS fixed_by_name
FROM bugs
LEFT JOIN projects ON bugs.project_id = projects.id
LEFT JOIN developers rep ON bugs.reported_by = rep.id
LEFT JOIN developers fix ON bugs.fixed_by = fix.id;

-- Все отчёты с разработчиками и проектами
SELECT 
    reports.week_start,
    developers.full_name AS developer,
    projects.project_name,
    reports.work_done,
    reports.problems
FROM reports
JOIN developers ON reports.developer_id = developers.id
JOIN projects ON reports.project_id = projects.id
ORDER BY reports.week_start DESC;

-- Объединённый запрос (все таблицы сразу)
SELECT 
    developers.full_name AS разработчик,
    projects.project_name AS проект,
    bugs.description AS ошибка,
    bugs.status AS статус_ошибки,
    reports.work_done AS сделано_за_неделю
FROM developers
LEFT JOIN reports ON developers.id = reports.developer_id
LEFT JOIN projects ON reports.project_id = projects.id
LEFT JOIN bugs ON projects.id = bugs.project_id AND bugs.fixed_by = developers.id
ORDER BY developers.id;