import json
import os
from datetime import timedelta, timezone
from enum import Enum
from pathlib import Path
from typing import Callable, Union

import numpy as np
import pandas
from qgis.core import (
    QgsApplication,
    QgsCoordinateReferenceSystem,
    QgsCoordinateTransform,
    QgsMapLayerProxyModel,
    QgsPointXY,
    QgsProject,
    QgsRasterLayer,
    QgsSettings,
)
from qgis.PyQt import QtCore, uic
from qgis.PyQt.QtGui import QColor, QFont, QIcon, QPixmap
from qgis.PyQt.QtWidgets import (
    QComboBox,
    QDialog,
    QFileDialog,
    QHBoxLayout,
    QInputDialog,
    QLabel,
    QMessageBox,
    QStyledItemDelegate,
    QTableView,
    QTableWidget,
    QTableWidgetItem,
    QWidget,
)

from openlog.__about__ import DIR_PLUGIN_ROOT
from openlog.datamodel.assay.generic_assay import AssaySeriesType, SphericalType
from openlog.datamodel.assay.spherical_assay import SphericalDefinition
from openlog.datamodel.assay.uncertainty import AssayColumnUncertainty, UncertaintyType
from openlog.datamodel.connection.openlog_connection import OpenLogConnection
from openlog.gui.utils.column_definition import ColumnDefinition
from openlog.gui.utils.column_definition_tablemodel import ColumnDefinitionTableModel
from openlog.gui.utils.mdl_assay_column_categorie import (
    AssayColumnCategoryTableModel,
    CategoryPolicy,
)
from openlog.gui.utils.mdl_assay_column_uncertainty import (
    AssayColumnUncertaintyTableModel,
)
from openlog.gui.utils.mdl_assay_spherical_data import SphericalDataTableModel
from openlog.toolbelt import PlgTranslator, encoding_utils
from openlog.toolbelt.log_handler import PlgLogger

CRS_KEY = "/crs"
DATE_FORMAT_KEY = "/date_format"
TIME_FORMAT_KEY = "/time_format"
COLUMN_LIST_KEY = "/column_list"
COLUMN_MAPPING_KEY = "/column_mapping"


class ColumnMappingItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for column mapping definition

        Args:
            parent:
        """
        super().__init__(parent)
        self.available_columns = []  # Define columns available in file
        self.other_options = [
            "From DTM",
            "Set constant",
        ]  # additional options for elevation

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for column mapping definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available column for mapping

        """
        editor = QComboBox(parent)
        optional = True
        if isinstance(index.model(), ColumnDefinitionTableModel):
            optional = index.siblingAtRow(ColumnDefinitionTableModel.COLUMN_ROW).data(
                ColumnDefinitionTableModel.OPTIONAL_ROLE
            )

        col_def = index.model().get_column_definition()
        # elevation column
        if (
            index.column() == 3
            and len(col_def) > 3
            and col_def[3].column == "Elevation"
        ):

            for i, item in enumerate(self.other_options):
                editor.addItem(item)
                font = QFont("default", italic=True)
                editor.setItemData(i, font, QtCore.Qt.FontRole)

        if optional:
            editor.addItem("")

        editor.addItems(self.available_columns)
        return editor


class SphericalMappingItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for spherical assay mapping definition

        Args:
            parent:
        """
        super().__init__(parent)
        self.available_columns = []  # Define columns available in file

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for column mapping definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available column for mapping

        """
        editor = QComboBox(parent)

        editor.addItems(self.available_columns)
        return editor


class PolarityMappingItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for column mapping definition

        Args:
            parent:
        """
        super().__init__(parent)
        self.available_columns = []  # Define columns available in file
        self.other_options = ["Reverse", "Normal", "Unknown"]

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for column mapping definition + other options

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available column for mapping

        """
        editor = QComboBox(parent)

        for index, item in enumerate(self.other_options):
            editor.addItem(item)
            font = QFont("default", italic=True)
            editor.setItemData(index, font, QtCore.Qt.FontRole)

        editor.addItems(self.available_columns)
        return editor


class SeriesTypeItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for series type definition

        Args:
            parent:
        """
        super().__init__(parent)

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for series type definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available series type

        """
        editor = QComboBox(parent)

        editor.addItems(
            [e.name for e in AssaySeriesType if e != AssaySeriesType.PLUGIN]
        )
        return editor


class CategoriesValidationType(Enum):
    APPEND = "append"  # read categories is append to current categories data
    RESTRICT = "restrict"  # read categories must be in current categories data
    REMOVE = "remove"  # data with categories not in current categories data are removed


class CategoriesValidationTypeItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for categories validation type definition

        Args:
            parent:
        """
        super().__init__(parent)

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for series type definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available series type

        """
        editor = QComboBox(parent)

        editor.addItems([e.name for e in CategoriesValidationType])
        return editor


class SphericalTypeItemDelegate(QStyledItemDelegate):
    def __init__(self, parent) -> None:
        """
        QStyledItemDelegate for spherical type definition

        Args:
            parent:
        """
        super().__init__(parent)

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for spherical type definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available spherical type

        """
        editor = QComboBox(parent)

        editor.addItems([e.name for e in SphericalType])
        return editor


class CategoriesTableItemDelegate(QStyledItemDelegate):
    def __init__(self, parent, openlogconnection: OpenLogConnection = None) -> None:
        """
        QStyledItemDelegate for categorie definition

        Args:
            parent:
        """
        super().__init__(parent)
        self.openlogconnection = openlogconnection

    def createEditor(self, parent, option, index) -> QWidget:
        """
        Create a QComboBox for series type definition

        Args:
            parent: QWidget
            option: QStyleOptionViewItem
            index: QModelIndex

        Returns: QComboBox with available series type

        """
        editor = QComboBox(parent)
        editor.setEditable(True)
        if self.openlogconnection:
            editor.addItems(
                [
                    c.name
                    for c in self.openlogconnection.get_categories_iface().get_available_categories_table()
                ]
            )
        return editor


class DelimitedTextImportWidget(QWidget):
    def __init__(
        self, parent=None, openlog_connection: OpenLogConnection = None
    ) -> None:
        """
        Widget to import panda dataframe from delimited text.

        Wanted dataframe column must be defined with set_expected_column.

        Dataframe can be retrieved with get_dataframe, dataframe column name will match column
        name defined in set_expected_column.

        User can define column mapping inside widget.

        A widget for CRS selection can be enabled with enable_crs_selection (off by default)


        Args:
            parent:
        """
        super().__init__(parent)
        self._openlog_connection = openlog_connection

        # translation
        self.tr = PlgTranslator().tr
        self.log = PlgLogger().log

        uic.loadUi(
            os.path.dirname(os.path.realpath(__file__))
            + "/delimited_text_import_widget.ui",
            self,
        )
        self.encoding_cbx.addItems(encoding_utils.ENCODING_LIST)
        self.encoding_cbx.setCurrentText("utf_8")
        self.encoding_cbx.currentIndexChanged.connect(self._update_table_and_fields)
        self.select_file_button.clicked.connect(self._select_file)
        self.delimiter_widget.delimiter_changed.connect(self._update_table_and_fields)
        self.filename_edit.editingFinished.connect(self._update_table_and_fields)
        self.enable_crs_selection(False)
        self.enable_date_format_selection(False)
        self.dateformat_selection_widget.format_changed.connect(
            self._update_table_and_fields
        )

        self._column_conversion = {}

        self.column_mapping_item_delegate = ColumnMappingItemDelegate(self)
        self.column_mapping_model = ColumnDefinitionTableModel(self)
        self.col_def_table_view.setModel(self.column_mapping_model)
        self.col_def_table_view.setItemDelegateForRow(
            self.column_mapping_model.MAPPING_ROW, self.column_mapping_item_delegate
        )
        self.col_def_table_view.setItemDelegateForRow(
            self.column_mapping_model.SERIES_TYPE_ROW, SeriesTypeItemDelegate(self)
        )
        self.col_def_table_view.horizontalHeader().setVisible(False)

        self.column_mapping_model.dataChanged.connect(self.update_result_table)

        self.add_column_button.clicked.connect(lambda: self.add_column("", ""))
        self.remove_column_button.clicked.connect(self._remove_column)
        self.remove_column_button.setEnabled(False)

        self.add_column_button.setIcon(QgsApplication.getThemeIcon("mActionAdd.svg"))
        self.remove_column_button.setIcon(
            QgsApplication.getThemeIcon("mActionRemove.svg")
        )

        self.col_def_table_view.selectionModel().selectionChanged.connect(
            self._update_button_status
        )

        # store z column mapping and value
        self._z_col_mapping = ""
        self.z_col_constant_value = None

        # SPHERICAL DATA BOX
        self.spherical_mapping_item_delegate = SphericalMappingItemDelegate(self)
        self.polarity_mapping_item_delegate = PolarityMappingItemDelegate(self)
        self.gpx_spherical_data.setVisible(False)
        self.mdl_spherical_data = SphericalDataTableModel(self)
        self.mdl_spherical_data.dataChanged.connect(self.update_result_table)
        self.spherical_table_view.setModel(self.mdl_spherical_data)
        self.spherical_table_view.setItemDelegateForColumn(
            self.mdl_spherical_data.TYPE_COL, SphericalTypeItemDelegate(self)
        )

        self.spherical_table_view.setItemDelegateForColumn(
            self.mdl_spherical_data.DIP_COL, self.spherical_mapping_item_delegate
        )
        self.spherical_table_view.setItemDelegateForColumn(
            self.mdl_spherical_data.AZIMUTH_COL, self.spherical_mapping_item_delegate
        )
        self.spherical_table_view.setItemDelegateForColumn(
            self.mdl_spherical_data.POLARITY_COL, self.polarity_mapping_item_delegate
        )

        self.btn_spherical_help.setIcon(
            QIcon(":/images/themes/default/mActionHelpContents.svg")
        )
        self.btn_spherical_help.clicked.connect(self._spherical_help_display)
        self.spherical_help_dialog = self._creation_spherical_help_dialog()

        # UNCERTAINTY GROUP BOX
        self.gpx_uncertainty.setVisible(False)
        self.mdl_assay_column_uncertainty = AssayColumnUncertaintyTableModel(self)
        self.mdl_assay_column_uncertainty.dataChanged.connect(self.update_result_table)
        self.uncertainty_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_uncertainty.UPPER_WHISKER_BOX_COLUMN,
            self.column_mapping_item_delegate,
        )
        self.uncertainty_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_uncertainty.LOWER_WHISKER_BOX_COLUMN,
            self.column_mapping_item_delegate,
        )
        self.uncertainty_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_uncertainty.UPPER_BOX_COLUMN,
            self.column_mapping_item_delegate,
        )
        self.uncertainty_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_uncertainty.LOWER_BOX_COLUMN,
            self.column_mapping_item_delegate,
        )
        self.uncertainty_table_view.setModel(self.mdl_assay_column_uncertainty)

        self.rbt_one_column.clicked.connect(
            self._update_uncertainty_table_model_columns
        )
        self.rbt_two_column.clicked.connect(
            self._update_uncertainty_table_model_columns
        )
        self.rbt_four_column.clicked.connect(
            self._update_uncertainty_table_model_columns
        )
        self._update_uncertainty_table_model_columns()
        self.btn_uncertainty_help.setIcon(
            QIcon(":/images/themes/default/mActionHelpContents.svg")
        )
        self.btn_uncertainty_help.clicked.connect(self._uncertainty_help_display)
        self.uncertainty_help_dialog = self._creation_uncertainty_help_dialog()

        # CATEGORIES GROUP BOX
        self.gpx_categories.setVisible(False)

        self.mdl_assay_column_category = AssayColumnCategoryTableModel(self)
        self.mdl_assay_column_category.dataChanged.connect(self.update_result_table)

        self.category_item_delegate = CategoriesTableItemDelegate(
            self, self._openlog_connection
        )
        self.categories_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_category.CATEGORIE_COLUMN,
            self.category_item_delegate,
        )
        self.category_validation_item_delegate = CategoriesValidationTypeItemDelegate(
            self
        )
        self.categories_table_view.setItemDelegateForColumn(
            self.mdl_assay_column_category.VALIDATION_COLUMN,
            self.category_validation_item_delegate,
        )
        self.categories_table_view.setModel(self.mdl_assay_column_category)

        # Quick fix for column type definition : must be updated when column type will be defined by user
        self.data_is_valid = True

        self.button_frame.setVisible(False)

        # DTM selection for elevation calculation
        self._x_col = ""
        self._y_col = ""
        self._z_col = ""
        self.dtm_layer_combobox.setFilters(QgsMapLayerProxyModel.RasterLayer)
        self.dtm_layer_combobox.setAllowEmptyLayer(True, self.tr("None"))
        self.enable_elevation_from_dtm(False)
        self.dtm_layer_combobox.layerChanged.connect(self._dtm_layer_changed)

    def restore_settings(self, base_key: str):
        """
        Restore settings from QgsSetting()

        Args:
            base_key: base key for QgsSettings

        """
        settings = QgsSettings()
        date_format = settings.value(base_key + DATE_FORMAT_KEY, "")
        if date_format:
            self.set_date_format(date_format)

        time_format = settings.value(base_key + TIME_FORMAT_KEY, "")
        if time_format:
            self.set_time_format(time_format)

        epsg_crs_id = settings.value(base_key + CRS_KEY, None)
        if epsg_crs_id:
            crs = QgsCoordinateReferenceSystem()
            if crs.createFromString(epsg_crs_id):
                self.mQgsProjectionSelectionWidget.setCrs(crs)
        column_list = settings.value(base_key + COLUMN_LIST_KEY)
        if column_list:
            for col in column_list:
                mapping = settings.value(base_key + COLUMN_MAPPING_KEY + f"/{col}", "")
                self.column_mapping_model.set_column_mapping(col, mapping)

        self.delimiter_widget.restore_settings(base_key)

    def save_setting(self, base_key: str):
        """
        Store settings in QgsSettings()

        Args:
            base_key: base key for QgsSettings

        """
        settings = QgsSettings()
        settings.setValue(
            base_key + DATE_FORMAT_KEY,
            self.dateformat_selection_widget.get_date_format(),
        )
        settings.setValue(
            base_key + TIME_FORMAT_KEY,
            self.dateformat_selection_widget.get_time_format(),
        )
        settings.setValue(
            base_key + CRS_KEY, self.mQgsProjectionSelectionWidget.crs().authid()
        )
        column_mapping = self.get_column_definition()
        columns_names = [x.column for x in column_mapping]
        settings.setValue(base_key + COLUMN_LIST_KEY, list(columns_names))
        for mapping in column_mapping:
            settings.setValue(
                base_key + COLUMN_MAPPING_KEY + f"/{mapping.column}", mapping.mapping
            )

        self.delimiter_widget.save_setting(base_key)

    def get_file_path(self) -> Path:
        """
        Get file path of current selected file

        Returns: (Path) current selected file

        """
        return Path(self.filename_edit.text())

    def set_column_conversion(
        self, column_conversion: {str: Callable[[str, pandas.DataFrame], pandas.Series]}
    ):
        """
        Define conversion callable for columns

        Args:
            column_conversion: map of column name associated with callable for conversion
        """
        self._column_conversion = column_conversion

    def set_column_definition(self, columns: [ColumnDefinition]) -> None:
        """
        Define dataframe expected column names

        Args:
            columns: dataframe column names
        """
        # Define column mapping
        self.column_mapping_model.dataChanged.disconnect(self.update_result_table)
        self.column_mapping_model.set_column_definition(columns)
        self.column_mapping_model.dataChanged.connect(self.update_result_table)

        # Check if datetime column is available
        self.enable_date_format_selection(
            any(x.series_type == AssaySeriesType.DATETIME for x in columns)
        )

        fixed_cols = [c.column for c in columns if c.fixed]

        # Define categories
        column_categories = []
        if self._openlog_connection:
            for col in columns:
                if col.category_name:
                    column_categories.append(
                        self._create_category_policy(col.column, col.category_name)
                    )
        self.mdl_assay_column_category.set_assay_column_categories(column_categories)
        self.mdl_assay_column_category.fixed_columns = fixed_cols
        self.gpx_categories.setVisible(True)

    def get_column_definition(self) -> [ColumnDefinition]:
        """
        Get dataframe expected column names
        """
        res = self.column_mapping_model.get_column_definition(False)

        # Add category table name
        assay_column_category = (
            self.mdl_assay_column_category.get_assay_column_categories()
        )
        for column_def in res:
            col = column_def.column
            if col in assay_column_category:
                column_def.category_name = assay_column_category[col]
        return res

    def get_assay_column_uncertainty(self) -> {str: AssayColumnUncertainty}:
        """
        Return dict of AssayColumnUncertainty

        Returns: {str: AssayColumnUncertainty}

        """
        result = self.mdl_assay_column_uncertainty.get_assay_column_uncertainty()

        # Update uncertainty column name to avoid column name duplication
        columns = self.column_mapping_model.get_column_definition(True)
        assay_columns_name = [col.column for col in columns]

        for column, uncertainty in result.items():
            if uncertainty.get_uncertainty_type() == UncertaintyType.ONE_COLUMN:
                uncertainty.upper_whisker_column = self._get_unique_column_name(
                    column, assay_columns_name, "wide"
                )
            if (
                uncertainty.get_uncertainty_type() == UncertaintyType.TWO_COLUMN
                or uncertainty.get_uncertainty_type() == UncertaintyType.FOUR_COLUMN
            ):
                uncertainty.upper_whisker_column = self._get_unique_column_name(
                    column, assay_columns_name, "outer_max"
                )
                uncertainty.lower_whisker_column = self._get_unique_column_name(
                    column, assay_columns_name, "outer_min"
                )

            if uncertainty.get_uncertainty_type() == UncertaintyType.FOUR_COLUMN:
                uncertainty.upper_box_column = self._get_unique_column_name(
                    column, assay_columns_name, "inner_max"
                )
                uncertainty.lower_box_column = self._get_unique_column_name(
                    column, assay_columns_name, "inner_min"
                )
        return result

    @staticmethod
    def _get_unique_column_name(
        init_col: str, assay_lower_columns_name_: [str], suffix: str = ""
    ) -> str:
        index_ = 1
        col = f"{init_col}_{suffix}"
        while col in assay_lower_columns_name_:
            col = f"{init_col}_{index_}"
            index_ = index_ + 1
        return col

    def get_used_category(self) -> []:
        """
        Return dict of category by column

        Returns: {str: str}

        """
        return list(
            set(self.mdl_assay_column_category.get_assay_column_categories().values())
        )

    def set_delimiter(self, delimiter: str) -> None:
        """
        Define delimiter for delimiter widget

        Args:
            delimiter: delimiter
        """
        self.delimiter_widget.set_delimiter(delimiter)

    def add_column(self, column: str = "", mapping: str = "") -> None:
        """
        Add a new column

        Args:
            column: (str) column name
            mapping: (str) column mapping

        """
        self.column_mapping_model.dataChanged.disconnect(self.update_result_table)
        self.column_mapping_model.add_column_definition(
            ColumnDefinition(
                column=column,
                mapping=mapping,
                series_type=AssaySeriesType.NUMERICAL,
                optional=True,
            )
        )
        self.column_mapping_model.dataChanged.connect(self.update_result_table)

    def set_button_layout_visible(self, visible: bool) -> None:
        """
        Change button layout visibility

        Args:
            visible: (bool) True to set button layout visible, False to set button layout invisible
        """
        self.button_frame.setVisible(visible)

    def _update_button_status(self) -> None:
        """
        Update button status from current selection. Used to define column remove enable

        """
        enable_delete = False
        for index in self.col_def_table_view.selectionModel().selectedIndexes():
            fixed = self.column_mapping_model.data(
                self.column_mapping_model.index(
                    self.column_mapping_model.COLUMN_ROW, index.column()
                ),
                QtCore.Qt.UserRole,
            )
            if not fixed:
                enable_delete = True

        self.remove_column_button.setEnabled(enable_delete)

    def _remove_column(self) -> None:
        """
        Remove column from current selection. Only not fixed column are deleted

        """
        cols = []
        for index in self.col_def_table_view.selectionModel().selectedIndexes():
            fixed = self.column_mapping_model.data(
                self.column_mapping_model.index(
                    self.column_mapping_model.COLUMN_ROW, index.column()
                ),
                QtCore.Qt.UserRole,
            )
            col = self.column_mapping_model.data(
                self.column_mapping_model.index(
                    self.column_mapping_model.COLUMN_ROW, index.column()
                )
            )
            if not fixed:
                cols.append(col)
        cols = set(cols)
        for col in cols:
            self.column_mapping_model.remove_column(col)
            self.mdl_assay_column_uncertainty.remove_column(col)
            self.mdl_assay_column_category.remove_column(col)
            self.update_result_table()

    def get_used_categories_values(self) -> {str: [str]}:
        """
        Get values imported of each category

        Returns: {str: [str]} list of values for each used category

        """
        result = {}
        df = self.get_dataframe()
        if df is not None:
            categories_map = (
                self.mdl_assay_column_category.get_assay_column_categories()
            )
            for col, category in categories_map.items():
                values = df[col].replace(to_replace="None", value=np.nan).dropna()
                if category not in result:
                    result[category] = values
                else:
                    result[category].append(values)
        return result

    def get_dataframe(
        self, nb_row: int = None, spherical: bool = False
    ) -> pandas.DataFrame:
        """
        Get panda dataframe from user selected file and column mapping

        Args:
            nb_row: read only a specific number of row (default None : all rows are read)
            spherical: if True, returns spherical columns in JSON-like format for import in database.

        Returns:
            panda dataframe with expected column

        """
        file_path = self.filename_edit.text()
        if file_path:
            columns = self.column_mapping_model.get_column_definition(True)

            # remove Elevation from columns (treated after)
            columns = [col for col in columns if col.column != self._z_col]

            # Define uncertainty mapping from model (columns from file header)
            uncertainty_columns_mapping = []
            for (
                col,
                uncertainty,
            ) in (
                self.mdl_assay_column_uncertainty.get_assay_column_uncertainty().items()
            ):
                uncertainty_columns_mapping += uncertainty.get_uncertainty_columns()

            # Define column mapping
            assay_columns_mapping = [
                col.mapping for col in columns
            ] + uncertainty_columns_mapping

            # Get uncertainty columns from class method to get updated names from assay column
            uncertainty_columns = []
            for col, uncertainty in self.get_assay_column_uncertainty().items():
                uncertainty_columns += uncertainty.get_uncertainty_columns()

            assay_columns_name = [col.column for col in columns] + uncertainty_columns

            # Get complete dataframe from file
            df = self._get_file_dataframe(file_path, nb_row, True)

            # Get selected columns
            df = df[assay_columns_mapping]

            # Rename columns with wanted name
            df.columns = assay_columns_name

            # Check that all columns are defined and date conversion
            df = self._add_wanted_columns(df)

            # Convert data with current series type
            df = self._series_type_conversion(columns, df)

            # Convert data with column conversion
            df = self._user_columns_conversion(df)

            # Update value for elevation
            df = self._update_elevation(df)

            # return true spherical data
            if spherical:

                df = self._create_spherical_data(df)

            return df
        else:
            return None

    def _create_spherical_data(self, df: pandas.DataFrame) -> pandas.DataFrame:
        """
        Create real spherical columns to import into database.
        A spherical column is in a JSON-like string format : {dip: 0, azimuth: 0, polarity:0, type:'line'}
        """
        raw_df = self._get_file_dataframe(self.filename_edit.text())
        sph_definitions = self.mdl_spherical_data.get_spherical_assay()

        # add fake columns for polarity constants
        for value, opt in enumerate(self.polarity_mapping_item_delegate.other_options):
            raw_df[opt] = value

        columns = self.get_column_definition()
        columns = [
            col for col in columns if col.series_type == AssaySeriesType.SPHERICAL
        ]
        for col in columns:
            col_name = col.column
            sph_def = sph_definitions[col_name]
            try:
                if sph_def.type_ == "LINE":

                    serie = raw_df.agg(
                        lambda x: {
                            "dip": float(x[sph_def.dip_col]),
                            "azimuth": float(x[sph_def.azimuth_col]),
                            "polarity": 0,
                            "type": "LINE",
                        },
                        axis=1,
                    )
                else:
                    serie = raw_df.agg(
                        lambda x: {
                            "dip": float(x[sph_def.dip_col]),
                            "azimuth": float(x[sph_def.azimuth_col]),
                            "polarity": float(x[sph_def.polarity_col]),
                            "type": "PLANE",
                        },
                        axis=1,
                    )

                serie = [json.dumps(elt) for elt in serie]
                # replace status message by JSON in df
                df[col_name] = serie
            except:
                df = df.drop(columns=[col_name])

        return df

    def _series_type_conversion(
        self, columns: [ColumnDefinition], df: pandas.DataFrame
    ) -> pandas.DataFrame:
        """
        Dataframe type conversion with defined columns series type

        Args:
            columns: [ColumnDefinition] column definition list
            df: input dataframe

        Returns: dataframe with column conversion

        """
        self.data_is_valid = True

        def _set_column_color_and_tooltip(col_: int, tooltip: str, color: str) -> None:
            self.column_mapping_model.dataChanged.disconnect(self.update_result_table)
            self.column_mapping_model.set_column_color_and_tooltip(
                col_, tooltip, QColor(color)
            )
            self.column_mapping_model.dataChanged.connect(self.update_result_table)

        remove_index = []
        for col_def in columns:
            col = col_def.column
            type_ = col_def.series_type.python_type()
            try:
                if col_def.series_type == AssaySeriesType.DATETIME:

                    tz = timezone(
                        timedelta(
                            hours=self.dateformat_selection_widget.get_time_offset()
                        )
                    )

                    defined_format = (
                        self.dateformat_selection_widget.get_datetime_format()
                    )
                    _format = None if defined_format == "auto" else defined_format

                    df[col] = (
                        pandas.to_datetime(
                            df[col],
                            format=_format,
                            errors="raise",
                        )
                        .dt.tz_localize(tz)
                        .dt.tz_convert("UTC")
                    )
                    df[col] = df[col].replace({pandas.NaT: None})
                elif col_def.series_type == AssaySeriesType.NOMINAL:
                    df[col] = df[col].astype(type_)
                elif col_def.series_type == AssaySeriesType.NUMERICAL:
                    df[col] = df[col].replace({"": None})
                    df[col] = df[col].astype(type_).replace({np.nan: None})
                elif col_def.series_type == AssaySeriesType.CATEGORICAL:
                    df[col] = df[col].astype(type_)
                    #  Check category values
                    categories = (
                        self.mdl_assay_column_category.get_assay_column_categories()
                    )
                    categories_table = categories[col_def.column]
                    validation = self.mdl_assay_column_category.get_assay_column_category_validation(
                        col_def.column
                    )
                    current_cat = [
                        c.name
                        for c in self._openlog_connection.get_categories_iface().get_available_categories_table()
                    ]
                    if categories_table not in current_cat:
                        values = []
                    else:
                        cat = self._openlog_connection.get_categories_iface().get_categories_table(
                            categories_table
                        )
                        values = self._openlog_connection.get_categories_iface().get_available_categories(
                            cat
                        )
                    # For now if no values available allow all values
                    if not len(values):
                        validation = CategoriesValidationType.APPEND.name
                    if validation == CategoriesValidationType.RESTRICT.name:
                        df[col] = df[col].apply(lambda x: x if x in values else None)
                    elif validation == CategoriesValidationType.REMOVE.name:
                        df[col] = df[col].apply(lambda x: x if x in values else np.nan)
                        remove_index += df[df[col].isna()].index.to_list()
                elif col_def.series_type == AssaySeriesType.IMAGERY:
                    df[col] = df[col].astype(str)

                    def _convert_path(
                        imported_file_path: Path, image_path_str: str
                    ) -> Union[Path, None]:
                        # Check if file is available
                        image_path = Path(image_path_str)
                        if not image_path.exists() and imported_file_path.exists():
                            image_path = imported_file_path.parent / image_path_str
                        if image_path.exists():
                            return str(image_path)
                        else:
                            return None

                    df[col] = df[col].apply(
                        lambda x: _convert_path(self.get_file_path(), x)
                    )

                # Remove rows with mandatory values not defined
                if not col_def.optional:
                    remove_index += df[df[col].isna()].index.to_list()

                _set_column_color_and_tooltip(col, "", "black")
            except (ValueError, pandas.errors.ParserError) as exc:
                error = self.tr(
                    f"Invalid data in {col} column. {type_} expected. {exc}"
                )
                _set_column_color_and_tooltip(col, error, "red")
                df[col] = np.nan
                self.data_is_valid = False

        df = df.drop(remove_index)
        return df

    def _update_elevation(self, df: pandas.DataFrame) -> pandas.DataFrame:
        """
        Update elevation column from selected DTM

        Args:
            df: input dataframe

        Returns: dataframe with calculated elevation column

        """
        # get mapping column
        column_def = self.column_mapping_model.get_column_definition(False)
        z_mapping = [
            col_def.mapping for col_def in column_def if col_def.column == self._z_col
        ]
        if len(z_mapping) == 0:
            return df

        z_mapping = z_mapping[0]

        df[self._z_col] = None
        if z_mapping == "From DTM":

            # Use isHidden instead of isVisible
            # Because isVisible check for current screen visibility
            dtm_layer = (
                self.dtm_layer_combobox.currentLayer()
                if not self.dtm_layer_combobox.isHidden()
                else QgsRasterLayer()
            )
            if (
                not df.empty
                and self._x_col
                and self._y_col
                and self._z_col
                and dtm_layer
                and dtm_layer.isValid()
            ):
                df[self._z_col] = df.apply(
                    lambda row: self._define_elevation(row), axis=1
                )

        elif z_mapping == "Set constant":
            # avoid pop up dialog each time
            if self._z_col_mapping != "Set constant":
                z_value, done = QInputDialog.getDouble(
                    self, "Input Dialog", "Enter Z value:"
                )

                if done:
                    df[self._z_col] = z_value
                    self.z_col_constant_value = z_value

            else:
                df[self._z_col] = self.z_col_constant_value

        elif z_mapping != "":
            file_path = self.filename_edit.text()
            raw_df = self._get_file_dataframe(file_path, df.shape[0], True)
            df[self._z_col] = raw_df[z_mapping].astype(float)

        self._z_col_mapping = z_mapping

        return df

    def _define_elevation(self, row):
        """
        Define elevation for current dataframe row from DTM layer and X/Y coordinate or available Z value

        Args:
            row: dataframe row

        Returns: Z value for dataframe row

        """
        if (
            row[self._z_col] is None
            and row[self._x_col] is not None
            and row[self._y_col] is not None
        ):
            return self._get_z_value_from_dtm(
                QgsPointXY(row[self._x_col], row[self._y_col])
            )
        else:
            return row[self._z_col]

    def _get_z_value_from_dtm(self, point: QgsPointXY) -> float:
        """
        Calculate Z value from selected DTM

        Args:
            point:  QgsPointXY input point

        Returns: z value from DTM if available, None otherwise

        """
        tr = QgsCoordinateTransform(
            self.mQgsProjectionSelectionWidget.crs(),
            self.dtm_layer_combobox.currentLayer().crs(),
            QgsProject.instance(),
        )
        point_dtm = tr.transform(point)
        z_val, res = (
            self.dtm_layer_combobox.currentLayer().dataProvider().sample(point_dtm, 1)
        )
        if res:
            z = z_val
        else:
            self.log(
                self.tr(
                    "Can't define DTM value for point : {0}/{1}. z value used is None."
                ).format(point.x(), point.y()),
                push=True,
                parent_location=self,
            )
            z = None
        return z

    def _create_spherical_preview(self, col: str) -> str:
        """
        Create status for spherical data preview.
        Args:
            col: column name

        Returns : Not defined or Valid or Values outside range or Wrong data type
        """

        # get spherical definitions
        sph_def = self.mdl_spherical_data.get_spherical_assay()[col]
        result = "Valid"
        # Not defined (missing columns in definition)
        if sph_def.type_ == "":
            result = "Not defined"
            return result

        if sph_def.type_ == "LINE":
            if sph_def.dip_col == "" or sph_def.azimuth_col == "":
                result = "Not defined"
                return result

        if sph_def.type_ == "PLANE":
            if (
                sph_def.dip_col == ""
                or sph_def.azimuth_col == ""
                or sph_def.polarity_col == ""
            ):
                result = "Not defined"
                return result

        # Wrong data type

        raw_df = self._get_file_dataframe(self.get_file_path(), None, False)

        ## fill polarity if a constant is selected
        if sph_def.polarity_col in self.polarity_mapping_item_delegate.other_options:
            raw_df[
                sph_def.polarity_col
            ] = self.polarity_mapping_item_delegate.other_options.index(
                sph_def.polarity_col
            )

        if not self._is_numeric_serie(raw_df[sph_def.dip_col]):
            result = "Wrong data type"
            return result

        if not self._is_numeric_serie(raw_df[sph_def.azimuth_col]):
            result = "Wrong data type"
            return result

        if sph_def.type_ == "PLANE":
            if not self._is_numeric_serie(raw_df[sph_def.polarity_col]):
                result = "Wrong data type"
                return result

        # Values outside range
        if (~raw_df[sph_def.dip_col].astype(float).between(-90.0, 90.0)).any():
            result = "Wrong range"
            return result
        if (~raw_df[sph_def.azimuth_col].astype(float).between(0.0, 360.0)).any():
            result = "Wrong range"
            return result
        if sph_def.type_ == "PLANE":
            if (
                ~raw_df[sph_def.polarity_col].astype(float).isin([0.0, 1.0, 2.0])
            ).any():
                result = "Wrong range"
                return result

        return result

    @staticmethod
    def _is_numeric_serie(s: pandas.Series) -> bool:
        """
        Check if a pandas.Series can be converted to numeric values.
        Args:
            s : series
        Returns:
            True if convertible, else False
        """
        try:
            s = s.astype(float)
            return True
        except ValueError:

            return False

    def _add_wanted_columns(self, df: pandas.DataFrame) -> pandas.DataFrame:
        """
        Add selected columns to dataframe if column not present

        Args:
            df: input dataframe

        Returns: dataframe with added columns if needed

        """
        all_column = self.column_mapping_model.get_column_definition()
        for mapping in all_column:
            col = mapping.column
            col_type = mapping.series_type
            if col not in df.columns:
                if col_type == AssaySeriesType.SPHERICAL:
                    df[col] = self._create_spherical_preview(col)

                else:
                    df[col] = None

        return df

    def _user_columns_conversion(self, df: pandas.DataFrame) -> pandas.DataFrame:
        """
        Convert dataframe columns with user specified conversion function

        Args:
            df: input dataframe

        Returns: dataframe with conversion

        """
        for col, conv in self._column_conversion.items():
            try:
                df[col] = conv(col, df)
            except BaseException as exc:
                QMessageBox.warning(
                    self, self.tr("Invalid column conversion"), str(exc)
                )
                df[col] = None
        return df

    def crs(self) -> QgsCoordinateReferenceSystem:
        """
        Get user selected CRS

        Returns: user selected CRS

        """
        return self.mQgsProjectionSelectionWidget.crs()

    def set_crs(self, crs: QgsCoordinateReferenceSystem) -> None:
        """
        Define selected crs

        Args:
            crs: QgsCoordinateReferenceSystem
        """
        self.mQgsProjectionSelectionWidget.setCrs(crs)

    def enable_crs_selection(self, enable: bool) -> None:
        """
        Enable or disable CRS selection

        Args:
            enable: True for CRS selection enable, False for CRS selection disable
        """
        self.crs_label.setVisible(enable)
        self.mQgsProjectionSelectionWidget.setVisible(enable)

    def enable_elevation_from_dtm(
        self, enable: bool, x_col: str = "", y_col: str = "", z_col: str = ""
    ):
        """
        Enable elevation calculation from selected DTM

        Args:
            enable: True for dtm display and elevation calculation
            x_col: (str) column containing x coordinate
            y_col: (str) column containing y coordinate
            z_col: (str) column containing z value for elevation
        """
        self.dtm_layer_combobox.setVisible(enable)
        self.dtm_label.setVisible(enable)
        self._x_col = x_col
        self._y_col = y_col
        self._z_col = z_col

    def enable_date_format_selection(self, enable: bool) -> None:
        """
        Enable or disable date format selection

        Args:
            enable: True for data format enable, False for date format disable
        """
        self.dateformat_selection_widget.setVisible(enable)

    def set_date_format(self, date_format: str) -> None:
        """
        Define date format

        Args:
            date_format: wanted date format
        """
        self.dateformat_selection_widget.set_date_format(date_format)

    def set_time_format(self, time_format: str) -> None:
        """
        Define time format

        Args:
            time_format: wanted time format
        """
        self.dateformat_selection_widget.set_time_format(time_format)

    def _select_file(self) -> None:
        file_path, filter_used = QFileDialog.getOpenFileName(
            self, "Select file", "", "(*.*)"
        )

        if file_path:
            self.filename_edit.setText(file_path)
            encoding = encoding_utils.predict_encoding(Path(file_path), 1)
            self.encoding_cbx.setCurrentText(encoding)
            self._update_table_and_fields()

    def _update_uncertainty_table_model_columns(self) -> None:
        self.uncertainty_table_view.setColumnHidden(
            self.mdl_assay_column_uncertainty.UPPER_WHISKER_BOX_COLUMN, False
        )
        self.uncertainty_table_view.setColumnHidden(
            self.mdl_assay_column_uncertainty.LOWER_WHISKER_BOX_COLUMN, False
        )
        self.uncertainty_table_view.setColumnHidden(
            self.mdl_assay_column_uncertainty.UPPER_BOX_COLUMN, False
        )
        self.uncertainty_table_view.setColumnHidden(
            self.mdl_assay_column_uncertainty.LOWER_BOX_COLUMN, False
        )

        if self.rbt_one_column.isChecked():
            self.mdl_assay_column_uncertainty.set_uncertainty_type(
                UncertaintyType.ONE_COLUMN
            )
            self.uncertainty_table_view.setColumnHidden(
                self.mdl_assay_column_uncertainty.LOWER_WHISKER_BOX_COLUMN, True
            )
            self.uncertainty_table_view.setColumnHidden(
                self.mdl_assay_column_uncertainty.UPPER_BOX_COLUMN, True
            )
            self.uncertainty_table_view.setColumnHidden(
                self.mdl_assay_column_uncertainty.LOWER_BOX_COLUMN, True
            )
        elif self.rbt_two_column.isChecked():
            self.mdl_assay_column_uncertainty.set_uncertainty_type(
                UncertaintyType.TWO_COLUMN
            )
            self.uncertainty_table_view.setColumnHidden(
                self.mdl_assay_column_uncertainty.UPPER_BOX_COLUMN, True
            )
            self.uncertainty_table_view.setColumnHidden(
                self.mdl_assay_column_uncertainty.LOWER_BOX_COLUMN, True
            )
        elif self.rbt_four_column.isChecked():
            self.mdl_assay_column_uncertainty.set_uncertainty_type(
                UncertaintyType.FOUR_COLUMN
            )
        self.uncertainty_table_view.resizeColumnsToContents()

    def _creation_uncertainty_help_dialog(self) -> QDialog:
        help_dialog = QDialog(self)
        help_dialog.setWindowTitle(self.tr("Uncertainty columns"))
        layout = QHBoxLayout()
        label = QLabel()
        pixmap = QPixmap(str(DIR_PLUGIN_ROOT / "resources" / "images" / "boxplot.png"))
        pixmap = pixmap.scaled(
            600, 600, QtCore.Qt.KeepAspectRatio, QtCore.Qt.SmoothTransformation
        )
        label.setPixmap(pixmap)
        label.setScaledContents(True)
        layout.addWidget(label)
        help_dialog.setLayout(layout)
        return help_dialog

    def _creation_spherical_help_dialog(self) -> QDialog:
        help_dialog = QDialog(self)
        help_dialog.setWindowTitle(self.tr("Spherical data definition"))
        layout = QHBoxLayout()
        label = QLabel()
        pixmap = QPixmap(
            str(DIR_PLUGIN_ROOT / "resources" / "images" / "spherical_def.png")
        )
        pixmap = pixmap.scaled(
            1100, 600, QtCore.Qt.KeepAspectRatio, QtCore.Qt.SmoothTransformation
        )
        label.setPixmap(pixmap)
        label.setScaledContents(True)
        layout.addWidget(label)
        help_dialog.setLayout(layout)
        return help_dialog

    def _spherical_help_display(self) -> None:
        self.spherical_help_dialog.show()

    def _dtm_layer_changed(self) -> None:
        self.update_result_table()

    def _uncertainty_help_display(self) -> None:
        self.uncertainty_help_dialog.show()

    def _update_table_and_fields(self) -> None:
        self._update_sample_table_and_header_combo()
        self.update_result_table()

    def _update_sample_table_and_header_combo(self) -> None:
        file_path = self.filename_edit.text()
        if file_path:
            array = self._get_file_dataframe(file_path, 100)
            self._update_table_content(self.sample_table_widget, array)
            headers = array.columns.to_list()
            self.column_mapping_item_delegate.available_columns = headers
            self.spherical_mapping_item_delegate.available_columns = headers
            self.polarity_mapping_item_delegate.available_columns = headers

            columns = self.column_mapping_model.get_column_definition()
            for mapping in columns:
                col = mapping.column
                # Automatic column mapping if header has same name as expected
                if col in headers and not mapping.mapping:
                    mapping.mapping = col

                # Check if column mapping is available
                if mapping.mapping not in headers:
                    mapping.mapping = ""

            self.column_mapping_model.dataChanged.disconnect(self.update_result_table)
            self.column_mapping_model.set_column_definition(columns)
            self.column_mapping_model.dataChanged.connect(self.update_result_table)

    def _create_category_policy(
        self, column_name: str, category: str = "", validation: str = ""
    ) -> CategoryPolicy:
        """
        Method creating default category name and validation policy.
        If these parameters are already edited by the user, we leave them as is.
        """
        combo = self.category_item_delegate.createEditor(self, None, None)
        existing_cat = [combo.itemText(i) for i in range(combo.count())]

        # if no name, propose default one
        category_name = f"{column_name}_cat" if category == "" else category

        # if no validation, propose default one
        if validation == "":
            val = CategoriesValidationType.APPEND
            if category_name in existing_cat:
                val = CategoriesValidationType.RESTRICT
            val = val.name
        else:
            val = validation

        return CategoryPolicy(
            column=column_name, category=category_name, validation=val
        )

    def update_result_table(self) -> None:
        columns = self.column_mapping_model.get_column_definition(False)
        # Check if datetime column is available
        self.enable_date_format_selection(
            any(x.series_type == AssaySeriesType.DATETIME for x in columns)
        )
        # Update column uncertainty table model
        current_column_uncertainty = (
            self.mdl_assay_column_uncertainty.get_assay_column_uncertainty()
        )
        # update spherical table model
        current_column_spherical = self.mdl_spherical_data.get_spherical_assay()

        current_column_categories = (
            self.mdl_assay_column_category.get_assay_column_categories()
        )
        column_uncertainty = {}
        column_spherical = {}
        column_categorical = {}
        column_validation = {}
        for column in columns:
            col_name = column.column
            if column.series_type == AssaySeriesType.NUMERICAL and not column.fixed:
                # Get current uncertainty if defined
                if col_name not in current_column_uncertainty:
                    column_uncertainty[col_name] = AssayColumnUncertainty()
                else:
                    column_uncertainty[col_name] = current_column_uncertainty[col_name]

            if column.series_type == AssaySeriesType.CATEGORICAL:
                if col_name not in current_column_categories:
                    column_categorical[col_name] = ""
                else:
                    column_categorical[col_name] = current_column_categories[col_name]

                column_validation[
                    col_name
                ] = self.mdl_assay_column_category.get_assay_column_category_validation(
                    col_name
                )

            if column.series_type == AssaySeriesType.SPHERICAL:
                if col_name not in current_column_spherical:
                    column_spherical[col_name] = SphericalDefinition()
                else:
                    column_spherical[col_name] = current_column_spherical[col_name]

        categories = [
            self._create_category_policy(key, cat_name, column_validation[key])
            for key, cat_name in column_categorical.items()
        ]

        # SPHERICAL DEF UPDATE
        self.mdl_spherical_data.dataChanged.disconnect(self.update_result_table)
        self.mdl_spherical_data.set_spherical_assay(column_spherical)
        self.mdl_spherical_data.dataChanged.connect(self.update_result_table)
        self.gpx_spherical_data.setVisible(len(column_spherical) != 0)

        # UNCERTAINTY UPDATE
        self.mdl_assay_column_uncertainty.dataChanged.disconnect(
            self.update_result_table
        )
        self.mdl_assay_column_uncertainty.set_assay_column_uncertainty(
            column_uncertainty
        )
        self.mdl_assay_column_uncertainty.dataChanged.connect(self.update_result_table)
        self.gpx_uncertainty.setVisible(len(column_uncertainty) != 0)

        # CATEGORY UPDATE
        self.mdl_assay_column_category.dataChanged.disconnect(self.update_result_table)
        self.mdl_assay_column_category.set_assay_column_categories(categories)

        self.mdl_assay_column_category.dataChanged.connect(self.update_result_table)
        self.gpx_categories.setVisible(len(column_categorical) != 0)

        file_path = self.filename_edit.text()
        if file_path:
            result = self.get_dataframe(100)
            self._update_table_content(self.result_table_widget, result)

    def _update_table_content(
        self, table_widget: QTableWidget, array: pandas.DataFrame
    ) -> None:
        # Dataframe columns are used as header
        headers = array.columns.to_list()
        table_widget.setColumnCount(len(headers))
        table_widget.setHorizontalHeaderLabels(headers)

        spherical_data = self.mdl_spherical_data.get_spherical_assay().keys()
        # Insert all dataframe rows
        table_widget.setRowCount(array.shape[0])
        columns = array.columns
        for j in range(0, table_widget.columnCount()):
            i = 0
            for r in array.itertuples(index=False):
                item = QTableWidgetItem(str(r[j]))
                if r[j] is None:
                    if columns[j] in ["EOH"]:
                        item.setBackground(QColor("orange"))
                    else:
                        item.setBackground(QColor("yellow"))
                elif pandas.isna(r[j]):
                    item.setBackground(QColor("red"))
                table_widget.setItem(i, j, item)

                # special case for spherical data
                if columns[j] in spherical_data:
                    if r[j] != "Valid":
                        item.setBackground(QColor("red"))
                    else:
                        item.setBackground(QColor("green"))

                i = i + 1

    def _get_file_dataframe(
        self, file_path: str, nrows: int = None, display_msgbox: bool = False
    ) -> pandas.DataFrame:
        try:
            array = pandas.read_csv(
                file_path,
                delimiter=self.delimiter_widget.get_delimiter(),
                skipinitialspace=True,
                keep_default_na=False,
                dtype=str,
                nrows=nrows,
                engine="python",
                quotechar=self.delimiter_widget.get_quote_char(),
                escapechar=self.delimiter_widget.get_escape_char(),
                encoding=self.encoding_cbx.currentText(),
            )
        except BaseException as exc:
            array = pandas.DataFrame()
            if display_msgbox:
                msgBox = QMessageBox(
                    QMessageBox.Warning,
                    self.tr("Input file read failed"),
                    self.tr(
                        "Invalid file format definition. Can't read input file. Check details for more information."
                    ),
                )
                msgBox.setDetailedText(str(exc))
                msgBox.exec()

        return array
