import math
import os
import sqlite3
import re
from datetime import datetime

from qgis.PyQt.QtCore import QVariant, QThread, QCoreApplication
from pyproj import Geod
from qgis.core import QgsProject, QgsGeometry, QgsVectorLayer, QgsFields, QgsField, \
    QgsFeature, QgsPointXY, QgsDistanceArea, \
    QgsFillSymbol, QgsSingleSymbolRenderer

from qgis.utils import iface

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_create_shange_geometry_base.ui'))


class cyanlove_import_create_shange_geometry(QtWidgets.QDockWidget, FORM_CLASS):
    closingPlugin = pyqtSignal()

    def __init__(self, parent=None):
        super(cyanlove_import_create_shange_geometry, self).__init__(parent)
        self.export_thread = None
        self.iface = iface
        self.canvas = iface.mapCanvas()
        self.setupUi(self)
        self.setFloating(True)

        self.is_running = False  # 线程状态标志
        self.setWindowFlags(QtCore.Qt.Dialog)  # 使对话框始终在前
        # 设置为模态窗口
        self.setWindowModality(QtCore.Qt.ApplicationModal)  # 或者使用 Qt.WindowModal
        self.comboBox_sqliteset.currentIndexChanged.connect(self.get_sqlite_table_columns)
        self.pushButton_createshange.clicked.connect(self.writer_shange_geometry)
        # 初始化调用获取sqlite表
        self.initget_sqlite_table()

    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()]

            # ============================================================
            # 定义需要跳过的 SpatiaLite / SQLite 系统表
            # ============================================================
            # 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_sqliteset.clear()
            if filtered_tables:
                self.comboBox_sqliteset.addItems(filtered_tables)
                self.comboBox_sqliteset.setCurrentIndex(0)
            else:
                self.comboBox_sqliteset.setCurrentIndex(-1)

        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_sqliteset.currentText()
        if not current_text:
            self.label_show.setText("表名无效")
            return
        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
        conn = sqlite3.connect(db_path, timeout=5)
        cursor = conn.cursor()
        try:
            # 使用方括号包裹表名，防止SQL注入
            query = f"PRAGMA table_info([{current_text}]);"  # nosec: 表名来自下拉框，相对安全
            cursor.execute(query)
            columns = cursor.fetchall()
            # 提取列名（不过滤，允许中文字段名）
            column_names = [column[1] for column in columns]
            self.comboBox_centerlng.clear()
            self.comboBox_centerlng.addItems(column_names)
            self.comboBox_centerlng.setCurrentIndex(0 if column_names else -1)
            self.comboBox_centerlat.clear()
            self.comboBox_centerlat.addItems(column_names)
            self.comboBox_centerlat.setCurrentIndex(0 if column_names else -1)
        except Exception as e:
            self.label_show.setText(f"错误：{str(e)}")
        finally:
            cursor.close()
            conn.close()

    # 绘制栅格
    def writer_shange_geometry(self):
        current_text = self.comboBox_sqliteset.currentText()
        lngstr = self.comboBox_centerlng.currentText()
        latstr = self.comboBox_centerlat.currentText()
        print("ssss")
        # 验证表名和列名（只检查非空，不过滤字符）
        if not current_text:
            self.label_show.setText("表名无效")
            return
        if not lngstr:
            self.label_show.setText("请选择经度列")
            return
        if not latstr:
            self.label_show.setText("请选择纬度列")
            return

        # 获取栅格尺寸
        try:
            raster_length = float(self.textEdit.toPlainText() or "50")
            raster_width = float(self.textEdit_2.toPlainText() or "50")
        except ValueError:
            self.label_show.setText("栅格尺寸必须是数字")
            return

        # 获取是否不填充颜色的选项
        no_fill = self.checkBox_no_fill.isChecked()

        # 获取边框宽度
        try:
            border_width = float(self.textEdit_border_width.toPlainText() or "0.5")
            if border_width <= 0:
                self.label_show.setText("边框宽度必须大于0")
                return
        except ValueError:
            self.label_show.setText("边框宽度必须是数字")
            return

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

        # 启动栅格创建线程
        self.label_show.setText("开始创建栅格...")
        QCoreApplication.processEvents()

        self.export_thread = RasterCreationThread(
            db_path, current_text, lngstr, latstr, raster_length, raster_width, no_fill, border_width
        )
        self.export_thread.updatelabel.connect(self.update_label)
        self.export_thread.layerAdded.connect(self.add_layer_to_project)
        self.export_thread.start()

    def update_label(self, value):
        self.label_show.setText(value)

    def add_layer_to_project(self, layer):
        """将图层添加到项目"""
        QgsProject.instance().addMapLayer(layer, True)
        self.canvas.refresh()


class RasterCreationThread(QThread):
    progress = pyqtSignal(int)
    layerAdded = pyqtSignal(object)
    updatelabel = pyqtSignal(str)

    def __init__(self, db_path, table_name, lng_column, lat_column, raster_length, raster_width, no_fill=False,
                 border_width=0.5):
        super().__init__()
        self.db_path = db_path
        self.table_name = table_name
        self.lng_column = lng_column
        self.lat_column = lat_column
        self.raster_length = raster_length  # 栅格长度（米）
        self.raster_width = raster_width  # 栅格宽度（米）
        self.no_fill = no_fill  # 是否不填充颜色
        self.border_width = border_width  # 边框宽度
        # 初始化地理计算对象（使用WGS84椭球体）
        self.geod = Geod(ellps='WGS84')

    def run(self):
        try:
            self.updatelabel.emit("正在从数据库读取中心点坐标和字段信息...")

            # 从数据库读取中心点坐标和所有字段信息
            center_points, all_fields_data, db_fields = self.get_center_points_and_fields_from_db()
            if not center_points:
                self.updatelabel.emit("没有找到有效的中心点数据")
                return

            self.updatelabel.emit(f"找到 {len(center_points)} 个中心点，开始创建栅格图层...")

            # 创建图层名称
            timestamp = datetime.now().strftime("%H%M%S")
            base_filename = os.path.splitext(os.path.basename(self.table_name))[0]
            layer_name = f'{base_filename}_Raster_{timestamp}'
            # 检查是否存在同名图层，如果存在则删除
            existing_layers = QgsProject.instance().mapLayersByName(layer_name)
            for layer in existing_layers:
                QgsProject.instance().removeMapLayer(layer.id())

            # 创建多边形图层
            layer = QgsVectorLayer("Polygon?crs=EPSG:4326", layer_name, "memory")
            provider = layer.dataProvider()

            # 添加字段 - 包括原始数据库表中的所有字段
            fields = QgsFields()
            for field_name, field_type in db_fields:
                qgis_field_type = self.get_qgis_field_type(field_type)
                fields.append(QgsField(field_name, qgis_field_type))

            provider.addAttributes(fields)
            layer.updateFields()

            # 根据是否填充颜色设置不同的样式
            if self.no_fill:
                # 不填充颜色，仅显示边框 - 使用无填充样式
                symbol = QgsFillSymbol.createSimple({
                    'style': 'no',  # 无填充
                    'color_border': '0,100,255',
                    'width_border': str(self.border_width)
                })
            else:
                # 半透明蓝色填充
                symbol = QgsFillSymbol.createSimple({
                    'color': '0,100,255,50',
                    'color_border': '0,100,255',
                    'width_border': str(self.border_width)
                })

            layer.setRenderer(QgsSingleSymbolRenderer(symbol))

            features = []
            total_points = len(center_points)
            valid_count = 0

            for index, (center_data) in enumerate(zip(center_points, all_fields_data)):
                center_point, original_data = center_data
                center_lng, center_lat = center_point

                try:
                    # 计算栅格的四个顶点（使用椭圆外接矩形方法）
                    rect_coords = self.calculate_raster_vertices_ellipse(
                        center_lng, center_lat, self.raster_length, self.raster_width
                    )

                    if rect_coords:
                        # 创建多边形几何体
                        polygon = QgsGeometry.fromPolygonXY([rect_coords])

                        if polygon.isGeosValid():
                            feature = QgsFeature()
                            feature.setFields(fields)
                            feature.setGeometry(polygon)

                            # 首先设置原始数据库表中的字段值
                            for i, (field_name, field_type) in enumerate(db_fields):
                                field_value = original_data[i]
                                feature[field_name] = field_value
                            features.append(feature)
                            valid_count += 1

                    # 进度更新
                    if (index + 1) % max(1, total_points // 20) == 0 or (index + 1) == total_points:
                        progress = int((index + 1) / total_points * 100)
                        fill_status = "无填充" if self.no_fill else "有填充"
                        self.updatelabel.emit(
                            f"进度: {progress}% - 已处理 {index + 1}/{total_points} 个中心点，成功 {valid_count} 个栅格 ({fill_status}, 边框: {self.border_width})")
                except Exception as e:
                    self.updatelabel.emit(f"处理点 {index} 时出错: {str(e)}")
                    continue

            # 批量添加要素
            if features:
                provider.addFeatures(features)
                layer.updateExtents()

                fill_status = "无填充" if self.no_fill else "有填充"
                final_msg = f"栅格创建完成！共生成 {len(features)} 个有效栅格 ({fill_status}, 边框宽度: {self.border_width})"
                self.updatelabel.emit(final_msg)
                self.layerAdded.emit(layer)
            else:
                self.updatelabel.emit("没有成功创建任何栅格")

        except Exception as e:
            self.updatelabel.emit(f"创建栅格时出错: {str(e)}")
            import traceback
            print(traceback.format_exc())

    def get_center_points_and_fields_from_db(self):
        """从数据库获取中心点坐标和所有字段数据"""
        center_points = []
        all_fields_data = []
        db_fields = []

        conn = sqlite3.connect(self.db_path, timeout=5)
        cursor = conn.cursor()

        try:
            # 获取表结构信息（使用方括号包裹表名）
            pragma_sql = f"PRAGMA table_info([{self.table_name}]);"  # nosec: 表名来自用户选择
            cursor.execute(pragma_sql)
            columns_info = cursor.fetchall()

            # 提取字段名和类型（不过滤，保留原始字段名）
            for column_info in columns_info:
                field_name = column_info[1]
                field_type = column_info[2]
                db_fields.append((field_name, field_type))

            if not db_fields:
                self.updatelabel.emit("表中无有效字段")
                return [], [], []

            # 使用 SELECT * 获取所有数据
            select_sql = f"SELECT * FROM [{self.table_name}];"  # nosec: 表名来自用户选择
            cursor.execute(select_sql)
            rows = cursor.fetchall()

            # 获取列名列表（与db_fields顺序一致）
            column_names = [field[0] for field in db_fields]

            # 找到经度和纬度字段的索引
            try:
                lng_index = column_names.index(self.lng_column)
                lat_index = column_names.index(self.lat_column)
            except ValueError:
                self.updatelabel.emit(f"未找到指定的经度字段({self.lng_column})或纬度字段({self.lat_column})")
                return [], [], []

            for row in rows:
                try:
                    lng = float(row[lng_index])
                    lat = float(row[lat_index])

                    # 验证坐标范围
                    if -180 <= lng <= 180 and -90 <= lat <= 90:
                        center_points.append((lng, lat))
                        all_fields_data.append(row)  # 保存所有字段的数据

                except (ValueError, TypeError):
                    # 跳过无法转换为浮点数的数据
                    continue

        except Exception as e:
            self.updatelabel.emit(f"读取数据库时出错: {str(e)}")
        finally:
            cursor.close()
            conn.close()

        return center_points, all_fields_data, db_fields

    def get_qgis_field_type(self, sqlite_type):
        """将SQLite字段类型映射到QGIS字段类型"""
        sqlite_type = str(sqlite_type).upper()

        if 'INT' in sqlite_type:
            return QVariant.Int
        elif 'REAL' in sqlite_type or 'FLOAT' in sqlite_type or 'DOUBLE' in sqlite_type:
            return QVariant.Double
        elif 'BOOL' in sqlite_type:
            return QVariant.Bool
        elif 'DATE' in sqlite_type:
            return QVariant.Date
        elif 'TIME' in sqlite_type or 'DATETIME' in sqlite_type:
            return QVariant.DateTime
        else:
            # 默认处理为字符串类型
            return QVariant.String

    def calculate_raster_vertices_ellipse(self, center_lng, center_lat, length_m, width_m):
        """
        使用椭圆外接矩形方法计算栅格的四个顶点
        """
        try:
            # 计算椭圆在四个方向上的边界点
            # 东方向（90度方位角）
            east_lng, east_lat, _ = self.geod.fwd(center_lng, center_lat, 90, length_m / 2)
            # 西方向（270度方位角）
            west_lng, west_lat, _ = self.geod.fwd(center_lng, center_lat, 270, length_m / 2)
            # 北方向（0度方位角）
            north_lng, north_lat, _ = self.geod.fwd(center_lng, center_lat, 0, width_m / 2)
            # 南方向（180度方位角）
            south_lng, south_lat, _ = self.geod.fwd(center_lng, center_lat, 180, width_m / 2)

            # 计算四个顶点
            # 东北顶点
            ne_lng, ne_lat, _ = self.geod.fwd(center_lng, center_lat, 45,
                                              math.sqrt((length_m / 2) ** 2 + (width_m / 2) ** 2))
            # 西北顶点
            nw_lng, nw_lat, _ = self.geod.fwd(center_lng, center_lat, 315,
                                              math.sqrt((length_m / 2) ** 2 + (width_m / 2) ** 2))
            # 东南顶点
            se_lng, se_lat, _ = self.geod.fwd(center_lng, center_lat, 135,
                                              math.sqrt((length_m / 2) ** 2 + (width_m / 2) ** 2))
            # 西南顶点
            sw_lng, sw_lat, _ = self.geod.fwd(center_lng, center_lat, 225,
                                              math.sqrt((length_m / 2) ** 2 + (width_m / 2) ** 2))

            # 返回闭合的多边形顶点列表（顺时针方向）
            return [
                QgsPointXY(sw_lng, sw_lat),
                QgsPointXY(nw_lng, nw_lat),
                QgsPointXY(ne_lng, ne_lat),
                QgsPointXY(se_lng, se_lat),
                QgsPointXY(sw_lng, sw_lat)  # 闭合多边形
            ]

        except Exception as e:
            self.updatelabel.emit(f"计算栅格顶点时出错 (中心点: {center_lng}, {center_lat}): {str(e)}")
            return None

    def calculate_raster_vertices_alternative(self, center_lng, center_lat, length_m, width_m):
        """
        备选方法：使用更精确的投影变换
        """
        try:
            # 使用QGIS的距离面积计算器
            d = QgsDistanceArea()
            d.setEllipsoid('WGS84')

            # 将中心点转换为QgsPointXY
            center_point = QgsPointXY(center_lng, center_lat)

            # 计算四个顶点
            bearing_east = 90
            bearing_west = 270
            bearing_north = 0
            bearing_south = 180

            # 东北
            ne_point = d.computeSpheroidProject(center_point, length_m / 2, bearing_east)
            ne_point = d.computeSpheroidProject(ne_point, width_m / 2, bearing_north)

            # 西北
            nw_point = d.computeSpheroidProject(center_point, length_m / 2, bearing_west)
            nw_point = d.computeSpheroidProject(nw_point, width_m / 2, bearing_north)

            # 东南
            se_point = d.computeSpheroidProject(center_point, length_m / 2, bearing_east)
            se_point = d.computeSpheroidProject(se_point, width_m / 2, bearing_south)

            # 西南
            sw_point = d.computeSpheroidProject(center_point, length_m / 2, bearing_west)
            sw_point = d.computeSpheroidProject(sw_point, width_m / 2, bearing_south)

            # 返回闭合的多边形顶点列表
            return [
                sw_point,
                nw_point,
                ne_point,
                se_point,
                sw_point
            ]

        except Exception as e:
            self.updatelabel.emit(f"备选方法计算栅格顶点时出错: {str(e)}")
            # 如果备选方法失败，回退到椭圆方法
            return self.calculate_raster_vertices_ellipse(center_lng, center_lat, length_m, width_m)