from typing import TYPE_CHECKING, Self
if TYPE_CHECKING:
    import pandas.core.frame
import numpy as np
from operator import xor
import pandas as pd
from qgis.PyQt.QtCore import Qt
from qgis.PyQt.QtWidgets import QWidget, QMessageBox, QFileDialog, QAbstractItemView, QTableWidgetItem, QComboBox, QHeaderView, QInputDialog
from qgis.core import QgsTask

from psycopg2 import sql as pgsql
from ..database_scripts.db import DB
from ..import_data.handle_text_data import create_table, create_polygons
from ..support_scripts.__init__ import (TR, check_text)
from ..support_scripts.qt_data import (_check_state, _enum_select_rows, _file_dialog_option,
                                       _header_view_resize_mode, _item_flag)
from ..support_scripts.create_layer import CreateLayer, add_background
from ..support_scripts.radio_box import RadioComboBox
from ..support_scripts.pyagriculture.agriculture import PyAgriculture
from ..widgets.import_xml_bin import ImportXmlBin
from ..support_scripts.notifier import report_warning


class Iso11783:
    def __init__(self: Self, parent_widget, type_: str) -> None:
        """For supporting the read of iso XML/Bin files"""
        self.py_agri = None
        self.db = parent_widget.db
        self.populate = parent_widget.populate
        self.parent = parent_widget
        self.data_type = type_
        self.sender = QWidget().sender
        self.IXB = ImportXmlBin()
        self.py_agri = None
        translate = TR('ImportXmlBin')
        self.tr = translate.tr
        self.combo = []
        self.tasks = []
        self.checkboxes1 = []
        self.checkboxes2 = []
        self.checkboxes3 = []
        self.checkboxes4 = []
        self.unit_boxes = {}

    def initiate_pyAgriculture(self: Self, path: str) -> None:
        """Connects the plugin to pyAgriculture."""
        self.py_agri = PyAgriculture(path)

    def run(self: Self) -> None:
        """Presents the sub widget ImportXmlBin and connects the different
        buttons to their function."""
        self.IXB.show()
        self.IXB.PBAddInputFolder.clicked.connect(self.open_input_folder)
        self.IXB.PBFindFields.clicked.connect(self.populate_second_table)
        self.IXB.PBAddParam.clicked.connect(self.add_to_param_list)
        self.IXB.PBRemParam.clicked.connect(self.remove_from_param_list)
        self.IXB.PBInsert.clicked.connect(self.add_to_database)
        if not self.parent.test_mode:
            self.IXB.exec()

    def add_to_canvas(self, schema, tbl, focus_cols):
        """At the end add the layers to the canvas, one layer for
        each of the columns in the focus_cols"""
        add_background()
        create_layer = CreateLayer(self.db)
        for param_layer in focus_cols:
            param_layer = check_text(param_layer)
            target_field = param_layer
            try:
                if self.data_type == 'harvest':
                    layer = self.db.add_postgis_layer(tbl, 'pos', '{schema}'.format(schema=schema),
                                                    check_text(param_layer.lower()))
                else:
                    layer = self.db.add_postgis_layer(tbl, 'polygon', '{schema}'.format(schema=schema),
                                                    check_text(param_layer.lower()))

                create_layer.create_layer_style(layer, check_text(target_field), tbl, schema)
            except Exception as layer_exc:
                report_warning(self.tr('Could not add/style layer for column "{col}" in {tbl}, skipping it: {err}').format(
                    col=target_field, tbl=tbl, err=layer_exc))
                continue

    def close(self, result, values):
        """Disconnects buttons and closes the widget when all tasks are completed, 
        also makes the call to add them to the canvas"""
        if values[0]:
            self.add_to_canvas(values[1], values[2], values[3])
        else:
             if values[1]:
                report_warning(values[2])
        self.added_nbrs += 1
        if self.added_nbrs == self.tasks_to_run:
            self.IXB.PBAddInputFolder.clicked.disconnect()
            self.IXB.PBFindFields.clicked.disconnect()
            self.IXB.PBAddParam.clicked.disconnect()
            self.IXB.PBRemParam.clicked.disconnect()
            self.IXB.PBInsert.clicked.disconnect()
            self.IXB.TWISODataSelect.itemChanged.disconnect(self.update_time_stamp)
            self.IXB.TWISODataAll.clear()
            self.IXB.TWISODataSelect.clear()
            self.IXB.TWColumnNames.clear()
            self.IXB.TWtoParam.clear()
            self.IXB.done(0)
            self.py_agri = None
            return True

    def open_input_folder(self: Self, path=False) -> None:
        """Opens a dialog and let the user select the folder where Taskdata are stored."""
        if path is not False:
            pass
        else:
            path = QFileDialog.getExistingDirectory(None, self.tr("Open a folder"), '',
                                                              _file_dialog_option('ShowDirsOnly'))
        if path != '':
            self.initiate_pyAgriculture(path)
            self.populate_first_table()      

    def get_task_data(self: Self) -> dict:
        """For those tasks that are marked with include the script will gather their data.
        The function will return a dict[task_nr] = [[field, timestamp]]"""
        task_names = {}
        for task_nr, data_set in enumerate(self.py_agri.tasks):  # type: pd.DataFrame
            try:
                if 'time_stamp' in data_set.columns:
                    time_stamp = data_set['time_stamp']
                else:
                    time_stamp = pd.Series(['1970-01-01'] * len(data_set))
                    data_set['time_stamp'] = time_stamp
                if 'geometry' in data_set.columns and 'longitude' not in data_set.columns:
                    try:
                        # Ensure the geometry column contains valid geometries
                        if not data_set['geometry'].isnull().all():
                            # Calculate centroids and create latitude and longitude columns
                            data_set['centroid'] = data_set['geometry'].apply(lambda geom: geom.centroid if geom else None)
                            data_set['latitude'] = data_set['centroid'].apply(lambda centroid: centroid.y if centroid else None)
                            data_set['longitude'] = data_set['centroid'].apply(lambda centroid: centroid.x if centroid else None)
                    except Exception as e:
                        print(f"Error calculating centroids: {e}")
                else:
                    lat = data_set['latitude']
                    lon = data_set['longitude']
                    if lat is None or lon is None:
                        continue
            except Exception as e:
                print(f'error: {e}')
                task_names[task_nr] = []
                continue
            # Remove rows where both latitude and longitude are 0
            data_set = data_set[~((data_set['latitude'] == 0) & (data_set['longitude'] == 0))]
            if len(data_set) == 0:
                task_names[task_nr] = []
                continue
            fields = []
            if len(data_set) < 100:
                divider = len(data_set)
            else:
                divider = 10
            union_parts = []
            union_params = []
            for index, row in data_set.iloc[::int(len(data_set)/divider)].iterrows():
                union_parts.append(pgsql.SQL(
                    "SELECT field_name FROM fields"
                    " WHERE st_intersects(polygon, st_geomfromtext(%s, 4326))"))
                union_params.append(f"POINT({row['longitude']} {row['latitude']})")
            query = (pgsql.SQL("WITH start_sel AS (")
                     + pgsql.SQL(" UNION ").join(union_parts)
                     + pgsql.SQL(") SELECT field_name FROM start_sel GROUP BY field_name"))
            fields_ = self.db.execute_and_return(query, params=tuple(union_params))
            if len(fields_) == 0 and not self.parent.test_mode:
                task_name = data_set.attrs.get('task_name', f'Task {task_nr}')
                report_warning(self.tr(
                    'The task "{task}" does not overlap any of your fields (e.g. near {lat}, {lon}).'
                    ' Check that the field it belongs to has been added, and that its boundary covers that area.'
                ).format(task=task_name,
                         lat=round(data_set['latitude'].iloc[0], 4),
                         lon=round(data_set['longitude'].iloc[0], 4)))
            for field in fields_:
                fields.append([field[0], time_stamp.values[-1]])
            task_names[task_nr] = fields
        return task_names

    def populate_first_table(self: Self) -> None:
        """Populates the task list."""
        self.checkboxes1 = []
        task_names, file_names = self.py_agri.gather_task_names()
        self.IXB.TWISODataAll.setRowCount(len(task_names))
        self.IXB.TWISODataAll.setColumnCount(3)
        self.IXB.TWISODataAll.setHorizontalHeaderLabels([self.tr('Get more info'), self.tr('Task name'), self.tr('File name')])
        self.IXB.TWISODataAll.setSelectionBehavior(_enum_select_rows())
        for i, row in enumerate(task_names):
            item1 = QTableWidgetItem('Include')
            item1.setFlags(_item_flag('ItemIsUserCheckable') | _item_flag('ItemIsEnabled'))
            item1.setCheckState(_check_state('Unchecked'))
            self.checkboxes1.append([item1, row, file_names[i]])
            item2 = QTableWidgetItem(row)
            item2.setFlags(xor(item2.flags(), _item_flag('ItemIsEditable')))
            item3 = QTableWidgetItem(file_names[i])
            item3.setFlags(xor(item3.flags(), _item_flag('ItemIsEditable')))
            self.IXB.TWISODataAll.setItem(i, 0, item1)
            self.IXB.TWISODataAll.setItem(i, 1, item2)
            self.IXB.TWISODataAll.setItem(i, 2, item3)

    def populate_second_table(self: Self) -> None:
        """Populates the list that is marked as include in the first table.
        Also calls py_agri to decode the binary data, this is done in a separate
        task."""
        self.tasks_to_include = []
        most_importants = []
        for row in self.checkboxes1:
            if row[0].checkState() == 2:
                self.tasks_to_include.append(row[2])
        if len(self.tasks_to_include) == 0:
            report_warning(self.tr('You need to select at least one of the tasks'))
            return
        self.py_agri.tasks = []
        self.combo = []
        self.checkboxes2 = []
        self.checkboxes3 = []
        self.checkboxes4 = []
        if self.parent.test_mode is False:
            task = QgsTask.fromFunction('Decode binary data', self.py_agri.gather_data, 
                                        self.tasks_to_include, 
                                        most_importants,
                                        on_finished=self.populate2)
            self.parent.tsk_mngr.addTask(task)
        else:
            self.py_agri.gather_data("debug", self.tasks_to_include, 
                                     most_importants)
            self.populate2()

    def update_time_stamp(self, item: QTableWidgetItem) -> None:
        """Updates the time_stamp in self.py_agri.tasks when the table is edited."""
        if item.column() == 1:  # Assuming the 'time_stamp' column is at index 1
            row = item.row()
            new_value = item.text()
            try:
                # Update the corresponding value in self.py_agri.tasks
                self.py_agri.tasks[row]['time_stamp'] = new_value
                print(f"Updated time_stamp for task {row} to {new_value}")
            except Exception as e:
                print(f"Error updating time_stamp: {e}")

    def populate2(self: Self, res: str="", values: str="") -> None:
        """The end of populate the second table when all data is decoded 
        from the qtask"""
        task_names = self.get_task_data()
        valids = 0
        for row in task_names.values():
            if len(row) > 0:
                valids += 1
        self.IXB.TWISODataSelect.setRowCount(valids)
        self.IXB.TWISODataSelect.setColumnCount(4)
        self.IXB.TWISODataSelect.setHorizontalHeaderLabels([self.tr('To include'), self.tr('Date'), self.tr('Field'),
                                                            self.tr('Crops')])
        header = self.IXB.TWISODataSelect.horizontalHeader()       
        header.setSectionResizeMode(0, _header_view_resize_mode('ResizeToContents'))
        header.setSectionResizeMode(1, _header_view_resize_mode('ResizeToContents'))
        header.setSectionResizeMode(2, _header_view_resize_mode('Stretch'))
        header.setSectionResizeMode(3, _header_view_resize_mode('ResizeToContents'))
        self.IXB.TWISODataSelect.setSelectionBehavior(_enum_select_rows())
        j = -1  # How may checkboxes that is added
        for i, row in enumerate(task_names.values()):
            if len(row) == 0:
                continue
            j += 1
            item1 = QTableWidgetItem('Include')
            item1.setFlags(_item_flag('ItemIsUserCheckable') | _item_flag('ItemIsEnabled'))
            item1.setCheckState(_check_state('Checked'))
            self.checkboxes2.append([j, j, item1])
            item2 = QTableWidgetItem(row[0][1])
            item2.setFlags(_item_flag('ItemIsSelectable') | _item_flag('ItemIsEnabled') | _item_flag('ItemIsEditable'))
            field_column = RadioComboBox()
            self.combo.append(field_column)
            for field, _ in row:
                field_column.addItem(field)
            field_column.setCurrentIndex(1)
            crops = QComboBox()
            self.populate.reload_crops(crops)
            self.IXB.TWISODataSelect.setItem(j, 0, item1)
            self.IXB.TWISODataSelect.setItem(j, 1, item2)
            self.IXB.TWISODataSelect.setCellWidget(j, 2, field_column)
            self.IXB.TWISODataSelect.setCellWidget(j, 3, crops)
            self.checkboxes3.append(field_column)
            self.checkboxes4.append(crops)
            self.tasks.append(self.rename_duplicate_columns(self.py_agri.tasks[i]))
        self.IXB.TWISODataSelect.itemChanged.connect(self.update_time_stamp)
        self.set_column_list()

    def add_to_param_list(self: Self) -> None:
        """Adds the selected columns to the list of fields that should be
        treated as "special" in the database both to work as a parameter that
        could be evaluated and as a layer that is added to the canvas"""
        rows_in_table = self.IXB.TWtoParam.rowCount()
        self.IXB.TWtoParam.setColumnCount(1)
        items_to_add = []
        existing_values = []
        for i in range(rows_in_table):
            existing_values.append(self.IXB.TWtoParam.item(i, 0).text())
        for i, item in enumerate(self.IXB.TWColumnNames.selectedItems()):
            if item.column() == 0 and item.text() not in existing_values:
                index = self.IXB.TWColumnNames.selectedIndexes()[i].row()
                unit = self.IXB.TWColumnNames.cellWidget(index, 4).currentText()
                if unit != item.text() and unit != '':
                    items_to_add.append(f'{item.text()}_{check_text(unit)}')
                else:
                    items_to_add.append(f'{check_text(item.text())}')
        for i, item in enumerate(items_to_add):
            self.IXB.TWtoParam.setRowCount(rows_in_table + i + 1)
            item1 = QTableWidgetItem(item)
            item1.setFlags(xor(item1.flags(), _item_flag('ItemIsEditable')))
            self.IXB.TWtoParam.setItem(i + rows_in_table, 0, item1)
        self.IXB.PBInsert.setEnabled(True)

    def remove_from_param_list(self):
        """Removes the selected columns from the list of fields that should be
        treated as "special" in the database"""
        if self.IXB.TWtoParam.selectedItems() is None:
            report_warning(self.tr('No row selected!'))
            return
        for item in self.IXB.TWtoParam.selectedItems():
            self.IXB.TWtoParam.removeRow(item.row())
        if self.IXB.TWtoParam.rowCount() == 0:
            self.IXB.PBInsert.setEnabled(False)

    def _collect_task_columns(self: Self) -> "tuple[list[str], dict[str, tuple[int, int]]]":
        """Walks every loaded task (not just the first) and returns the union
        of their non-geometry, non-empty columns in first-seen order, plus
        a {column_name: (task_index, position_in_that_task)} map used to read
        that column's own stats/unit from the task it actually came from -
        tasks don't all log the same columns, so no single task can be used
        as the source for all of them."""
        valid_columns = []
        sources = {}
        for t_idx, task in enumerate(self.tasks):
            pos = -1
            for column in task.columns:
                if column in ['latitude', 'longitude', 'geometry']:
                    continue
                pos += 1
                if column in sources:
                    continue
                if task[column].isnull().all():
                    continue
                if len(task.attrs['unit_row']) <= pos:
                    continue
                sources[column] = (t_idx, pos)
                valid_columns.append(column)
        return valid_columns, sources

    def set_column_list(self: Self) -> None:
        """Populates the column table with the union of columns across all
        loaded tasks - a column missing from one task but present in another
        must still be configurable here."""
        self.IXB.TWColumnNames.clear()  # Clear the table
        self.IXB.TWColumnNames.setRowCount(0)  # Reset row count
        self.IXB.TWColumnNames.setColumnCount(6)  # Set the number of columns

        # Set the horizontal header labels
        self.IXB.TWColumnNames.setHorizontalHeaderLabels([
            self.tr('Column name'),
            self.tr('Mean value'),
            self.tr('Min value'),
            self.tr('Max value'),
            self.tr('Unit'),
            self.tr('Scale')
        ])

        # Check if there are tasks to populate the table
        if self.tasks is None or len(self.tasks) == 0:
            return
        valid_columns, sources = self._collect_task_columns()

        # Populate the table with data
        self.IXB.TWColumnNames.setRowCount(len(valid_columns))
        self.unit_boxes = {}
        self.IXB.TWColumnNames.setSelectionBehavior(_enum_select_rows())

        for i, row in enumerate(valid_columns):
            t_idx, pos = sources[row]
            task = self.tasks[t_idx]
            item1 = QTableWidgetItem(row)
            item1.setFlags(xor(item1.flags(), _item_flag('ItemIsEditable')))
            self.IXB.TWColumnNames.setItem(i, 0, item1)

            try:
                mean = str(round(task[row].mean(), 2))
            except Exception:
                mean = ''
            item2 = QTableWidgetItem(mean)
            self.IXB.TWColumnNames.setItem(i, 1, item2)

            try:
                _min = str(task[row].min())
            except Exception:
                _min = ''
            item3 = QTableWidgetItem(_min)
            self.IXB.TWColumnNames.setItem(i, 2, item3)

            try:
                _max = str(task[row].max())
            except Exception:
                _max = ''
            item4 = QTableWidgetItem(_max)
            self.IXB.TWColumnNames.setItem(i, 3, item4)

            unit = task.attrs['unit_row'][pos]
            unit_col = self.get_units_option(unit)
            self.unit_boxes[len(self.unit_boxes)] = {'box': unit_col, 'org_item': unit}
            unit_col.__setattr__('index', i)
            unit_col.__setattr__('org_item', unit)
            unit_col.currentTextChanged.connect(self.change_unit_type)
            self.IXB.TWColumnNames.setCellWidget(i, 4, unit_col)

            item6 = QTableWidgetItem("1")
            self.IXB.TWColumnNames.setItem(i, 5, item6)

    @staticmethod
    def rename_duplicate_columns(df):
        cols = pd.Series(df.columns)
        for dup in cols[cols.duplicated()].unique():
            cols[cols[cols == dup].index.values.tolist()] = [dup + '_' + str(i) if i != 0 else dup for i in range(sum(cols == dup))]
        df.columns = cols
        return df

    def change_unit_type(self):
        index = self.sender().index
        org_item = self.sender().org_item
        new_unit = self.IXB.TWColumnNames.cellWidget(index, 4).currentText()
        if org_item == new_unit:
            return
        new_value = None
        if org_item == 'ft':
            if new_unit == 'in':
                new_value = 12
            if new_unit == 'cm':
                new_value = 30.48
            if new_unit == 'm':
                new_value = 0.3048
        if org_item == 'in':
            if new_unit == 'in':
                new_value = 1 / 12
            if new_unit == 'cm':
                new_value = 2.54
            if new_unit == 'm':
                new_value = 0.0254
        if org_item == 'lb/bu':
            if new_unit == 'kg/m3':
                new_value = 12.87
        if org_item == 'lb/h':
            if new_unit =='kg/h':
                new_value = 0.453592
        if org_item == 'ac/h':
            if new_unit == 'ha/h':
                new_value = 0.404686
        if new_unit == 'kg/ha':
            if org_item == 'bu/ac':
                new_value = 67
            if org_item == 'lb/ac':
                new_value = 1.12085
        if org_item == 'gal/h':
            if new_unit == 'l/h':
                new_value = 3.7854
        if new_unit == 'C':
            new_value = 'C'
        if new_unit == 'F':
            new_value = 'F'
        if new_value is not None:
            self.IXB.TWColumnNames.item(index, 5).setText(str(new_value))

    @staticmethod
    def get_units_option(org_unit: str) -> RadioComboBox:
        unit_col = RadioComboBox()
        unit_col.addItem(org_unit)
        if org_unit == 'C':
            unit_col.addItem('F')
        if org_unit == 'F':
            unit_col.addItem('C')
        if org_unit == 'ft':
            unit_col.addItem('in')
            unit_col.addItem('cm')
            unit_col.addItem('m')
        if org_unit == 'in':
            unit_col.addItem('ft')
            unit_col.addItem('cm')
            unit_col.addItem('m')
        if org_unit == 'lb/bu':
            unit_col.addItem('kg/m3')
        if org_unit == 'lb/h':
            unit_col.addItem('kg/h')
        if org_unit == 'ac/h':
            unit_col.addItem('ha/h')
        if org_unit in ['bu/ac', 'lb/ac']:
            unit_col.addItem('kg/ha')
        if org_unit == 'gal/h':
            unit_col.addItem('l/h')
        return unit_col

    @staticmethod
    def cel2far(celsius) -> float:
        fahrenheit = 9.0 / 5.0 * celsius + 32
        return fahrenheit

    @staticmethod
    def far2cel(fahrenheit) -> float:
        celsius = (fahrenheit - 32) * 5.0 / 9.0
        return  celsius

    def prep_data(self: Self) -> list:
        """Gather data from the combo-checkboxes and check that they are valid."""
        fields = []
        crops = []
        dates = []
        focus_cols = []
        focus_col = []
        idxs = []
        rows_in_table = self.IXB.TWtoParam.rowCount()
        for i in range(rows_in_table):
            focus_col.append(check_text(self.IXB.TWtoParam.item(i, 0).text().strip()))
        found = False
        for tbl_idx, check_idx,  cbox in self.checkboxes2:
            if cbox.checkState() == 2:
                found = True
                if self.IXB.TWISODataSelect.item(tbl_idx, 1) is None:
                    continue
                dates.append(self.IXB.TWISODataSelect.item(tbl_idx, 1).text())
                field = self.checkboxes3[check_idx].currentText()
                if field == self.tr('--- Select field ---'):
                    report_warning(self.tr('You need to select a crop'))
                    return [False]
                fields.append(field)
                crop = self.checkboxes4[check_idx].currentText()
                if crop == self.tr('--- Select crop ---'):
                    report_warning(self.tr('You need to select a crop'))
                    return [False]
                crops.append(crop)
                focus_cols.append(focus_col)
                idxs.append(tbl_idx)
        if not found:
            report_warning(self.tr('You need to select at least one of the tasks'))
            return [False]
        return [True, fields, crops, dates, focus_cols, idxs]

    def scale_dfs(self: Self, df: "pandas.core.frame.DataFrame") -> list:
        scale_factors = {
            self.IXB.TWColumnNames.item(row_idx, 0).text(): self.IXB.TWColumnNames.item(row_idx, 5).text()
            for row_idx in range(self.IXB.TWColumnNames.rowCount())
        }
        for col in df.attrs['columns']:
            if col in ['latitude', 'longitude', 'geometry']:
                continue
            # A column this task doesn't have configured (e.g. it's only
            # present in a different task in the batch) is left untouched.
            scale_f = scale_factors.get(col, '1')
            if scale_f == 'C':
                df[col] = self.far2cel(df[col])
                continue
            elif scale_f == 'F':
                df[col] = self.cel2far(df[col])
                continue
            try:
                value = float(scale_f)
                df[col] = df[col] * value
            except ValueError:
                QMessageBox(None, self.tr('Error'), self.tr('The number must only contain numbers and .'))
                return [False]
            except TypeError:
                pass
        return [True, df]

    def get_col_types(self: Self) -> dict:
        """Gather the column type (0=int, 1=float, 2=string) for every column
        across all loaded tasks, keyed by column name - tasks can differ in
        which columns they have and in what order, so a single task's column
        order can't be used as a stand-in for every task's."""
        col_types = {}
        for task in self.tasks:
            for column, dtype in task.dtypes.items():
                if column in col_types:
                    continue
                if dtype == np.int64:
                    col_types[column] = 0
                elif dtype == np.float64:
                    col_types[column] = 1
                elif dtype == np.str_:
                    col_types[column] = 2
                else:
                    col_types[column] = 2
        return col_types

    def get_col_units(self: Self) -> dict:
        """returns a list with the unit of all columns, if None '' is added."""
        col_units = {}
        for index in range(self.IXB.TWColumnNames.rowCount()):
            column = self.IXB.TWColumnNames.item(index, 0).text()
            new_unit = self.IXB.TWColumnNames.cellWidget(index, 4).currentText()
            if new_unit == column:
                col_units[column] = '' 
            elif new_unit != '':
                col_units[column] = f'_{check_text(new_unit)}'
            else:
                col_units[column] = ''
        return col_units

    def _prompt_ferti_nutrient(self: Self) -> str:
        """Which nutrient this whole batch of fertilizer imports actually
        is - asked once per "Insert" click, not once per field/date,
        since one import batch is one spreading operation: one product
        goes in the spreader per pass, not a per-row/per-field mix (see
        CropSimulation._load_imported_ferti_events, which reads the
        answer back from a constant column this adds to each created
        table below). Cancelling keeps the original nitrogen-only
        assumption rather than blocking the import."""
        if self.parent.test_mode:
            return getattr(self, '_test_ferti_nutrient', 'N')
        nutrient, ok = QInputDialog.getItem(
            self.IXB, self.tr('Fertilizer nutrient'),
            self.tr('Which nutrient does this import represent?\n'
                    '(one product per spreading pass, so this applies to '
                    'every field/date selected below)'),
            ['N', 'P', 'K', 'Mg', 'S', 'Na'], 0, False)
        return nutrient if ok else 'N'

    def add_to_database(self: Self) -> bool:
        """Initiate the insertion of data to the database."""
        col_types = self.get_col_types()
        col_units = self.get_col_units()
        prep_data = self.prep_data()
        if not prep_data[0]:
            return
        fields = prep_data[1]
        crops = prep_data[2]
        dates = prep_data[3]
        focus_cols = prep_data[4]
        idxs = prep_data[5]
        self.added_nbrs = 0
        self.tasks_to_run = len(fields)
        nutrient = self._prompt_ferti_nutrient() if self.data_type == 'ferti' else None
        for i, field in enumerate(fields):
            try:
                # idxs[i], not i: fields/crops/dates/focus_cols only contain
                # the checked rows, so if any row in between got unchecked,
                # i alone would drift from which task this row actually is.
                df = self.tasks[idxs[i]]
                success = self.scale_dfs(df)
                if not success[0]:
                    return False

                crop = crops[i]
                date = dates[i]
                columns = []
                table = f'{check_text(field)}_{check_text(crop)}_{check_text(date)}'
                for col in df.columns:
                    columns.append(check_text(col.strip()))
                # A parameter chosen "to be analysed" may not be present in
                # every task in the batch - only index/style it for the
                # tasks that actually have it, skip it for the rest.
                task_focus_col = [c for c in focus_cols[i] if c in columns]
                suc, insert_sql, _ = create_table(self.db, self.data_type, columns,
                                                'latitude', 'longitude', 'time_stamp', '',
                                                col_types, column_units=col_units, table=table,
                                                ask_replace=False, test_mode=self.parent.test_mode,
                                                task_nr=i)
                if not suc:
                    report_warning(self.tr('A table for "{field}" ({date}) already exists, skipping it.').format(
                        field=field, date=date))
                    self.tasks_to_run -= 1
                    continue
                if nutrient:
                    self.db.execute_sql(pgsql.SQL(
                        "ALTER TABLE {schema}.{tbl} ADD COLUMN nutrient text DEFAULT %s"
                    ).format(schema=pgsql.Identifier(self.data_type), tbl=pgsql.Identifier(table)),
                        params=(nutrient,))

                if not self.parent.test_mode:
                    db = DB(self.parent.dock_widget, path=self.parent.plugin_dir, test_mode=self.parent.test_mode)
                    connected = db.set_conn(False)
                    task = QgsTask.fromFunction(f'Adding field: {field}{prep_data[5][i]}', insert_data,
                                                db, df, self.data_type,
                                                insert_sql, table, field, task_focus_col,
                                                col_types, i, on_finished=self.close)
                    self.parent.tsk_mngr.addTask(task)
                else:
                    res = insert_data(None, self.db, df, self.data_type,
                                                insert_sql, table, field, task_focus_col,
                                                col_types, i)
                    # self.close(True, res)
                    return res[0]
            except Exception as e:
                print(e)

def insert_data(qtask: None, db: DB, data: pd.DataFrame, schema: str, insert_sql: str, tbl_name: str, 
                field: str, focus_col: list, col_types: list, tsk_nr:int) -> tuple[bool, str, str, list[list[str]]]:
    """Makes the actual insertion to the database (first to a temp table and then to the correct table)."""
    try:
        row_placeholders = []
        batch_params = []
        count_db_insert = 0
        for row_nr, row in data.iterrows():
            placeholders = []
            lat_lon_insert = False
            for col in data.columns:
                if col in ['latitude', 'longitude']:
                    if not lat_lon_insert:
                        placeholders.append("ST_PointFromText(%s, 4326)")
                        batch_params.append(f"POINT({row['longitude']} {row['latitude']})")
                        lat_lon_insert = True
                    continue
                if col == 'time_stamp':
                    placeholders.append("%s")
                    batch_params.append(str(row['time_stamp']))
                    continue
                val_str = str(row[col])
                if val_str == 'nan' or val_str.lower() == 'none':
                    placeholders.append("NULL")
                elif col_types.get(col, 2) == 2:
                    placeholders.append("%s")
                    batch_params.append(val_str)
                else:
                    placeholders.append("%s")
                    batch_params.append(row[col])
            row_placeholders.append("(" + ", ".join(placeholders) + ")")
            if count_db_insert > 1_000:
                sql = insert_sql + ", ".join(row_placeholders)
                db.execute_sql(sql, params=tuple(batch_params), return_failure=True)
                if qtask is not None:
                    qtask.setProgress(row_nr / len(data) * 80)
                row_placeholders = []
                batch_params = []
                count_db_insert = 0
            else:
                count_db_insert += 1
            if qtask is not None:
                if qtask.isCanceled():
                    return False, False, "was cancelled"
        if count_db_insert > 0:
            sql = insert_sql + ", ".join(row_placeholders)
            db.execute_sql(sql, params=tuple(batch_params))
        if qtask is not None:
            qtask.setProgress(80)

        query = pgsql.SQL(
            "SELECT * INTO {schema}.{tbl}"
            " FROM {schema}.{temp_tbl}"
            " WHERE st_intersects(pos,"
            " (SELECT polygon FROM fields WHERE field_name = %s))"
        ).format(
            schema=pgsql.Identifier(schema),
            tbl=pgsql.Identifier(tbl_name),
            temp_tbl=pgsql.Identifier(f"temp_table{tsk_nr}"))
        suc = db.execute_sql(query, params=(field,), return_failure=True, return_row_count=True)
        if qtask is not None:
            qtask.setProgress(85)
        if not suc[0]:
            return False, True, suc[1]
        if suc[2] == 0:
            return False, True, 'No data was found on that field.'
        if schema != 'harvest':
            r = create_polygons(db, schema, tbl_name, field)
        if not suc[0]:
            return False, True, r[1]
        db.execute_sql(
            pgsql.SQL("DROP TABLE {schema}.{tbl}").format(
                schema=pgsql.Identifier(schema),
                tbl=pgsql.Identifier(f"temp_table{tsk_nr}")))
        if qtask is not None:
            qtask.setProgress(90)
        db.create_indexes(tbl_name, focus_col, schema, primary_key=False)
        if qtask is not None:
            qtask.setProgress(100)
        return True, schema, tbl_name, focus_col
    except Exception as e:
        return False, e