from qgis.utils import iface
from qgis.PyQt.QtCore import QSettings, QTranslator, QCoreApplication,Qt,QThreadPool
from qgis.core import QgsMessageLog, QgsInterval, QgsDateTimeRange, QgsTemporalNavigationObject, QgsVectorLayerSimpleLabeling, QgsWkbTypes, QgsPalLayerSettings, QgsTemplatedLineSymbolLayerBase, QgsMarkerSymbol, QgsSimpleMarkerSymbolLayer, QgsMarkerLineSymbolLayer, QgsRuleBasedRenderer, QgsLineSymbol, QgsClassificationQuantile, QgsGeometry, QgsVectorLayer, QgsFeature, QgsClassificationEqualInterval, QgsRendererRangeLabelFormat, QgsStyle, QgsGraduatedSymbolRenderer, QgsSingleSymbolRenderer, QgsSymbol, QgsFilledMarkerSymbolLayer, QgsSymbolLayer, QgsProperty, Qgis
from qgis.PyQt.QtWidgets import QShortcut,QListWidgetItem,QFileDialog,QStackedWidget,QListView,QLineEdit,QDialog,QTableWidgetItem
from qgis.PyQt.QtGui import QFont, QColor

from .db import *
from .workers import *
from .compat import *

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import psycopg2.extras
import traceback


def renderMapPlot(layer,data,cur,config,first_time_var=None,colorlabel=None,color_classes=None,colormode=None,colorramp=None,feature=None,varRotation=None,size_symbolMin=None,size_symbolMax=None,rotation_symbolMin=None,rotation_symbolMax=None,networkReportDlg=None):
    #print(data)
    if colorlabel:
        # Construct the expression for one decimal place formatting
        label_expression = f"format_number(\"{'color_' + data['color']['name'].split('$')[0]}\", 1)"

        # --- Label Settings ---
        palyr = QgsPalLayerSettings()
        palyr.enabled = True
        palyr.fieldName = label_expression 
        
        # Ensure QGIS knows this is an expression, not just a field name
        palyr.isExpression = True 
        
        # Configure placement using modern enums
        if layer.geometryType() == QgsWkbTypes.PointGeometry:
            palyr.placement = Qgis.LabelPlacement.OverPoint
        elif layer.geometryType() == QgsWkbTypes.LineGeometry:
            palyr.placement = Qgis.LabelPlacement.Line
        else: # Polygon
            palyr.placement = Qgis.LabelPlacement.AroundPoint
            # For polygons, sometimes 'centroid' placement is more reliable
            # palyr.placement = Qgis.LabelPlacement.PointOnSurface 

        # Customize text formatting (using points or millimeters is more reliable than map units)
        palyr.textColor = QColor(0, 0, 0)
        palyr.fontSizeInMapUnits = False # Use millimeters (False) or points (True and adjust unit)
        palyr.fontSize = 10 # 10 mm/points size
        palyr.fontFamily = "Arial"
        
        # Optional: Enable showing all labels, even colliding ones, for debugging
        palyr.limitLabelMapUnits = False
        palyr.scaleMax = 0
        palyr.scaleMin = 0
        palyr.displayAllLabels = True # Force display for troubleshooting


        # --- Apply Settings ---
        layer_settings = QgsVectorLayerSimpleLabeling(palyr)
        layer.setLabeling(layer_settings)
        layer.setLabelsEnabled(True)
        
        # --- Refresh Map Canvas ---
        # Trigger a repaint to force QGIS to re-render the labels immediately
        layer.triggerRepaint() 
        iface.mapCanvas().refresh()
        #print(f"Labels enabled for layer '{layer.name()}' using expression: {label_expression}")


    # ===== BUILD RENDERER (in main thread!) =====
    #print("Building renderer in main thread...")
    renderer = None

    if data['color']['mode']:
        # target field name used for graduated renderer
        target_field = 'color_' + data['color']['name'].split('$')[0]
        #print("Renderer target field:", target_field)
        if data['color']['name'].split('$')[0] == 'mdot' and feature=='line':
            # --- mdot: rule-based renderer with arrows (positive / negative)
            attr = "color_mdot"
            arrow_size = 3
            num_classes = int(color_classes)
            classification_mode = colormode
            color_ramp_name = colorramp

            style_mgr = QgsStyle().defaultStyle()
            ramp = style_mgr.colorRamp(color_ramp_name)
            if ramp is None:
                raise ValueError(f"Color ramp '{color_ramp_name}' not found in QGIS Style Manager")

            # compute vals (absolute) and bounds
            vals = [abs(f[attr]) for f in layer.getFeatures() if f[attr] is not None]
            if not vals:
                # fallback: set single-class renderer
                #print("No values for attr", attr, " -> single symbol")
                renderer = QgsSingleSymbolRenderer(QgsSymbol.defaultSymbol(layer.geometryType()))
            else:
                vmin, vmax = min(vals), max(vals)
                if vmin == vmax:
                    # degenerate case -> single class
                    bounds = [vmin, vmax]
                    num_classes = 1
                else:
                    if classification_mode == 'Equal Count':
                        percentiles = np.linspace(0, 100, num_classes + 1)
                        bounds = np.percentile(vals, percentiles).tolist()
                    else:
                        step = (vmax - vmin) / num_classes
                        bounds = [vmin + i * step for i in range(num_classes + 1)]

                root_rule = QgsRuleBasedRenderer.Rule(None)

                for i_cls in range(num_classes):
                    lower = bounds[i_cls]
                    upper = bounds[i_cls + 1]
                    frac = i_cls / (num_classes - 1) if num_classes > 1 else 0.0
                    color = ramp.color(frac)

                    # Positive flows
                    expr_pos = f'("{attr}" >= {lower} AND "{attr}" < {upper} AND "{attr}" > 0)'
                    line_sym_pos = QgsLineSymbol.createSimple({'color': color.name(), 'width': '0.7'})

                    marker_line_pos = QgsMarkerLineSymbolLayer()
                    # try to use newer API if available
                    if hasattr(marker_line_pos, "setPlacementType"):
                        marker_line_pos.setPlacementType(QgsTemplatedLineSymbolLayerBase.CentralPoint)
                    else:
                        marker_line_pos.setPlacement(QgsMarkerLineSymbolLayer.CentralPoint)
                    # rotate according to line direction
                    marker_line_pos.setDataDefinedProperty(QgsSymbolLayer.PropertyAngle, QgsProperty.fromValue(True))

                    arrow_layer_pos = QgsSimpleMarkerSymbolLayer(
                        shape=QgsSimpleMarkerSymbolLayer.Triangle,
                        color=color,
                        size=arrow_size
                    )
                    arrow_layer_pos.setAngle(90)  # rightward
                    arrow_marker_pos = QgsMarkerSymbol()
                    arrow_marker_pos.changeSymbolLayer(0, arrow_layer_pos)
                    marker_line_pos.setSubSymbol(arrow_marker_pos)
                    line_sym_pos.appendSymbolLayer(marker_line_pos)

                    rule_pos = QgsRuleBasedRenderer.Rule(line_sym_pos)
                    rule_pos.setFilterExpression(expr_pos)
                    rule_pos.setLabel(f"{lower:.2f} – {upper:.2f}")
                    root_rule.appendChild(rule_pos)

                    # Negative flows
                    expr_neg = f'("{attr}" >= -{upper} AND "{attr}" < -{lower} AND "{attr}" < 0)'
                    line_sym_neg = QgsLineSymbol.createSimple({'color': color.name(), 'width': '0.7'})

                    marker_line_neg = QgsMarkerLineSymbolLayer()
                    if hasattr(marker_line_neg, "setPlacementType"):
                        marker_line_neg.setPlacementType(QgsTemplatedLineSymbolLayerBase.CentralPoint)
                    else:
                        marker_line_neg.setPlacement(QgsMarkerLineSymbolLayer.CentralPoint)
                    marker_line_neg.setDataDefinedProperty(QgsSymbolLayer.PropertyAngle, QgsProperty.fromValue(True))

                    arrow_layer_neg = QgsSimpleMarkerSymbolLayer(
                        shape=QgsSimpleMarkerSymbolLayer.Triangle,
                        color=color,
                        size=arrow_size
                    )
                    arrow_layer_neg.setAngle(270)  # leftward
                    arrow_marker_neg = QgsMarkerSymbol()
                    arrow_marker_neg.changeSymbolLayer(0, arrow_layer_neg)
                    marker_line_neg.setSubSymbol(arrow_marker_neg)
                    line_sym_neg.appendSymbolLayer(marker_line_neg)

                    rule_neg = QgsRuleBasedRenderer.Rule(line_sym_neg)
                    rule_neg.setFilterExpression(expr_neg)
                    rule_neg.setLabel("")  # hidden
                    root_rule.appendChild(rule_neg)

                renderer = QgsRuleBasedRenderer(root_rule)

        else:
            # --- Graduated renderer branch ---
            if colormode == 'Equal Count':
                classification_method = QgsClassificationQuantile()
            else:
                classification_method = QgsClassificationEqualInterval()
            classification_method.setLabelPrecision(1)
            classification_method.setLabelTrimTrailingZeroes(True)

            default_style = QgsStyle().defaultStyle()
            color_ramp = default_style.colorRamp(colorramp)

            renderer = QgsGraduatedSymbolRenderer()
            renderer.setClassAttribute(target_field)
            renderer.setClassificationMethod(classification_method)
            renderer.updateClasses(layer, int(color_classes))
            renderer.updateColorRamp(color_ramp)
            symbol = QgsSymbol.defaultSymbol(layer.geometryType())
            if feature == 'line':
                symbol.setWidth(2)
            else:
                symbol.setSize(4)
            renderer.updateSymbols(symbol)

    else:
        # no color mode -> single symbol
        renderer = QgsSingleSymbolRenderer(QgsSymbol.defaultSymbol(layer.geometryType()))

    # ===== SIZE / ROTATION data-defined properties (apply to marker symbol) =====
    # Note: we need to modify a symbol for the renderer. For rule-based renderer, modify
    # top-level symbol in case of single-symbol rules; for graduated renderer we update symbols.
    try:
        if data['size']['mode'] or data['rotation']['mode']:
            #print("Applying size/rotation DDPs...")
            # create a base symbol to apply changes to
            base_symbol = QgsSymbol.defaultSymbol(layer.geometryType())
            style = {}
            if varRotation:
                style['name'] = 'arrow'
            else:
                style['name'] = 'point'
            style['color'] = 'black'
            symbolLayer = QgsFilledMarkerSymbolLayer.create(style)
            base_symbol.changeSymbolLayer(0, symbolLayer)

            # size scale
            if data['size']['mode']:
                if data['size']['mode'] == 'var':
                    min_value = getMinTimeTableValue(data['size']['var_function'], cur, config, data['size']['table_name'], data['size']['name'].split('$')[0], data['time']['starttime'], data['time']['endtime'])
                    max_value = getMaxTimeTableValue(data['size']['var_function'], cur, config, data['size']['table_name'], data['size']['name'].split('$')[0], data['time']['starttime'], data['time']['endtime'])
                else:
                    min_value = getMinTableValue(cur, config, data['size']['table_name'], data['size']['name'])
                    max_value = getMaxTableValue(cur, config, data['size']['table_name'], data['size']['name'])

                scale_expr = """coalesce(scale_exp("{}", {}, {}, {}, {}, 0.57), 0)""".format(
                    'size_' + data['size']['name'].split('$')[0],
                    min_value, max_value,
                    float(size_symbolMin), float(size_symbolMax)
                )
                # property for size or stroke width
                prop = QgsSymbolLayer.PropertyStrokeWidth if feature == 'line' else QgsSymbolLayer.PropertySize
                base_symbol.symbolLayer(0).setDataDefinedProperty(prop, QgsProperty.fromExpression(scale_expr))

            # rotation
            if data['rotation']['mode']:
                if data['rotation']['mode'] == 'var':
                    min_value = getMinTimeTableValue(data['rotation']['var_function'], cur, config, data['rotation']['table_name'], data['rotation']['name'].split('$')[0], data['time']['starttime'], data['time']['endtime'])
                    max_value = getMaxTimeTableValue(data['rotation']['var_function'], cur, config, data['rotation']['table_name'], data['rotation']['name'].split('$')[0], data['time']['starttime'], data['time']['endtime'])
                else:
                    min_value = getMinTableValue(cur, config, data['rotation']['table_name'], data['rotation']['name'])
                    max_value = getMaxTableValue(cur, config, data['rotation']['table_name'], data['rotation']['name'])

                rotation_expr = """coalesce(scale_exp("{}", {}, {}, {}, {}, 0.57), 0)""".format(
                    'rotation_' + data['rotation']['name'].split('$')[0],
                    min_value, max_value,
                    float(rotation_symbolMin), float(rotation_symbolMax)
                )
                base_symbol.symbolLayer(0).setDataDefinedProperty(QgsSymbolLayer.PropertyAngle, QgsProperty.fromExpression(rotation_expr))

            # apply the base symbol to renderer
            try:
                renderer.updateSymbols(base_symbol)
            except Exception:
                renderer.setSymbol(base_symbol)

    except:
        QgsMessageLog.logMessage(
            traceback.format_exc(),
            "Districts",
            MessageCritical
        )

    # ===== APPLY RENDERER (after labeling) =====
    try:
        #print("Applying renderer to layer...")
        layer.setRenderer(renderer)
    except:
        QgsMessageLog.logMessage(
            traceback.format_exc(),
            "Districts",
            MessageCritical
        )

    # ===== ADD TO PROJECT =====
    #print("Adding layer to project...")
    QgsProject.instance().addMapLayer(layer)    
    
    # ===== TEMPORAL CONTROLLER (if applicable) =====
    if first_time_var:
        #print("Configuring temporal controller...")
        # set temporal properties on the layer
        temp_prop = layer.temporalProperties()
        temp_prop.setIsActive(True)
        temp_prop.setStartField('time')
        temp_prop.setEndField('time')
        temp_prop.setLimitMode(Qgis.VectorTemporalLimitMode.IncludeBeginIncludeEnd)
        temp_prop.setMode(Qgis.VectorTemporalMode(2))

        temporalController = iface.mapCanvas().temporalController()
        temporalNavigationObject = sip.cast(temporalController, QgsTemporalNavigationObject)

        temporalNavigationObject.setNavigationMode(Qgis.TemporalNavigationMode.Animated)
        temporalNavigationObject.setFramesPerSecond(2)
        temporalNavigationObject.setTemporalExtents(
            QgsDateTimeRange(
                getDatetimeFromString(data['time']['starttime']),
                getDatetimeFromString(data['time']['endtime'])
            )
        )
        temporalNavigationObject.setLooping(True)
        temporalNavigationObject.setAnimationState(Qgis.AnimationState.Forward)

        interval = QgsInterval()
        dt = data['time']['dt']
        if dt == 'hour':
            interval.setHours(1)
        elif dt == 'day':
            interval.setDays(1)
        elif dt == 'month':
            interval.setMonths(1)
        else:
            try:
                interval.setHours(float(dt))
            except Exception:
                interval.setHours(1)

        temporalNavigationObject.setFrameDuration(interval)
        
    # ===== FINAL REFRESH =====
    layer.triggerRepaint()
    try:
        iface.layerTreeView().refreshLayerSymbology(layer.id())
    except:
        QgsMessageLog.logMessage(
            traceback.format_exc(),
            "Districts",
            MessageCritical
        )
    iface.mapCanvas().refresh()
    
def showOnMapMemoryLayer(vars,config,plugin_dir,feature,layer_name):   
    #print('showOnMapMemoryLayer')
    column_names=[vars[var]['name'].split('$')[0] for var in vars if var not in ['time','data'] and vars[var]['mode']]
    column_types=[var for var in vars if var not in ['time','data'] and vars[var]['mode']]
    #print(column_names)
    #print(column_types)
    
    # make new memory layer
    temp_layer = QgsVectorLayer("{}?crs=epsg:{}&field=id:integer&{}{}".format(
        'LineStringZ' if feature=='line' else 'PointZ',
        loadProjectConfig(config)['srid'],
        'field=time:datetime&' if vars['time']['first_time_var'] else '',
        '&'.join(['field={}:numeric'.format(type+'_'+name) for type,name in zip(column_types,column_names)])),layer_name, "memory")
    temp_layer.startEditing()
    #print('temp_layer generated')

    #make new features
    for i in vars['data']:
        feat = QgsFeature(temp_layer.fields())
        feat["id"] = i['fid']
        if vars['time']['first_time_var']:
            feat["time"] = str(i['time'])
        feat.setGeometry(QgsGeometry.fromWkt(i['geom']))
        for type,name in zip(column_types,column_names):
            feat[type+'_'+name]=float(i[type])
        temp_layer.addFeature(feat)
    #print('features added')
    
    return temp_layer

class WorkerShowOnMap(QRunnable):
    """Worker thread
    Inherits from QRunnable to handle worker thread setup, signals and wrap-up."""
    def __init__(self,*args,**kwargs):
        super().__init__()
        self.args=args
        #print('WorkerShowOnMap')
        #print(kwargs)
        self.signals=APISignals2()
        self.dlg_main=kwargs['dlg_main']
        self.config=kwargs['config']
        self.dlg=kwargs['dlg']
        if self.dlg:
            self.dlg.process_running=True
        self.vars=kwargs['vars']
        self.feature= kwargs['feature']
        self.layer_name= kwargs['layer_name']
        self.lineSegVisLength= kwargs['lineSegVis']
        self.enable= kwargs['enable']
        self.networkReportDlg= kwargs['networkReportDlg']
        self.conn=""
        self.cur=""
        self.plugin_dir=kwargs['plugin_dir']
        self.simData=kwargs['simData']
        self.time_values=['Values','Hourly average','Daily average','Monthly average']
        self.conn = dbConnect(self.config,True)
        self.vars=None
        if self.conn:
            self.cur=self.conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

    def getShowOnMapVarsDict(self):
        """{'color'/'size'/'rotation':{'mode':'var'/'par'/False, 'name': var_name/par_name, 'var_function': function_items, 'table_name': DB table},time:{starttime,endtime},data}"""
        vars={'color': {'mode':False, 'name': '', 'var_function': '', 'table_name': ''},
            'size': {'mode':False, 'name': '', 'var_function': '', 'table_name': ''},
            'rotation': {'mode':False, 'name': '', 'var_function': '', 'table_name': ''},
            'time': {'starttime': '', 'endtime':'', 'dt':False, 'first_time_var': ''},
            'data': ''}
            
        if self.dlg:
            if self.dlg.checkbox_varColor.isChecked():
                if self.dlg.rbtn_colorVar.isChecked():
                    vars['color']['mode']='var'
                    vars['color']['var_function']=self.dlg.color_function.currentText()
                    vars['time']['starttime']=self.dlg.starttime.text()
                    vars['time']['endtime']=self.dlg.endtime.text()
                    name=self.dlg.color_var.currentText()
                    vars['color']['table_name']=self.dlg.color_table_name
                else:
                    vars['color']['mode']='par'
                    name=self.dlg.color_par.currentText()
                    vars['color']['table_name']=self.dlg.feature+'s'
                if name:
                    vars['color']['name']=name
            if self.dlg.checkbox_varSize.isChecked():
                if self.dlg.rbtn_sizeVar.isChecked():
                    vars['size']['mode']='var'
                    vars['size']['var_function']=self.dlg.size_function.currentText()
                    vars['time']['starttime']=self.dlg.starttime.text()
                    vars['time']['endtime']=self.dlg.endtime.text()
                    name=self.dlg.size_var.currentText()
                    vars['size']['table_name']=self.dlg.size_table_name    
                else:
                    vars['size']['mode']='par'
                    name=self.dlg.size_par.currentText()
                    vars['size']['table_name']=self.dlg.feature+'s'
                if name:
                    vars['size']['name']=name
            if self.dlg.checkbox_varRotation.isChecked():
                if self.dlg.rbtn_rotationVar.isChecked():
                    vars['rotation']['mode']='var'
                    vars['rotation']['var_function']=self.dlg.rotation_function.currentText()
                    vars['time']['starttime']=self.dlg.starttime.text()
                    vars['time']['endtime']=self.dlg.endtime.text()
                    name=self.dlg.rotation_var.currentText()
                    vars['rotation']['table_name']=self.dlg.rotation_table_name
                else:
                    vars['rotation']['mode']='par'
                    name=self.dlg.rotation_par.currentText()
                    vars['rotation']['table_name']=self.dlg.feature+'s'
                if name:
                    vars['rotation']['name']=name
        else:
            #print('kwargs vars')
            vars=self.vars
            
        self.signals.progress.emit(5)  
        vars=self.getShowOnMapData(vars)
        return vars
    
    def getShowOnMapData(self,vars):
        #print(vars)
        #drop old line_seg_%_vis tables
        sql="""WITH tables_to_drop AS (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = '{}'
      AND table_name LIKE 'line_s_%_vis'
)
SELECT execute_dynamic_sql(format('DROP TABLE "{}".%I;', table_name))
    FROM tables_to_drop;""".format(self.config['versionName'],self.config['versionName']) # nosec B608
        self.cur.execute(sql)
        
        if vars['color']['var_function'] in self.time_values:
            time_color=True
        else:
            time_color=False
        if vars['size']['var_function'] in self.time_values:
            time_size=True
        else:
            time_size=False  
        if vars['rotation']['var_function'] in self.time_values:
            time_rotation=True
        else:
            time_rotation=False
            
        self.first_time_var='color' if time_color else ('size' if time_size else ('rotation' if time_rotation else False))
        #print('++first-time-var: '+str(self.first_time_var))
        if self.first_time_var:
            dt=getAvergageByMode(vars[self.first_time_var]['var_function'],self.cur,self.config,vars[self.first_time_var]['table_name']) #hours
        else:
            dt=False
        #print(dt)
        first_par_var='color' if vars['color']['mode'] else ('size' if vars['size']['mode'] else ('rotation' if vars['rotation']['mode'] else False))
        #print(self.first_time_var)
        #print(first_par_var)
        first_group=self.first_time_var if self.first_time_var else first_par_var
        
        #if simulation results and line feature
        if self.simData and self.feature=='line':
            #print('update DB with visualization tables')
            #re-create table line_seg_vis, which holds the geometry of the visualized pipe segements
            #check if > calculated segments
                
            srid=loadProjectConfig(self.config)['srid']
            
            #store cumulate simulation results in _vis table if lineSegVis!=0 and var
            if vars['color']['mode']=='var' and vars['color']['name'].split('$')[0] in ('temp','p') or vars['size']['mode']=='var' and vars['size']['name'].split('$')[0] in ('temp','p'):
                sql="""DROP TABLE IF EXISTS "{}".line_seg_vis;
CREATE TABLE IF NOT EXISTS "{}".line_seg_vis
(
    id serial,
    lid integer NOT NULL,
    lid_seg integer NOT NULL,
    geom geometry(LineStringZ,{})
);
SELECT segmentize({},'{}','line_seg_vis');""".format(self.config['versionName'],self.config['versionName'],srid,1000000 if vars['color']['var_function']=='specific dp' else self.lineSegVisLength,self.config['versionName']) # nosec B608
                #print(sql)
                self.cur.execute(sql)
            
                #load var data to the coresponding visualization table using averaged values for merged segments
                if vars['color']['mode']=='var' and vars['color']['var_function']!='specific dp' :
                    sql="""DROP TABLE IF EXISTS "{}".line_s_{}_vis CASCADE;
CREATE TABLE "{}".line_s_{}_vis
(
	id serial,
    fid integer,
    time timestamp,
	geom geometry(LineStringZ,{}),
	segment integer,
	"${}" numeric,
	CONSTRAINT line_s_{}_vis_pkey PRIMARY KEY (id)
);""".format(self.config['versionName'],vars['color']['name'],self.config['versionName'],vars['color']['name'],srid,vars['color']['name'].split('$')[0],vars['color']['name']) # nosec B608
                    
                    if vars['color']['name'].split('$')[0]=='p': #linear interpolation using pressure values at start and end of the line
                        #print('++++interpoation++++')
                        sql+="""\nINSERT INTO "{}".line_s_{}_vis (fid,time,"${}",geom,segment)
    SELECT s.fid,time,s.seg1_v+CASE WHEN seg_counter.count=1 THEN (s.seg2_v-s.seg1_v)/2 ELSE (s.seg2_v-s.seg1_v)/(seg_counter.count-1)*(seg.lid_seg-1) END AS value,seg.geom,seg.lid_seg
        FROM 
            (Select a.fid,a."$p" AS seg1_v,b."$p" AS seg2_v,a.time
                FROM (SELECT fid,"$p",time FROM "{}".line_s_{} WHERE segment =1 ORDER BY fid,time) a,
                    (SELECT fid,"$p",time FROM "{}".line_s_{} WHERE segment =2 ORDER BY fid,time) b
                WHERE a.fid=b.fid AND a.time=b.time
                ORDER BY a.fid,a.time) s, 
            "{}".line_seg_vis seg,
            (SELECT lid, count(*) AS count FROM "{}".line_seg_vis GROUP BY lid ORDER BY lid) seg_counter
        WHERE  seg_counter.lid=s.fid AND s.fid=seg.lid AND time BETWEEN '{}' AND '{}'
        ORDER BY s.fid,seg.lid_seg,time;""".format(self.config['versionName'],vars['color']['name'],vars['color']['name'].split('$')[0],self.config['versionName'],vars['color']['name'],self.config['versionName'],vars['color']['name'],self.config['versionName'],self.config['versionName'],vars['time']['starttime'],vars['time']['endtime'])  # nosec B608   
                    else:
                        sql+="""\nINSERT INTO "{}".line_s_{}_vis (fid,time,"${}",geom,segment)
    SELECT s.fid,time,avg(s."${}") AS value,seg.geom,seg.lid_seg
        FROM "{}".line_s_{} s, "{}".line_seg_vis seg
        WHERE ST_dwithin(ST_LineSubstring(seg.geom,0.01,0.99),s.geom,0.001) AND s.fid=seg.lid AND time BETWEEN '{}' AND '{}'
        GROUP BY time,s.fid,seg.geom,seg.lid_seg
        ORDER BY fid,lid_seg,time;""".format(self.config['versionName'],vars['color']['name'],vars['color']['name'].split('$')[0],vars['color']['name'].split('$')[0],self.config['versionName'],vars['color']['name'],self.config['versionName'],vars['time']['starttime'],vars['time']['endtime']) # nosec B608
                    sql+="""CREATE INDEX "idx_line_s_{}_vis_fid_time_segment" ON {}."line_s_{}_vis" (fid, time, segment);""".format(vars['color']['name'],self.config['versionName'],vars['color']['name']) # nosec B608
                    #print(sql)
                    self.cur.execute(sql)
                    
                    vars['color']['table_name']=vars['color']['table_name']+'_vis'
                
                if vars['size']['mode']=='var':
                    if vars['color']['name'] !=  vars['size']['name']: #if color and size car name is same --> do not twice
                        sql="""DROP TABLE IF EXISTS "{}".line_s_{}_vis CASCADE;
CREATE TABLE "{}".line_s_{}_vis
(
	id serial,
    fid integer,
    time timestamp,
	geom geometry(LineStringZ,{}),
	segment integer,
	"${}" numeric, -- use $ in order not to have a conflict the var column with oder columns
	CONSTRAINT line_s_{}_vis_pkey PRIMARY KEY (id)
);""".format(self.config['versionName'],vars['size']['name'],self.config['versionName'],vars['size']['name'],srid,vars['size']['name'].split('$')[0],vars['size']['name']) # nosec B608
                        sql+="""\nINSERT INTO "{}".line_s_{}_vis (fid,time,"${}",geom,segment)
    SELECT s.fid,time,avg(s."${}") AS value,seg.geom,seg.lid_seg
        FROM "{}".line_s_{} s, "{}".line_seg_vis seg
        WHERE ST_dwithin(ST_LineSubstring(seg.geom,0.01,0.99),s.geom,0.001) AND s.fid=seg.lid AND time BETWEEN '{}' AND '{}'
        GROUP BY time,s.fid,seg.geom,seg.lid_seg
        ORDER BY fid,lid_seg,time;""".format(self.config['versionName'],vars['size']['name'],vars['size']['name'].split('$')[0],vars['size']['name'].split('$')[0],self.config['versionName'],vars['size']['name'],self.config['versionName'],vars['time']['starttime'],vars['time']['endtime']) # nosec B608
                        sql+="""CREATE INDEX "idx_line_s_{}_vis_fid_time_segment" ON {}."line_s_{}_vis" (fid, time, segment);""".format(vars['size']['name'],self.config['versionName'],vars['size']['name']) # nosec B608
                        #print(sql)
                        self.cur.execute(sql)
                        
                    
                    vars['size']['table_name']=vars['size']['table_name']+'_vis'
                    
        self.signals.progress.emit(10)
        #print('get-data')
        if self.first_time_var or first_par_var:
            sql="""SELECT {}.{} AS fid, ST_AsText({}.geom) AS geom {}, {}
    FROM {}{}{}
    GROUP BY {}
    ORDER BY {} {}.{};""".format(first_group,'fid' if vars[first_group]['mode']=='var' else 'id',first_group, # nosec B608
                """,date_trunc('{}', {}.time) AS time""".format(dt,self.first_time_var) if self.first_time_var and dt in ['hour','day','month'] else (',{}.time'.format(self.first_time_var) if self.first_time_var else ''), # nosec B608
                ','.join([i for i in # nosec B608
                [('avg(color."${}") AS color'.format(vars['color']['name'].split('$')[0]) if dt else 'color."${}" AS color'.format(vars['color']['name'].split('$')[0])) if time_color else ('color.color' if vars['color']['mode']=='var' else ('color."{}"'.format(vars['color']['name']) +' AS color' if vars['color']['mode'] else '')), # nosec B608
                'avg(size."${}") AS size'.format(vars['size']['name'].split('$')[0]) if time_size else ('size.size' if vars['size']['mode']=='var' else ('size."{}"'.format(vars['size']['name']) +' AS size' if vars['size']['mode'] else '')), # nosec B608
                'avg(rotation."${}") AS rotation'.format(vars['rotation']['name'].split('$')[0]) if time_rotation else ('rotation.rotation' if vars['rotation']['mode']=='var' else ('rotation."{}"'.format(vars['rotation']['name']) +' AS rotation' if vars['rotation']['mode'] else '') )]  # nosec B608
                if i]), # nosec B608
                ','.join([i for i in  #from  # nosec B608
                    [""""{}".{} color""".format(self.config['versionName'],vars['color']['table_name']) if time_color else ( # nosec B608
                    """({}) color""".format(self.getNonTimeVarFunctionValue(vars['color'],'color',vars['time']))  # nosec B608
                    if vars['color']['mode']=='var' else ('"{}".{} AS color'.format(self.config['versionName'],vars['color']['table_name']) if vars['color']['mode']=='par' else '')), # nosec B608
                    """"{}".{} size""".format(self.config['versionName'],vars['size']['table_name']) if time_size else ( # nosec B608
                    """({}) size""".format(self.getNonTimeVarFunctionValue(vars['size'],'size',vars['time']))   # nosec B608
                    if vars['size']['mode']=='var'else ('"{}".{} AS size'.format(self.config['versionName'],vars['size']['table_name']) if vars['size']['mode']=='par' else '')), # nosec B608
                    """"{}".{} rotation""".format(self.config['versionName'],vars['rotation']['table_name']) if time_rotation else ( # nosec B608
                    """({}) rotation""".format(self.getNonTimeVarFunctionValue(vars['rotation'],'rotation',vars['time']))  # nosec B608
                    if vars['rotation']['mode']=='var' else ('"{}".{} AS rotation'.format(self.config['versionName'],vars['rotation']['table_name']) if vars['rotation']['mode']=='par' else ''))]  # nosec B608
                    if i]), # nosec B608
                '\n    WHERE ' if len([var for var in vars if var not in ['time','data']and vars[var]['mode']])>=2  or self.first_time_var else '', # nosec B608
                ' AND '.join([i for i in #where  # nosec B608
                    [' AND '.join([first_group+('.fid=' if vars[first_group]['mode']=='var' else '.id=')+var+('.fid' if vars[var]['mode']=='var' else '.id')  # nosec B608
                        for var in vars if var not in ['time','data']+[first_group] and vars[var]['mode']]), # nosec B608                      
                    ' AND '.join([first_group+'.geom='+var+'.geom'  # nosec B608
                        for var in vars if self.first_time_var and var not in ['time','data']+[self.first_time_var] and vars[var]['mode']=='var']),     # nosec B608                       
                    ' AND '.join([self.first_time_var+'.time='+var+'.time' for var in vars if var not in ['time','data']+[self.first_time_var] and vars[var]['var_function'] in self.time_values]), # nosec B608
                    """{}.time <= '{}' AND {}.time >= '{}'""".format(self.first_time_var,vars['time']['endtime'],self.first_time_var,vars['time']['starttime']) if self.first_time_var else ''] if i]), # nosec B608
                ','.join([i for i in #group by  # nosec B608
                    ["ST_asText({}.geom)".format(first_group), # nosec B608
                    """date_trunc('{}', {}.time)""".format(dt,self.first_time_var) if self.first_time_var and dt in ['hour','day','month'] else ('{}.time'.format(self.first_time_var) if self.first_time_var else ''), # nosec B608
                    '{}.{}'.format(first_group,'fid' if vars[first_group]['mode']=='var' else 'id'), # nosec B608
                    ','.join([var+'.'+(var if vars[var]['mode']=='var' and vars[var]['var_function']!='Values' else '"'+('$' if vars[var]['mode']=='var' else '')+vars[var]['name'].split('$')[0]+'"') for var in vars if var not in ['time','data'] and (vars[var]['mode'] and vars[var]['var_function'] not in self.time_values or vars[var]['var_function']=='Values')])] if i]), # nosec B608
                """date_trunc('{}', {}.time),""".format(dt,self.first_time_var) if self.first_time_var and dt in ['hour','day','month'] else ('{}.time,'.format(self.first_time_var) if self.first_time_var else ''), #order by  # nosec B608
                first_group,'fid' if vars[first_group]['mode']=='var' else 'id') # nosec B608
            #print(sql)
        self.cur.execute(sql)
        data=self.cur.fetchall()
        self.signals.progress.emit(60)  
        #get dt if not averaged
        if not dt and self.first_time_var:
            sql="""WITH sub AS(
    SELECT a.time-LAG(a.time, 1) OVER () as dt
        FROM (SELECT time FROM "{}".{} GROUP BY time) a
        GROUP BY time 
        ORDER BY time
)
SELECT EXTRACT(epoch FROM dt)/3600 AS dt FROM sub WHERE dt IS NOT NULL LIMIT 1;""".format(self.config['versionName'],vars[self.first_time_var]['table_name']) # nosec B608
            #print(sql)
            self.cur.execute(sql)
            dt=self.cur.fetchone()['dt']
            #print(dt)

        vars['data']=data
        vars['time']['dt']=dt
        vars['time']['first_time_var']=self.first_time_var
        return vars
    
    def getNonTimeVarFunctionValue(self,var,type,time):
        if var['var_function']=='Max':
            sql="""SELECT fid,geom AS geom,max("${}") AS {} FROM "{}".{} WHERE time BETWEEN '{}' AND '{}' GROUP BY fid,geom ORDER BY fid""".format(var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['starttime'],time['endtime']) # nosec B608
        elif var['var_function']=='Min':
            sql="""SELECT fid,geom AS geom,min("${}") AS {} FROM "{}".{} WHERE time BETWEEN '{}' AND '{}' GROUP BY fid,geom ORDER BY fid""".format(var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['starttime'],time['endtime']) # nosec B608
        elif var['var_function']=='Average':
            sql="""SELECT fid,geom AS geom,avg("${}") AS {} FROM "{}".{} WHERE time BETWEEN '{}' AND '{}' GROUP BY fid,geom ORDER BY fid""".format(var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['starttime'],time['endtime']) # nosec B608
        elif var['var_function']=='Last value':
            sql="""SELECT DISTINCT ON (fid{}) 
    fid,geom,"${}" AS {},time
FROM "{}".{}
WHERE time <= '{}'
ORDER BY fid{},time DESC""".format(',segment' if var['name'].split('$')[0] in ['p','temp'] and 'line_' in var['table_name'] else '' ,var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['endtime'],',segment' if var['name'].split('$')[0] in ['p','temp'] and 'line_' in var['table_name'] else '') # nosec B608
        elif var['var_function']=='Sum':
             sql="""SELECT fid,geom AS geom,sum("${}") AS {} FROM "{}".{} WHERE time BETWEEN '{}' AND '{}' GROUP BY fid,geom ORDER BY fid""".format(var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['starttime'],time['endtime']) # nosec B608
        elif var['var_function']=='First value':
            sql="""SELECT DISTINCT ON (fid{}) 
    fid,geom AS geom,"${}" AS {},time
FROM "{}".{}
WHERE time >= '{}'
ORDER BY fid{},time ASC;""".format(',segment' if var['name'].split('$')[0] in ['p','temp'] and 'line_' in var['table_name'] else '',var['name'].split('$')[0],type,self.config['versionName'],var['table_name'],time['starttime'],',segment' if var['name'].split('$')[0] in ['p','temp'] and 'line_' in var['table_name'] else '') # nosec B608
        elif var['var_function']=='specific dp':
            sql="""WITH length_per_fid AS (
    SELECT lid AS fid, st_length(geom) AS len, geom
    FROM {}.line_seg_vis
)
SELECT 
    a.fid,lp.geom,
    MAX(ABS(a."$p" - b."$p") / lp.len) AS {}
FROM {}.{} a
JOIN {}.{} b
    ON a.fid = b.fid
   AND a.time = b.time
   AND a.segment = 1
   AND b.segment = 2
JOIN length_per_fid lp
    ON lp.fid = a.fid
WHERE a.time between '{}' and '{}'
GROUP BY a.fid, lp.geom""".format( # nosec B608
                self.config['versionName'],type,self.config['versionName'],var['table_name'],self.config['versionName'],var['table_name'],time['starttime'],time['endtime']) # nosec B608
        #print(sql)
        return sql

            
    def showOnMap(self):
        self.vars=self.getShowOnMapVarsDict()      
        self.signals.progress.emit(65)  
        if not self.networkReportDlg:
            self.temp_layer = showOnMapMemoryLayer(self.vars,self.config,self.plugin_dir,self.feature,self.layer_name)
            self.signals.progress.emit(98)      
            self.signals.finished.emit('',self)
 
    @pyqtSlot()
    def run(self):
        #print('Show data on map')
        self.progress_value=1
        self.signals.progress.emit(self.progress_value)
        try:
            self.showOnMap()
            self.signals.progress.emit(100)  
            self.signals.finished.emit('Data has been successfully visualized on map!',self)  
        except Exception as e:
            self.signals.error.emit(str(e))  
            self.signals.progress.emit(0) 
            self.signals.finished.emit('Data visualization has failed!',self)  
