---

ALTER TABLE reports ADD COLUMN project_id INT NOT NULL AFTER developer_id;
ALTER TABLE reports ADD FOREIGN KEY (project_id) REFERENCES projects(id);



SELECT 
    developers.id AS developer_id,
    developers.full_name AS developer_name,
    developers.role,
    projects.id AS project_id,
    projects.project_name,
    projects.client,
    projects.status AS project_status,
    bugs.id AS bug_id,
    bugs.description AS bug_description,
    bugs.fixed AS bug_is_fixed,
    reports.id AS report_id,
    reports.report_date,
    reports.done AS work_done,
    reports.problems
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, reports.report_date DESC, bugs.id;