import os
import sqlite3
import re

from qgis.PyQt.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_import_layer_tosqlite_base.ui'))

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


class cyanlove_import_layerattribute_tosqlite(QtWidgets.QDockWidget, FORM_CLASS):

    def __init__(self, parent=None):
        super(cyanlove_import_layerattribute_tosqlite, self).__init__(parent)
        self.export_thread = None
        self.setupUi(self)
        self.setFloating(True)
        self.setWindowFlags(QtCore.Qt.Dialog)  # 使对话框始终在前
        self.btn_import_sqlite.clicked.connect(self.import_layer_tosqlite)
        self.checkBox_4g.toggled.connect(self.on_4g_toggled)
        self.checkBox_5g.toggled.connect(self.on_5g_toggled)
        self.checkBox_4g.stateChanged.connect(self.update_textedit_state)
        self.checkBox_5g.stateChanged.connect(self.update_textedit_state)

    def update_progress(self, value):
        self.progressBar.setValue(value)  # 更新进度条的值

    def on_4g_toggled(self, checked):
        if checked:
            self.checkBox_5g.setChecked(False)

    def on_5g_toggled(self, checked):
        if checked:
            self.checkBox_4g.setChecked(False)

    def update_textedit_state(self):
        # 获取当前两个复选框的状态
        check4g = self.checkBox_4g.isChecked()
        check5g = self.checkBox_5g.isChecked()

        # 如果两个都没选中，启用textEdit
        if not check4g and not check5g:
            self.textEdit.setEnabled(True)
        else:
            self.textEdit.setEnabled(False)

    def export_finished(self):
        print("导入完成！")
        self.label_show.setText('导入完成！')
        self.progressBar.setValue(100)

    def import_layer_tosqlite(self):
        tablename = self.textEdit.toPlainText()
        if self.checkBox_4g.isChecked():
            tablename = "TB_Cell4g"
        if self.checkBox_5g.isChecked():
            tablename = "TB_Cell5g"
        selected_layer = self.mMapLayerComboBox.currentLayer()
        if tablename == "":
            self.label_show.setText('没有输入表名称！')
            return
        # 验证表名：只允许字母、数字、下划线，且不能以数字开头
        if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', tablename):
            self.label_show.setText('表名包含非法字符！')
            return
        if selected_layer is not None:
            # 开始导入
            self.label_show.setText('开始导入.....')
            # 确保使用现有的进度条
            self.progressBar.setRange(0, 100)  # 设置进度条范围
            self.progressBar.setValue(0)  # 初始化进度条值
            self.progressBar.show()  # 显示进度条 (如果之前是隐藏状态)
            # 启动导出线程
            self.export_thread = ExportThread(selected_layer, tablename)
            self.export_thread.progress.connect(self.update_progress)  # 连接信号到槽
            self.export_thread.finished.connect(self.export_finished)  # 导出完成后连接到槽
            self.export_thread.start()  # 启动线程


class ExportThread(QThread):
    progress = pyqtSignal(int)  # 声明一个信号
    finished = pyqtSignal()     # 添加完成信号

    def __init__(self, selected_layer, tablename):
        super().__init__()
        self.selected_layer = selected_layer
        self.tablename = tablename

    def run(self):
        # 验证字段名安全性的函数
        def is_safe_identifier(name):
            return bool(re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', name))

        # 获取字段名列表并验证
        field_list = []
        for field in self.selected_layer.fields():
            field_name = field.name()
            if not is_safe_identifier(field_name):
                # 如果字段名不安全，跳过或报错（这里选择安全地转义，但实际图层字段名一般是安全的）
                # 为尽可能安全，我们将其用方括号包裹并继续，但最好还是检查
                pass
            field_list.append(field)

        # 构建安全字段名列表（用于SQL标识符）
        safe_field_names = [f"[{field.name()}]" for field in field_list]
        if self.tablename in ("TB_Cell4g", "TB_Cell5g"):
            safe_field_names.append("[边界P]")

        # 参数化查询时使用的占位符列表
        placeholders = ["?" for _ in range(len(safe_field_names))]

        # 创建表：根据字段类型定义列
        field_definitions = []
        for field in field_list:
            field_type = field.type()
            if field_type in (QVariant.Int, QVariant.Double):
                field_definitions.append(f"[{field.name()}] DOUBLE")
            else:
                field_definitions.append(f"[{field.name()}] TEXT")
        if self.tablename in ("TB_Cell4g", "TB_Cell5g"):
            field_definitions.append("[边界P] TEXT")

        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')

        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 删除旧表（表名已验证）
        cursor.execute(f"DROP TABLE IF EXISTS [{self.tablename}]")  # nosec: 表名已安全验证

        # 创建表（表名和字段名都已验证或内置安全）
        create_query = f"CREATE TABLE [{self.tablename}] ({', '.join(field_definitions)})"  # nosec
        cursor.execute(create_query)

        conn.execute('BEGIN TRANSACTION')

        total_features = self.selected_layer.featureCount()
        try:
            for index, feature in enumerate(self.selected_layer.getFeatures()):
                row_values = []
                for field in field_list:
                    value = feature[field.name()]
                    # 对于参数化查询，直接传原始值即可，不需要手动加引号
                    row_values.append(value if value is not None else None)
                if self.tablename in ("TB_Cell4g", "TB_Cell5g"):
                    boundary_wkt = feature.geometry().asWkt() if feature.geometry() else None
                    row_values.append(boundary_wkt)

                # 使用参数化 INSERT 语句
                insert_query = f"INSERT INTO [{self.tablename}] ({', '.join(safe_field_names)}) VALUES ({', '.join(placeholders)})"  # nosec
                cursor.execute(insert_query, row_values)

                # 进度更新
                progress_value = int((index + 1) / total_features * 100)
                self.progress.emit(progress_value)

            conn.commit()
        except Exception as e:
            print(f"Transaction failed: {str(e)}")
            conn.rollback()
        finally:
            cursor.close()
            conn.close()
            self.finished.emit()