ody_migr_members.py

#!/usr/bin/env python
"""Main module to migrate members data and history."""


import os
import logging
import time
import math
import gzip
import csv
from psycopg2.extras import DictCursor
from copy import deepcopy

# import only necessary variables for settings
from ody_migr_config import (INSERT,
                             INSERT_ONE,
                             SELECT,
                             SELECT_RETURN,
                             DELETE,
                             SUMMARY,
                             TABLE_EXISTS,
                             KEY_MEMBERS,
                             DATA_OPT_MEMBERS,
                             DATA_OPT_SWITCH_ACCT_MEMBERS,
                             DATA_OPT_MEMDATA,
                             DATA_OPT_SWITCH_ACCOUNTS,
                             MEMBERS_PAGE_SIZE,
                             USERID_CROSS_REF_CSV_FILE,
                             MAMMOTH_ORPHANED_MEMLIST_CSV_FILE,
                             MAMMOTH_NEVER_LOGGEDIN_MEMLIST_CSV_FILE,
                             SCHEDULED_TRANSFER_OUTLIERS_CSV_FILE,
                             ADMIN_CUSURVEY_ID_MAP_CSV_FILE,
                             SWITCH_ACC_BATCH_SIZE,
                             SCHEDULE_INTERVAL_MAPPING,
                             DATA_OPT_MEMHIST,
                             DATA_OPT_CREATE_HIST,
                             DATA_OPT_GET_HIST,
                             HIST_CREATE_WAIT_TIME,
                             MEMHIST_DOWNLOADED_FILE,
                             MEMHIST_ACCOUNT_HISTORY_FILENAME,
                             MEMHIST_LOAN_HISTORY_FILENAME,
                             MEMBER_ACC_RIGHTS,
                             DEF_MEMACCT_RIGHTS_OPTIONS,
                             ACTION_OPT_CLEAN,
                             ACTION_OPT_MIGRATE,
                             ACTION_OPT_SUMMARY,
                             CU2_SPEC18,
                             AUDITUSER_DESCMAP
                             )

from ody_migr_transaction import PgTransaction
from ody_migr_transaction import pg_crsr_hndlr_decrtr
import ody_migr_db_handler as pg_handler
from ody_migr_mmth_endpoint import MammothMigration
from ody_migr_utils import (get_valid_json,
                            get_ith_batch,
                            get_strip_value_dict,
                            file_exc_decorator,
                            generate_mdx_hash)
from ody_migr_loans import cleanup_loanapps

LOGGER = logging.getLogger(__name__)


@pg_crsr_hndlr_decrtr
def cuuser_main_handler(members_batch_transaction,
                        ody_uname,
                        mmth_uname_also_ody_mem_accnum,
                        verbose,
                        cuuser_acc_record,
                        cuquestselect_records,
                        forceremain_update_list,
                        _cu):
    """Populate cuusers table from Mammoth and populate related tables in Ody

    This method is a part of memdata data category.

    Args:
        members_batch_transaction:      PgTransaction instance to prepare
                                        insertion transaction for the current
                                        batch of members
        ody_uname:                      odyssey user_name
        mmth_uname_also_ody_mem_accnum: mammoth user_name, and odysseys acc num
        verbose:                        verbosity flag
        cuuser_acc_record:              cuusers response dict from Mammoth
                                        1 insertion in <cu>user in odyssey also
                                        includes 1 record insertion in
                                        <cu>memberacct, <cu>user, <cu>group,
                                        <cu>usercontact tables and 4 records
                                        in <cu>memberaccrights table.
        cuquestselect_records:          records from this table are used to
                                        prepare mfaquest column in <cu>user
        forceremain_update_list:        list to collect memebrs whose
                                        forceremain is updated
        _cu:                            current cu code

    Returns:
        user_id:                        returning user_id from <cu>user
        member_acct_balance_attempt:    to be populated in other related tables
        member_acct_balance_stamp:      to be populated in other related tables

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors

    """
    cu_lower = _cu.lower()
    assert type(cuquestselect_records) is list
    assert type(cuuser_acc_record) is dict

    cuuser_returning_col = "user_id"

    if len(cuuser_acc_record) == 0:
        return

    mfaquest = get_valid_json({})  # default

    mfaquest_dict = {"answers": {}}
    if len(cuquestselect_records) != 0:
        for cuquest_record in cuquestselect_records:
            qid = cuquest_record["quest_id"].strip()
            ans = cuquest_record["answer"].strip()
            mfaquest_dict["answers"][qid] = ans
    mfaquest = get_valid_json(mfaquest_dict)

    # table names related to <cu>user
    member_acct_tbl_name = "{}memberacct".format(cu_lower)
    member_acct_rights_tbl_name = "{}memberacctrights".format(cu_lower)
    cuuser_tbl_name = "{}user".format(cu_lower)
    group_table_name = "{}group".format(cu_lower)
    profile_tbl_name = "cu_profile"
    contact_tbl_name = "{}usercontact".format(cu_lower)

    # <cu>group columns
    cu_group_columns = ["group_name", "profile_id", "contact", "tax_id"]

    # new user_name in odyssey
    cuuser_acc_record["user_name"] = ody_uname
    # move pktattempt column to pkattempt
    cuuser_acc_record["pkattempt"] = get_strip_value_dict(
        "pktattempt", cuuser_acc_record, pop=True)

    # prepare <cu>mermberacct record
    # values from mammoth's cuusers that go to the <cu>memberacct
    member_acct_balance_attempt = get_strip_value_dict(
        "pkattempt", cuuser_acc_record, default=0)
    member_acct_balance_stamp = get_strip_value_dict(
        "pktstamp", cuuser_acc_record, pop=True)
    member_acct_billpayid = get_strip_value_dict(
        "billpayid", cuuser_acc_record, pop=True)
    member_acct_estmt_flag = get_strip_value_dict(
        "estmt_flag", cuuser_acc_record, pop=True, default='N')
    member_acct_rdcsetting = int(float(get_strip_value_dict(
        "depositlimit", cuuser_acc_record, pop=True, default=0)))

    # get profile info
    profile_where_conditions = {
        "profile_code": "DEF",
        "cu": _cu
    }
    profile_select_columns = ["profile_id"]
    # get profile_id for this CU for "DEF" profile code
    cu_profile_records = members_batch_transaction(
        SELECT_RETURN,
        profile_tbl_name,
        where_conditions=profile_where_conditions,
        select_columns=profile_select_columns
    )

    assert len(cu_profile_records) == 1
    profile_id = cu_profile_records[0][0]

    # <cu>group values to be inserted
    cu_group_values = [
        "g{}".format(ody_uname),
        profile_id,
        0,
        None
    ]

    # insert <cu>group and return group_id
    members_batch_transaction(
        INSERT_ONE,
        group_table_name,
        cu_group_columns,
        cu_group_values,
        returning_col="group_id"
    )
    # <cu>group.group_id [Populate <cu>group and cu_profile tables first
    group_id = members_batch_transaction.cur.fetchone()[0]

    # <cu>usercontact table columns
    contact_columns = [
        "address1",
        "address2",
        "city",
        "state",
        "zip",
        "phones"
    ]

    # <cu>usercontact record
    contact_values = ["", "", "", "", "", ""]

    # insert <cu>usercontact record and return contact_id
    members_batch_transaction(
        INSERT_ONE,
        contact_tbl_name,
        contact_columns,
        contact_values,
        returning_col="contact_id"
    )

    # empty record with <cu>usercontact and use contact_id
    contact = members_batch_transaction.cur.fetchone()[0]

    # finally prepare <cu>user record
    cuuser_acc_record["mfaquest"] = mfaquest
    cuuser_acc_record["group_id"] = group_id
    # cuuser_rec["primary_account"] = primary_account
    cuuser_acc_record["other_rights"] = None
    cuuser_acc_record["ip"] = None
    cuuser_acc_record["is_group_primary"] = True  # always true
    cuuser_acc_record["contact"] = contact

    cuuser_forceremain = get_strip_value_dict("forceremain", cuuser_acc_record)
    cuuser_forcechange = get_strip_value_dict("forcechange", cuuser_acc_record)

    # update forceremain to 5  to unlock users with
    # forcechange == 'N' and forceremain == 0
    if cuuser_forcechange is not None and cuuser_forceremain is not None:
        if cuuser_forcechange == 'N' and int(cuuser_forceremain) == 0:
            cuuser_acc_record["forceremain"] = 5
            forceremain_update_list.append(mmth_uname_also_ody_mem_accnum)

    del cuuser_acc_record["pktdate"]
    del cuuser_acc_record["cu"]
    del cuuser_acc_record["user_alias"]

    assert "user_alias" not in cuuser_acc_record
    assert "cu" not in cuuser_acc_record
    assert "pktdate" not in cuuser_acc_record
    assert "pktstamp" not in cuuser_acc_record
    assert "billpayid" not in cuuser_acc_record
    assert "estmt_flag" not in cuuser_acc_record

    columns = list(cuuser_acc_record.keys())
    values = list(cuuser_acc_record.values())
    values = [val.strip() if type(val) == str else val for val in values]

    # populate <cu>user table and return user_id
    members_batch_transaction(
        INSERT_ONE,
        cuuser_tbl_name,
        columns,
        values,
        returning_col=cuuser_returning_col
    )

    user_id = members_batch_transaction.cur.fetchone()[0]

    # prepare <cu>memberaccount table record
    # <cu>memberaccount table columns
    member_accnt_columns = [
        "accountnumber",
        "estmnt_flag",
        "billpayid",
        "rdcsetting",
        # "restrictions",
        "primary_user",
        "balance_stamp",
        "balance_attempt",
        "allowenroll"
    ]

    # <cu>memberaccount table record
    member_accnt_record = [
        mmth_uname_also_ody_mem_accnum,
        member_acct_estmt_flag,
        member_acct_billpayid,
        member_acct_rdcsetting,
        # "",
        user_id,
        member_acct_balance_stamp,
        member_acct_balance_attempt,
        False
    ]

    # print currently inserted <cu>user record if verbose
    if verbose:
        members_batch_transaction(
            SELECT,
            cuuser_tbl_name,
            where_conditions={
                "user_id": user_id
            }
        )

    assert len(member_accnt_columns) == len(member_accnt_record)

    # populate <cu>memberaccount table
    members_batch_transaction(
        INSERT_ONE,
        member_acct_tbl_name,
        member_accnt_columns,
        member_accnt_record
    )

    # prepare <cu>memberaccountrights table
    memberacctrights_cols = [
        "user_id",
        "accountnumber",
        "whichright",
        "allowed",
        "platform"
    ]

    # prepare <cu>memberacctrights records
    for memberacctright in MEMBER_ACC_RIGHTS:
        memberacct_rec = []
        platform = get_valid_json(DEF_MEMACCT_RIGHTS_OPTIONS)
        if memberacctright == "ACCESS":
            platform = None

        memberacct_rec = [
            user_id,
            mmth_uname_also_ody_mem_accnum,
            memberacctright,
            True,
            platform
        ]
        # populate <cu>memberaccountrights table
        members_batch_transaction(
            INSERT_ONE,
            member_acct_rights_tbl_name,
            memberacctrights_cols,
            memberacct_rec
        )

    # return user_id, balance_attempt and balance_stamp to be used in other
    # related tables of the same member in a same transaction
    return (user_id,
            member_acct_balance_attempt,
            member_acct_balance_stamp)


def cu_alert_table_transform(user_id, alrt_type_code, alrt_record):
    """Prepares a single cu_alerts record for all 4 alert type codes

    Update (inplace) alrt_record keys to match with the current schema of
    cu_alerts table in Odyssey.

    Args:
        user_id:            returning user_id from <cu>user table
        alert_type_code:    {'C': checking, 'L': loan, 'T': transaction,
                            'B': balance}
        alrt_record:        dictionary of a single record from one of the
                            {cualertcheck, cualertbal, cualertloan,
                            cualerttrans} tables from Mammoth

    """
    alrt_record.pop('id', None)

    assert "cu" in alrt_record
    assert "accountnumber" in alrt_record

    if alrt_type_code == 'L':
        if "loannumber" in alrt_record:
            alrt_record["accounttype"] = alrt_record.pop("loannumber")
        if "alert_days_prior" in alrt_record:
            alrt_record["notifyloandaysprior"] = alrt_record.pop(
                "alert_days_prior")

    alrt_record["user_id"] = user_id
    alrt_record["alerttype"] = alrt_type_code

    alrt_record["certnumber"] = alrt_record.get(
        "certnumber", None)  # 0

    alrt_record["incbal"] = alrt_record.get("incbal", None)  # 0
    alrt_record["incamt"] = alrt_record.get("incamt", None)  # 0
    alrt_record["inctransdesc"] = alrt_record.get(
        "inctransdesc", None)  # None

    YesNoMap = {
        "Y": 1,
        "N": 0
    }

    for col in ["useavailbal", "notifyrange"]:
        Y_or_N = get_strip_value_dict(
            col, alrt_record, default=None)

        if Y_or_N in [None, '']:
            alrt_record[col] = None
        else:
            assert Y_or_N in YesNoMap
            alrt_record[col] = YesNoMap[Y_or_N]


@pg_crsr_hndlr_decrtr
def cualerts_handler(members_batch_transaction,
                     _verbose,
                     _cualert_check_records,
                     _cualert_bal_records,
                     _cualert_loan_records,
                     _cualert_trans_records,
                     user_id):
    """Populate updated records in cu_alerts table in Odyssey

    Associated tables from Mammoth are cualertcheck, cualertbal, cualertloan
    and cualerttrans

    Args:
        members_batch_transaction:  PgTransaction instance to prepare
                                    insertion transaction for the current
                                    batch of members
        _verbose:                   verbosity flag
        _cualert_check_records:     list of dictionaries: cualertcheck records
        _cualert_bal_records:       list of dictionaries: cualertbal records
        _cualert_loan_records:      list of dictionaries: cualertloan records
        _cualert_trans_records:     list of dictionaries: cualerttrans records
        user_id:                    returning user_id from <cu>user table

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    alert_types = {
        'C': _cualert_check_records,
        'L': _cualert_loan_records,
        'T': _cualert_trans_records,
        'B': _cualert_bal_records
    }
    for alrt_type_code in alert_types.keys():
        clbt_alert_records = alert_types[alrt_type_code]
        for alrt_record in clbt_alert_records:
            cu_alert_table_transform(user_id, alrt_type_code, alrt_record)

        # insert the whole  C or L or B or T collection to the table
        members_batch_transaction(
            INSERT,
            "cu_alerts",
            collection=clbt_alert_records
        )


def prepare_money_desktop_xref(_cu,
                               user_id,
                               mmth_uname_also_ody_mem_accnum,
                               _mmth_accnum_to_ody_userid_ref_csv_writer,
                               mdx_record,
                               acc_or_loan,
                               mdx_cc_type18_flag=False):
    """Prepare a MDX complying CSV record

    Applies to all associated subaccounts(loanbalance and accountbalance
    records) of each member.
    """
    xref_record = []
    # old_user_id
    xref_record.append("{}:{}".format(_cu, mmth_uname_also_ody_mem_accnum))
    # new_user_id
    xref_record.append("{}-{}".format(_cu, user_id))
    # new_member_id
    xref_record.append("M-{}-{}".format(_cu, user_id))
    # old_member_id
    xref_record.append("M-{}:{}".format(_cu, mmth_uname_also_ody_mem_accnum))

    # 1. handle accountbalance useracconts i.e. acc_or_loan == 'A'
    if (acc_or_loan == "A"):
        deposittype = get_strip_value_dict("deposittype", mdx_record).upper()
        certnum = get_strip_value_dict("certnumber", mdx_record)
        accounttype = get_strip_value_dict("accounttype", mdx_record)

        # account_type
        if deposittype == 'Y':
            xref_record.append('CHECKING')
        else:
            xref_record.append('SAVINGS')

        # acctid
        if certnum == 0:
            xref_record.append(accounttype)
        else:
            xref_record.append("{}_{}".format(accounttype, certnum))

        # key
        acc_key = "D|{}|{}|{}".format(mmth_uname_also_ody_mem_accnum,
                                      accounttype,
                                      certnum)
        xref_record.append(acc_key)
        # newid
        xref_record.append(generate_mdx_hash(_cu, acc_key))
        # add a csv record
        _mmth_accnum_to_ody_userid_ref_csv_writer.writerow(xref_record)

    # 2. handle loanbalance useraccounts i.e. acc_or_loan == 'L'
    else:
        cbtype = mdx_record["cbtype"]
        loannumber = get_strip_value_dict("loannumber", mdx_record)

        # if credit card type 18 handling flag is on, identify such record
        # as CREDIT_CARD type
        if mdx_cc_type18_flag:
            if cbtype == '18':
                cc_key = "C|{}|{}".format(mmth_uname_also_ody_mem_accnum,
                                         loannumber)
                xref_record.extend(
                    [
                        'CREDIT_CARD', # account_type
                        loannumber, # acctid
                        cc_key, # key
                        generate_mdx_hash(_cu, cc_key) # newid
                    ]
                )
                # add a csv record
                _mmth_accnum_to_ody_userid_ref_csv_writer.writerow(xref_record)

        # include loans
        else:
            if cbtype != '18' or cbtype is None:
                loan_key = "L|{}|{}".format(mmth_uname_also_ody_mem_accnum,
                                            loannumber)
                xref_record.extend(
                    [
                        'LOAN', # account_type
                        loannumber, # acctid
                        loan_key, # key
                        generate_mdx_hash(_cu, loan_key) # newid
                    ]
                )
                # add a csv record
                _mmth_accnum_to_ody_userid_ref_csv_writer.writerow(xref_record)


def handle_overloaded_accounts(from_or_to):
    """Update from/to format for overloaded accounts

    Args: from_or_to

        Examples: To:
        "L|XXXXX|LN06:01@YYYYY" **overloaded
        "L|XXXXX|LN06:01"
        "L|XXXXX|LN06:01|0"

        Examples: From:
        "D|XXXXX|SAV0:00@YYYYY" **overloaded
        "D|XXXXX|SAV0:00"
        "D|XXXXX|SAV0:00|0"

    Returns: updated value for from_or_to ("from" or "to" attribute
                in cu_scheduledtxn.txn_data)
        Examples
        "L|XXXXX|LN06:01@YYYYY" ---> "L|YYYYY|LN06:01" **overloaded
        "L|XXXXX|LN06:01" ---> "L|XXXXX|LN06:01"
        "L|XXXXX|LN06:01|0" ---> "L|XXXXX|LN06:01|0"

        "D|XXXXX|SAV0:00@YYYYY" ---> "D|YYYYY|SAV0:00" **overloaded
        "D|XXXXX|SAV0:00" ---> "D|XXXXX|SAV0:00"
        "D|XXXXX|SAV0:00|0" ---> "D|XXXXX|SAV0:00|0"
    """
    elems = from_or_to.split("|")
    assert len(elems) >= 3

    if "@" in elems[2]:
        from_to_subaccount, overloaded_accountnumber = elems[2].split("@")
        elems[1] = overloaded_accountnumber
        elems[2] = from_to_subaccount

    return "|".join(elems)


@pg_crsr_hndlr_decrtr
def migrate_individual_memdata(members_batch_transaction,
                               member_data,
                               ody_uname,
                               mmth_uname_also_ody_mem_accnum,
                               _verbose,
                               _cu,
                               _mmth_accnum_to_ody_userid_ref_csv_writer,
                               _mmth_repeating_transfers_outliers,
                               forceremain_update_list,
                               cusurveymaster_surveyid_map,
                               money_desktop_credit_card_type18_flag):
    """Migrate member data tables of individual member"""
    cu_lower = _cu.lower()

    # handle cuusers, memberaccnt and related tables
    (user_id,
     memacct_balance_attempt,
     member_acct_balance_stamp) = cuuser_main_handler(
        members_batch_transaction,
        ody_uname,
        mmth_uname_also_ody_mem_accnum,
        _verbose,
        member_data["cuusers"],
        member_data["cuquestselect"],
        forceremain_update_list,
        _cu
    )

    # handle cualerts
    cualerts_handler(
        members_batch_transaction,
        _verbose,
        member_data["cualertcheck"],
        member_data["cualertbal"],
        member_data["cualertloan"],
        member_data["cualerttrans"],
        user_id
    )

    # handle tables with no schema change
    mmth_tbls_no_schema_change = [
        "holds",
        "culogtrack"
    ]
    for mmth_key in mmth_tbls_no_schema_change:
        mmth_data_nsc = member_data[mmth_key]

        if mmth_key == "holds":
            ody_table_name = "{}{}".format(
                cu_lower, mmth_key)
        else:
            ody_table_name = mmth_key

        members_batch_transaction(
            INSERT,
            ody_table_name,
            collection=mmth_data_nsc
        )

    # handle tables with odyssey's user_id as relation
    # curepeattx also falls in this category if need be

    # handle cuadmeco separately: If we import two CU's from different produ-
    # ction databases (eg. IDADIV from www6 and SNOCOPE from www3), there is
    # a possibility that the message id's will be duplicated. We reassign
    # messageid's and also update the parentid's for each record.
    tbl_cuadmeco = "cuadmeco"
    tbl_cuadmeco_mmth_data = member_data[tbl_cuadmeco]

    if type(tbl_cuadmeco_mmth_data) == dict:
        for mmth_msg_id, cuadmeco_message_records in\
                tbl_cuadmeco_mmth_data.items():
            members_batch_transaction.cur.execute(
                "select nextval('cuadmeco_messageid_seq')")
            next_ody_message_id = members_batch_transaction.cur.fetchone()[0]
            this_message_thread_collection = []
            for ind, rec in enumerate(cuadmeco_message_records):
                # first record is always a parent of the message thread
                if ind == 0:
                    rec["messageid"] = next_ody_message_id
                else:
                    # all other records are threads and we let psycopg2 pick
                    # the messageid itself
                    del rec["messageid"]
                # for all records, we UPDATE the parentid
                rec["parentid"] = next_ody_message_id

                rec["user_id"] = user_id
                del rec["accountnumber"]
                rec["admin"] = cu_lower
                this_message_thread_collection.append(rec)
            members_batch_transaction(
                INSERT,
                tbl_cuadmeco,
                collection=this_message_thread_collection
            )

    for cusurveysays_rec in member_data["cusurveysays"]:
        assert "surveyid" in cusurveysays_rec, "cusurveysays: missing surveyid"
        # invalid surveyid: not existing in cusurveymaster
        if cusurveysays_rec["surveyid"].strip() \
           not in cusurveymaster_surveyid_map:
            LOGGER.warning("Invalid surveyid found: {}".format(
                cusurveysays_rec["surveyid"].strip()))
        # valid surveyid
        else:
            cusurveysays_rec["surveyid"] = cusurveymaster_surveyid_map[
                cusurveysays_rec["surveyid"].strip()]
            assert (cusurveysays_rec["accountnumber"].strip() ==
                    mmth_uname_also_ody_mem_accnum)
            cusurveysays_rec["user_id"] = user_id
            members_batch_transaction(
                INSERT_ONE,
                "cusurveysays",
                list(cusurveysays_rec.keys()),
                list(cusurveysays_rec.values())
            )

    mmth_tbls_userid_relation = ["cucmsresponse",  "userlogins"]

    for mmth_key in mmth_tbls_userid_relation:
        mmth_data_with_userid = member_data[mmth_key]

        if mmth_key == "userlogins":
            ody_table_name = "{}{}".format(
                cu_lower, mmth_key)
        else:
            ody_table_name = mmth_key

        for mmth_rec in mmth_data_with_userid:
            assert (mmth_rec["accountnumber"].strip() ==
                    mmth_uname_also_ody_mem_accnum)
            mmth_rec["user_id"] = user_id

            if mmth_key == "userlogins":
                del mmth_rec["accountnumber"]
                del mmth_rec["userloginid"]
                mmth_rec["user_name"] = ody_uname

        members_batch_transaction(
            INSERT,
            ody_table_name,
            collection=mmth_data_with_userid
        )

    # these columns and the following default values are being used to populate
    # records in <cu>useraccounts table and are associated with the records in
    # (a) cross account records coming from culivetx (b) <cu>accountbalance
    # and (c) <cu>loanbalance tables.
    # fields for <cu>useraccounts table
    cu_useraccounts_cols = [
        "user_id", "accountnumber",
        "accounttype", "certnumber",
        "recordtype", "display_name",
        "view_balances",
        "view_transactions",
        "int_deposit",
        "int_withdraw",
        "ext_deposit",
        "ext_withdraw",
        "display_order"
    ]
    # (order of columns and values mentioned below must match)
    # default values to be used for columns from the end of the list
    # cu_useraccounts_cols i.e. ("view_balances", "view_transactions",
    # "int_deposit", "int_withdraw", "ext_deposit", "ext_withdraw",
    # "display_order") in order.
    cuuseraccounts_rec_default_crossaccts = [
        False, False, True, False, False, False, 0]

    culivetx_records_for_crossaccts = member_data["culivetx_x"]
    # x signifies related to crossaccounts (and useraccounts) for records
    # coming from culivetx
    for mmth_culivetx_x in culivetx_records_for_crossaccts:
        del mmth_culivetx_x["id"]
        del mmth_culivetx_x["cu"]

        acc_type_x = get_strip_value_dict("accounttype", mmth_culivetx_x)
        deposit_type_x = get_strip_value_dict("deposittype", mmth_culivetx_x)
        accnum_x = get_strip_value_dict("accountnumber", mmth_culivetx_x)
        tomember_x = get_strip_value_dict("tomember", mmth_culivetx_x)
        description_x = get_strip_value_dict("description", mmth_culivetx_x)
        # Now also prepare values to insert in <cu>useraccounts table as well
        useraccounts_rec_from_culivetx = []
        # following append order must align with the order of columns in
        # cu_useraccounts_cols
        # append user_id for useraccounts
        useraccounts_rec_from_culivetx.append(user_id)
        # append accountnumber for useraccounts
        useraccounts_rec_from_culivetx.append(accnum_x)
        # append accounttype for useraccounts
        useraccounts_rec_from_culivetx.append("{}#{}".format(
            acc_type_x, tomember_x))
        # append certnumber for useraccounts
        useraccounts_rec_from_culivetx.append(0)
        # append recordtype for useraccounts
        if deposit_type_x.upper() in ['S', 'Y', 'N', 'C']:
            useraccounts_rec_from_culivetx.append("T")
        else:
            useraccounts_rec_from_culivetx.append("P")
        # append display_name for useraccounts
        useraccounts_rec_from_culivetx.append(description_x)
        # append remaining column default values for useraccounts
        useraccounts_rec_from_culivetx.extend(
            cuuseraccounts_rec_default_crossaccts)
        # insert a single useraccounts record based on culivetx crossaccount
        # records
        members_batch_transaction(
            INSERT_ONE,
            "{}useraccounts".format(cu_lower),
            cu_useraccounts_cols,
            useraccounts_rec_from_culivetx
        )

    members_batch_transaction(
        INSERT,
        "{}crossaccounts".format(cu_lower),
        collection=culivetx_records_for_crossaccts
    )

    # add two columns to specify additional default values for records
    # coming from <cu>accountbalance and <cu>loanbalance
    cu_useraccounts_cols.append("display_qty")
    cu_useraccounts_cols.append("display_qty_type")
    # (order of columns and values mentioned below must match)
    # default values to be used for columns from the end of the list
    # cu_useraccounts_cols i.e. ("display_name", "view_balances",
    # "view_transactions", "int_deposit", "int_withdraw", "ext_deposit",
    # "ext_withdraw", "display_order", "display_qty", "display_qty_type")
    # in order.
    # Difference: Records (to be inserted as normal records to
    # useraccounts) that come from loanbalance and accountbalance have
    # extra info for display_name, display_qty and display_qty_type columns
    # than the records that come from crossaccounts (records from culivetx
    # in Mammoth with specific conditions).
    cuuseraccounts_rec_default_ln_bal = [
        None, True, True, True, True, True, True, 0, 30, "D"]

    # <cu>accountbalance table [similar schema in Mammoth and
    # also in Odyssey for Live and Batch CUs]
    account_balance_coll = member_data["accountbalance"]
    for accbal_rec in account_balance_coll:
        acc_type = get_strip_value_dict("accounttype", accbal_rec)
        deposit_type = get_strip_value_dict("deposittype", accbal_rec)
        accbal_accnum = get_strip_value_dict("accountnumber", accbal_rec)
        accbal_certnum = get_strip_value_dict("certnumber", accbal_rec)

        assert acc_type is not None
        assert accbal_accnum is not None
        assert accbal_accnum == mmth_uname_also_ody_mem_accnum

        may_deposit = False
        may_withdraw = False
        if deposit_type.upper() in ['Y', 'N', 'S']:
            may_deposit = True
            may_withdraw = True
        accbal_rec["interestrate"] = None
        accbal_rec["maturitydate"] = None
        accbal_rec["misc1"] = None
        accbal_rec["regdcount"] = None
        accbal_rec["may_deposit"] = may_deposit
        accbal_rec["may_withdraw"] = may_withdraw
        accbal_rec["balance_stamp"] = member_acct_balance_stamp
        accbal_rec["history_stamp"] = member_acct_balance_stamp
        accbal_rec["balance_attempt"] = memacct_balance_attempt
        accbal_rec["history_attempt"] = memacct_balance_attempt
        # Now also prepare values to insert in <cu>useraccounts table as well
        useraccounts_rec_from_accbal = []
        # following append order must align with the order of columns in
        # cu_useraccounts_cols
        # append user_id for <cu>useraccounts
        useraccounts_rec_from_accbal.append(user_id)
        # append accountnumber for <cu>useraccounts
        useraccounts_rec_from_accbal.append(accbal_accnum)
        # append accounttype for <cu>useraccounts
        useraccounts_rec_from_accbal.append(acc_type)
        # append certnumber for <cu>useraccounts
        useraccounts_rec_from_accbal.append(accbal_certnum)
        # append recordtype for <cu>useraccounts
        useraccounts_rec_from_accbal.append("D")
        useraccounts_rec_from_accbal.extend(cuuseraccounts_rec_default_ln_bal)

        # insert a single useraccounts record based on loanbalance
        members_batch_transaction(
            INSERT_ONE,
            "{}useraccounts".format(cu_lower),
            cu_useraccounts_cols,
            useraccounts_rec_from_accbal
        )

        # prepare a record to prepare MoneyDesktop cross reference document
        # based on accountbalance records
        prepare_money_desktop_xref(_cu,
                               user_id,
                               mmth_uname_also_ody_mem_accnum,
                               _mmth_accnum_to_ody_userid_ref_csv_writer,
                               accbal_rec,
                               "A")

    # insert a accountbalance collection
    members_batch_transaction(
        INSERT,
        "{}accountbalance".format(cu_lower),
        collection=account_balance_coll
    )

    # <cu>loanbalance table [different schema in Mammoth
    # for Live and Batch CUs]
    loan_balance_coll = member_data["loanbalance"]
    for lnbal_rec in loan_balance_coll:
        lnbal_accnum = get_strip_value_dict("accountnumber",
                                            lnbal_rec)
        lnbal_lnnum = get_strip_value_dict("loannumber",
                                           lnbal_rec)

        assert lnbal_accnum is not None
        assert lnbal_accnum == mmth_uname_also_ody_mem_accnum

        credit_limit = get_strip_value_dict("creditlimit", lnbal_rec)
        lnbal_rec["payoff"] = get_strip_value_dict(
            "payoff", lnbal_rec, default=0)
        lnbal_rec["currentdue"] = 0  # default, NOT NULL

        lnbal_rec["misc1"] = get_strip_value_dict(
            "misc1", lnbal_rec, default=None)

        lnbal_rec["cbtype"] = get_strip_value_dict(
            "type", lnbal_rec, pop=True)

        lnbal_rec["balance_stamp"] = member_acct_balance_stamp
        lnbal_rec["history_stamp"] = member_acct_balance_stamp
        lnbal_rec["balance_attempt"] = memacct_balance_attempt
        lnbal_rec["history_attempt"] = memacct_balance_attempt

        if float(credit_limit) > 0:
            lnbal_rec["may_addon"] = True
        else:
            lnbal_rec["may_addon"] = False

        lnbal_rec["may_payment"] = True

        # Columns present in MAMMOTH in Batch but not in Live CUs
        # We take the appropriate value if the column exist,
        # otherwise use the default values
        lnbal_rec["unpaidinterest"] = get_strip_value_dict(
            "unpaidinterest", lnbal_rec, default=None)

        lnbal_rec["frequencyperyear"] = get_strip_value_dict(
            "frequencyperyear", lnbal_rec, default=None)

        lnbal_rec["originalamount"] = get_strip_value_dict(
            "originalamount", lnbal_rec, default=None)

        lnbal_rec["originaldate"] = get_strip_value_dict(
            "originaldate", lnbal_rec, default=None)

        lnbal_rec["term"] = get_strip_value_dict(
            "term", lnbal_rec, default=None)

        lnbal_rec["creditdisability"] = get_strip_value_dict(
            "cdi", lnbal_rec, pop=True, default=None)

        lnbal_rec["creditlife"] = get_strip_value_dict(
            "cli", lnbal_rec, pop=True, default=None)

        # Now also prepare values to insert in <cu>useraccounts table as well
        useraccounts_rec_from_lnbal = []
        # following append order must align with the order of columns in
        # cu_useraccounts_cols
        # append user_id for <cu>useraccounts
        useraccounts_rec_from_lnbal.append(user_id)
        # append accountnumber  for <cu>useraccounts
        useraccounts_rec_from_lnbal.append(lnbal_accnum)
        # append accounttype for <cu>useraccounts
        useraccounts_rec_from_lnbal.append(lnbal_lnnum)
        # append certtype  for <cu>useraccounts
        useraccounts_rec_from_lnbal.append(0)
        # append recordtype for <cu>useraccounts
        useraccounts_rec_from_lnbal.append("L")
        useraccounts_rec_from_lnbal.extend(cuuseraccounts_rec_default_ln_bal)

        # insert a single useraccounts record based on loanbalance
        members_batch_transaction(
            INSERT_ONE,
            "{}useraccounts".format(cu_lower),
            cu_useraccounts_cols,
            useraccounts_rec_from_lnbal
        )

        # prepare a record to prepare MoneyDesktop cross reference document
        # based on loanbalance records
        prepare_money_desktop_xref(_cu,
                                   user_id,
                                   mmth_uname_also_ody_mem_accnum,
                                   _mmth_accnum_to_ody_userid_ref_csv_writer,
                                   lnbal_rec,
                                   "L",
                                   money_desktop_credit_card_type18_flag)

    # insert a loanbalance collection
    members_batch_transaction(
        INSERT,
        "{}loanbalance".format(cu_lower),
        collection=loan_balance_coll
    )

    # transform curepeattx to cu_scheduledtxn
    curepeattx_mammoth_records = member_data["curepeattx"]
    for mmth_curepeattx_rec in curepeattx_mammoth_records:
        mmth_curepeattx_rec_copy = deepcopy(mmth_curepeattx_rec)

        del mmth_curepeattx_rec["txid"]

        curepeattx_interval = get_strip_value_dict(
            "interval",
            mmth_curepeattx_rec,
            pop=True
        )

        mmth_curepeattx_rec["repeating_parameters"] = get_valid_json({
            "interval": SCHEDULE_INTERVAL_MAPPING[curepeattx_interval]})

        mmth_curepeattx_rec["user_id"] = user_id

        mmth_curepeattx_rec["interval_count"] = get_strip_value_dict(
            "intervalcount",
            mmth_curepeattx_rec,
            pop=True
        )

        mmth_curepeattx_rec["next_trigger_date"] = get_strip_value_dict(
            "nexttriggerdate",
            mmth_curepeattx_rec,
            pop=True
        )

        mmth_curepeattx_rec["end_date"] = get_strip_value_dict(
            "stopdate",
            mmth_curepeattx_rec,
            pop=True
        )

        curepeattx_startdate = get_strip_value_dict(
            "startdate",
            mmth_curepeattx_rec,
            pop=True
        )

        mmth_curepeattx_rec["start_date"] = curepeattx_startdate
        mmth_curepeattx_rec["create_date"] = curepeattx_startdate
        mmth_curepeattx_rec["approved_date"] = curepeattx_startdate
        mmth_curepeattx_rec["last_edit_date"] = curepeattx_startdate

        mmth_curepeattx_rec["approved_by"] = user_id
        mmth_curepeattx_rec["last_edit_by"] = user_id
        mmth_curepeattx_rec["approved_status"] = 10

        mmth_curepeattx_rec["failure_count"] = 0
        mmth_curepeattx_rec["feature_code"] = "TRN"

        # prepare txn_data field
        curepeattx_transactioncode = get_strip_value_dict(
            "transactioncode",
            mmth_curepeattx_rec,
            pop=True
        )

        curepeattx_fromsuffix = get_strip_value_dict(
            "fromsuffix",
            mmth_curepeattx_rec,
            pop=True
        )
        curepeattx_tosuffix = get_strip_value_dict(
            "tosuffix",
            mmth_curepeattx_rec,
            pop=True
        )
        curepeattx_accountnumber = get_strip_value_dict(
            "accountnumber",
            mmth_curepeattx_rec,
            pop=True
        )

        curepeattx_tomember = get_strip_value_dict(
            "tomember",
            mmth_curepeattx_rec,
            pop=True
        )

        curepeattx_amount = get_strip_value_dict(
            "amount",
            mmth_curepeattx_rec,
            pop=True
        )

        from_type = ""
        to_type = ""

        if curepeattx_transactioncode == "AT":
            from_type = "D"
            to_type = "D"
        elif curepeattx_transactioncode in ["LP", "CP"]:
            from_type = "D"
            # cross-account: L
            if curepeattx_accountnumber != curepeattx_tomember:
                to_type = "L"

            else:
                # retrieve cbtype of the target account to identify whether the
                # target account is loan account or credit card
                for loanbalance_rec in loan_balance_coll:
                    if curepeattx_tosuffix == loanbalance_rec["loannumber"]:
                        if loanbalance_rec["cbtype"] == '18':
                            to_type = "C"
                        else:
                            to_type = "L"
                        break

            # target loan/credit card account does not exist; L
            # Note: when testing with SNOCOPE records; there were couple
            # instances of scheduled transfers (inactive) that include transfer
            # to non-existent credit card accounts. Log such instances
            # of scheduled transfers. However, these inactive records are
            # allowed to be updated in banking. As per discussion with Mark,
            # even if such instances are updated to be active from banking
            # side, these will be caught in a process of
            # execution of scheduled transfers; hence are to be handled
            # from there.
            if to_type == "":
                to_type = "L"
                _mmth_repeating_transfers_outliers.writerow(
                    [("'{}' is not a valid loan/credit card account "
                      "for member '{}'.").format(curepeattx_tosuffix,
                                                 curepeattx_accountnumber),
                     get_valid_json(mmth_curepeattx_rec_copy)])

        elif curepeattx_transactioncode in ["LA", "CA"]:
            from_type = "L"
            to_type = "D"

        if from_type == "" or to_type == "":
            error_msg = "cu_scheduledtxn from_type or to_type not populated"
            LOGGER.error(error_msg)
            # raise SystemExit(error_msg)

        # Loans with transaction code LA should not have the additional `|0|
        # appended to the "from".
        if curepeattx_transactioncode == "LA":
            from_txn = "{}|{}|{}".format(
                from_type,
                curepeattx_accountnumber,
                curepeattx_fromsuffix)
        else:
            from_txn = "{}|{}|{}|{}".format(
                from_type,
                curepeattx_accountnumber,
                curepeattx_fromsuffix,
                0)

        # Loans with transaction codes LP or CP should not have the additional
        # `|0| appended to the "to".
        if curepeattx_transactioncode in ["LP", "CP"]:
            to_txn = "{}|{}|{}".format(
                to_type,
                curepeattx_tomember,
                curepeattx_tosuffix)
        else:
            to_txn = "{}|{}|{}|{}".format(
                to_type,
                curepeattx_tomember,
                curepeattx_tosuffix,
                0)

        # After ISUCU migration, we noted problems for any scheduled transfer
        # that involved a cross-account. This should fix the problem where
        # accounts were reported as ‘no longer has rights’ for the future
        # migrating credit unions
        if curepeattx_accountnumber != curepeattx_tomember:
            if curepeattx_transactioncode == 'AT':
                curepeattx_transactioncode = 'XA'
            elif curepeattx_transactioncode == 'LP':
                curepeattx_transactioncode = 'XP'

        cuscheduled_txndata = {}
        cuscheduled_txndata["txn"] = {
            "from": handle_overloaded_accounts(from_txn),
            "frommember": curepeattx_accountnumber,
            "fromsuffix": curepeattx_fromsuffix,
            "fromtype": from_type,
            "to": handle_overloaded_accounts(to_txn),
            "tomember": curepeattx_tomember,
            "tosuffix": curepeattx_tosuffix,
            "totype": to_type,
            "amount": curepeattx_amount,
            "transactioncode": curepeattx_transactioncode,
            "deposittype": from_type,
            "memo": ""
        }
        mmth_curepeattx_rec["txn_data"] = get_valid_json(cuscheduled_txndata)

    # insert a transformed list of dictionary to cu_scheduledtxn
    members_batch_transaction(
        INSERT,
        "cu_scheduledtxn",
        collection=curepeattx_mammoth_records
    )


    # <cu>audituser table
    cuauditusers_coll = member_data["cuauditusers"]
    for cuaudituser_rec in cuauditusers_coll:
        mmth_cu = get_strip_value_dict(
            "cu", cuaudituser_rec, pop=True)
        assert mmth_cu == _cu
        del cuaudituser_rec["admuser"]
        cuaudituser_rec["user_id"] = user_id
        cuaudituser_rec["auditdate"] = get_strip_value_dict(
            "chdate", cuaudituser_rec, pop=True)
        cuaudituser_rec["auditaction"] = get_strip_value_dict(
            "action", cuaudituser_rec, pop=True)
        cuaudituser_rec["auditsrctype"] = "U"
        cuaudituser_rec["auditsrcuser_name"] = get_strip_value_dict(
            "user_name", cuaudituser_rec, pop=True)
        cuaudituser_rec["auditsrcemail"] = get_strip_value_dict(
            "email", cuaudituser_rec, pop=True, default="a@b.cde")
        cuaudituser_rec["auditsrcip"] = "127.0.0.1"
        cuaudituser_rec["accountnumber"] = mmth_uname_also_ody_mem_accnum

        # Give a description if it exists.
        # SHOULD be one of the following in AUDITUSER_DESCMAP.
        if cuaudituser_rec["auditaction"] in AUDITUSER_DESCMAP:
            val = cuaudituser_rec["auditaction"]
            cuaudituser_rec["auditfulldesc"] = AUDITUSER_DESCMAP[val]

    # insert a <cu>audituser collection
    members_batch_transaction(
        INSERT,
        "{}audituser".format(cu_lower),
        collection=cuauditusers_coll
    )

    # insert cuovermicr data
    cuovermicr_coll = member_data["cuovermicr"]
    members_batch_transaction(
        INSERT,
        "cuovermicr",
        collection=cuovermicr_coll
    )


def get_ody_user_ids(member):
    """Get Odyssey user_name and member account number

    Args:
        member: dictionary (with content <cuusers>.user_name and
                <cuusers>.user_alias from Mammoth)

    Returns:
        ody_uname (odyssey user_name):      Mammoth's <cuusers>.user_alias if
                                            any, otherwise <cuusers>.user_name
        mmth_uname_also_ody_mem_accnum (odyssey member acc num):
                                            Mammoth's <cuusers>.user_name
    """
    mmth_uname_also_ody_mem_accnum = member["user_name"]
    mmth_ualias = member["user_alias"]
    ody_uname = ""

    if hasattr(mmth_uname_also_ody_mem_accnum, 'strip'):
        mmth_uname_also_ody_mem_accnum =\
            mmth_uname_also_ody_mem_accnum.strip()

    if hasattr(mmth_ualias, 'strip'):
        mmth_ualias = mmth_ualias.strip()

    # get appropriate odyssey username based on user_name and user_alias
    if mmth_ualias is not None and mmth_ualias != "":
        assert (mmth_uname_also_ody_mem_accnum is
                not None and
                mmth_uname_also_ody_mem_accnum !=
                ""), ("Mammoth 'user_name' is empty!")
        ody_uname = mmth_ualias

    else:
        assert (mmth_uname_also_ody_mem_accnum is
                not None and
                mmth_uname_also_ody_mem_accnum !=
                ""), ("Both 'user_alias' and"
                      "'user_name' from Mammoth empty")
        ody_uname = mmth_uname_also_ody_mem_accnum
    return ody_uname, mmth_uname_also_ody_mem_accnum


@file_exc_decorator
def copy_http_file_stream(_resp, _target_path, _cu):
    """Stream download the http response content to a local file

    Raises:
        SystemExit: if IOError, NameError or PermissionError is caught
    """
    LOGGER.info("[gethistfile]: Downloading (stream download, decode gzip and "
                "deflate transfer-encodings) zipped sql file - `{}`"
                .format(_target_path))

    # An important note about using Response.iter_content versus Response.raw.
    # Response.iter_content will automatically DECODE (not decompress) the gzip
    # and deflate transfer-encodings. Response.raw is a raw stream of bytes –
    # it does not transform the response content. If you really need access to
    # the bytes as they were returned, use Response.raw.
    # https://en.wikipedia.org/wiki/Chunked_transfer_encoding

    with open(_target_path, "wb") as f:
        for chunk in _resp.iter_content(chunk_size=8192 * 2):
            if chunk:
                f.write(chunk)
    LOGGER.info("[gethistfile]: Download complete.")


@pg_crsr_hndlr_decrtr
@file_exc_decorator
def execute_sql_script_from_file(_target_gzip, _cu):
    """Loads the COPY FROM command including file and execute a bulk
    insertion command to populate <cu>accounthistory and <cu>loanhistory
    tables.

    Args:
        _target_gzip:   gzip file that contains COPY FROM script and csv
                        formatted content from Mammoth for <cu>accounthistory
                        and <cu>loanhistory tables

    Raises:
        SystemExit: if IOError, NameError or PermissionError is caught
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    LOGGER.info("[gethistfile]: Loading local memhist files and "
                "preparing SQL execution!")

    accounthistory_sql = "COPY {}accounthistory".format(_cu.lower())
    loanhistory_sql = "COPY {}loanhistory".format(_cu.lower())

    accounthistory_complete_sql = ""
    loanhistory_complete_sql = ""

    zip_dir = os.path.dirname(_target_gzip)
    accounthistory_file = os.path.join(
        zip_dir, MEMHIST_ACCOUNT_HISTORY_FILENAME)
    loanhistory_file = os.path.join(zip_dir, MEMHIST_LOAN_HISTORY_FILENAME)

    DECODE_OPTION = "utf-8"
    write_loan = False
    write_account = False

    # separate accounthistory and loanhistory content in two separate files
    with open(accounthistory_file, "wb") as f_a, \
            open(loanhistory_file, "wb") as f_l:
        with gzip.open(_target_gzip, "rb") as f:
            for line in f.readlines():
                decoded_line = line.decode(DECODE_OPTION)

                if(accounthistory_sql in decoded_line):
                    write_account = True
                    write_loan = False
                    accounthistory_complete_sql = decoded_line
                    continue

                if(loanhistory_sql in decoded_line):
                    write_account = False
                    write_loan = True
                    loanhistory_complete_sql = decoded_line
                    continue

                # this order depends on which records are added first when
                # creating a gzip file on Mammoth side (as of 2018/09/21,
                # accounthistory records are written first)
                if write_account:
                    f_a.write(line)

                if write_loan:
                    f_l.write(line)

    # read the file-like objects from disk and run COPY to populate records
    # in the respective tables
    with pg_handler.PGSession() as conn:
        with conn.cursor() as cur:
            memhist_transaction = PgTransaction(conn, cur)
            cleanup_memhist(memhist_transaction, _cu, commit_later=True)

            with open(accounthistory_file, "rb") as f_a, \
                    open(loanhistory_file, "rb") as f_l:

                memhist_transaction.cur.copy_expert(
                    accounthistory_complete_sql, f_a)

                memhist_transaction.cur.copy_expert(
                    loanhistory_complete_sql, f_l)

            # commit after all the tables are populated
            memhist_transaction.commit()


@pg_crsr_hndlr_decrtr
@file_exc_decorator
def move_members_hist(_server, _verbose, _cu, _user, _passwd):
    """Move members history tables

    Steps:
    1. createhistfile: creates the gzip file including sql command on Mammoth
    2. gethistfile: downloads gzip file from Mammoth, saves as a local copy
                    then execute the COPY command using the content from
                    downloaded file.

    Migration involves reading the downloaded content, preparing the COPY FROM
    sql command for <cu>accounthistory and <cu>loanhistory tables and commiting
    the changes.

    Args:
        _server:       Mammoth endpoint server
        _verbose:      verbose flag
        _cu:           current credit union code
        _user:         Mammoth monitor username
        _passwd:       Mammoth monitor password

    Raises:
        SystemExit: if IOError, NameError or PermissionError is caught
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    # always deleted after successful migration and before clean startup
    valid_history_gzip_import = MEMHIST_DOWNLOADED_FILE.format(_cu.lower())
    # check if the file has already been pulled and something went
    # wrong on Odyssey side earlier
    if os.path.exists(valid_history_gzip_import):
        LOGGER.warning("Local copy of SQL dump file exist! You might be "
                       "loading outdated file! If you think the data might "
                       " have been changed from last time, clean up and re-run"
                       " to download the most recent data!")
        # local gzip file already exist, execute
        execute_sql_script_from_file(valid_history_gzip_import, _cu)

    else:
        # connect to Mammoth, create and get history file
        historyfile_create_request = MammothMigration(
            _cu,
            DATA_OPT_CREATE_HIST,
            _server,
            _user,
            _passwd,
            params={"restart": "Y"}
        )
        historyfile_create_request.run()
        hist_creation_resp = historyfile_create_request.response
        mmth_histfile_path = hist_creation_resp["file"]
        # histfile_create_status = hist_creation_resp["error"]

        if historyfile_create_request.histstatus != "":
            LOGGER.info("[createhistfile] status: {}".format(
                historyfile_create_request.histstatus))
        else:
            LOGGER.info(("[createhistfile] status: History file is being "
                         "created on Mammoth location: `{}.gz`").format(
                mmth_histfile_path))

        mammoth_prep_done = False
        hist_get_resp = None

        while not mammoth_prep_done:
            LOGGER.info("Waiting for {} seconds for memhist file preparation "
                        "on Mammoth side".format(HIST_CREATE_WAIT_TIME))
            # wait for somethime while the database records are being extracted
            # on Mammoth for accounthistory and accountloan tables
            time.sleep(HIST_CREATE_WAIT_TIME)

            # get the prepared gzipped file from Mammoth and stream download
            historyfile_get_request = MammothMigration(
                _cu,
                DATA_OPT_GET_HIST,
                _server,
                _user,
                _passwd,
                stream=True
            )
            historyfile_get_request.run()

            if historyfile_get_request.histstatus == "":
                LOGGER.info("[gethistfile] status: SQL dump file on Mammoth "
                            "is ready for download.")
                hist_get_resp = historyfile_get_request.response
                mammoth_prep_done = True
            else:
                LOGGER.info("[createhistfile] status: {}".format(
                    historyfile_get_request.histstatus))

        assert hist_get_resp is not None
        # first download/stream download data to local gzip file
        copy_http_file_stream(hist_get_resp, valid_history_gzip_import, _cu)

        # check if the content imported to the local gzip file, then execute
        if os.path.exists(valid_history_gzip_import):
            execute_sql_script_from_file(valid_history_gzip_import, _cu)

        # if file is not found, error
        else:
            error_msg = "Something went wrong in {} phase".format(
                DATA_OPT_GET_HIST)
            LOGGER.error(error_msg)
            raise SystemExit(error_msg)


def get_valid_to_be_imported_members(cu_lower, _mmth_members_list):
    """Check invalid memberacct nums and exclude already imported members

    1. Identify non-integer member account numbers and exclude from the list
    2. Check locally on Odyssey what members have already been imported and
    exclude them from the import list

    Arguments:
        cu_lower -- lower case CU code
        _mmth_members_list -- all members' list for this CU

    Returns:
        updated_uname_accnums_list -- list of to be imported member accounts
        non_integer_mem_ids -- invalid non integer member accounts for logging
    """
    updated_uname_accnums_list = []
    LOGGER.info("Excluding already imported members from the import list...")

    # in test server, sometimes there are member account number
    # that are not integer, just catch them and throw warning
    # if there are any such members
    non_integer_mem_ids = []

    with pg_handler.PGSession() as conn:
        with conn.cursor() as cur:
            # temporary transaction
            members_batch_transaction = PgTransaction(conn, cur)

            # now iterate each member of this batch and migrate
            # contents obtained from Mammoth to Odyssey
            for mem in _mmth_members_list:
                # get odyssey username and memberacct info
                un_accnums = get_ody_user_ids(mem)
                ody_uname = un_accnums[0]
                memberacct_num = un_accnums[1]

                # just check if the member account number is integer
                try:
                    int(memberacct_num)
                except ValueError:
                    non_integer_mem_ids.append(un_accnums)
                    continue

                cuuser_table = "{}user".format(cu_lower)

                ody_cuusers_count = members_batch_transaction(
                    SELECT_RETURN,
                    cuuser_table,
                    return_only_count=True,
                    where_conditions={
                        "user_name": ody_uname
                    }
                )
                assert ody_cuusers_count <= 1

                if ody_cuusers_count == 1:
                    # member already migrated/exists in Odyssey, skip
                    continue
                else:
                    # add to import list
                    updated_uname_accnums_list.append(un_accnums)

    return updated_uname_accnums_list, non_integer_mem_ids


def skip_never_loggedin_cuuser(cuuser_record_mmth):
    """Filter mammoth cuusers record on lastlogin and pktdate"""
    # filter out users with no prior login and packet date
    lastlogin_cuuser = cuuser_record_mmth[
        "lastlogin"].strip() if hasattr(cuuser_record_mmth[
            "lastlogin"], "strip") else cuuser_record_mmth["lastlogin"]
    pktdate_cuuser = cuuser_record_mmth[
        "pktdate"].strip() if hasattr(cuuser_record_mmth[
            "pktdate"], "strip") else cuuser_record_mmth["pktdate"]

    if lastlogin_cuuser in [None, ''] and  pktdate_cuuser in [None, '']:
        return True
    else:
        return False


@file_exc_decorator
@pg_crsr_hndlr_decrtr
def move_members_data(_mmth_members_dict,
                      _server,
                      _verbose,
                      _cu,
                      _uname,
                      _passwd):
    """Migrate memdata obtained from Mammoth to Odyssey.

    Migrate a batch of members at a time to reduce the Mammoth http endpoint
    roundtrip.

    Args:
        _mmth_members_dict:  members list dictionary of the
                            response json dictionary from mammoth
        _server:       Mammoth endpoint server
        _verbose:      verbose flag
        _cu:           current credit union code
        _uname:        Mammoth monitor username
        _passwd:       Mammoth monitor password

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """

    cu_lower = _cu.lower()
    members_list = _mmth_members_dict["data"]["list"]

    # exclude already imported users in Odyssey from import list
    updated_uname_accnums_list, non_integer_mem_id_all =\
        get_valid_to_be_imported_members(
            cu_lower,
            members_list
        )

    # collect orphaned users
    orphaned_members_list = []
    # collect members with forceremain updated to unlock
    forceremain_update_list = []
    # collect members with no prior login info
    # (cuusers.lastlogin and cuusers.pktdate is null)
    never_loggedin_members_list = []

    # total members that will be migrated
    total_members = len(updated_uname_accnums_list)

    # total batches will be migrated
    total_batches = math.ceil(total_members / MEMBERS_PAGE_SIZE)

    LOGGER.info("Importing total of {} members' "
                "data over {} batch(es)...".format(
                    total_members, total_batches))

    # flag to check if the member to user cross reference csv file exists
    reference_file_exist_before_open = os.path.exists(
        USERID_CROSS_REF_CSV_FILE.format(cu_lower))

    # flag to check if the csv file recording orphaned mammoth users exists
    mmth_orphaned_list_file_exist_before_open = os.path.exists(
        MAMMOTH_ORPHANED_MEMLIST_CSV_FILE.format(cu_lower))

    # flag to check if the csv file recording never logged in members
    mmth_never_logged_in_list_file_exist_before_open = os.path.exists(
        MAMMOTH_NEVER_LOGGEDIN_MEMLIST_CSV_FILE.format(cu_lower))

    # flag to check if the csv file logging repeating transfers outliers
    mmth_schedxfer_outlier_exist_before_open = os.path.exists(
        SCHEDULED_TRANSFER_OUTLIERS_CSV_FILE.format(cu_lower))

    # read the cusurveymaster.surveyid mapping to memory
    cusurveymaster_surveyid_map = {}
    if os.path.exists(ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(cu_lower)):
        with open(ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(cu_lower),
                  newline='') as cusurveymap_csv:
            reader = csv.reader(cusurveymap_csv, delimiter=',', quotechar='|')
            next(reader) # skip header
            for [oldid, newid] in reader:
                cusurveymaster_surveyid_map[oldid.strip()] = newid.strip()
    else:
        raise SystemExit("cusurveymaster.surveyid mappings `{}` not found!".format(
            ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(cu_lower)))

    # open the user id cross reference csv file and orphaned member list csv
    # file in a context manager
    with open(USERID_CROSS_REF_CSV_FILE.format(cu_lower),
              'a',
              newline='') as csvfile,\
              open(MAMMOTH_ORPHANED_MEMLIST_CSV_FILE.format(cu_lower),
                   'a',
                   newline='') as csvfile_orphaned,\
                   open(MAMMOTH_NEVER_LOGGEDIN_MEMLIST_CSV_FILE.format(cu_lower),
                        'a',
                        newline='') as csvfile_never_loggedin,\
                        open(SCHEDULED_TRANSFER_OUTLIERS_CSV_FILE.format(cu_lower),
                             'a',
                             newline='') as csvfile_outlier_schedxfer:

        _mmth_accnum_to_ody_userid_ref_csv_writer = csv.writer(
            csvfile, delimiter=',',
            quoting=csv.QUOTE_MINIMAL
        )
        _mmth_orphaned_memlist_csv_writer = csv.writer(
            csvfile_orphaned,
            delimiter=',',
            quotechar='|',
            quoting=csv.QUOTE_MINIMAL
        )
        _mmth_never_loggedin_memlist_csv_writer = csv.writer(
            csvfile_never_loggedin,
            delimiter=',',
            quotechar='|',
            quoting=csv.QUOTE_MINIMAL
        )
        _mmth_repeating_transfers_outliers = csv.writer(
            csvfile_outlier_schedxfer,
            delimiter=',',
            quotechar='|',
            quoting=csv.QUOTE_MINIMAL
        )

        if not reference_file_exist_before_open:
            # then we need to write the header row
            _mmth_accnum_to_ody_userid_ref_csv_writer.writerow(
                ["old_user_id", "new_user_id", "new_member_id", "old_member_id",
                "account_type", "old_acctid", "key", "new_id"]
            )

        if not mmth_orphaned_list_file_exist_before_open:
            # write the header row
            _mmth_orphaned_memlist_csv_writer.writerow(
                ["orphaned_member_account", "user_alias", 'lastlogin', 'pktdate'])

        if not mmth_never_logged_in_list_file_exist_before_open:
            # write the header row
            _mmth_never_loggedin_memlist_csv_writer.writerow(
                ["never_loggedin_member_account",
                 "user_alias",
                 'lastlogin',
                 'pktdate',
                 'estmt_flag'])
        if not mmth_schedxfer_outlier_exist_before_open:
            # write the header row
            _mmth_repeating_transfers_outliers.writerow(
                ["description", "original_mammoth_curepeattx_record"]
            )

        # replicating Miki's www4:/home/httpd/hcubin/MxConvXRef
        # script to prepare a MoneyDesktop mammoth member to
        # Odyssey accounts mapping
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                cu_admininfo_transaction = PgTransaction(conn, cur)

                select_cols_cuadmin = ["flagset2"]
                cuadmin_where = {"cu": _cu}
                cuadmin_rows = cu_admininfo_transaction(
                    SELECT_RETURN,
                    "cuadmin",
                    where_conditions=cuadmin_where,
                    select_columns=select_cols_cuadmin
                )
                if(len(cuadmin_rows) < 1):
                    raise SystemExit("`cuadmin` record does not exist "
                                     "for this credit union: {}".format(cu_lower))

                flagset2 = cuadmin_rows[0][0] # returns integer
                money_desktop_credit_card_type18_flag =  CU2_SPEC18 & flagset2

        # iteratively, migrate a batch of members at a time
        for batch_i in range(total_batches):
            # Start a transaction to migrate memdata for a batch of members
            # that is not migrated yet to Odyssey
            with pg_handler.PGSession() as conn:
                with conn.cursor() as cur:

                    # instantiate batch transaction
                    members_batch_transaction = PgTransaction(conn, cur)
                    ody_uname_and_accnums_list = get_ith_batch(
                        updated_uname_accnums_list,
                        batch_i,
                        MEMBERS_PAGE_SIZE
                    )

                    # list of valid member account number to be queried
                    # with to Mammoth
                    members_http_params = [un_accnum[1]
                                           for un_accnum in
                                           ody_uname_and_accnums_list]

                    if len(members_http_params) > 0:
                        LOGGER.info("`{}` valid members are being imported "
                                    "in batch `{}`.".format(
                                        len(members_http_params),
                                        batch_i + 1))

                        # fetch data from Mammoth for this batch of
                        # members `memdata`
                        this_batch_migration = MammothMigration(
                            _cu,
                            DATA_OPT_MEMDATA,
                            _server,
                            _uname,
                            _passwd,
                            params={KEY_MEMBERS: get_valid_json(
                                members_http_params)}
                        )
                        this_batch_migration.run()
                        this_batch_migration_data =\
                            this_batch_migration.response["data"]["account"]

                        # now iterate each member of this batch and migrate
                        # contents obtained from Mammoth to Odyssey
                        for ody_uname, mmth_uname_also_ody_mem_accnum in\
                                ody_uname_and_accnums_list:

                            this_member_data = this_batch_migration_data[
                                mmth_uname_also_ody_mem_accnum]

                            # check for orphaned memberaccounts
                            accountbalance_records_count = len(
                                this_member_data["accountbalance"])
                            loanbalance_records_count = len(
                                this_member_data["loanbalance"])
                            cuuser_acc_record = this_member_data["cuusers"]
                            assert type(cuuser_acc_record) is dict
                            mammoth_lastlogin = get_strip_value_dict(
                                "lastlogin", cuuser_acc_record)

                            mammoth_estmt_flag = get_strip_value_dict(
                                "estmt_flag", cuuser_acc_record)

                            mammoth_user_alias = get_strip_value_dict(
                                "user_alias", cuuser_acc_record)

                            # Filter orphan member account: do not migrate
                            if accountbalance_records_count == 0\
                                    and loanbalance_records_count == 0\
                                    and mammoth_estmt_flag != 'Y':
                                orphaned_members_list.append(
                                    mmth_uname_also_ody_mem_accnum)

                                csv_record_orphan = [
                                    mmth_uname_also_ody_mem_accnum,
                                    mammoth_user_alias,
                                    mammoth_lastlogin,
                                    cuuser_acc_record["pktdate"]
                                ]

                                # record the member account in a csv file
                                _mmth_orphaned_memlist_csv_writer.writerow(
                                    csv_record_orphan)
                                # important
                                continue

                            # Filter non-orphan member who might have
                            # never logged in to the Mammoth: do not migrate
                            if skip_never_loggedin_cuuser(cuuser_acc_record):
                                never_loggedin_members_list.append(
                                    mmth_uname_also_ody_mem_accnum)
                                # record the member account in a csv file
                                _mmth_never_loggedin_memlist_csv_writer.writerow(
                                    [mmth_uname_also_ody_mem_accnum,
                                     mammoth_user_alias,
                                     mammoth_lastlogin,
                                     cuuser_acc_record["pktdate"],
                                     mammoth_estmt_flag])
                                continue

                            # At this point, member is not orphan and has logged in
                            # atleast once: migrate this member account
                            else:
                                # migrate individual member data
                                migrate_individual_memdata(
                                    members_batch_transaction,
                                    this_member_data,
                                    ody_uname,
                                    mmth_uname_also_ody_mem_accnum,
                                    _verbose,
                                    _cu,
                                    _mmth_accnum_to_ody_userid_ref_csv_writer,
                                    _mmth_repeating_transfers_outliers,
                                    forceremain_update_list,
                                    cusurveymaster_surveyid_map,
                                    money_desktop_credit_card_type18_flag
                                )

                    else:
                        LOGGER.info(
                            "No valid members to import in batch `{}`.".format(
                                batch_i + 1))

                # commit after each batch of members
                members_batch_transaction.commit()
                LOGGER.info("[PROGRESS] Members imported:"" {}/{} "
                            "| Batch: {}/{}".format(
                                min(MEMBERS_PAGE_SIZE * (batch_i + 1),
                                    total_members),
                                total_members,
                                batch_i + 1,
                                total_batches)
                            )

    # log invalid members with non-integer member accounts (usually seen in
    # dev mode with cruisecu, scrubcu, etc.)
    if len(non_integer_mem_id_all) > 0:
        nimi_msg = "`{}` non-integer members encountered: [{}]".format(
            len(non_integer_mem_id_all),
            ", ".join([i[1] for i in non_integer_mem_id_all]))
        LOGGER.warning(nimi_msg)

    # log orphaned members list
    if len(orphaned_members_list) > 0:
        LOGGER.warning("`{}` orphaned members were "
                       "found. Following members were "
                       "not imported: [{}]".format(
                           len(orphaned_members_list),
                           ", ".join(orphaned_members_list)))

    # log members with forceremain updated
    if len(forceremain_update_list) > 0:
        LOGGER.warning("`forceremain` was updated for `{}` "
                       "members: [{}]".format(
                           len(forceremain_update_list),
                           ", ".join(forceremain_update_list)))

    # log never loggedin members
    if len(never_loggedin_members_list) > 0:
        LOGGER.warning("`{}` members that never logged in to Mammoth were "
                       "found. Following members were not "
                       "imported : [{}]".format(
                           len(never_loggedin_members_list),
                           ", ".join(never_loggedin_members_list)))


def populate_switch_accounts(switch_acc_transaction,
                             cu_lower,
                             switch_acc_from,
                             switch_acc_data,
                             non_existent_switch_accounts):
    """Populate switch accounts in useraccounts and memberacctrights tables

    Add additional records in useraccounts and memberacctrights tables
    for switch accounts.

    Arguments:
        switch_acc_transaction:     instance of PgTransaction
        cu_lower:                   lowercase CU code
        switch_acc_from:            member account number for which we want
                                    to add switch account
        switch_acc_data:            list of culivetx data for member accounts
                                    which we want to add as switch accounts
        non_existent_switch_accounts: list of invalid tomember switch accounts
    """

    switch_acc_from = switch_acc_from.strip()
    from_accnum_where_conditions = {
        "accountnumber": switch_acc_from,
    }
    select_columns = ["primary_user"]

    # 1. get user_id_from for switch_acc_from
    user_from = switch_acc_transaction(
        SELECT_RETURN,
        "{}memberacct".format(cu_lower),
        where_conditions=from_accnum_where_conditions,
        select_columns=select_columns
    )

    # do not proceed if the from accountnumber is invalid in Odyssey
    # If record does not exist, member account is either locked or the
    # member has never been used in online banking in Mammoth
    if len(user_from) == 0:
        invalid_switch_account_from = {
            "Invalid Switch Account From": switch_acc_from,
            "Switch Account To": [acc_to["tomember"].strip()
                                  for acc_to in switch_acc_data]
        }
        LOGGER.warning("Cannot switch from member account `{}`. It is either"
                       " locked"
                       " or the member has never been used in"
                       " online banking in Mammoth."
                       .format(switch_acc_from) + \
                       invalid_switch_account_from.__str__())
        non_existent_switch_accounts.append(invalid_switch_account_from)
        # important; do not proceed after this point
        return

    # from accountnumber is valid in Odyssey
    user_from = user_from[0][0]

    for switch_acc_to in switch_acc_data:
        to_member = switch_acc_to["tomember"].strip()
        to_accnum_where_conditions = {
            "accountnumber": to_member,
        }

        user_to = switch_acc_transaction(
            SELECT_RETURN,
            "{}memberacct".format(cu_lower),
            where_conditions=to_accnum_where_conditions,
            select_columns=select_columns
        )

        # 2. Verify that record exists for the `tomember` in memberacct table
        # If record does not exist, member account is either locked or the
        # member has never been used in online banking in Mammoth
        if len(user_to) == 0:
            invalid_switch_account_to = {
                "Switch Account From": switch_acc_from,
                "Invalid Switch Account To": to_member
            }

            LOGGER.warning("Cannot switch to member account `{}`. It is either"
                           " locked"
                           " or the member has never been used in"
                           " online banking in Mammoth."
                           .format(to_member) + invalid_switch_account_to.__str__())

            non_existent_switch_accounts.append(invalid_switch_account_to)

        else:
            # 3. If tomember exists in <cu>memberacct table,
            # get user_id_to for to_member
            user_to = user_to[0][0]

            # 4. Get all the records for user_id_to + to_member from
            # useraccounts table and memberacctrights table
            #    4.1 For all the records in those two tables, replace
            #    user_id_to with user_id_from

            user_accounts_records = switch_acc_transaction(
                SELECT_RETURN,
                "{}useraccounts".format(cu_lower),
                where_conditions={'user_id': user_to,
                                  'accountnumber': to_member}
            )

            new_user_accounts_records = []
            for user_accounts_record in user_accounts_records:
                user_accounts_record = dict(user_accounts_record)
                user_accounts_record["user_id"] = user_from
                new_user_accounts_records.append(user_accounts_record)

            switch_acc_transaction(
                INSERT,
                "{}useraccounts".format(cu_lower),
                collection=new_user_accounts_records
            )

            memberacctrights_records = switch_acc_transaction(
                SELECT_RETURN,
                "{}memberacctrights".format(cu_lower),
                where_conditions={
                    'user_id': user_to,
                    'accountnumber': to_member
                }
            )

            new_memberacctrights_records = []
            for memberacctrights_record in memberacctrights_records:
                memberacctrights_record = dict(memberacctrights_record)
                memberacctrights_record["user_id"] = user_from
                new_memberacctrights_records.append(memberacctrights_record)

            switch_acc_transaction(
                INSERT,
                "{}memberacctrights".format(cu_lower),
                collection=new_memberacctrights_records
            )


@pg_crsr_hndlr_decrtr
def move_switch_accounts(_mmth_switch_accts_dict,
                         _server,
                         _verbose,
                         _cu,
                         _uname,
                         _passwd):
    """Import all switch accounts for a Credit Union

    This operation is intended to be imported after all the member data
    is already migrated.

    Arguments:
        _mmth_switch_accts_dict:    Mammoth response data
        _cu:                        CU Code
        _server:                    Mammoth endpoint server
        _uname:                     Mammoth monitor username
        _passwd:                    Mammoth monitor password
        _verbose:                   level of verbosity
    """

    cu_lower = _cu.lower()
    switch_acc_members_list = _mmth_switch_accts_dict["members"]
    # total members from response that we will be migrating
    total_members = len(switch_acc_members_list)
    # total batches we will be migrating
    total_batches = math.ceil(total_members / SWITCH_ACC_BATCH_SIZE)

    LOGGER.info("Importing total of {} members' switch account data!".format(
        total_members))
    non_existent_switch_accounts = []
    # iteratively, migrate a batch of members at a time
    for batch_i in range(total_batches):
        # Start a transaction to migrate memdata for a batch of members
        # that is not migrated yet to Odyssey
        with pg_handler.PGSession() as conn:
            with conn.cursor(cursor_factory=DictCursor) as cur:
                # with conn.cursor() as cur:

                # batch transaction
                switch_acc_transaction = PgTransaction(conn, cur)

                batch_switch_acc_members = get_ith_batch(
                    switch_acc_members_list,
                    batch_i,
                    SWITCH_ACC_BATCH_SIZE
                )

                # fetch data from Mammoth for this batch of members
                this_batch_migration = MammothMigration(
                    _cu,
                    DATA_OPT_SWITCH_ACCOUNTS,
                    _server,
                    _uname,
                    _passwd,
                    params={KEY_MEMBERS: get_valid_json(
                        batch_switch_acc_members)}
                )

                this_batch_migration.run()
                this_batch_migration_data = this_batch_migration.response

                for switch_acc_member, switch_acc_data in \
                        this_batch_migration_data["culivetx"].items():
                    populate_switch_accounts(
                        switch_acc_transaction,
                        cu_lower,
                        switch_acc_member,
                        switch_acc_data,
                        non_existent_switch_accounts
                    )

                # commit after each batch of switch account related members
                switch_acc_transaction.commit()
                # print progress
                LOGGER.info("[PROGRESS] Switch accounts imported:"
                            " {}/{} | Batch: {}/{}".format(
                                min(SWITCH_ACC_BATCH_SIZE * (batch_i + 1),
                                    total_members),
                                total_members,
                                batch_i + 1,
                                total_batches)
                            )

    # Log any invalid tomember in Mammoth
    if len(non_existent_switch_accounts) > 0:
        unused_memberaccts_in_mmth_msg = ("These `{}` member accounts out "
                                          "of `{}` total switch accounts are "
                                          "either locked or the members have "
                                          "never been used online banking in "
                                          "Mammoth. Please let the Credit "
                                          "Union know about it! ")

        LOGGER.warning(get_valid_json({unused_memberaccts_in_mmth_msg.format(
            len(non_existent_switch_accounts), total_members):
            non_existent_switch_accounts}))


@file_exc_decorator
@pg_crsr_hndlr_decrtr
def cleanup_memdata(del_memdata_trnscn, _cu, **kwargs):
    """Cleanup memdata content

    If kwargs.get('commit_later') flag is True, we do not commit the
    transaction here (and only cleanup database tables, not the files),
    meaning that the the cleanup may have been executed before actual
    migration. If commit_later flag is False, we commit the transaction
    here and proceed with the file cleanup.

    Args:
        del_memdata_trnscn:     instance of PgTransaction to which this
                                cleanup process is part of
        **kwargs:               Options:
                                commit_later: flag to commit transaction later
                                    or now
                                accountnumber: if we want to filter delete
                                    based on account number

    Raises:
        SystemExit: if IOError, NameError or PermissionError is caught
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    commit_later = kwargs.get("commit_later", False)
    accountnumber = kwargs.get("accountnumber", None)
    cu_lower = _cu.lower()

    # tables that can be conditioned only on CU for deletion
    tbls_condn_cu_only = ["cuadmeco", "cu_scheduledtxn", "cuovermicr"]
    cu_where = {
        "cu": _cu
    }

    # tables that can be conditioned both on CU and accountnum for deletion
    # accountnum is optional, but conditioning on CU is NECESSARY to make
    # sure we are deleting the content of the right CU
    # curepeattx also falls in this category, if need be
    tbls_condn_cu_accnum = [
        "cusurveysays",
        "cucmsresponse",
        "cu_alerts",
        "culogtrack"
    ]

    # tables that can be conditioned only on accountnumber for deletion
    # if need be, otherwise, delete altogether
    tbls_condn_accnum_only = [
        "{}accountbalance".format(cu_lower),
        "{}loanbalance".format(cu_lower),
        "{}useraccounts".format(cu_lower),
        "{}crossaccounts".format(cu_lower),
        "{}holds".format(cu_lower)
    ]
    accnum_where = {}

    # tables that can be deleted altogether
    tbls_del_all = [
        "{}audituser".format(cu_lower),
        "{}userlogins".format(cu_lower),
        "{}memberacct".format(cu_lower),
        "{}memberacctrights".format(cu_lower),
        "{}group".format(cu_lower),
        "{}usercontact".format(cu_lower),
        "{}transdtl".format(cu_lower),
        "{}transhdr".format(cu_lower),
        "{}user".format(cu_lower)  # put it as a last element
    ]
    cu_and_accnum_where = {
        "cu": _cu,
    }

    # add optional account number condition,
    # helpful during development
    if accountnumber is not None:
        assert type(accountnumber) == str
        cu_and_accnum_where["accountnumber"] = accountnumber
        accnum_where["accountnumber"] = accountnumber

    for tbl in tbls_condn_cu_only:
        del_memdata_trnscn(
            DELETE,
            tbl,
            where_conditions=cu_where
        )

    for tbl in tbls_condn_cu_accnum:
        del_memdata_trnscn(
            DELETE,
            tbl,
            where_conditions=cu_and_accnum_where
        )

    for tbl in tbls_condn_accnum_only:
        del_memdata_trnscn(
            DELETE,
            tbl,
            where_conditions=accnum_where
        )

    for tbl in tbls_del_all:
        del_memdata_trnscn(
            DELETE,
            tbl
        )

    LOGGER.info("Cleaning up LOANAPPS records.")
    # also cleanup loanapps
    cleanup_loanapps(del_memdata_trnscn, _cu, commit_later=commit_later)

    if not commit_later:
        # remove the user_id/accountnum mappings reference csv file if exists
        if os.path.exists(USERID_CROSS_REF_CSV_FILE.format(cu_lower)):
            os.remove(USERID_CROSS_REF_CSV_FILE.format(cu_lower))

        # remove the orphaned members list containing csv file if exists
        if os.path.exists(MAMMOTH_ORPHANED_MEMLIST_CSV_FILE.format(cu_lower)):
            os.remove(MAMMOTH_ORPHANED_MEMLIST_CSV_FILE.format(cu_lower))

        # remove the never logged in members list containing csv file
        if os.path.exists(MAMMOTH_NEVER_LOGGEDIN_MEMLIST_CSV_FILE.format(cu_lower)):
            os.remove(MAMMOTH_NEVER_LOGGEDIN_MEMLIST_CSV_FILE.format(cu_lower))

        # remove the never logged in members list containing csv file
        if os.path.exists(SCHEDULED_TRANSFER_OUTLIERS_CSV_FILE.format(cu_lower)):
            os.remove(SCHEDULED_TRANSFER_OUTLIERS_CSV_FILE.format(cu_lower))

        # commit all the table deletions here
        del_memdata_trnscn.commit()
        del_memdata_trnscn(
            SUMMARY,
            DATA_OPT_MEMDATA,
            _cu
        )


@file_exc_decorator
@pg_crsr_hndlr_decrtr
def cleanup_memhist(del_memhist_transaction, _cu, **kwargs):
    """Cleanup memhist content

    If kwargs.get('commit_later') flag is True, we do not commit the
    transaction here (and only cleanup database tables, not the files),
    meaning that the the cleanup may have been executed before actual
    migration. If commit_later flag is False, we commit the transaction
    here and proceed with the file cleanup.

    Args:
        del_memhist_transaction:     instance of PgTransaction to which this
                                cleanup process is part of
        **kwargs:               Options:
                                commit_later: flag to commit transaction later
                                    or now
                                accountnumber: if we want to filter delete
                                    based on account number

    Raises:
        SystemExit: if IOError, NameError or PermissionError is caught
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    commit_later = kwargs.get("commit_later", False)
    accountnumber = kwargs.get("accountnumber", None)

    accnum_where = {}
    if accountnumber is not None:
        assert type(accountnumber) == str
        accnum_where["accountnumber"] = accountnumber

    hist_tbls_to_clean = ["{}{}".format(
        _cu.lower(), tbl_suffx) for
        tbl_suffx in ["accounthistory", "loanhistory"]]

    for tbl in hist_tbls_to_clean:
        del_memhist_transaction(
            DELETE,
            tbl,
            where_conditions=accnum_where
        )

    if not commit_later:
        memhist_file = MEMHIST_DOWNLOADED_FILE.format(_cu.lower())
        memhist_dir = os.path.dirname(memhist_file)

        accounthistory_file = os.path.join(
            memhist_dir, MEMHIST_ACCOUNT_HISTORY_FILENAME)

        loanhistory_file = os.path.join(
            memhist_dir, MEMHIST_LOAN_HISTORY_FILENAME)

        # cleanup file based contents, first
        for memhist_f in [memhist_file, accounthistory_file, loanhistory_file]:
            if os.path.exists(memhist_f):
                os.remove(memhist_f)

        # commit all the table deletions here
        del_memhist_transaction.commit()
        del_memhist_transaction(
            SUMMARY,
            DATA_OPT_MEMHIST,
            _cu
        )


MEM_HIST_DATA_CONFIG = {
    DATA_OPT_MEMDATA: {
        ACTION_OPT_CLEAN: cleanup_memdata,
        ACTION_OPT_MIGRATE: move_members_data,
        "description": "Members Data",
    },
    DATA_OPT_MEMHIST: {
        ACTION_OPT_CLEAN: cleanup_memhist,
        ACTION_OPT_MIGRATE: move_members_hist,
        "description": "Members History",
    },
    DATA_OPT_SWITCH_ACCOUNTS: {
        ACTION_OPT_MIGRATE: move_switch_accounts,
        "description": "Members Switch Account",
    },
}


@pg_crsr_hndlr_decrtr
def migrate_members(cu,
                    server,
                    action,
                    user,
                    passwd,
                    verbose,
                    data_ctgry,
                    reset):
    """Entry point to the memdata AND history migration.

    Args:
        cu:         current credit union code
        server:     Mammoth endpoint server
        action:     one of the migration options to be
                    performed on settings data category
        user:       Mammoth monitor username
        passwd:     Mammoth monitor password
        data_ctgry: memdata or memhist
        verbose:    level of verbosity
        reset:      flag to cleanup tables before migration

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    # check if the tables already exist
    with pg_handler.PGSession() as conn:
        with conn.cursor() as cur:
            tables_exist_transaction = PgTransaction(conn, cur)

            (tables_exist_flag,
             do_not_exist_list) = tables_exist_transaction(
                TABLE_EXISTS,
                data_ctgry,
                cu.lower()
            )

            if not tables_exist_flag:
                error_msg = ("Stopping migration. Following table"
                             " schemas do not exist: {}").format(
                    get_valid_json(", ".join(do_not_exist_list)))
                LOGGER.error(error_msg)
                raise SystemExit(error_msg)
            else:
                LOGGER.info(
                    "All required target tables exist! Continuing migration..")

    # clean up memdata or memhist
    if action == ACTION_OPT_MIGRATE:
        if reset:
            with pg_handler.PGSession() as conn:
                with conn.cursor() as cur:
                    delete_transaction = PgTransaction(conn, cur)
                    MEM_HIST_DATA_CONFIG[data_ctgry][ACTION_OPT_CLEAN](
                        delete_transaction, cu)

        # log summary before migration
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                summary_transaction = PgTransaction(conn, cur)
                summary_transaction(
                    SUMMARY,
                    data_ctgry,
                    cu,
                    msg_prefix="BEFORE"
                )

        # migrate member data from Mammoth to Odyssey
        if data_ctgry == DATA_OPT_MEMDATA:
            members_list_migration = MammothMigration(
                cu,
                DATA_OPT_MEMBERS,
                server,
                user,
                passwd
            )

            members_list_migration.run()
            # list for memdata
            all_members_list = members_list_migration.response

            # Migrate memdata or switch accounts to Odyssey
            MEM_HIST_DATA_CONFIG[data_ctgry][action](
                all_members_list,
                server,
                verbose,
                cu,
                user,
                passwd
            )

        elif data_ctgry == DATA_OPT_SWITCH_ACCOUNTS:
            switch_acc_members_migration = MammothMigration(
                cu,
                DATA_OPT_SWITCH_ACCT_MEMBERS,
                server,
                user,
                passwd
            )

            switch_acc_members_migration.run()
            # list for memdata
            switch_acc_members_list = switch_acc_members_migration.response

            # Migrate memdata or switch accounts to Odyssey
            MEM_HIST_DATA_CONFIG[data_ctgry][action](
                switch_acc_members_list,
                server,
                verbose,
                cu,
                user,
                passwd
            )

        # migrate member history from Mammoth to Odyssey
        elif data_ctgry == DATA_OPT_MEMHIST:
            MEM_HIST_DATA_CONFIG[data_ctgry][action](
                server,
                verbose,
                cu,
                user,
                passwd
            )

        # log summary after migration
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                summary_transaction = PgTransaction(conn, cur)
                summary_transaction(
                    SUMMARY,
                    data_ctgry,
                    cu,
                    msg_prefix="AFTER"
                )

        LOGGER.info("{} migration completed!"
                    .format(MEM_HIST_DATA_CONFIG[data_ctgry]["description"]))

    # clean up memdata or memhist
    elif action == ACTION_OPT_CLEAN:
        if data_ctgry == DATA_OPT_SWITCH_ACCOUNTS:
            LOGGER.error("`{}` is not available for `{}`".format(
                ACTION_OPT_CLEAN, DATA_OPT_SWITCH_ACCOUNTS))
        else:
            with pg_handler.PGSession() as conn:
                with conn.cursor() as cur:
                    delete_transaction = PgTransaction(conn, cur)
                    MEM_HIST_DATA_CONFIG[data_ctgry][
                        action](delete_transaction, cu)

            LOGGER.info("{} clean up completed!".format(
                MEM_HIST_DATA_CONFIG[data_ctgry]["description"]))

    # log summary of table records count
    elif action == ACTION_OPT_SUMMARY:
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                summary_transaction = PgTransaction(conn, cur)
                summary_transaction(
                    SUMMARY,
                    data_ctgry,
                    cu
                )