import sys, socket, pyodbc, sqlite3
from PyQt5.QtWidgets import (
    QApplication, QWidget, QVBoxLayout, QHBoxLayout,
    QLineEdit, QPushButton, QComboBox,
    QTableWidget, QTableWidgetItem, QStatusBar, QMessageBox, QTextEdit, QHeaderView, QSplitter, QGridLayout
)
from PyQt5.QtCore import Qt

class SQLQueryApp(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()
        
    def initUI(self):
        self.setWindowTitle("SQL Query Tool")
        
        # Main layout
        self.main_layout = QVBoxLayout()
        input_layout = QHBoxLayout()
        
        # Input for office code or IP
        self.input_code_ip = QLineEdit(self)
        self.input_code_ip.setPlaceholderText("Enter Office Code (xxxxx) or IP Address")
        input_layout.addWidget(self.input_code_ip)

        # Username input
        self.username_input = QLineEdit(self)
        self.username_input.setPlaceholderText("Username (default: TRANSF)")
        self.username_input.setText("TRANSF")
        input_layout.addWidget(self.username_input)

        # Password input
        self.password_input = QLineEdit(self)
        self.password_input.setPlaceholderText("Password (default: FSNART)")
        self.password_input.setEchoMode(QLineEdit.Password)
        self.password_input.setText("FSNART")
        input_layout.addWidget(self.password_input)

        # Database selection
        self.database_combo = QComboBox(self)
        self.database_combo.addItems(["ESTORE", "INFOBUS", "LOCALPOS", "TESTDB"])
        input_layout.addWidget(self.database_combo)

        # Run Script Button
        self.run_button = QPushButton("Run Script", self)
        self.run_button.clicked.connect(self.run_query)
        input_layout.addWidget(self.run_button)

        # Stop Button
        self.stop_button = QPushButton("Stop", self)
        self.stop_button.clicked.connect(self.stop_query)
        input_layout.addWidget(self.stop_button)

        self.main_layout.addLayout(input_layout)

        # SQL command input
        self.sql_command_input = QTextEdit(self)
        self.sql_command_input.setPlaceholderText("Enter SQL command(s) here, separated by semicolons...")
        self.main_layout.addWidget(self.sql_command_input)

        # Grid layout for results
        self.grid_layout = QGridLayout()
        self.main_layout.addLayout(self.grid_layout)

        # Status Bar
        self.status_bar = QStatusBar(self)
        self.main_layout.addWidget(self.status_bar)

        self.setLayout(self.main_layout)

    def office_code_to_ip(self, office_code):
        transformed_code = f"pth{office_code}"
        try:
            ip_address = socket.gethostbyname(transformed_code)
            return ip_address
        except socket.error as e:
            QMessageBox.critical(self, "Error", f"Could not resolve {transformed_code}: {e}")
            return None

    def run_query(self):
        code_or_ip = self.input_code_ip.text().strip()
        ip = None

        database = self.database_combo.currentText()

        # If "TESTDB" is selected, connect to the SQLite database directly
        if database == "TESTDB":
            conn_str = "DRIVER={SQLite3};DATABASE=testdb.db"
            try:
                conn = pyodbc.connect(conn_str)
            except pyodbc.Error as e:
                QMessageBox.critical(self, "Database Error", str(e))
                return
        else:
            # Determine if input is an IP or an office code
            if code_or_ip:
                parts = code_or_ip.split('.')
                if len(parts) == 4 and all(part.isdigit() and 0 <= int(part) <= 255 for part in parts):
                    ip = code_or_ip
                else:
                    ip = self.office_code_to_ip(code_or_ip)

            if not ip:
                QMessageBox.warning(self, "Input Error", "Please provide a valid office code or IP address.")
                return
            
            username = self.username_input.text().strip()
            password = self.password_input.text().strip()

            conn_str = f"DRIVER={{SQL Server}};SERVER={ip};DATABASE={database};UID={username};PWD={password}"
            try:
                conn = pyodbc.connect(conn_str)
            except pyodbc.Error as e:
                QMessageBox.critical(self, "Database Error", str(e))
                return

        sql_commands = self.sql_command_input.toPlainText().strip().split(';')

        # Clear previous results
        for i in reversed(range(self.grid_layout.count())):
            widget = self.grid_layout.itemAt(i).widget()
            if widget:
                widget.deleteLater()

        if not sql_commands or all(cmd.strip() == "" for cmd in sql_commands):
            QMessageBox.warning(self, "Input Error", "Please enter valid SQL commands.")
            return

        try:
            for row_index, command in enumerate(sql_commands):
                command = command.strip()
                if command:
                    cursor = conn.cursor()
                    cursor.execute(command)

                    # Fetch results
                    results = cursor.fetchall()
                    columns = [column[0] for column in cursor.description]

                    # Create a new splitter for each result set
                    splitter = QSplitter(Qt.Vertical)

                    result_table = QTableWidget(self)
                    result_table.setRowCount(len(results))
                    result_table.setColumnCount(len(columns))
                    result_table.setHorizontalHeaderLabels(columns)

                    for row_idx, row in enumerate(results):
                        for col_idx, item in enumerate(row):
                            table_item = QTableWidgetItem(str(item))
                            table_item.setFlags(table_item.flags() & ~Qt.ItemIsEditable)
                            table_item.setTextAlignment(Qt.AlignLeft | Qt.AlignVCenter)
                            result_table.setItem(row_idx, col_idx, table_item)

                    result_table.resizeColumnsToContents()
                    result_table.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)

                    # Add the table to the splitter
                    splitter.addWidget(result_table)

                    # Add the splitter to the grid layout
                    self.grid_layout.addWidget(splitter, row_index, 0)

            self.status_bar.showMessage("Queries executed successfully", 5000)

        except pyodbc.Error as e:
            QMessageBox.critical(self, "Database Error", str(e))

    def stop_query(self):
        QMessageBox.information(self, "Stop", "Stop functionality not implemented yet.")

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = SQLQueryApp()
    window.resize(800, 600)
    window.show()
    sys.exit(app.exec_())