import psycopg2
import re
import configparser
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)

    @staticmethod
    def _is_safe_identifier(name: str) -> bool:
        """验证表名/列名：只允许字母、数字、下划线，且不以数字开头"""
        return bool(re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', name))

    def get_wangge_show(self):
        cgi = self.txt_write.toPlainText().strip()
        if cgi == "":
            self.label_show.setText('没有填写cgi！')
            return
        # 限制 CGI 格式（仅允许字母数字、下划线、短横线）
        if not re.match(r'^[A-Za-z0-9_\-]+$', cgi):
            self.label_show.setText('cgi包含非法字符！')
            return
        self.shenshaojunSetPT(cgi)

    def shenshaojunSetPT(self, cgistr: str):
        # 直接硬编码表名，消除动态拼接（表名固定，安全）
        query = """
            SELECT cgi, cgi_name, gh, gpscenterlng, gpscenterlat, cnt_rsrp, cnt_rsrp110
            FROM tb_2025年4月栅格cell2025
            WHERE cgi = %s
            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', (cgistr,))

        query2 = """
            SELECT cgi, cgi_name, gh, gpscenterlng, gpscenterlat, cnt_rsrp, cnt_rsrp110
            FROM tb_2025年4月栅格cell2025
            WHERE cgi = %s
            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(query2, "SSJ_SG2", 'chartreuse', (cgistr,))

    def GetFeaturesPT(self, query: str, layName: str, FillColor: str, params: tuple):
        try:
            # 读取配置文件
            config = configparser.ConfigParser()
            config.read('config.ini', encoding='utf-8')
            # 所有值都是字符串
            pg_conf = dict(config.items('PostgreSQL'))

            # 单独构建连接参数字典，手动转换类型
            connect_kwargs = {
                'database': pg_conf['database'],
                'user': pg_conf['user'],
                'password': pg_conf['password'],
                'host': pg_conf['host'],
                'port': int(pg_conf['port']),  # 转换为 int
                'options': pg_conf.get('options')  # 可能没有此字段
            }
            # 移除值为 None 的项
            connect_kwargs = {k: v for k, v in connect_kwargs.items() if v is not None}

            conn = psycopg2.connect(**connect_kwargs)

            print("Database opened successfully")
            cursor = conn.cursor()
            cursor.execute(query, params)  # 参数化查询
            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)
            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 = center_lat1 = 0
            k = 1

            for row in results:
                center_lon = float(row[3])
                center_lat = float(row[4])
                if k == 1:
                    center_lon1, center_lat1 = center_lon, center_lat
                k += 1

                delta_lon = width / 111320.0
                delta_lat = length / 110540.0

                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_geom = 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_geom)
                provider.addFeatures([feature])

            QgsProject.instance().addMapLayer(layer, True)
            fill_symbol = QgsFillSymbol.createSimple({
                'color': '255,0,0,0',
                'outline_color': FillColor,
                'outline_width': '0.8',
                'style': 'solid'
            })
            layer.setRenderer(QgsSingleSymbolRenderer(fill_symbol))

            if k > 1:
                iface.mapCanvas().setCenter(QgsPointXY(center_lon1, center_lat1))
            self.insertable(layName)
            iface.mapCanvas().refresh()

            cursor.close()
            conn.close()

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

    def shenshaojunSetPT2(self, cgistr: str):
        # 无用户输入，直接硬编码表名
        query = """
            SELECT longitude, latitude, 栅格id, 总样本点
            FROM tb_7月至9月全省45g全量栅格样本统计
        """
        self.GetFeaturesPT2(query, "SSJ_SGwww", 'red', ())

    def GetFeaturesPT2(self, query: str, layName: str, FillColor: str, params: tuple):
        try:
            # 读取配置文件
            config = configparser.ConfigParser()
            config.read('config.ini', encoding='utf-8')
            # 所有值都是字符串
            pg_conf = dict(config.items('PostgreSQL'))

            # 单独构建连接参数字典，手动转换类型
            connect_kwargs = {
                'database': pg_conf['database'],
                'user': pg_conf['user'],
                'password': pg_conf['password'],
                'host': pg_conf['host'],
                'port': int(pg_conf['port']),  # 转换为 int
                'options': pg_conf.get('options')  # 可能没有此字段
            }
            # 移除值为 None 的项
            connect_kwargs = {k: v for k, v in connect_kwargs.items() if v is not None}

            conn = psycopg2.connect(**connect_kwargs)
            print("Database opened successfully")
            cursor = conn.cursor()
            cursor.execute(query, params)
            results = cursor.fetchall()

            if not results:
                self.label_show.setText('没有查询到数据！')
                return
            else:
                self.label_show.setText('有数据！')

            length = width = 54

            layers = QgsProject.instance().mapLayersByName(layName)
            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 = center_lat1 = 0
            k = 1

            for row in results:
                center_lon = float(row[0])
                center_lat = float(row[1])
                if k == 1:
                    center_lon1, center_lat1 = center_lon, center_lat
                k += 1

                delta_lon = width / 111320.0
                delta_lat = length / 110540.0

                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_geom = 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_geom)
                provider.addFeatures([feature])

            QgsProject.instance().addMapLayer(layer, True)
            fill_symbol = QgsFillSymbol.createSimple({
                'color': '255,0,0,0',
                'outline_color': FillColor,
                'outline_width': '0.8',
                'style': 'solid'
            })
            layer.setRenderer(QgsSingleSymbolRenderer(fill_symbol))

            if k > 1:
                iface.mapCanvas().setCenter(QgsPointXY(center_lon1, center_lat1))
            self.insertable(layName)
            iface.mapCanvas().refresh()

            cursor.close()
            conn.close()

        except Exception as e:
            self.label_show.setText(str(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