# -*- coding: utf-8 -*-
"""
/***************************************************************************
 cyanloveDockWidget
                                 A QGIS plugin
 申少军的工具箱
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                             -------------------
        begin                : 2024-10-19
        git sha              : $Format:%H$
        copyright            : (C) 2024 by cyan
        email                : shenshaojun@139.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""

import sqlite3
import re
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_dockwidget_base.ui'))


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

    def __init__(self, parent=None):

        super(cyanloveDockWidget, self).__init__(parent)
        self.setupUi(self)
        self.setFloating(True)
        self.tbname_wanggeshow = "tb_3月栅格cell2025"
        self.setWindowFlags(QtCore.Qt.Dialog)  # 使对话框始终在前
        self.btn_neight5g.clicked.connect(self.GetNeightCell)
        self.btn_neight4g.clicked.connect(self.GetNeightCell)

    def closeEvent(self, event):
        self.closingPlugin.emit()
        print("dock")
        event.accept()

    def _validate_cgi(self, cgi_str):
        """验证 CGI 输入，只允许字母数字和短横线，并返回 (nodeb, cell)"""
        if not cgi_str:
            return False, None, None
        # 允许字母数字和短横线，格式如 "ABC123-456"
        if not re.match(r'^[A-Za-z0-9]+-[A-Za-z0-9]+$', cgi_str):
            return False, None, None
        parts = cgi_str.split('-')
        return True, parts[0], parts[1]

    def GetNeightCell(self):
        sender = self.sender()

        cgistr = self.txt_write.toPlainText().strip()
        valid, nodeb_id, cell_id = self._validate_cgi(cgistr)
        if not valid:
            self.label_2.setText('cgi填写错误！请使用格式: NodeBID-CellID (仅字母数字)')
            return

        try:
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()

            # 根据不同的按钮，使用完全静态的 SQL 语句（表名、列名全部硬编码）
            if sender == self.btn_neight4g:
                # 创建索引（静态 SQL）
                cursor.execute("CREATE INDEX IF NOT EXISTS index_tbcell ON [TB_Cell4g] ([LTE基站标识], [LTE小区标识])")
                cursor.execute("CREATE INDEX IF NOT EXISTS index_neight ON [TB_Neighbor4g] ([Scell_NodeB], [Scell_CellID])")

                # 主查询（参数化，无任何字符串拼接）
                query = """
                    SELECT A.CellType, A.NodeBSet, CellIDSet, B.[LTE小区名称], B.边界P 
                    FROM (
                        SELECT 'Scell' AS CellType, ? AS NodeBSet, ? as CellIDSet           
                        UNION ALL 
                        SELECT 'Ncell' AS CellType, Ncell_NodeB AS NodeBSet, Ncell_CellID as CellIDSet  
                        FROM [TB_Neighbor4g] 
                        WHERE Scell_NodeB = ? AND Scell_CellID = ?
                    ) A 
                    INNER JOIN [TB_Cell4g] B 
                    ON A.NodeBSet = B."LTE基站标识" AND A.CellIDSet = B."LTE小区标识"
                """
                cursor.execute(query, (nodeb_id, cell_id, nodeb_id, cell_id))

            elif sender == self.btn_neight5g:
                cursor.execute("CREATE INDEX IF NOT EXISTS index_tbcell ON [TB_Cell5g] ([NR基站标识], [NR小区标识])")
                cursor.execute("CREATE INDEX IF NOT EXISTS index_neight ON [TB_Neighbor5g] ([Scell_NodeB], [Scell_CellID])")

                query = """
                    SELECT A.CellType, A.NodeBSet, CellIDSet, B.[NR小区名称], B.边界P 
                    FROM (
                        SELECT 'Scell' AS CellType, ? AS NodeBSet, ? as CellIDSet           
                        UNION ALL 
                        SELECT 'Ncell' AS CellType, Ncell_NodeB AS NodeBSet, Ncell_CellID as CellIDSet  
                        FROM [TB_Neighbor5g] 
                        WHERE Scell_NodeB = ? AND Scell_CellID = ?
                    ) A 
                    INNER JOIN [TB_Cell5g] B 
                    ON A.NodeBSet = B."NR基站标识" AND A.CellIDSet = B."NR小区标识"
                """
                cursor.execute(query, (nodeb_id, cell_id, nodeb_id, cell_id))

            else:
                self.label_2.setText('无效的操作！')
                cursor.close()
                conn.close()
                return

            results = cursor.fetchall()

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

            # 以下为地图渲染部分，与 SQL 注入无关，保持原样
            canvas = iface.mapCanvas()
            map_extent = canvas.extent()

            layer = self.create_polygon_layer('SSJ_SG3')
            linelayer = self.create_line_layer('SSJ_SG3_line')
            if not layer.isValid():
                self.label_2.setText('图层无效，无法创建！')
                cursor.close()
                conn.close()
                return

            provider = layer.dataProvider()
            providerline = linelayer.dataProvider()
            provider.addAttributes([
                QgsField('name', QVariant.String),
                QgsField('cginame', QVariant.String)
            ])
            layer.updateFields()

            first_feature_geom = None
            for row in results:
                sid = row[0]
                geomset = row[4]
                rect_geometry = QgsGeometry.fromWkt(geomset)
                feature = QgsFeature()
                feature.setGeometry(rect_geometry)
                feature.setAttributes([sid, '111'])
                provider.addFeatures([feature])

                if first_feature_geom is None:
                    first_feature_geom = rect_geometry
                    self.txt_readline.setPlainText(row[3])  # 主小区名称
                else:
                    if not map_extent.intersects(rect_geometry.boundingBox()):
                        points = [
                            QgsPointXY(first_feature_geom.centroid().asPoint()),
                            QgsPointXY(rect_geometry.centroid().asPoint())
                        ]
                        line_geom = QgsGeometry.fromPolylineXY(points)
                        line_feature = QgsFeature()
                        line_feature.setGeometry(line_geom)
                        providerline.addFeatures([line_feature])

            # 渲染分类
            categoryA = QgsRendererCategory('Scell', QgsFillSymbol.createSimple(
                {'color': 'red', 'outline_color': 'black', 'outline_width': '0.5'}), 'Scell')
            categoryB = QgsRendererCategory('Ncell', QgsFillSymbol.createSimple(
                {'color': 'blue', 'outline_color': 'black', 'outline_width': '0.5'}), 'Ncell')
            renderer = QgsCategorizedSymbolRenderer('name', [categoryA, categoryB])
            layer.setRenderer(renderer)

            QgsProject.instance().addMapLayer(layer, True)
            QgsProject.instance().addMapLayer(linelayer, True)
            self.label_2.setText('图层添加成功！')

            if first_feature_geom:
                center_point = first_feature_geom.centroid().asPoint()
                canvas.setCenter(center_point)

            iface.mapCanvas().refresh()
            cursor.close()
            conn.close()

        except Exception as e:
            self.label_2.setText(f"错误: {str(e)}")
            print(f"Error: {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