import os
import sqlite3

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_栅格分析')
        conn = sqlite3.connect(db_path, timeout=5)
        cursor = conn.cursor()
        try:
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            all_tables = [row[0] for row in cursor.fetchall()]

            # ============================================================
            # 定义需要跳过的表名（完整列表）
            # ============================================================
            # 1. 精确匹配（完全相等，不区分大小写）
            exact_skip = [
                'geometry_columns',
                'geometry_columns_auth',
                'spatial_ref_sys',
                'spatial_ref_sys_auth',
                'spatial_ref_sys_aux',  # 新增
                'views_geometry_columns',
                'virts_geometry_columns',
                'ElementaryGeometries',
                'SpatialIndex',
                'sqlite_sequence',
                'spatialite_history',  # 新增
                'data_licenses',  # 新增
                'KNN2',  # 新增
                'sql_statements_log'  # 新增
            ]
            exact_skip_lower = {name.lower() for name in exact_skip}

            # 2. 前缀匹配（以这些开头就跳过）
            prefix_skip = [
                'sqlite_',  # SQLite 内部表
                'geometry_',  # 某些 SpatiaLite 表
                'idx_',  # 索引表
                'rtree_',  # R树索引表
                'views_',  # 视图表
                'virts_geometry',  # 新增：以 virts_geometry 开头的表
            ]

            # 过滤表名
            filtered_tables = []
            for name in all_tables:
                if not name:
                    continue
                # 精确匹配（不区分大小写）
                if name.lower() in exact_skip_lower:
                    continue
                # 前缀匹配
                skip_prefix = False
                for prefix in prefix_skip:
                    if name.lower().startswith(prefix.lower()):
                        skip_prefix = True
                        break
                if skip_prefix:
                    continue
                filtered_tables.append(name)

            self.comboBox.clear()
            if filtered_tables:
                self.comboBox.addItems(filtered_tables)
                self.comboBox.setCurrentIndex(0)
            else:
                self.comboBox.setCurrentIndex(-1)
                self.comboBox_2.clear()

        except Exception as e:
            self.label_show.setText(f"错误：{str(e)}")
        finally:
            cursor.close()
            conn.close()

    # 其他方法（get_sqlite_table_columns 和 create_index_sqlite）保持不变
    # 但为了完整性，我这里也附上完整的两个方法

    def get_sqlite_table_columns(self):
        current_text = self.comboBox.currentText()
        if not current_text:
            self.comboBox_2.clear()
            return

        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
        conn = sqlite3.connect(db_path, timeout=5)
        cursor = conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info([{current_text}]);")
            columns = cursor.fetchall()
            column_names = [column[1] for column in columns]
            self.comboBox_2.clear()
            self.comboBox_2.addItems(column_names)
            if 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 not current_table or not current_column:
            self.label_show.setText("请先选择表和列！")
            return

        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:
            cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND name=?;", (index_name,))
            if cursor.fetchone() is None:
                cursor.execute(f"CREATE INDEX {index_name} ON [{current_table}]([{current_column}]);")
                conn.commit()
                self.label_show.setText("创建索引成功！")
            else:
                self.label_show.setText("索引已存在，无需创建。")
        except sqlite3.Error as e:
            self.label_show.setText(f"错误：{str(e)}")
        finally:
            cursor.close()
            conn.close()