ody_migr_loans.py

#!/usr/bin/env python
"""Main module to migrate loanapps data."""


import logging
import csv
import os
import json
# import only necessary variables for settings
from ody_migr_config import (INSERT,
                             INSERT_ONE,
                             SELECT_RETURN,
                             DELETE,
                             SUMMARY,
                             TABLE_EXISTS,
                             DATA_OPT_LOANAPP,
                             ACTION_OPT_CLEAN,
                             ACTION_OPT_MIGRATE,
                             ACTION_OPT_SUMMARY,
                             LOANAPP_SCHEMAMASTER_LOANID_MAP_CSV_FILE,
                             LOANAPP_USER_USERID_MAP_CSV_FILE)

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_strip_value_dict,
                            file_exc_decorator,
                            log_progress)

LOGGER = logging.getLogger(__name__)


@pg_crsr_hndlr_decrtr
def move_loanapps(loans_transaction,
               mmth_loanapp_dict,
               verbose,
               cucode):
    """Migrate loanapps data in a single transaction

    Args:
        loans_transaction:  transaction to commit all insertions for loanapps
        mmth_loanapp_dict:   response dictionary from Mammoth for loanapps
        verbose:           verbosity flag
        cucode:                current credit union code

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    mmth_loanapp_data = mmth_loanapp_dict["data"]
    progress_dict = {"completed": 0, "total": 5}

    # 1. Populate lnappschemamaster table (also maintain mapping of
    # old and new loanid)
    mmth_lnappschemamaster_records = mmth_loanapp_data["lnappschemamaster"]
    # store old and new loanid mapping
    lnappschemamaster_loanid_map = {}

    # also write old loanid to new loanid mapping to a csv file for bookkeeping
    with open(LOANAPP_SCHEMAMASTER_LOANID_MAP_CSV_FILE.format(cucode.lower()),
              'w', newline='') as loanidmap_csvfile:
        loanid_columns = ["mammoth_lnappschemamaster_loanid",
                      "odyssey_lnappschemamaster_loanid"]
        csvwriter_loanid = csv.writer(loanidmap_csvfile, delimiter=",",
                                      quotechar='|',quoting=csv.QUOTE_MINIMAL)
        csvwriter_loanid.writerow(loanid_columns)

        for lnappschemamaster_rec in mmth_lnappschemamaster_records:
            old_loanid = lnappschemamaster_rec["loanid"].strip()
            # allow creating new loanid
            del lnappschemamaster_rec["loanid"]
            # insert and get back the new loanid
            loans_transaction(
                INSERT_ONE,
                "lnappschemamaster",
                list(lnappschemamaster_rec.keys()),
                list(lnappschemamaster_rec.values()),
                returning_col="loanid"
            )
            new_loanid = loans_transaction.cur.fetchone()[0]
            # maintain mapping of old loanid to new loanid
            lnappschemamaster_loanid_map[old_loanid] = new_loanid
            # write to a csv file
            csvwriter_loanid.writerow([old_loanid, new_loanid])

    progress_dict["completed"] += 1
    log_progress(progress_dict)

    # 2. Populate lnappschemadetail table
    lnappschemadetail_detailid_map = {}
    mmth_lnappschemadetail_records = mmth_loanapp_data["lnappschemadetail"]
    for lnappschemadetail_rec in mmth_lnappschemadetail_records:
        old_detailid = lnappschemadetail_rec["detailid"].strip()
        # allow creating new detailid
        del lnappschemadetail_rec["detailid"]

        lnappschemadetail_rec["loanid"] = lnappschemamaster_loanid_map[
            lnappschemadetail_rec["loanid"].strip()]

        # insert and get back the new detailid
        loans_transaction(
            INSERT_ONE,
            "lnappschemadetail",
            list(lnappschemadetail_rec.keys()),
            list(lnappschemadetail_rec.values()),
            returning_col="detailid"
        )
        new_detailid = loans_transaction.cur.fetchone()[0]
        # maintain mapping of old detaild to new detailid to be used
        # for lnappuserresponse.respapplication formfield mapping
        lnappschemadetail_detailid_map[old_detailid] = new_detailid

    progress_dict["completed"] += 1
    log_progress(progress_dict)

    # 3. Populate lnappuser table
    mmth_lnappuser_records = mmth_loanapp_data["lnappuser"]
    # store old and new lnappuser userid mapping
    lnappuser_userid_map = {}
    mmth_userids_with_no_ody_banking_user_id = []

    # also write old userid to new userid mapping to a csv file for bookkeeping
    with open(LOANAPP_USER_USERID_MAP_CSV_FILE.format(cucode.lower()),
              'w', newline='') as useridmap_csvfile:

        userid_columns = ["mammoth_lnappuser_userid",
                          "odyssey_lnappuser_userid"]
        csvwriter_userid = csv.writer(useridmap_csvfile, delimiter=",",
                                      quotechar='|',quoting=csv.QUOTE_MINIMAL)
        csvwriter_userid.writerow(userid_columns)

        for lnappuser_rec in mmth_lnappuser_records:
            old_userid = get_strip_value_dict("userid", lnappuser_rec)
            # allow creating new userid
            del lnappuser_rec["userid"]

            # drop user_name, and populate session_account
            mmth_banking_user_name = lnappuser_rec["user_name"]
            del lnappuser_rec["user_name"]
            lnappuser_rec["session_account"] = mmth_banking_user_name

            # Populate banking_user_id if associated Mammoth lnappuser.user_name
            # has corresponding Odyssey userid (<cu>memberacct.primary_user)
            # If not, log the list of unassociated Mammoth loanappuser.userid
            if mmth_banking_user_name not in [None, ""]:
                ody_banking_user_id = loans_transaction(
                    SELECT_RETURN,
                    "{}memberacct".format(cucode.lower()),
                    where_conditions={"accountnumber": mmth_banking_user_name},
                    select_columns=["primary_user"]
                )
                if(len(ody_banking_user_id) > 0):
                    # Eg. [(xxxx,)]
                    lnappuser_rec["banking_user_id"] = ody_banking_user_id[0][0]
                else:
                    mmth_userids_with_no_ody_banking_user_id.append(old_userid)

            # insert loan app user one by one
            loans_transaction(
                INSERT_ONE,
                "lnappuser",
                list(lnappuser_rec.keys()),
                list(lnappuser_rec.values()),
                returning_col="userid"
            )
            new_userid = loans_transaction.cur.fetchone()[0]
            # maintain mapping of old userid to new userid
            lnappuser_userid_map[old_userid] = new_userid
            # write to csv file
            csvwriter_userid.writerow([old_userid, new_userid])

    if len(mmth_userids_with_no_ody_banking_user_id) > 0:
        LOGGER.warning("`{}` Loanapps user(s) (lnappuser.userids) in Mammoth"
                       " were found to have no associated Odyssey userid"
                       " (<cu>memberacct.primary_user)."
                       " Developers can refer to the mapping file to look"
                       " at the Odyssey lnappuser.userids if needed."
                       " Mammoth lnappuser.userids : [{}]".format(
                           len(mmth_userids_with_no_ody_banking_user_id),
                           ", ".join(mmth_userids_with_no_ody_banking_user_id)))

    progress_dict["completed"] += 1
    log_progress(progress_dict)

    # 4. Populate lnappuser_questselect table
    mmth_lnappuser_questselect_records = mmth_loanapp_data["lnappuser_questselect"]
    for lnappuser_questselect_rec in mmth_lnappuser_questselect_records:
        lnappuser_questselect_rec["userid"] = lnappuser_userid_map[lnappuser_questselect_rec["userid"].strip()]

    loans_transaction(
        INSERT,
        "lnappuser_questselect",
        collection=mmth_lnappuser_questselect_records
    )

    progress_dict["completed"] += 1
    log_progress(progress_dict)

    # 5. Populate lnappuserresponse table
    mmth_lnappuserresponse_records = mmth_loanapp_data["lnappuserresponse"]

    invalid_responses_indices = []

    for ind, lnappuserresponse_rec in enumerate(mmth_lnappuserresponse_records):
        # allow creating new respid
        del lnappuserresponse_rec["respid"]

        # look for instances where userid's in lnappuserresponse
        # table have no associated userid's in lnappuser table
        # if found any, mark for deletion and continue
        if lnappuserresponse_rec["userid"].strip() not in lnappuser_userid_map:
            invalid_responses_indices.append(ind)
            continue

        # update userid and loanid with the ones created newly on Odyssey
        lnappuserresponse_rec["userid"] = lnappuser_userid_map[lnappuserresponse_rec["userid"].strip()]
        lnappuserresponse_rec["loanid"] = lnappschemamaster_loanid_map[lnappuserresponse_rec["loanid"].strip()]

        # exists in www{3,6}, does not exist in www4 and Odyssey
        if "resploandesc" in lnappuserresponse_rec:
            del lnappuserresponse_rec["resploandesc"]

        # respapplication field is json_encoded text; load as a dictionary
        respapplication = json.loads(lnappuserresponse_rec["respapplication"])

        # update old detailid with new detailid in
        # formfield keys in respapplication column
        # update only the content in the json object format; must include
        # the keys that is in the form 'formfield_DETAILID'
        if type(respapplication) == dict:
            updated_respapplication = {}
            for k_resp_formfield in list(respapplication.keys()):
                assert "formfield" in k_resp_formfield
                ffield, resp_old_detailid = k_resp_formfield.strip().split("_")
                # prepare a new formfield key using the new detailid
                new_k_resp_formfield = "{}_{}".format(
                    ffield,
                    lnappschemadetail_detailid_map[resp_old_detailid])
                updated_respapplication[new_k_resp_formfield] = respapplication[k_resp_formfield]
            # update the respapplication content with updated detailid in the formfield
            lnappuserresponse_rec["respapplication"] = get_valid_json(updated_respapplication)

    # remove lnappuserresponse records
    [mmth_lnappuserresponse_records.pop(i) for i in sorted(invalid_responses_indices, reverse=True)]

    loans_transaction(
        INSERT,
        "lnappuserresponse",
        collection=mmth_lnappuserresponse_records
    )

    progress_dict["completed"] += 1
    log_progress(progress_dict)


@file_exc_decorator
@pg_crsr_hndlr_decrtr
def cleanup_loanapps(del_loans_transaction, cucode, **kwargs):
    """Cleanup loanapps content

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

    Args:
        del_loans_transaction:    instance of PgTransaction to which this
                                    cleanup process is part of
        cucode:                        current credit union code
        **kwargs:               Optional parameters:
                                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)
    where_condition_cu = {"cu": cucode}

    # obtain list of associated loanids for cucode
    lnappschemamaster_loanids = del_loans_transaction(
        SELECT_RETURN,
        "lnappschemamaster",
        select_columns=["loanid"],
        where_conditions=where_condition_cu
    )

    # obtain list of associated userids for loanapp users for cucode
    lnappuser_userids = del_loans_transaction(
        SELECT_RETURN,
        "lnappuser",
        select_columns=["userid"],
        where_conditions=where_condition_cu
    )


    # Delete lnappschemadetail and lnappuserresponse records based
    # on loanids for cucode
    # Eg. select * from lnappschemadetail where loanid in
    #     (select loanid from lnappschemamaster where cu=CUCODE);
    for tbl_del_loanid in ["lnappschemadetail", "lnappuserresponse"]:
        for lnappschemamaster_loanid in lnappschemamaster_loanids:
            del_loans_transaction(
                DELETE,
                tbl_del_loanid,
                where_conditions={"loanid": lnappschemamaster_loanid[0]}
            )

    # Delete lnappuser_questselect and lnappuserresponse records based
    # on loanids for cucode
    # Eg. delete from lnappuser_questselect where userid in
    #     (select userid from lnappuser WHERE cu=CUCODE);
    for tbl_del_userid in ["lnappuser_questselect", "lnappuserresponse"]:
        for lnappuser_userid in lnappuser_userids:
            del_loans_transaction(
                DELETE,
                tbl_del_userid,
                where_conditions={"userid": lnappuser_userid[0]}

            )

    # cleanup loanapp based tables conditioned on cucode
    # Eg. delete from {lnappschemamaster, lnappuser}
    #     where cu='CUCODE'
    for tbl in ["lnappschemamaster", "lnappuser"]:
        del_loans_transaction(
            DELETE,
            tbl,
            where_conditions=where_condition_cu
        )

    if os.path.exists(LOANAPP_SCHEMAMASTER_LOANID_MAP_CSV_FILE.format(cucode.lower())):
        os.remove(LOANAPP_SCHEMAMASTER_LOANID_MAP_CSV_FILE.format(cucode.lower()))

    if os.path.exists(LOANAPP_USER_USERID_MAP_CSV_FILE.format(cucode.lower())):
        os.remove(LOANAPP_USER_USERID_MAP_CSV_FILE.format(cucode.lower()))

    if not commit_later:
        # commit all the table deletions here
        del_loans_transaction.commit()
        del_loans_transaction(
            SUMMARY,
            DATA_OPT_LOANAPP,
            cucode
        )


@pg_crsr_hndlr_decrtr
def migrate_loanapps(cu, server, action, user, passwd, verbose, reset):
    """Entry point to the loanapps 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
        verbose:    level of verbosity
        reset:      flag to cleanup tables before migration

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    # check if the required tables 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_OPT_LOANAPP,
                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..")

    # migrate loanapps data
    if action == ACTION_OPT_MIGRATE:
        if reset:
            # clean up loanapps data
            with pg_handler.PGSession() as conn:
                with conn.cursor() as cur:
                    delete_transaction = PgTransaction(conn, cur)
                    cleanup_loanapps(delete_transaction, cu)

        # Get loanapps from Mammoth
        loanapps_migration = MammothMigration(
            cu,
            DATA_OPT_LOANAPP,
            server,
            user,
            passwd
        )
        loanapps_migration.run()

        LOGGER.info("Initiating migrating response data to odyssey")

        # migrate to Odyssey
        # start a transaction to migrate memdata for each member
        # that is not migrated yet to Odyssey
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                loans_transaction = PgTransaction(conn, cur)
                # log summary before migration
                loans_transaction(
                    SUMMARY,
                    DATA_OPT_LOANAPP,
                    cu,
                    msg_prefix="BEFORE"
                )
                # we will only commit this cleanup later together with
                # all the migrations for settings
                cleanup_loanapps(loans_transaction, cu, commit_later=True)

                # migrate obtained response to Odyssey
                move_loanapps(
                    loans_transaction,
                    loanapps_migration.response,
                    verbose,
                    cu
                )
                loans_transaction.commit()

                # log summary after migration
                loans_transaction(
                    SUMMARY,
                    DATA_OPT_LOANAPP,
                    cu,
                    msg_prefix="AFTER"
                )

        LOGGER.info("Loan apps migration completed!")

    # clean up loanapps data
    elif action == ACTION_OPT_CLEAN:
        with pg_handler.PGSession() as conn:
            with conn.cursor() as cur:
                delete_transaction = PgTransaction(conn, cur)
                cleanup_loanapps(delete_transaction, cu)

        LOGGER.info("Loan apps records clean up completed!")

    # log display loanapps tables' records count summary
    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_OPT_LOANAPP,
                    cu
                )