'''
/***************************************************************************
Name	   :  crops_model.py
Description:  Crops model class for FEWSTools plugin,
              updated from QGIS2
copyright  :  (C) 2022-2023 by FEWS
email      :  jhowton@contractor.usgs.gov
Created    :  05/12/2022
Modified   :  Date - Name - Changes Made
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
'''
import copy

from fews_tools.utilities import database_utilities
from fews_tools.models.workspace_setup_model import WorkspaceSetupModel


class CropsModel:
    '''
    Class to hold crop table record information, methods
    crops_attributes = [
            'cropName', 'F1', 'F2', 'F3', 'K1', 'K2',
            'K3', 'Root', 'RootIni', 'KP', 'C1', 'C2',
            'DefaultCrop', 'Comments']
    '''

    def __init__(self):
        self.crops_table_name = 'Crops'
        self.crop_fields_dic = None
        initial_default_crop = self.query_default_crop_name()
        self.query_named_crop(initial_default_crop)
        self.wrksp_setup = WorkspaceSetupModel()

    def get_all_crop_names(self):
        '''
        Extract crop names from crops table. Used to fill
        crop combo box when loading form.
        '''
        sql = 'SELECT * FROM ' + self.crops_table_name
        qry_result = database_utilities.query_db(sql, None, True)
        crop_list = []
        for row in qry_result:
            crop_list.append(row[0])
        return crop_list

    def query_named_crop(self, ds_name):
        '''
        Extracts crop information from database and loads it into the class
        variables.
        :param ds_name(string): Name of crop of interest.
        :return err(boolean): True if not found, else false
        '''
        err = True
        sql = ("SELECT * FROM " + self.crops_table_name +
               " WHERE CropName=?")
        params = (str(ds_name), )
        qry_result = database_utilities.query_db(sql, params, False)
        if qry_result:  # update the dictionary with the returned query
            self.crop_fields_dic = {
                'CropName': qry_result[0],
                'F1': qry_result[1],
                'F2': qry_result[2],
                'F3': qry_result[3],
                'K1': qry_result[4],
                'K2': qry_result[5],
                'K3': qry_result[6],
                'Root': qry_result[7],
                'RootIni': qry_result[8],
                'KP': qry_result[9],
                'C1': qry_result[10],
                'C2': qry_result[11],
                'DefaultCrop': qry_result[12],
                'Comments': qry_result[13]}

            err = False

        return err

    def get_crop_dictionary(self):
        '''
        Return dataset dictionary
        '''
        return self.crop_fields_dic

    def write_crop_record(self, widget_vals):
        '''
        Writes or updates a row in the crops table.
        :params
            widget_vals(list): The widget values from form in this order.
               'CropName', 'F1', 'F2', 'F3', 'K1', 'K2',
               'K3', 'Root', 'RootIni', 'KP', 'C1', 'C2',
               'DefaultCrop', 'Comments'
        '''
        err = self.query_named_crop(widget_vals[0])
        loc_widget_vals = copy.deepcopy(widget_vals)

        # If the crop already exists in the database
        if err is False:
            loc_widget_vals.append(loc_widget_vals.pop(0))
            sql_write =\
                ("UPDATE " + self.crops_table_name +
                 " SET F1=?,"
                 "F2=?,F3=?,K1=?,K2=?,"
                 "K3=?,Root=?,RootIni=?,"
                 "KP=?,C1=?,C2=?,"
                 "DefaultCrop=?, Comments=? WHERE CropName = ?")

        # If the crop doesn't exist in the database
        else:
            sql_write =\
                ("INSERT INTO " + self.crops_table_name +
                 "(CropName, F1, F2, F3, K1, K2,"
                 "K3, Root, RootIni, KP, C1, C2,"
                 "DefaultCrop, Comments)"
                 " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")

        params = tuple(loc_widget_vals)
        database_utilities.write_db(sql_write, params)

        # update the default crop if necessary
        if widget_vals[-2] == 'Yes':
            self.unset_default_flag_on_non_selected_crops(widget_vals[0])

    def delete_crop_record(self, crop_name):
        '''
        Deletes selected crop
        :param ds_name(string): Name of crop to delete
        '''
        sql = ('DELETE FROM ' + self.crops_table_name +
               ' WHERE CropName = ?')
        params = (crop_name, )
        database_utilities.write_db(sql, params)

    def query_default_crop_name(self):
        '''
        Extracts default crop name and returns it
        If none are marked as default it returns the first crop record
        :return ret_val(string): Default crop name
        '''
        sql = ("SELECT * FROM " + self.crops_table_name +
               " WHERE DefaultCrop = 'Yes'")
        qry_result = database_utilities.query_db(sql)
        if qry_result:
            ret_val = qry_result[0]
        else:
            # get the first crop in the list
            ds_name_list = self.get_all_crop_names()
            ret_val = ds_name_list[0]
        return ret_val

    def unset_default_flag_on_non_selected_crops(self, default_crop_name):
        '''
        Update the default crop.
        :params default_crop_name(string): The new default crop name.
        '''
        # Get default crop's data type
        self.query_named_crop(default_crop_name)
        # Update dataset
        params = (default_crop_name,)
        sql_write = \
            ("UPDATE " + self.crops_table_name +
             " SET DefaultCrop = 'No' WHERE CropName != ? ")
        database_utilities.write_db(sql_write, params)

        sql_write = \
            ("UPDATE " + self.crops_table_name +
             " SET DefaultCrop = 'Yes' WHERE CropName = ?")
        database_utilities.write_db(sql_write, params)
