import os
import sqlite3

from PyQt5.QtCore import QThread, QVariant
from qgis.PyQt import QtWidgets, uic, QtCore
from qgis.PyQt.QtCore import pyqtSignal

from .cyanlove_config_read_intfile import readconfig

FORM_CLASS, _ = uic.loadUiType(os.path.join(
    os.path.dirname(__file__), 'cyanlove_sqlite_create_index_base.ui'))

# 配置文件int格式
pathsaveint = os.path.join(os.path.dirname(__file__), 'config.ini')


class cyanlove_sqlite_create_index(QtWidgets.QDockWidget, FORM_CLASS):

    def __init__(self, parent=None):
        super(cyanlove_sqlite_create_index, self).__init__(parent)
        self.export_thread = None
        self.setupUi(self)
        self.setFloating(True)
        self.setWindowFlags(QtCore.Qt.Dialog)  # 使对话框始终在前
        self.setWindowModality(QtCore.Qt.ApplicationModal)
        self.btn_create_index.clicked.connect(self.create_index_sqlite)
        self.comboBox.currentIndexChanged.connect(self.get_sqlite_table_columns)
        # 初始化调用获取sqlite表
        self.initget_sqlite_table()
        self.get_sqlite_table_columns()

    def initget_sqlite_table(self):
        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
        # 连接到 SQLite 数据库
        conn = sqlite3.connect(db_path, timeout=5)
        cursor = conn.cursor()
        try:
            # 执行查询以获取表名
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            # 获取所有表名
            table_names = [row[0] for row in cursor.fetchall()]
            # 过滤掉空字符串
            table_names = [name for name in table_names if name]  # 只保留非空的表名
            self.comboBox.clear()
            self.comboBox.addItems(table_names)
            self.comboBox.setCurrentIndex(0)

        except Exception as e:
            self.label_show.setText(f"错误：{str(e)}")
        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()

    def get_sqlite_table_columns(self):
        current_text = self.comboBox.currentText()
        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
        # 连接到 SQLite 数据库
        conn = sqlite3.connect(db_path, timeout=5)
        cursor = conn.cursor()
        try:
            if current_text is not None:
                # 执行查询以获取表名
                cursor.execute(f"PRAGMA table_info([{current_text}]);")
                columns = cursor.fetchall()
                # 提取列名
                column_names = [column[1] for column in columns]  # column[1] 是列名
                self.comboBox_2.clear()
                self.comboBox_2.addItems(column_names)
                self.comboBox_2.setCurrentIndex(0)
        except Exception as e:
            self.label_show.setText(f"错误：{str(e)}")
        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()

    def create_index_sqlite(self):
        current_table = self.comboBox.currentText()
        current_column = self.comboBox_2.currentText()

        if current_table and current_column:  # Ensure both values are not empty
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            index_name = f"index_{current_table}_{current_column}"
            self.label_show.setText("开始创建...")
            try:
                # Check if the index already exists
                cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND name=?;", (index_name,))
                index_exists = cursor.fetchone()

                if not index_exists:  # If the index doesn't exist, create it
                    cursor.execute(f"CREATE INDEX {index_name} ON [{current_table}]([{current_column}]);")
                    conn.commit()  # Ensure changes are saved
                    self.label_show.setText("创建索引成功！")
                else:
                    self.label_show.setText("索引已存在，无需创建。")

            except sqlite3.Error as e:
                self.label_show.setText(f"错误：{str(e)}")
            finally:
                # Close cursor and connection
                cursor.close()
                conn.close()
