---

import sys
import os
import mysql.connector
from dotenv import load_dotenv
from PyQt6.QtWidgets import QApplication, QTableWidget, QTableWidgetItem

load_dotenv()

conn = mysql.connector.connect(
    host=os.getenv("DB_HOST"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    database=os.getenv("DB_NAME"),
    port=int(os.getenv("DB_PORT", 3306))
)

cur = conn.cursor()
cur.execute("SELECT * FROM view_1")

data = cur.fetchall()
headers = [i[0] for i in cur.description]

app = QApplication(sys.argv)

table = QTableWidget()
table.setWindowTitle("Данные из view_1")
table.resize(900, 400)

table.setColumnCount(len(headers))
table.setRowCount(len(data))
table.setHorizontalHeaderLabels(headers)

for i, row in enumerate(data):
    for j, value in enumerate(row):
        table.setItem(i, j, QTableWidgetItem(str(value)))

table.show()

cur.close()
conn.close()

sys.exit(app.exec())

DROP VIEW IF EXISTS view_1;

CREATE VIEW view_1 AS
SELECT
    d.department_name AS otdel,
    COUNT(DISTINCT e.employee_id) AS kolvo_sotrudnikov,
    COUNT(DISTINCT p.project_id) AS kolvo_proektov,
    COUNT(DISTINCT t.task_id) AS kolvo_zadach,
    SUM(
        CASE
            WHEN t.status = 'выполнена' THEN 1
            ELSE 0
        END
    ) AS vypolneno_zadach
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN tasks t ON e.employee_id = t.employee_id
LEFT JOIN projects p ON t.project_id = p.project_id
GROUP BY d.department_id, d.department_name;