import os
import sqlite3
import re
import traceback
from datetime import datetime
from collections import Counter, defaultdict

from qgis.PyQt.QtCore import QThread, QVariant, pyqtSignal
from qgis.PyQt import QtWidgets, uic, QtCore
from qgis.PyQt.QtXml import QDomDocument
from qgis.core import (
    QgsWkbTypes, QgsGeometry, QgsApplication,
    QgsVectorLayer, QgsDataSourceUri, QgsReadWriteContext,
    QgsCategorizedSymbolRenderer, QgsRendererCategory,
    QgsFillSymbol, QgsSingleSymbolRenderer
)

from .cyanlove_config_read_intfile import readconfig

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


# ================== SQL 安全辅助函数 ==================
def safe_identifier(name: str) -> str:
    """
    将标识符（表名、列名）用双引号包裹，并转义内部的双引号。
    适用于 SQLite 标识符。
    """
    return '"' + name.replace('"', '""') + '"'


def safe_sql_string(value: str) -> str:
    """
    将字符串值用单引号包裹，并转义内部的单引号。
    适用于 SQL 字符串字面量。
    """
    return "'" + value.replace("'", "''") + "'"


# =====================================================

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()
        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_\u4e00-\u9fa5][A-Za-z0-9_\u4e00-\u9fa5]*$', 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__(parent=None)
        self.selected_layer = selected_layer
        self.tablename = tablename

    def run(self):
        def log(msg):
            print(f"[{datetime.now().strftime('%H:%M:%S')}] {msg}")

        conn = None
        cursor = None

        try:
            log("开始导入...")
            layer = self.selected_layer
            if layer is None:
                log("错误：未选择图层")
                self.finished.emit()
                return

            log(f"图层: {layer.name()} | 要素数: {layer.featureCount()} | 含几何: {layer.isSpatial()}")

            # ========== 获取字段信息 ==========
            all_fields = layer.fields()
            geom_field_index = -1
            if hasattr(all_fields, 'geometryIndex'):
                geom_field_index = all_fields.geometryIndex()
            if geom_field_index == -1:
                for i in range(all_fields.count()):
                    f = all_fields[i]
                    if (hasattr(f, 'isGeometry') and f.isGeometry()) or f.type() == QVariant.UserType:
                        geom_field_index = i
                        break

            has_geometry = layer.isSpatial() and layer.geometryType() != QgsWkbTypes.UnknownGeometry

            # 属性字段（排除几何字段）
            attr_fields = [all_fields[i] for i in range(all_fields.count()) if i != geom_field_index]

            # ========== ★ 处理重复字段名，生成唯一列名 ==========
            raw_names = [f.name() for f in attr_fields]
            name_counts = Counter(raw_names)

            # 预占系统保留列名（pk_id 为主键，geometry 为空间列）
            used_names = set()
            used_names.add('pk_id')          # 主键列名
            if has_geometry:
                used_names.add('geometry')   # 几何列名

            seen_count = defaultdict(int)          # 记录每个原始名称已分配的数量
            new_column_names = []                  # 新列名列表（顺序与 attr_fields 一致）

            for f in attr_fields:
                raw = f.name()
                if name_counts[raw] == 1:
                    # 非重复字段，尽量保留原名
                    new_name = raw
                    # 若与已有名称（或保留列名）冲突，则追加数字
                    suffix = 1
                    while new_name in used_names:
                        new_name = f"{raw}_{suffix}"
                        suffix += 1
                else:
                    # 重复字段
                    count = seen_count[raw]        # 当前已分配的数量（0 表示第一个）
                    if count == 0:
                        new_name = raw             # 第一个保留原名
                    else:
                        new_name = f"{raw}_{count}"
                    # 确保最终名称唯一
                    suffix = count
                    while new_name in used_names:
                        suffix += 1
                        new_name = f"{raw}_{suffix}"
                    seen_count[raw] = suffix + 1   # 更新已分配数量

                new_column_names.append(new_name)
                used_names.add(new_name)

            # 安全化处理（加双引号）
            safe_attr_names = [safe_identifier(name) for name in new_column_names]
            placeholders = ["?" for _ in range(len(attr_fields))]

            # ========== 连接数据库 ==========
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            if not db_path:
                log("错误：未配置数据库路径")
                self.finished.emit()
                return

            conn = sqlite3.connect(db_path)
            conn.enable_load_extension(True)

            # 加载 SpatiaLite 扩展（完全保留原逻辑）
            spatialite_loaded = False
            ext_list = ['mod_spatialite', 'libspatialite']
            if os.name == 'nt':
                ext_list = ['mod_spatialite', 'mod_spatialite.dll']
            else:
                ext_list = ['mod_spatialite', 'libspatialite.so']

            for ext in ext_list:
                try:
                    conn.load_extension(ext)
                    spatialite_loaded = True
                    log(f"已加载扩展: {ext}")
                    break
                except:
                    continue

            if not spatialite_loaded and has_geometry:
                try:
                    qgis_prefix = QgsApplication.prefixPath()
                    if os.name == 'nt':
                        ext_path = os.path.join(qgis_prefix, 'bin', 'mod_spatialite.dll')
                    else:
                        ext_path = os.path.join(qgis_prefix, 'lib', 'libspatialite.so')
                    if os.path.exists(ext_path):
                        conn.load_extension(ext_path)
                        spatialite_loaded = True
                        log(f"已加载扩展: {ext_path}")
                except:
                    pass

            if has_geometry and not spatialite_loaded:
                log("错误：无法加载SpatiaLite扩展，导入中止")
                self.finished.emit()
                return

            cursor = conn.cursor()

            # ========== 采样前 100 行，推断字段类型（基于原始字段名） ==========
            total = layer.featureCount()
            sample_limit = min(100, total)
            sample_values = {f.name(): [] for f in attr_fields}
            for idx, feature in enumerate(layer.getFeatures()):
                if idx >= sample_limit:
                    break
                for f in attr_fields:
                    val = feature[f.name()]
                    if val is not None and val != '':
                        sample_values[f.name()].append(val)

            def infer_type(values):
                """根据样本值列表推断 SQLite 类型"""
                if not values:
                    return 'TEXT'
                has_float = False
                has_int = False
                has_text = False
                for v in values:
                    if isinstance(v, int):
                        has_int = True
                    elif isinstance(v, float):
                        if v.is_integer():
                            has_int = True
                        else:
                            has_float = True
                    elif isinstance(v, str):
                        s = v.strip()
                        if not s:
                            continue
                        try:
                            if '.' in s or 'e' in s or 'E' in s:
                                float(s)
                                has_float = True
                            else:
                                int(s)
                                has_int = True
                        except ValueError:
                            has_text = True
                    else:
                        has_text = True
                if has_text:
                    return 'TEXT'
                elif has_float:
                    return 'REAL'
                elif has_int:
                    return 'INTEGER'
                else:
                    return 'TEXT'

            # 为每个新列名分配类型（根据原始字段名）
            column_types = {}
            for raw_name, new_name in zip(raw_names, new_column_names):
                column_types[new_name] = infer_type(sample_values.get(raw_name, []))

            # ========== 创建数据表（含自增主键 pk_id） ==========
            safe_tablename = safe_identifier(self.tablename)
            cursor.execute(f"DROP TABLE IF EXISTS {safe_tablename}")

            pk_def = "pk_id INTEGER PRIMARY KEY AUTOINCREMENT"
            attr_defs = []
            for new_name in new_column_names:
                field_name_safe = safe_identifier(new_name)
                sql_type = column_types[new_name]
                attr_defs.append(f"{field_name_safe} {sql_type}")

            all_defs = [pk_def] + attr_defs
            create_sql = f"CREATE TABLE {safe_tablename} ({', '.join(all_defs)})"
            cursor.execute(create_sql)
            log("数据表创建成功（含自增主键 pk_id）")

            # ========== 添加几何列 ==========
            if has_geometry:
                srid = layer.crs().postgisSrid() if layer.crs().isValid() else 4326
                if srid <= 0:
                    srid = 0

                # 检查几何类型一致性
                base_type_set = set()
                for feat in layer.getFeatures():
                    geom = feat.geometry()
                    if geom and not geom.isEmpty():
                        wkb = geom.wkbType()
                        base = QgsWkbTypes.flatType(wkb)
                        if base != QgsWkbTypes.Unknown:
                            base_type_set.add(base)

                if len(base_type_set) > 1:
                    log("错误：图层包含多种基础几何类型（如点、线、面混合），无法使用单类型列。请先统一类型或改用GEOMETRY。")
                    self.finished.emit()
                    return
                elif len(base_type_set) == 0:
                    log("错误：图层没有有效几何要素，无法确定几何类型")
                    self.finished.emit()
                    return
                else:
                    base_type = base_type_set.pop()
                    multi_wkb = QgsWkbTypes.multiType(base_type)
                    if multi_wkb != QgsWkbTypes.Unknown:
                        target_geom_type = QgsWkbTypes.displayString(multi_wkb).upper()
                    else:
                        target_geom_type = QgsWkbTypes.displayString(base_type).upper()
                    # 去除维度后缀
                    for suffix in (' M', ' Z', ' 2.5D'):
                        if target_geom_type.endswith(suffix):
                            target_geom_type = target_geom_type[:-len(suffix)]

                log(f"使用几何列类型: {target_geom_type}，SRID: {srid}")

                table_literal = safe_sql_string(self.tablename)
                safe_geom_type = safe_sql_string(target_geom_type)
                add_geom_sql = f"SELECT AddGeometryColumn({table_literal}, 'geometry', {srid}, {safe_geom_type}, 'XY')"
                cursor.execute(add_geom_sql)
                log(f"几何列添加成功，类型: {target_geom_type}")

                # 使用 CastToMulti 兼容单/多部分几何
                geom_sql = f"CastToMulti(GeomFromWKB(?, {srid}))"
                insert_sql = f"""
                    INSERT INTO {safe_tablename} ({', '.join(safe_attr_names)}, geometry)
                    VALUES ({', '.join(placeholders)}, {geom_sql})
                """ # nosec B608
            else:
                insert_sql = f"""
                    INSERT INTO {safe_tablename} ({', '.join(safe_attr_names)})
                    VALUES ({', '.join(placeholders)})
                """ # nosec B608

            # ========== 插入数据 ==========
            if total == 0:
                log("无数据，已创建空表")
                conn.commit()
                self.finished.emit()
                return

            conn.execute('BEGIN TRANSACTION')
            for idx, feature in enumerate(layer.getFeatures()):
                row_values = []
                for f in attr_fields:
                    val = feature[f.name()]
                    if val is None:
                        row_values.append(None)
                    elif isinstance(val, (int, float)):
                        row_values.append(val)
                    else:
                        row_values.append(str(val) if val != '' else None)

                if has_geometry:
                    geom = feature.geometry()
                    row_values.append(geom.asWkb() if geom and not geom.isEmpty() else None)

                cursor.execute(insert_sql, row_values)

                progress_val = int((idx + 1) / total * 100)
                self.progress.emit(progress_val)
                if (idx + 1) % 10000 == 0:
                    log(f"已处理 {idx + 1}/{total} 条")

            conn.commit()
            log(f"数据导入成功，共 {total} 条记录")

            # ========== 创建空间索引 ==========
            if has_geometry:
                try:
                    log("开始创建空间索引...")
                    table_literal = safe_sql_string(self.tablename)
                    create_index_sql = f"SELECT CreateSpatialIndex({table_literal}, 'geometry')"
                    cursor.execute(create_index_sql)
                    result = cursor.fetchone()
                    if result and result[0] == 1:
                        log("空间索引创建成功")
                    else:
                        log("空间索引创建完成（请检查确认）")
                    conn.commit()
                except Exception as e:
                    log(f"创建空间索引失败: {e}")
                    log("空间索引创建失败，但数据已导入完成，可在DB Manager中手动创建")

        except Exception as e:
            log(f"导入失败: {e}")
            log(traceback.format_exc())
            if conn:
                try:
                    conn.rollback()
                except:
                    pass
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                try:
                    conn.close()
                except:
                    pass
            self.finished.emit()
            log("导入线程结束")