# -*- coding: utf-8 -*-
"""
Excel to Geometry (merge Sample + Replacement, semicolon multi-file version, 2025-10-14)

• Accepts multiple Excel files separated by semicolons (;)
• Has Browse button for file selection
• Reads “Sample SSU” or fallback “AONCR-SAMPLE HH” + optional “Replacement SSU” sheets
• Detects WKT column flexibly (ignores spaces/case)
• Converts single-point MULTIPOINT to POINT
• Splits multi-point MULTIPOINTs into separate POINT features
• Adds field “source_data”: 1 = Sample/Selected, 2 = Replacement
• Output layer contains only POINT geometries
• Compatible with QGIS 3.22 – 3.36
"""

from qgis.PyQt.QtCore import QCoreApplication, QVariant
from qgis.core import (
    QgsProcessing, QgsProcessingAlgorithm, QgsProcessingException,
    QgsProcessingParameterFile, QgsProcessingParameterCrs,
    QgsProcessingParameterFeatureSink, QgsFeatureSink,
    QgsVectorLayer, QgsFeature, QgsGeometry, QgsPointXY, QgsWkbTypes, QgsField
)
import os, re


class ExcelToGeometry(QgsProcessingAlgorithm):
    INPUT, CRS, OUTPUT = 'EXCEL', 'CRS', 'OUTPUT'

    def tr(self, msg): 
        return QCoreApplication.translate('ExcelToGeometry', msg)

    def name(self): 
        return 'excel_to_geometry'

    def displayName(self): 
        return self.tr('Excel to Geometry')

    def group(self): 
        return ''

    def groupId(self): 
        return ''

    def createInstance(self): 
        return ExcelToGeometry()

    def shortHelpString(self):
        return self.tr(
            'Select one Excel file using the Browse button, then you may manually append more file paths '
            'separated by semicolons (;) in the text box.\n\n'
            'Each file may contain “Sample SSU” or fallback “AONCR-SAMPLE HH”, '
            'and optionally “Replacement SSU” sheets. Automatically detects '
            'the WKT column (case-insensitive, ignores spaces). Splits MULTIPOINTs '
            'into POINT geometries. Adds field “source_data”: 1 = Sample/Selected, 2 = Replacement.'
        )

    # ---------- parameters ----------
    def initAlgorithm(self, config=None):
        # Use File type to keep browse button visible
        self.addParameter(
            QgsProcessingParameterFile(
                self.INPUT,
                self.tr('Excel file(s) (semicolon-separated)'),
                behavior=QgsProcessingParameterFile.File,
                extension='xlsx'
            )
        )
        self.addParameter(
            QgsProcessingParameterCrs(
                self.CRS,
                self.tr('CRS for numeric coordinates (ignored for real WKT)'),
                defaultValue='EPSG:4326'
            )
        )
        self.addParameter(
            QgsProcessingParameterFeatureSink(
                self.OUTPUT,
                self.tr('Output layer')
            )
        )

    # ---------- core ----------
    def processAlgorithm(self, params, context, feedback):
        path_string = self.parameterAsFile(params, self.INPUT, context)
        if not path_string:
            raise QgsProcessingException(self.tr('No Excel files specified.'))

        # Split semicolon-separated paths (allow manual editing)
        excel_files = [p.strip().strip('"') for p in path_string.split(';') if p.strip()]
        if not excel_files:
            raise QgsProcessingException(self.tr('No valid Excel files found in input.'))

        crs = self.parameterAsCrs(params, self.CRS, context)
        primary_sheets = ['Sample SSU', 'Selected Samples', 'AONCR-SAMPLE HH']
        replacement_sheet = 'Replacement SSU'

        layers = []
        wkt_fields = {}
        source_codes = {}

        for path in excel_files:
            if not os.path.isfile(path):
                feedback.pushWarning(self.tr(f'Skipping missing file: {path}'))
                continue

            feedback.pushInfo(self.tr(f'Processing Excel file: {path}'))
            safe_path = path.replace('\\', '/')

            # --- Load primary sheet ---
            primary_layer = None
            for name in primary_sheets:
                uri = f'{safe_path}|layername={name}'
                lyr = QgsVectorLayer(uri, os.path.basename(path), 'ogr')
                if lyr.isValid():
                    wkt_field = next((f.name() for f in lyr.fields() if 'wkt' in f.name().strip().lower()), None)
                    if not wkt_field:
                        raise QgsProcessingException(self.tr(f'Column containing “WKT” not found in “{name}”.'))
                    primary_layer = lyr
                    layers.append(lyr)
                    wkt_fields[lyr] = wkt_field
                    source_codes[lyr] = 1
                    feedback.pushInfo(self.tr(f'  ✓ Found primary sheet: "{name}"'))
                    break

            if primary_layer is None:
                feedback.pushWarning(self.tr(f'No valid primary sheet found in {path}. Skipping.'))
                continue

            # --- Load replacement sheet ---
            uri = f'{safe_path}|layername={replacement_sheet}'
            lyr = QgsVectorLayer(uri, replacement_sheet.lower().replace(' ', '_'), 'ogr')
            if lyr.isValid():
                wkt_field = next((f.name() for f in lyr.fields() if 'wkt' in f.name().strip().lower()), None)
                if not wkt_field:
                    raise QgsProcessingException(self.tr(f'Column containing “WKT” not found in “{replacement_sheet}”.'))
                layers.append(lyr)
                wkt_fields[lyr] = wkt_field
                source_codes[lyr] = 2
                feedback.pushInfo(self.tr(f'  ✓ Found replacement sheet: "{replacement_sheet}"'))
            else:
                feedback.pushInfo(self.tr('  ⚪ “Replacement SSU” sheet not found, skipping.'))

        if not layers:
            raise QgsProcessingException(self.tr('No valid sheets found to process.'))

        total = sum(l.featureCount() for l in layers)
        if total == 0:
            raise QgsProcessingException(self.tr('All available sheets are empty.'))

        # --- Helper: string → geometry ---
        num_pat = re.compile(r'[-+]?\d*\.?\d+(?:[eE][-+]?\d+)?')

        def to_geom(txt):
            txt = (txt or '').strip()
            g = QgsGeometry.fromWkt(txt)
            if not g.isNull():
                return g
            nums = num_pat.findall(txt)
            if len(nums) >= 2:
                try:
                    return QgsGeometry.fromPointXY(QgsPointXY(float(nums[0]), float(nums[1])))
                except ValueError:
                    pass
            return QgsGeometry()

        # --- Output layer: POINT + new field ---
        geom_type = QgsWkbTypes.Point
        fields = layers[0].fields()
        fields.append(QgsField('source_data', QVariant.Int))
        sink, dest_id = self.parameterAsSink(params, self.OUTPUT, context, fields, geom_type, crs)
        if sink is None:
            raise QgsProcessingException(self.invalidSinkError(params, self.OUTPUT))

        # --- Merge + Split MULTIPOINTs ---
        step = 100.0 / max(total, 1)
        done = imported = skipped = 0

        for lyr in layers:
            wkt_field = wkt_fields[lyr]
            src_code = source_codes[lyr]
            for feat in lyr.getFeatures():
                if feedback.isCanceled():
                    break
                done += 1
                geom = to_geom(feat[wkt_field])
                if geom.isNull():
                    skipped += 1
                    feedback.setProgress(int(done * step))
                    continue

                feats_to_add = []
                if geom.isMultipart():
                    for pt in geom.asMultiPoint():
                        nf = QgsFeature(fields)
                        nf.setAttributes(feat.attributes() + [src_code])
                        nf.setGeometry(QgsGeometry.fromPointXY(pt))
                        feats_to_add.append(nf)
                else:
                    nf = QgsFeature(fields)
                    nf.setAttributes(feat.attributes() + [src_code])
                    nf.setGeometry(geom)
                    feats_to_add.append(nf)

                for nf in feats_to_add:
                    sink.addFeature(nf, QgsFeatureSink.FastInsert)
                    imported += 1

                feedback.setProgress(int(done * step))

        feedback.pushInfo(self.tr(f'Imported {imported} POINT feature(s); skipped {skipped}.'))
        return {self.OUTPUT: dest_id}