import psycopg2
from PyQt5.QtCore import pyqtSignal, QVariant
from qgis.PyQt import uic
from .cyanlove_config_read_intfile import *
from qgis._core import QgsProject, QgsVectorLayer, QgsPointXY, QgsGeometry, QgsFeature, QgsFillSymbol, \
    QgsSingleSymbolRenderer, QgsField, QgsRendererCategory, \
    QgsCategorizedSymbolRenderer, QgsFields
from qgis.utils import iface

from PyQt5 import QtCore, QtWidgets

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


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

    def __init__(self, parent=None):
        super(cyanlove_sg_search, self).__init__(parent)
        self.setupUi(self)
        self.setFloating(True)
        self.tbname_wanggeshow = "tb_2025年4月栅格cell2025"
        self.tbname_wanggeshowhuiju = "tb_2025年4月栅格shange汇聚"
        self.setWindowFlags(QtCore.Qt.Dialog)  # 使对话框始终在前
        self.btn_sg_cgisearch.clicked.connect(self.get_wangge_show)



    def get_wangge_show(self):
        cgi = self.txt_write.toPlainText()
        if cgi == "":
            self.label_show.setText('没有填写cgi！')
            return
         # self.shenshaojunSetPT(cgi)
        self.shenshaojunSetPT2(cgi)

        # 获取栅格显示

    def shenshaojunSetPT(self, cgistr: str):

        query = """SELECT cgi,cgi_name,gh,gpscenterlng,gpscenterlat,cnt_rsrp,cnt_rsrp110
                         FROM """ + self.tbname_wanggeshow + """
                         WHERE cgi='""" + cgistr + """'
                         AND cast(cnt_rsrp as DOUBLE PRECISION)>=20
                         AND (cast(cnt_rsrp110  as DOUBLE PRECISION)/cast(cnt_rsrp as DOUBLE PRECISION))<0.8"""



        self.GetFeaturesPT(query, "SSJ_SG", 'red')

        query = """SELECT cgi,cgi_name,gh,gpscenterlng,gpscenterlat,cnt_rsrp,cnt_rsrp110
                          FROM """ + self.tbname_wanggeshow + """
                          WHERE cgi='""" + cgistr + """'
                          AND cast(cnt_rsrp as DOUBLE PRECISION)>=20
                          AND (cast(cnt_rsrp110  as DOUBLE PRECISION)/cast(cnt_rsrp as DOUBLE PRECISION))>=0.8"""
        self.GetFeaturesPT(query, "SSJ_SG2", 'chartreuse')

    def GetFeaturesPT(self, query: str, layName: str, FillColor: str):
        # PT 数据库文件路径
        try:
            conn = psycopg2.connect(database="postgres",
                                    user="postgres",
                                    password="ssj123",
                                    host="localhost",
                                    port="5432")

            print("Database opened successfully")
            cursor = conn.cursor()
            cursor.execute(query)
            # 获取查询结果
            results = cursor.fetchall()

            if not results:  # 如果结果为空
                self.label_show.setText('没有查询到数据！')
                return
            else:
                self.label_show.setText('有数据！')
            # 矩形的尺寸（单位：米）
            length = 19  # 矩形的长度
            width = 38  # 矩形的宽度
            # 获取所有图层
            layers = QgsProject.instance().mapLayersByName(layName)
            # 如果找到图层，则删除它
            if layers:
                for layer in layers:
                    # 从项目中移除图层
                    QgsProject.instance().removeMapLayer(layer.id())
            # 创建一个新的矢量图层
            layer = QgsVectorLayer("Polygon?crs=EPSG:4326", layName, "memory")
            provider = layer.dataProvider()

            fields = QgsFields()  # 创建字段集合
            fields.append(QgsField("gh", QVariant.String))  # 添加字符串类型字段
            fields.append(QgsField("gpscenterlng", QVariant.String))  # 添加字符串类型字段
            fields.append(QgsField("gpscenterlat", QVariant.String))  # 添加字符串类型字段
            fields.append(QgsField("rsrp", QVariant.String))  # 添加字符串类型字段
            fields.append(QgsField("rsrp110", QVariant.String))  # 添加字符串类型字段

            provider.addAttributes(fields)  # 将字段添加到数据提供者
            layer.updateFields()  # 更新图层的字段

            center_lon1 = 0
            center_lat1 = 0
            k = 1

            # 打印结果
            for row in results:
                center_lon = float(row[3])  # 经度 gpscenterlng
                center_lat = float(row[4])  # 纬度 gpscenterlat
                if k == 1:
                    center_lon1 = center_lon
                    center_lat1 = center_lat
                    # 小区名称显示
                    # self.txt_readline.setText(row[0])
                k = k + 1
                # 1度经度约为111320米，1度纬度约为110540米
                delta_lon = float(width / 111320)
                delta_lat = float(length / 110540)

                # 计算矩形的四个角点
                bottom_left = QgsPointXY(center_lon - delta_lon / 2, center_lat - delta_lat / 2)
                bottom_right = QgsPointXY(center_lon + delta_lon / 2, center_lat - delta_lat / 2)
                top_left = QgsPointXY(center_lon - delta_lon / 2, center_lat + delta_lat / 2)
                top_right = QgsPointXY(center_lon + delta_lon / 2, center_lat + delta_lat / 2)
                # 创建矩形的几何形状
                rect_geometry = QgsGeometry.fromPolygonXY(
                    [[bottom_left, bottom_right, top_right, top_left, bottom_left]])
                # 创建一个新特征并设置几何形状
                feature = QgsFeature(fields)  # 创建新特征
                feature["gh"] = row[2]
                feature["gpscenterlng"] = row[3]
                feature["gpscenterlat"] = row[4]
                feature["rsrp"] = row[5]
                feature["rsrp110"] = row[6]
                feature.setGeometry(rect_geometry)

                # 将特征添加到图层
                provider.addFeatures([feature])

            # 将图层添加到项目中
            root = QgsProject.instance().layerTreeRoot()
            QgsProject.instance().addMapLayer(layer, True)

            # 设置图层的颜色，内部填充为红色
            fill_symbol = QgsFillSymbol.createSimple({'color': '255,0,0,0',
                                                      'outline_color': FillColor,
                                                      'outline_width': '0.8',
                                                      'style': 'solid'})  # alpha 值范围 0-255，0 完全透明，255 不透明
            renderer = QgsSingleSymbolRenderer(fill_symbol)
            layer.setRenderer(renderer)

            # 获取图层数量
            layers = QgsProject.instance().mapLayers()
            layer_count = len(layers)

            # 设置试图到第一个点
            target_point = QgsPointXY(center_lon1, center_lat1)
            iface.mapCanvas().setCenter(target_point)
            # 更改图层顺序
            self.insertable(layName)
            # 刷新地图视图
            iface.mapCanvas().refresh()
            # 关闭连接
            cursor.close()
            conn.close()

        except Exception as e:
            self.label_show.setText(e)

    def shenshaojunSetPT2(self,cgistr: str):
        cgi = self.txt_write.toPlainText()
        # query = ("SELECT gpscenterlng,gpscenterlat,gh,"
        #          "cnt_rsrp,cnt_rsrp110,sum_rsrp,"
        #          "CASE WHEN (cnt_rsrp110/cnt_rsrp) <0.8 THEN '弱覆盖'"
        #          "ELSE '正常' END AS fenlei,"
        #          "(sum_rsrp/cnt_rsrp-140) as rsrpavg"
        #          " from tb_2025年6月栅格shange汇聚 "
        #          " Where city='黔西南' and cnt_rsrp>=20")
        query = ("SELECT longitude,latitude,栅格id,总样本点 from tb_7月至9月全省45g全量栅格样本统计"
                 

                 )
        print(query)

        self.GetFeaturesPT2(query, "SSJ_SGwww", 'red')

    def GetFeaturesPT2(self, query: str, layName: str, FillColor: str):
        # PT 数据库文件路径
        try:
            conn = psycopg2.connect(database="postgres",
                                    user="postgres",
                                    password="ssj123",
                                    host="localhost",
                                    port="5432",
                                    options="-c statement_timeout=50000"  # 设置查询超时为5000毫秒（5秒）
                                    )

            print("Database opened successfully PT2")
            cursor = conn.cursor()
            cursor.execute(query)
            # 获取查询结果
            results = cursor.fetchall()

            if not results:  # 如果结果为空
                self.label_show.setText('没有查询到数据！')
                return
            else:
                self.label_show.setText('有数据！')
            # 矩形的尺寸（单位：米）
            length = 54  # 矩形的长度
            width = 54  # 矩形的宽度
            # 获取所有图层
            layers = QgsProject.instance().mapLayersByName(layName)
            # 如果找到图层，则删除它
            if layers:
                for layer in layers:
                    # 从项目中移除图层
                    QgsProject.instance().removeMapLayer(layer.id())
            # 创建一个新的矢量图层
            layer = QgsVectorLayer("Polygon?crs=EPSG:4326", layName, "memory")
            provider = layer.dataProvider()

            fields = QgsFields()  # 创建字段集合
            fields.append(QgsField("longitude", QVariant.Double))  # 添加字符串类型字段
            fields.append(QgsField("latitude", QVariant.Double))  # 添加字符串类型字段
            fields.append(QgsField("栅格id", QVariant.String))  # 添加字符串类型字段
            fields.append(QgsField("总样本点", QVariant.String))  # 添加字符串类型字段

            provider.addAttributes(fields)  # 将字段添加到数据提供者
            layer.updateFields()  # 更新图层的字段

            center_lon1 = 0
            center_lat1 = 0
            k = 1

            # 打印结果
            for row in results:
                center_lon = float(row[0])  # 经度 gpscenterlng
                center_lat = float(row[1])  # 纬度 gpscenterlat

                if k == 1:
                    center_lon1 = center_lon
                    center_lat1 = center_lat
                    # 小区名称显示
                    # self.txt_readline.setText(row[0])
                k = k + 1
                # 1度经度约为111320米，1度纬度约为110540米
                delta_lon = float(width / 111320)
                delta_lat = float(length / 110540)

                # 计算矩形的四个角点
                bottom_left = QgsPointXY(center_lon - delta_lon / 2, center_lat - delta_lat / 2)
                bottom_right = QgsPointXY(center_lon + delta_lon / 2, center_lat - delta_lat / 2)
                top_left = QgsPointXY(center_lon - delta_lon / 2, center_lat + delta_lat / 2)
                top_right = QgsPointXY(center_lon + delta_lon / 2, center_lat + delta_lat / 2)
                # 创建矩形的几何形状
                rect_geometry = QgsGeometry.fromPolygonXY(
                    [[bottom_left, bottom_right, top_right, top_left, bottom_left]])
                # 创建一个新特征并设置几何形状
                feature = QgsFeature(fields)  # 创建新特征
                feature["栅格id"] = row[2]
                feature["总样本点"] = row[3]

                feature.setGeometry(rect_geometry)

                # 将特征添加到图层
                provider.addFeatures([feature])

            # 将图层添加到项目中
            root = QgsProject.instance().layerTreeRoot()
            QgsProject.instance().addMapLayer(layer, True)

            # 设置图层的颜色，内部填充为红色
            fill_symbol = QgsFillSymbol.createSimple({'color': '255,0,0,0',
                                                      'outline_color': FillColor,
                                                      'outline_width': '0.8',
                                                      'style': 'solid'})  # alpha 值范围 0-255，0 完全透明，255 不透明
            renderer = QgsSingleSymbolRenderer(fill_symbol)
            layer.setRenderer(renderer)

            # 获取图层数量
            layers = QgsProject.instance().mapLayers()
            layer_count = len(layers)

            # 设置试图到第一个点
            target_point = QgsPointXY(center_lon1, center_lat1)
            iface.mapCanvas().setCenter(target_point)
            # 更改图层顺序
            self.insertable(layName)
            # 刷新地图视图
            iface.mapCanvas().refresh()
            # 关闭连接
            cursor.close()
            conn.close()

        except Exception as e:
            self.label_show.setText(e)

    @staticmethod
    def insertable(tbname: str):
        # 获取当前项目
        project = QgsProject.instance()
        tblist = project.mapLayers().values()
        countlayers = len(tblist)

        root = project.layerTreeRoot()
        # 获取表名称
        vl = QgsProject.instance().mapLayersByName(tbname)[0]

        myvl = root.findLayer(vl.id())

        myvlclone = myvl.clone()

        parent = myvl.parent()

        parent.insertChildNode(countlayers - 1, myvlclone)

        root.removeChildNode(myvl)

    @staticmethod
    def create_polygon_layer(layname: str):
        # 获取所有图层
        layers = QgsProject.instance().mapLayersByName(layname)

        # 如果找到图层，则删除它
        if layers:
            for layer in layers:
                # 从项目中移除图层
                QgsProject.instance().removeMapLayer(layer.id())

        # 创建一个新的矢量图层
        new_layer = QgsVectorLayer("Polygon?crs=EPSG:4326", layname, "memory")

        # 检查图层是否有效
        if not new_layer.isValid():
            print(f"图层 '{layname}' 创建失败！")
            return None

        # 将新图层添加到项目
        QgsProject.instance().addMapLayer(new_layer)

        # 返回新创建的图层
        return new_layer

    @staticmethod
    def create_line_layer(layname: str):
        # 获取所有图层
        layers = QgsProject.instance().mapLayersByName(layname)

        # 如果找到图层，则删除它
        if layers:
            for layer in layers:
                # 从项目中移除图层
                QgsProject.instance().removeMapLayer(layer.id())

        # 创建一个新的矢量图层
        new_layer = QgsVectorLayer("LineString?crs=EPSG:4326", layname, "memory")

        # 检查图层是否有效
        if not new_layer.isValid():
            print(f"图层 '{layname}' 创建失败！")
            return None

        # 将新图层添加到项目
        QgsProject.instance().addMapLayer(new_layer)

        # 返回新创建的图层
        return new_layer
