import sys

from PySide6.QtCore import QLibraryInfo, qVersion, Qt
from PySide6.QtSql import QSqlQuery, QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import QApplication, QMessageBox, QDialog, QWidget, QInputDialog, QLineEdit, QVBoxLayout, \
    QTableView, QPushButton, QLabel


def create_table(name=None):
    """Create table in the database and fill some rows."""
    table_query = QSqlQuery()
    if not name:
        name = 'PYSIDE 1542'
    table_query.exec_(
        f"""CREATE TABLE IF NOT EXISTS '{name}' (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            full_name VARCHAR(200) DEFAULT ('')
        )
        """
    )
    table_query.exec_(f"INSERT INTO '{name}' (full_name) VALUES ('bbb')")
    table_query.exec_(f"INSERT INTO '{name}' (full_name) VALUES ('aaa')")
    table_query.exec_(f"INSERT INTO '{name}' (full_name) VALUES ('zzz')")
    table_query.exec_(f"INSERT INTO '{name}' (full_name) VALUES ('ccc')")


def create_connection(database_name):
    """Create and open a database connection."""
    connection = QSqlDatabase.addDatabase("QSQLITE")
    connection.setDatabaseName(database_name)
    if not connection.open():
        QMessageBox.warning(
            None,
            "PYSIDE-1542",
            f"Database Error: {connection.lastError().text()}",
        )
        return False
    create_table()
    query = QSqlQuery()
    query.exec_("SELECT sqlite_version()")
    query.next()
    print('Sqlite version: ', query.value(0))
    return True


class Window(QWidget):
    def __init__(self):
        super(Window, self).__init__()
        self.model = QSqlTableModel()
        self.model.setTable("PYSIDE 1542")
        self.model.select()
        while self.model.canFetchMore():
            self.model.fetchMore()
        layout = QVBoxLayout()
        self.button_create_table = QPushButton(self)
        self.button_create_table.setText("Create new table")
        self.button_create_table.clicked.connect(self.new_table_action)
        layout.addWidget(self.button_create_table)
        self.button_create_table = QPushButton(self)
        self.button_create_table.setText("Open exists table")
        self.button_create_table.clicked.connect(self.open_table_action)
        layout.addWidget(self.button_create_table)
        self.label_table_name = QLabel(self)
        self.label_table_name.setText(f"Table name: \n{self.model.tableName()}")
        layout.addWidget(self.label_table_name)
        self.table_view = QTableView(self)
        self.table_view.setModel(self.model)
        self.table_view.setSortingEnabled(True)
        self.call_table_view_no_sort()
        self.table_view.horizontalHeader().sectionClicked.connect(self.on_header_clicked)
        layout.addWidget(self.table_view)
        self.button_model_set_sort = QPushButton(self)
        self.button_model_set_sort.setText('Set sort\nCall "self.model.setSort(1, Qt.DescendingOrder)"')
        self.button_model_set_sort.clicked.connect(self.call_model_set_sort)
        layout.addWidget(self.button_model_set_sort)
        self.button_table_view_sort_by_column = QPushButton(self)
        self.button_table_view_sort_by_column.setText('Set sort\nCall "self.table_view.sortByColumn(1, Qt.AscendingOrder)"')
        self.button_table_view_sort_by_column.clicked.connect(self.call_table_view_sort_by_column)
        layout.addWidget(self.button_table_view_sort_by_column)
        self.button_table_view_no_sort = QPushButton(self)
        self.button_table_view_no_sort.setText('Unsort\nCall "self.table_view.sortByColumn(-1, Qt.AscendingOrder)"')
        self.button_table_view_no_sort.clicked.connect(self.call_table_view_no_sort)
        layout.addWidget(self.button_table_view_no_sort)

        self.setLayout(layout)

    def new_table_action(self):
        db = QSqlDatabase.database()
        text, ok = QInputDialog.getText(self, "Create table",
                                        "Table name:", QLineEdit.Normal,
                                        "")
        if ok and text != '':
            text = text.replace("'", "_")
            if text in db.tables():
                QMessageBox.warning(
                    self, self.windowTitle(),
                    f"Table with this name already exists.",
                )
                return
            create_table(text)
            self.model.setTable(text)
            self.model.select()
            while self.model.canFetchMore():
                self.model.fetchMore()
            self.label_table_name.setText(f"Table name: \n{self.model.tableName()}")

    def open_table_action(self):
        db = QSqlDatabase.database()
        db_tables_names = db.tables()
        if 'sqlite_sequence' in db_tables_names:
            db_tables_names.remove('sqlite_sequence')
        text, ok = QInputDialog.getItem(self, "Open table",
                                        "Table name:", db_tables_names,
                                        0, False)
        if ok and text != '':
            self.model.setTable(text)
            self.model.select()
            while self.model.canFetchMore():
                self.model.fetchMore()
            self.label_table_name.setText(f"Table name: \n{self.model.tableName()}")

    def call_model_set_sort(self):
        self.model.setSort(1, Qt.DescendingOrder)
        self.model.select()
        print("--------------------------------------------------")
        print("self.model.setSort(1, Qt.DescendingOrder)")
        print(self.model.selectStatement())

    def call_table_view_sort_by_column(self):
        self.table_view.sortByColumn(1, Qt.AscendingOrder)
        print("--------------------------------------------------")
        print("self.table_view.sortByColumn(1, Qt.AscendingOrder)")
        print(self.model.selectStatement())

    def call_table_view_no_sort(self):
        self.table_view.sortByColumn(-1, Qt.AscendingOrder)
        print("--------------------------------------------------")
        print("self.table_view.sortByColumn(-1, Qt.AscendingOrder)")
        print(self.model.selectStatement())

    def on_header_clicked(self, index):
        print("--------------------------------------------------")
        print(f"Table horizontal header of column {index} clicked.")
        print(self.model.selectStatement())


if __name__ == '__main__':
    print('Python {}.{}'.format(sys.version_info[0], sys.version_info[1]))
    print(QLibraryInfo.build())
    app = QApplication(sys.argv)
    if not create_connection("PYSIDE-1542.sqlite"):
        sys.exit(1)
    window = Window()
    window.setWindowTitle(qVersion())
    window.show()
    sys.exit(app.exec_())