import time
import json
from qgis.core import QgsCoordinateReferenceSystem
from .HttpRequest import HttpRequest
from .SQLiteManager import SQLiteManager
from .PluginLogger import PluginLogger
from ..PluginHelper import PluginHelper


class WfsGet(object):
    """
    Classe implémentant une requête en HTTP GET pour une couche WFS.
    """

    def __init__(self, parameters) -> None:
        """
        Constructeur.

        :param parameters: les paramètres nécessaires pour lancer une requête HTTP GET
        :type parameters: dict
        """
        self.__logger = PluginLogger("WfsGet").getPluginLogger()
        self.context = None
        if PluginHelper.keyExist('context', parameters):
            self.context = parameters['context']
        if PluginHelper.keyExist('urlHostEspaceCo', parameters):
            self.urlHostEspaceCo = parameters['urlHostEspaceCo']
            self.url = parameters['urlHostEspaceCo'] + '/gcms/api/wfs'
        if PluginHelper.keyExist('proxies', parameters):
            self.proxies = parameters['proxies']
        if PluginHelper.keyExist('headers', parameters):
            self.headers = parameters['headers']
        if PluginHelper.keyExist('databasename', parameters):
            self.databasename = parameters['databasename']
        if PluginHelper.keyExist('layerName', parameters):
            self.layerName = parameters['layerName']
        if PluginHelper.keyExist('geometryName', parameters):
            self.geometryName = parameters['geometryName']
        if PluginHelper.keyExist('sridProject', parameters):
            self.sridProject = parameters['sridProject']
        if PluginHelper.keyExist('sridLayer', parameters):
            self.sridLayer = parameters['sridLayer']
        if PluginHelper.keyExist('bbox', parameters):
            self.bbox = parameters['bbox']
        self.parametersGcmsGet = {}
        if PluginHelper.keyExist('detruit', parameters):
            self.bDetruit = parameters['detruit']
        if PluginHelper.keyExist('isStandard', parameters):
            self.isStandard = parameters['isStandard']
        if PluginHelper.keyExist('is3D', parameters):
            self.is3D = parameters['is3D']
        if PluginHelper.keyExist('numrec', parameters):
            self.numrec = int(parameters['numrec'])
        # Paramètres pour insérer un objet dans une table SQLite
        self.parametersForInsertsInTable = {'tableName': self.layerName, 'geometryName': self.geometryName,
                                            'sridTarget': self.sridProject, 'sridSource': self.sridLayer,
                                            'isStandard': self.isStandard, 'is3D': self.is3D,
                                            'geometryType': ""}
        if PluginHelper.keyExist('databaseid', parameters):
            self.databaseid = parameters['databaseid']
        if PluginHelper.keyExist('tableid', parameters):
            self.tableid = parameters['tableid']

    def __initParametersGcmsGet(self, filterDelete=False) -> None:
        """
        Initialisation des paramètres pour une requête HTTP GET.

        :param filterDelete: à True, si la requête doit récupérer les objets détruits d'une couche BDUni
        :type filterDelete: bool
        """
        offset = 0
        maxFeatures = 10000  # Limite maximum d'objet de l'API 
        # Passage des paramètres pour l'url
        self.__setService()
        self.__setRequest()
        self.__setOutputFormat()
        self.__setTypeName()
        if self.numrec != 0:
            self.__setNumrec()
        if self.bbox is not None:
            self.__setBBox()
        if self.bDetruit or self.numrec != 0:
            self.__setFilter(filterDelete, self.numrec)
        self.__setOffset(offset)
        self.__setMaxFeatures(maxFeatures)
        self.__setVersion('1.0.0')

    def __makeRequestDeletedObjects(self) -> None:
        """
        Retrouver les objets détruits d'une table BDUni pour supprimer les enregistrements dans la base SQLite
        du projet utilisateur.
        """
        self.__initParametersGcmsGet(True)
        while True:
            response = HttpRequest.nextRequest(self.url, headers=self.headers, proxies=self.proxies,
                                               params=self.parametersGcmsGet)
            if response['status'] == 'error':
                break

            if len(response['features']) == 0 and response['stop']:
                break
            # Synchronisation (maj) de toutes les couches
            # ou un update après un post (enregistrement des couches actives)
            else:
                for feature in response['features']:
                    cleabs = SQLiteManager.selectColumnFromTableWithCondition("cleabs", self.layerName,
                                                                              "cleabs", feature['cleabs'])
                    if cleabs is None:
                        continue
                    else:
                        # si la cleabs est trouvée dans la base SQLite du client alors il faut supprimer
                        # l'enregistrement
                        SQLiteManager.deleteRowsInTableBDUni(self.layerName, [cleabs[0]])
            self.__setOffset(response['offset'])
            if response['stop']:
                break

    def gcmsGet(self, bExtraction=False) -> ():
        """
        Envoie une requête GET et met à jour les tables SQLite du projet en cours.
        NB : le dernier numéro de mise à jour (numRec) d'une table est fixé à 0 pour une table différente de la BDUni.

        :param bExtraction: à True quand il s'agit d'extraire les données sur une zone de travail
        :type bExtraction: bool

        :return: le dernier numéro de mise à jour sur une table et un message de fin
        """
        message = ""
        self.__initParametersGcmsGet()
        start = time.time()
        totalRows = 0
        requestCount = 0
        if self.isStandard:
            maxNumrec = 0
        else:
            # Try to get maxNumrec, but don't block extraction if it fails
            try:
                print("[INFO] Attempting to get maxNumrec for BDUni layer...")
                maxNumrec = self.getMaxNumrec()
                print("[INFO] MaxNumrec: {}".format(maxNumrec))
            except Exception as e:
                print("[WARNING] Failed to get maxNumrec (server error), defaulting to 0")
                print("[WARNING] Error: {}".format(str(e)))
                self.__logger.warning("getMaxNumrec failed: {}, continuing with 0".format(str(e)))
                maxNumrec = 0

        print("\n" + "="*80)
        print("[EXTRACTION START] Layer: {}".format(self.layerName))
        print("[EXTRACTION] Database ID: {}".format(self.databaseid if hasattr(self, 'databaseid') else 'N/A'))
        print("[EXTRACTION] Table ID: {}".format(self.tableid if hasattr(self, 'tableid') else 'N/A'))
        print("[EXTRACTION] Mode: {}".format("Extraction" if bExtraction else "Synchronisation"))
        print("[EXTRACTION] MaxFeatures per request: {}".format(self.parametersGcmsGet.get('maxFeatures', 'N/A')))
        print("="*80)
        self.__logger.info("Starting extraction for layer: {} (mode: {})".format(
            self.layerName, "Extraction" if bExtraction else "Synchronisation"))

        sqliteManager = SQLiteManager()
        MAX_RETRIES = 4  # Balanced for proxy issues
        RETRY_DELAY = 2  # Base seconds between retries
        proxy_failures = 0
        using_proxy = self.proxies is not None and len(self.proxies) > 0
        PROXY_FAILURE_THRESHOLD = 2  # Disable proxy after this many failures
        
        if using_proxy:
            print("[INFO] Proxy configured: {}".format(list(self.proxies.values())[0] if self.proxies else 'None'))
            print("[INFO] Proxy will be disabled after {} consecutive failures".format(PROXY_FAILURE_THRESHOLD))
        
        while True:
            requestCount += 1
            print("\n[REQUEST #{}] Fetching data from API...".format(requestCount))
            print("[REQUEST #{}] Offset: {}, MaxFeatures: {}".format(
                requestCount, 
                self.parametersGcmsGet.get('offset', 0),
                self.parametersGcmsGet.get('maxFeatures', 'N/A')))
            if using_proxy:
                print("[REQUEST #{}] Using proxy: {}".format(requestCount, list(self.proxies.keys()) if self.proxies else 'None'))
            
            # Retry logic with exponential backoff for transient errors
            response = None
            current_proxies = self.proxies
            
            # If proxy has failed too many times, bypass it from the start
            if proxy_failures >= PROXY_FAILURE_THRESHOLD and using_proxy:
                print("[REQUEST #{}] !!! PROXY DISABLED - {} previous failures, using DIRECT connection !!!".format(
                    requestCount, proxy_failures))
                current_proxies = None
                self.proxies = None
                using_proxy = False
            
            for attempt in range(1, MAX_RETRIES + 1):
                if attempt > 1:
                    # Progressive delays: 2s, 4s, 6s
                    delay = RETRY_DELAY * attempt
                    print("[REQUEST #{}] Retry attempt {}/{} after {} seconds...".format(
                        requestCount, attempt, MAX_RETRIES, delay))
                    time.sleep(delay)
                
                # Try without proxy after 2 failed proxy attempts
                if attempt == 3 and proxy_failures >= 2 and using_proxy:
                    print("[REQUEST #{}] !!! PROXY FAILING - Attempting direct connection (no proxy) !!!".format(requestCount))
                    current_proxies = None
                
                response = HttpRequest.nextRequest(self.url, headers=self.headers, proxies=current_proxies,
                                                   params=self.parametersGcmsGet)
                
                if response['status'] == 'ok':
                    if attempt > 1:
                        print("[REQUEST #{}] Success after {} attempts".format(requestCount, attempt))
                        if current_proxies is None and using_proxy:
                            print("[REQUEST #{}] !!! Success with DIRECT connection (proxy bypassed) !!!".format(requestCount))
                            # Continue without proxy for remaining requests
                            self.proxies = None
                            using_proxy = False
                    # Reset proxy failure counter on success
                    proxy_failures = 0
                    break
                else:
                    # Detailed error information
                    errorReason = response.get('reason', 'Unknown error')
                    errorUrl = response.get('url', 'N/A')
                    errorCode = response.get('code', 'N/A')
                    errorDetails = response.get('details', 'N/A')
                    is_proxy_error = response.get('is_proxy_error', False)
                    
                    if is_proxy_error:
                        proxy_failures += 1
                        print("[REQUEST #{}] PROXY ERROR detected (total proxy failures: {})".format(requestCount, proxy_failures))                        # Switch to direct connection immediately if threshold reached
                        if proxy_failures >= PROXY_FAILURE_THRESHOLD and using_proxy and current_proxies is not None:
                            print("[REQUEST #{}] !!! PROXY FAILURE THRESHOLD REACHED - Switching to DIRECT connection NOW !!!".format(requestCount))
                            current_proxies = None
                            # Don't count this as a retry attempt - try again immediately with direct connection
                            continue                    
                    print("[REQUEST #{}] Attempt {}/{} FAILED".format(requestCount, attempt, MAX_RETRIES))
                    print("  - Error type: {}".format('PROXY ERROR' if is_proxy_error else 'Network/Server error'))
                    print("  - Error reason: {}".format(errorReason[:200]))
                    print("  - Error code: {}".format(errorCode))
                    print("  - Details: {}".format(errorDetails))
                    
                    if attempt == MAX_RETRIES:
                        message += "[WfsGet.py::gcms_get::nextRequest] {0} : {1}".format(response['status'], errorReason[:200])
                        print("\n" + "!"*80)
                        print("[ERROR] Request PERMANENTLY FAILED after {} attempts".format(MAX_RETRIES))
                        print("[ERROR] Full error: {}".format(message[:300]))
                        if is_proxy_error:
                            print("[ERROR] PROXY CONNECTION ISSUE - Check proxy settings or network")
                            print("[ERROR] Consider disabling proxy or checking proxy server status")
                        else:
                            print("[ERROR] Server may be overloaded or rate-limiting requests")
                        print("[ERROR] Total features extracted before error: {}".format(totalRows))
                        print("!"*80 + "\n")
                        self.__logger.error("Request failed after {} retries: {}".format(MAX_RETRIES, message))
                        break
            
            # If all retries failed, exit the loop
            if response['status'] == 'error':
                return maxNumrec, message

            featuresReceived = len(response['features'])
            print("[REQUEST #{}] Received {} features".format(requestCount, featuresReceived))
            
            if featuresReceived == 0 and response['stop']:
                print("[REQUEST #{}] No more data to fetch".format(requestCount))
                break
            
            # si c'est une table standard (non BDUni) ou une extraction,
            # on insére tous les objets dans la base SQLite en appliquant un filtre avec la zone de travail active
            if self.isStandard in (True, 1) or bExtraction is True:
                # Appliquer le filtrage géométrique fin pour les extractions
                features_to_insert = response['features']
                if bExtraction is True:
                    print("[REQUEST #{}] Applying geometric filter...".format(requestCount))
                    features_to_insert = self.__filterFeaturesWithWorkArea(response['features'])
                    featuresAfterFilter = len(features_to_insert)
                    print("[REQUEST #{}] Features after filter: {} (filtered out: {})".format(
                        requestCount, featuresAfterFilter, featuresReceived - featuresAfterFilter))
                
                print("[REQUEST #{}] Inserting {} features into SQLite...".format(requestCount, len(features_to_insert)))
                insertedRows = sqliteManager.insertRowsInTable(self.parametersForInsertsInTable, features_to_insert)
                totalRows += insertedRows
                print("[REQUEST #{}] Inserted {} rows. Total: {}".format(requestCount, insertedRows, totalRows))
                
                # Memory cleanup - release references (Python GC will handle cleanup automatically)
                features_to_insert = None
                response['features'] = None
                print("[REQUEST #{}] Memory cleanup completed".format(requestCount))
                
            # sinon c'est une synchronisation (maj) de toutes les couches
            # ou un update après un post (enregistrement des couches actives)
            else:
                print("[REQUEST #{}] Processing BDUni synchronization...".format(requestCount))
                
                # Chargement des cleabs en mémoire 
                if requestCount == 1:
                    print("[REQUEST #{}] Loading existing cleabs into memory for fast lookup...".format(requestCount))
                    existingCleabs = set()
                    try:
                        sql = "SELECT cleabs FROM {} WHERE cleabs IS NOT NULL".format(SQLiteManager._quote_identifier(self.layerName)) #nosec B608
                        connection = SQLiteManager.sqlite3Connect()
                        cursor = connection.cursor()
                        cursor.execute(sql)
                        existingCleabs = {row[0] for row in cursor.fetchall()}
                        cursor.close()
                        connection.close()
                        print("[REQUEST #{}] Loaded {} existing cleabs for fast lookup".format(requestCount, len(existingCleabs)))
                    except Exception as e:
                        print("[REQUEST #{}] Warning: Could not preload cleabs: {}".format(requestCount, e))
                        existingCleabs = set()
                elif 'existingCleabs' not in locals():
                    existingCleabs = set()
                
                processedInBatch = 0
                featuresToInsert = []
                cleabsToDelete = []
                # Traiter les features reçues en batch pour minimiser les opérations SQL
                for feature in response['features']:
                    featureCleabs = feature.get('cleabs')
                    
                    
                    if featureCleabs in existingCleabs:
                        # modification d'un objet - marquer pour suppression puis réinsertion
                        cleabsToDelete.append(featureCleabs)
                        featuresToInsert.append(feature)
                    else:
                        # création d'un nouvel objet
                        featuresToInsert.append(feature)
                        existingCleabs.add(featureCleabs)  # Ajouter au cache
                    
                    processedInBatch += 1
                
                # Suppression en batch des cleabs à mettre à jour
                if cleabsToDelete:
                    print("[REQUEST #{}] Deleting {} existing features for update...".format(requestCount, len(cleabsToDelete)))
                    SQLiteManager.deleteRowsInTableBDUni(self.layerName, cleabsToDelete)
                
                # Insertion en batch de toutes les features
                if featuresToInsert:
                    print("[REQUEST #{}] Inserting {} features...".format(requestCount, len(featuresToInsert)))
                    insertedRows = sqliteManager.insertRowsInTable(self.parametersForInsertsInTable, featuresToInsert)
                    totalRows += insertedRows
                
                print("[REQUEST #{}] Processed {} BDUni features. Total: {}".format(requestCount, processedInBatch, totalRows))
                
               
                response['features'] = None
                
            self.__setOffset(response['offset'])
            if response['stop']:
                print("[REQUEST #{}] Extraction complete flag received".format(requestCount))
                break
            else:
                # Small delay between requests to avoid overwhelming the server
                if requestCount % 10 == 0:  # Every 10 requests
                    print("[REQUEST #{}] Pausing 1 second to avoid server overload...".format(requestCount))
                    time.sleep(1)
        # suppression des objets pour une table BDUni et différent d'une extraction
        if self.isStandard in (False, 0) and not bExtraction:
            print("\n[CLEANUP] Processing deleted objects for BDUni...")
            self.__makeRequestDeletedObjects()
            print("[CLEANUP] Deleted objects processed")
        
        # nettoyage de la base SQLite
        print("\n[CLEANUP] Running SQLite VACUUM to optimize database...")
        SQLiteManager.vacuumDatabase()
        print("[CLEANUP] Database optimization completed")
        
        end = time.time()
        timeResult = end - start
        
        # Detailed completion statistics
        print("\n" + "="*80)
        print("[EXTRACTION COMPLETE] Layer: {}".format(self.layerName))
        print("[STATISTICS] Total features extracted: {}".format(totalRows))
        print("[STATISTICS] Total API requests: {}".format(requestCount))
        print("[STATISTICS] Average features per request: {}".format(
            round(totalRows / requestCount, 1) if requestCount > 0 else 0))
        print("[STATISTICS] Total time: {} seconds ({} minutes)".format(
            round(timeResult, 1), round(timeResult / 60, 2)))
        if totalRows > 0:
            print("[STATISTICS] Processing speed: {} features/second".format(
                round(totalRows / timeResult, 1) if timeResult > 0 else 0))
        print("="*80 + "\n")
        
        self.__logger.info("Extraction completed: {} features in {} seconds ({} requests)".format(
            totalRows, round(timeResult, 1), requestCount))
        
        if timeResult > 60:
            if message == '':
                message = "{0} objet(s), extrait(s) en : {1} minute(s)".format(totalRows, round(timeResult / 60, 1))
        else:
            if totalRows == 0:
                message += "Pas d'objets extraits"
            else:
                if message == '':
                    message = "{0} objet(s), extrait(s) en : {1} seconde(s)".format(totalRows, round(timeResult, 1))
        return maxNumrec, message

    def __filterFeaturesWithWorkArea(self, features) -> list:
        """
        Filtrer les objets en cherchant ceux qui intersectent la zone de travail.
        Applique un filtre géométrique fin après le filtre BBox de la requête HTTP.

        :param features: les objets retournés par la requête
        :type features: list
        :return: la liste des objets intersectant la géométrie réelle de la zone de travail
        """
        # Si pas de contexte, on ne peut pas filtrer
        if self.context is None:
            return features

        target_crs = QgsCoordinateReferenceSystem.fromEpsgId(self.sridLayer)
        return PluginHelper.filterWithWorkArea(self.context, features, geometryKey=self.geometryName,
                                               targetCrs=target_crs)

    def getMaxNumrec(self) -> int:
        """
        Requête pour récupérer le dernier numéro de réconciliation (NumRec) d'une table BDUni. Chaque mise à jour
        d'une table BDUni se fait par l'intermédiaire d'une surface qui entoure les objets mis à jour appelée
        réconciliation. Chaque réconciliation porte un numéro de séquence, le NumRec.

        :return: le numéro de la mise à jour.
        """
        url = "{0}/gcms/api/databases/{1}/tables/{2}/max-numrec".format(self.urlHostEspaceCo, self.databaseid,
                                                                        self.tableid)
        response = HttpRequest.makeHttpRequest(url, proxies=self.proxies, headers=self.headers,
                                               launchBy='getMaxNumrec : {}'.format(self.layerName))
        
        # Succès : get (code 200) post (code 201)
        if response.status_code == 200 or response.status_code == 201:
            numrec = response.json()
        else:
            message = "code : {} raison : {} response_text: {}".format(response.status_code, response.reason, response.text)
            print("[ERROR] getMaxNumrec failed: {}".format(message))
            self.__logger.error("getMaxNumrec failed: {}".format(message))
            raise Exception("WfsGet.getMaxNumrec -> {}".format(message))
        return numrec

    def __setService(self) -> None:
        """Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'service'."""
        self.parametersGcmsGet['service'] = 'WFS'

    def __setVersion(self, version) -> None:
        """
        Fixe le numéro de version d'une requête GET en complètant le dictionnaire des paramètres avec l'item 'version'.

        :param version: version du protocole HTTP
        :type version: str
        """
        self.parametersGcmsGet['version'] = version

    def __setRequest(self) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'request' en fixant le type
        à 'GetFeature'.
        """
        self.parametersGcmsGet['request'] = 'GetFeature'

    def __setOutputFormat(self, outputFormat='JSON') -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'outputFormat' en fixant le format
        de retour pour la réponse.

        :param outputFormat: type de réponse attendue, par défaut JSON. Autres formats GeoJSON | CSV | GML
        :type outputFormat: str
        """
        self.parametersGcmsGet['outputFormat'] = outputFormat

    def __setTypeName(self) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'typename'.
        """
        typename = "{0}:{1}".format(self.databasename, self.layerName)
        self.parametersGcmsGet['typename'] = typename

    def __setNumrec(self) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'numrec'.
        """
        self.parametersGcmsGet['gcms_numrec'] = self.numrec

    def __setFilter(self, _filter, numrec) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'filter'.
        Le filtre doit être du genre :
        filter={"$and":[{"class_adm":"Autoroute","longueur":"$gte":10}]}

        :param _filter: à True, permet de récupérer les objets détruits dans une table
        :type _filter: bool

        :param numrec: le dernier numéro de réconciliation de la table, il sert au filtrage des objets
                        à 0 pour une couche standard
        :type numrec: int
        """
        filters = {}
        print( "[DEBUG] Setting filter for GET request: filterDelete={}, numrec={}".format(_filter, numrec))
        # 1) Filtre 'detruit' si applicable
        # bDetruit indique si la couche est standard ou BDUni
        if self.bDetruit:
            filters['detruit'] = _filter

        # 2) Filtre 'numrec' si applicable
        if numrec != 0:
            filters['gcms_numrec'] = {"$gte": numrec}
            # clauses.append({"numrec": {"$gte": numrec}})

        # 3) Construction du JSON final selon le nombre de filtres
        if len(filters) == 1:
            # Un seul filtre → on envoie l'objet seul
            loadFilters = filters
        else:
            # Deux filtres (ou plus) → on met tout dans un $and
            # On crée une liste de dictionnaires individuels pour chaque condition
            conditions = [{key: value} for key, value in filters.items()]
            loadFilters = {"$and": conditions}

        self.parametersGcmsGet["filter"] = json.dumps(loadFilters)

    def __setBBox(self) -> None:
        """Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'bbox'."""
        self.parametersGcmsGet['bbox'] = self.bbox.boxToStringWithSrid(self.sridProject, self.sridLayer)

    def __setOffset(self, offset) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'offset'

        :param offset: nombre de pages que retourne une requête, initialisé à 0 pour la première.
        :type offset: int
        """
        self.parametersGcmsGet['offset'] = offset

    def __setMaxFeatures(self, maxFeatures) -> None:
        """
        Complète le dictionnaire des paramètres en vue d'une requête GET par l'item 'maxFeatures'.

        :param maxFeatures: nombre d'objets maximum que retourne une réponse du serveur
        :type maxFeatures: int
        """
        self.parametersGcmsGet['maxFeatures'] = maxFeatures

