from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine("sqlite+pysqlite:///mydatabase.db", echo=True)

region_id = 1

with Session(engine) as s:
    rows = s.execute(text(f"""
    SELECT r.title, p.*
    FROM populations p
    JOIN regions r ON r.id = p.region_id
    WHERE region_id = :rid and year = :y
    """), {"rid": region_id, "y": 2021})
    for r in rows:
        print(r.year, r.title, r.id, r.population)

    rows2 = s.execute(text(f"""
    SELECT *
    FROM regions
    """))
    for r in rows2:
        print(r)

with Session(engine) as s:
    query = """
    INSERT INTO populations(region_id, year, population)
    VALUES(:rid, :y, :p)
    """
    s.execute(text(query), {"rid": 1, "y": 2018, "p": 2404195})
    
    s.commit()

with Session(engine) as s:
    query = """
    UPDATE populations
    SET population = 2404190
    WHERE id = :id
    """

    s.execute(text(query), {"id": 9})
    s.commit()


with Session(engine) as s:
    query = """
    DELETE 
    FROM populations
    WHERE id in (8,9)
    """

    s.execute(text(query), {"id": 9})
    s.commit()

import sys
import os
import PySide6
from PySide6.QtWidgets import QApplication, QMainWindow, QDialog, QListWidgetItem, QMessageBox
from PySide6 import QtCore
from mainwindow import Ui_MainWindow
from edit_dialog import Ui_Dialog
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session


dirname = os.path.dirname(PySide6.__file__)
plugin_path = os.path.join(dirname, 'plugins', 'platforms')
os.environ['QT_QPA_PLATFORM_PLUGIN_PATH'] = plugin_path


class EditDialog(QDialog):
    def __init__(self, regions, *args, **kwargs) -> None:
        super().__init__(*args, **kwargs)
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)

        self.ui.btnAdd.clicked.connect(self.accept)
        self.ui.btnCancel.clicked.connect(self.reject)

        print(regions)
        for r in regions.values():
            self.ui.cmbRegions.addItem(r.title, r)

    def get_data(self):
        return {
            "region_id": self.ui.cmbRegions.currentData().id,
            "year": self.ui.txtYear.text(),
            "population": self.ui.txtPopulation.text()
        }


class UpdateDialog(EditDialog):
    def __init__(self, regions, init_data, *args, **kwargs) -> None:
        super().__init__(regions, *args, **kwargs)

        self.ui.btnAdd.setText('Изменить')
        self.ui.cmbRegions.setEnabled(False)

        region_name = regions[init_data.region_id].title
        self.ui.cmbRegions.setCurrentText(region_name)

        self.ui.txtPopulation.setText(str(init_data.population))
        self.ui.txtYear.setText(str(init_data.year))


class MainWindow(QMainWindow):
    def __init__(self):
        super(MainWindow, self).__init__()
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)

        self.engine = create_engine("sqlite+pysqlite:///mydatabase.db", echo=True)

        self.load_regions()
        self.load_years()
        self.load_population()

        self.ui.cmbRegions.currentIndexChanged.connect(self.load_population)
        self.ui.cmbYear.currentIndexChanged.connect(self.load_population)
        self.ui.btnAdd.clicked.connect(self.on_btnAdd_click)
        self.ui.btnRemove.clicked.connect(self.on_btnRemove_click)
        self.ui.btnEdit.clicked.connect(self.on_btnEdit_click)

    def on_btnEdit_click(self):
        item = self.ui.lstItems.currentItem()
        init_data = item.data(QtCore.Qt.ItemDataRole.UserRole)

        dialog = UpdateDialog(self.regions, init_data)
        r = dialog.exec()
        if r == 0:
            return
        
        data = dialog.get_data()

        with Session(self.engine)  as s:
            query = """
            UPDATE populations
            SET year = :y, population = :p
            WHERE id = :id
            """

            s.execute(text(query), {
                "y": data['year'],
                "p": data['population'],
                "id": init_data.id,
            })
            s.commit()

        self.load_population()
        self.load_years()

    def on_btnRemove_click(self):
        item = self.ui.lstItems.currentItem()
        data = item.data(QtCore.Qt.ItemDataRole.UserRole)

        r = QMessageBox.question(self, "Подтверждение", "Точно ли хотите удалить запись?")
        if r == QMessageBox.StandardButton.No:
            return

        with Session(self.engine) as s:
            query = """
            DELETE 
            FROM populations 
            WHERE id = :id
            """

            s.execute(text(query), {"id": data.id})
            s.commit()

        self.load_population()
        self.load_years()
    
    def on_btnAdd_click(self):
        dialog = EditDialog(self.regions)
        r = dialog.exec()
        if r == 0:
            return

        data = dialog.get_data()
        with Session(self.engine) as s:
            query = """
            INSERT INTO populations(region_id, year, population)
            VALUES (:rid, :y, :p)
            """

            s.execute(text(query), {
                "rid": data['region_id'],
                "y": data['year'],
                "p": data['population'],
            })
            s.commit()

        self.load_population()
        self.load_years()

    def load_population(self):
        regions_data = self.ui.cmbRegions.currentData()
        if regions_data:
            region_id = self.ui.cmbRegions.currentData().id
        else:
            region_id = 0

        year = self.ui.cmbYear.currentText()

        self.ui.lstItems.clear()

        with Session(self.engine) as s:
            query = """
            SELECT *
            FROM populations
            WHERE (:rid = 0 OR region_id = :rid) 
                AND (:y = '-' OR year = :y)
            ORDER BY  year DESC
            """

            rows = s.execute(text(query), {"rid": region_id, "y": year})

            for r in rows:
                region_name = self.regions[r.region_id].title

                item = QListWidgetItem(f"{region_name} {r.year} {r.population}")
                item.setData(QtCore.Qt.ItemDataRole.UserRole, r)
                self.ui.lstItems.addItem(item)

    def load_regions(self):
        self.regions = {}

        with Session(self.engine) as s:
            query = """
            SELECT *
            FROM regions
            """

            rows = s.execute(text(query))
            for r in rows:
                self.regions[r.id] = r

        self.ui.cmbRegions.addItem("-")
        for r in self.regions.values():
            self.ui.cmbRegions.addItem(r.title, r)
            
    def load_years(self):
        self.ui.cmbYear.clear()
        self.ui.cmbYear.addItem("-")

        with Session(self.engine) as s:
            query = """
            SELECT DISTINCT year
            FROM populations
            ORDER BY 1 DESC
            """

            rows = s.execute(text(query))
            for r in rows:
                self.ui.cmbYear.addItem(str(r.year))


if __name__ == "__main__":
    app = QApplication(sys.argv)

    window = MainWindow()
    window.show()

    sys.exit(app.exec())

def load_population(self):
    # тут не трогаю
    regions_data = self.ui.cmbRegions.currentData()
    if regions_data:
        region_id = self.ui.cmbRegions.currentData().id
    else:
        region_id = 0

    year = self.ui.cmbYear.currentText()

    self.ui.lstItems.clear()
    self.rows = [] # << !!!ТУТ ЗАВОЖУ ПУСТОЙ СПИСОК

    with Session(self.engine) as s:
        # тут тоже ничего не трогаю
        query = """
        SELECT *
        FROM populations
        WHERE (:rid = 0 OR region_id = :rid) 
            AND (:y = '-' OR year = :y)
        ORDER BY  year DESC
        """

        rows = s.execute(text(query), {"rid": region_id, "y": year})

        for r in rows:
            region_name = self.regions[r.region_id].title

            item = QListWidgetItem(f"{region_name} {r.year} {r.population}")
            item.setData(QtCore.Qt.ItemDataRole.UserRole, r)
            self.ui.lstItems.addItem(item)

            self.rows.append(r) # << !!! ТУТ  ЗАПОЛНЯЮ СОЗДАННЫ ВЫШЕ СПИСОК self.rows ДАННЫМИ ИЗ ЗАПРОСА

    self.show_statistics() # << И В САМОМ КОНЦЕ ВЫЗОВ метода статистики

def show_statistics(self):
    max_row = max(self.rows, key=lambda row: row.population)
    min_row = min(self.rows, key=lambda row: row.population)
    
    # для расчета среднего,
    # не забудьте добавить в верху файла
    # from statistics import *
    avg_population = int(mean([i.population for i in self.rows]))

    region_name = self.regions[max_row.region_id].title
    min_region_name = self.regions[min_row.region_id].title

    self.ui.lblStatistics.setText(f"""
    Наибольший показатель числености населения составил 
    <br> <b style="color: #f542c8; font-size: 16px">{max_row.population}</b> человека в  <b style="color: #4290f5">{max_row.year}</b> году в регионе <u><b>{region_name}</b></u>
    <hr>
    Наименьший показатель числености населения составил 
    <br> <b style="color: #f542c8; font-size: 16px">{min_row.population}</b> человека в <b style="color: #4290f5">{min_row.year}</b> году в регионе <u><b>{min_region_name}</b></u>
    <hr>
    Среднее значение населения составило: <span style="background: yellow">{avg_population} человека</span>
    """)

def draw_line_chart(self):
    # заводим словарик под группировку по регионам
    self.data_by_regions = {}
    
    # группируем по регионам
    for row in self.rows:
        items = self.data_by_regions.setdefault(row.region_id, [])
        items.append(row)
    
    # создаем объект графика
    chart = QtCharts.QChart()
    
    # для каждого региона
    for region_id, region_data in self.data_by_regions.items():
        # вытаскиваем имя региона
        region_name = self.regions[region_id].title
         
        # создаем последовательность точек для вывода 
        series = QtCharts.QLineSeries()
        # даем ей название
        series.setName(region_name)
        # включаем отображение точек
        series.setPointsVisible(True)
        series.setMarkerSize(4)
        
        # заполняем последовательность точек
        for row in region_data:
            # row.year -- это координа X, row.population -- координата Y
            series.append(row.year, row.population)
        
        # добавляем последовательность точек на график
        chart.addSeries(series)
       
    # активируем отображение осей
    chart.createDefaultAxes()
    chart.axisX().setTitleText("Год")
    chart.axisX().setLabelFormat("%i")
    chart.axisX().setMax(chart.axisX().max() + 1)
    chart.axisX().setMin(chart.axisX().min() - 1)
    
    chart.axisY().setLabelFormat("%i")
    chart.axisY().setTitleText("Население, чел.")
    chart.axisY().setMax(chart.axisY().max() + 100000)
    chart.axisY().setMin(chart.axisY().min() - 100000)
    
    # подключаем график к форме
    self.ui.chartView1.setChart(chart)

def draw_pie_chart(self):
    self.data_by_regions = {}

    for row in self.rows:
        items = self.data_by_regions.setdefault(row.region_id, [])
        items.append(row)

    series = QtCharts.QPieSeries()
    for region_id, region_data in self.data_by_regions.items():
        region_name = self.regions[region_id].title
        avg = int(mean([i.population for i in region_data]))
        series.append(f"{region_name}", avg)
    
    series.setLabelsVisible()

    chart = QtCharts.QChart()
    chart.addSeries(series)

    self.ui.chartView2.setChart(chart)

def draw_bar_chart(self):
    self.data_by_regions = {}

    years = set()
    for row in self.rows:
        items = self.data_by_regions.setdefault(row.region_id, {})
        items[row.year] = row.population
        years.add(row.year)

    years = sorted(years)

    series = QtCharts.QHorizontalStackedBarSeries()

    for region_id, region_data in self.data_by_regions.items():
        region_title = self.regions[region_id].title
        bar_set = QtCharts.QBarSet(region_title)
        for year in years:
            value = region_data.get(year, 0)
            bar_set.append(value)
        
        bar_set.setLabelColor("#0d0082")
        series.append(bar_set)

    series.setLabelsVisible()
    series.setLabelsPrecision(20)

    chart = QtCharts.QChart()
    chart.setAnimationOptions(QtCharts.QChart.AnimationOption.SeriesAnimations)
    chart.addSeries(series)
    chart.createDefaultAxes()

    axis = QtCharts.QBarCategoryAxis()
    axis.append([str(i) for i in years])
    chart.setAxisY(axis)
    series.attachAxis(axis)

    self.ui.chartView3.setChart(chart)

class ItemsModel(QtCore.QAbstractTableModel):
    def __init__(self, *args, **kwargs) -> None:
        super().__init__(*args, **kwargs)
        self.items = []
        self.regions = {}

    def setItems(self, items):
        self.beginResetModel()
        self.items = items
        self.endResetModel()

    def setRegion(self, regions):
        self.beginResetModel()
        self.regions = regions
        self.endResetModel()

    def rowCount(self, *args, **kwargs) -> int:
        return len(self.items)
    
    def columnCount(self, *args, **kwargs) -> int:
        return 4
    
    def data(self, index: QtCore.QModelIndex, role: QtCore.Qt.ItemDataRole):
        if not index.isValid():
            return
        
        if role == QtCore.Qt.ItemDataRole.DisplayRole:
            region_info = self.items[index.row()]
            col = index.column()
            if col == 0:
                return f'{region_info.id}'
            elif col == 1:
                region_title = self.regions[region_info.region_id].title
                return region_title
            elif col == 2:
                return f'{region_info.year}'
            elif col == 3:
                return f'{region_info.population}'
        elif role == QtCore.Qt.ItemDataRole.UserRole:
            return self.items[index.row()]

    def headerData(self, section: int, orientation: QtCore.Qt.Orientation, role: QtCore.Qt.ItemDataRole):
        if role == QtCore.Qt.ItemDataRole.DisplayRole:
            if orientation == QtCore.Qt.Orientation.Horizontal:
                return {
                    0: "id",
                    1: "Регион",
                    2: "Год",
                    3: "Население",
                }.get(section)

self.model = ItemsModel()
self.ui.tblItems.setModel(self.model)

# чтобы авторесайзить
self.ui.tblItems.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.ResizeMode.ResizeToContents)

def load_population(self):
    regions_data = self.ui.cmbRegions.currentData()
    if regions_data:
        region_id = self.ui.cmbRegions.currentData().id
    else:
        region_id = 0

    year = self.ui.cmbYear.currentText()

    self.rows = []

    with Session(self.engine) as s:
        #...
    
    # тут пробрасываем в модель данные
    self.model.setItems(self.rows)

    #...