ody_migr_admin.py

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


import logging
import csv
import os
# import only necessary variables for settings
from ody_migr_config import (INSERT,
                             INSERT_ONE,
                             SELECT_RETURN,
                             DELETE,
                             SUMMARY,
                             TABLE_EXISTS,
                             DATA_OPT_ADMIN,
                             ACTION_OPT_CLEAN,
                             ACTION_OPT_MIGRATE,
                             ACTION_OPT_SUMMARY,
                             ADMIN_CUSURVEY_ID_MAP_CSV_FILE,
                             CUADMINPROGS_PERMISSIONS_MAPPING)

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_admin(admin_transaction,
               _mmth_admin_dict,
               _verbose,
               _cu):
    """Migrate admin data in a single transaction

    Args:
        admin_transaction:  transaction to commit all insertions for admin
        _mmth_admin_dict:   response dictionary from Mammoth for admin
        _verbose:           verbosity flag
        _cu:                current credit union code

    Raises:
        psycopg2.Error, psycopg2.Warning on db operation errors
    """
    mmth_admin_data = _mmth_admin_dict["data"]

    progress_dict = {"completed": 0, "total": 16}

    # First populate admin user specific tables
    mmth_admin_users_coll = mmth_admin_data["adminusers"]
    for adminuser_dict in mmth_admin_users_coll:
        cuadminusers_coll = adminuser_dict["cuadminusers"]

        # no content for cuadminusers
        if type(cuadminusers_coll) == list:
            assert len(cuadminusers_coll) == 0
            cuadminusers_coll = {}

        else:
            assert type(cuadminusers_coll) == dict
            mfaquest = get_valid_json({})  # default
            mfaquest_dict = {"answers": {}}

        # Prepare cuadminusers.mfaquest field using cuadmquestselect
        # records from Mammoth. Do not populate cuadmquestselect
        # table: this table is not used in Odyssey. Eventually, will
        # be deleted from Odyssey.
        cuadmquestselect_coll = adminuser_dict["cuadmquestselect"]
        if len(cuadmquestselect_coll) != 0:
            for cuquest_record in cuadmquestselect_coll:
                if cuadminusers_coll != {}:
                    qid = cuquest_record["quest_id"].strip()
                    ans = cuquest_record["answer"].strip()
                    mfaquest_dict["answers"][qid] = ans

        # populate cuadminusers table
        if cuadminusers_coll != {}:
            mfaquest = get_valid_json(mfaquest_dict)
            cuadminusers_coll["mfaquest"] = mfaquest
            # Make cuadminusers.user_name lowercase
            cuadminusers_coll["user_name"] = cuadminusers_coll[
                "user_name"].lower()
            admin_transaction(
                INSERT_ONE,
                "cuadminusers",
                list(cuadminusers_coll.keys()),
                list(cuadminusers_coll.values())
            )

        # populate cuadminallow, cuadminexclude tables
        for tbl_allow_exclude_progs in ["cuadminallow", "cuadminexclude"]:

            # populate cuadminallow, cuadminexclude tables
            cuadmin_allow_exclude_data = adminuser_dict[
                tbl_allow_exclude_progs]

            for ind in range(len(cuadmin_allow_exclude_data)):
                cuadminallow_rec = cuadmin_allow_exclude_data[ind]
                cuadminallow_program = get_strip_value_dict("program",
                                                            cuadminallow_rec)

                # Make {cuadminallow, cuadminexclude}.user_name lowercase
                cuadminallow_rec["user_name"] =\
                    cuadminallow_rec["user_name"].lower()

                if cuadminallow_program in CUADMINPROGS_PERMISSIONS_MAPPING:
                    cuadminallow_rec["program"] = \
                        CUADMINPROGS_PERMISSIONS_MAPPING[cuadminallow_program]

            admin_transaction(
                INSERT,
                tbl_allow_exclude_progs,
                collection=cuadmin_allow_exclude_data
            )

        # populate cuauditadmin table: records to be populated are already
        # preprocessed on Mammoth side
        cuauditadmin_coll = adminuser_dict["cuauditadmin"]

        for cuaudadm_rec in cuauditadmin_coll:
            # Make cuauditadmin.user_name and cuauditadmin.auditsrcuser_name
            # lowercase
            cuaudadm_rec["user_name"] = cuaudadm_rec["user_name"].lower()

            mmth_cu = get_strip_value_dict(
                "cu", cuaudadm_rec)
            assert mmth_cu == _cu
            del cuaudadm_rec["admuser"]
            cuaudadm_rec["auditdate"] = get_strip_value_dict(
                "chdate", cuaudadm_rec, pop=True)
            cuaudadm_rec["auditaction"] = get_strip_value_dict(
                "action", cuaudadm_rec, pop=True)
            cuaudadm_rec["auditsrctype"] = "A"
            cuaudadm_rec["auditsrcuser_name"] = get_strip_value_dict(
                "user_name", cuaudadm_rec)
            cuaudadm_rec["auditsrcemail"] = get_strip_value_dict(
                "email", cuaudadm_rec, pop=True, default="a@b.cde")
            cuaudadm_rec["auditsrcip"] = "127.0.0.1"

        admin_transaction(
            INSERT,
            "cuauditadmin",
            collection=cuauditadmin_coll
        )

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

    cusurvey_surveyid_map = {}
    mmth_cusurveymaster_records = mmth_admin_data["cusurveymaster"]
    for cusurvey_rec in mmth_cusurveymaster_records:
        old_id = cusurvey_rec["surveyid"].strip()
        del cusurvey_rec["surveyid"]
        admin_transaction(
            INSERT_ONE,
            "cusurveymaster",
            list(cusurvey_rec.keys()),
            list(cusurvey_rec.values()),
            returning_col="surveyid"
        )
        new_id = admin_transaction.cur.fetchone()[0]
        cusurvey_surveyid_map[old_id] = new_id

    adm_tbls_cusurvey = ["cusurveyquest", "cusurveydetail"]
    for tbl_cusurvey in adm_tbls_cusurvey:
        tbl_coll = mmth_admin_data[tbl_cusurvey]

        for cusurvey_other_rec in tbl_coll:
            assert "surveyid" in cusurvey_other_rec
            cusurvey_other_rec["surveyid"] = cusurvey_surveyid_map[
                cusurvey_other_rec["surveyid"].strip()]

        admin_transaction(
            INSERT,
            tbl_cusurvey,
            collection=tbl_coll
        )

    with open(ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(_cu.lower()),
              'w',
              newline='') as cusurvey_csvfile:
        cusurvey_csvwriter = csv.writer(
            cusurvey_csvfile, delimiter=',',
            quotechar='|',
            quoting=csv.QUOTE_MINIMAL
        )
        cusurvey_csvwriter.writerow(["cusurveymaster_surveyid_old, cusurveymaster_surveyid_new"])
        for survey_oldid, survey_newid in cusurvey_surveyid_map.items():
            cusurvey_csvwriter.writerow([survey_oldid, survey_newid])

    # apply identity transformation and insert as is to the following
    # tables with no schema change on Mamoth and Odyssey
    adm_tbls_no_schema_chng = [
        "cuadmnotify",
        "cuhavetrans",
        "cusurveyintro",
        "cutrusteddetail"
    ]

    for tbl in adm_tbls_no_schema_chng:
        admin_transaction(
            INSERT,
            tbl,
            collection=mmth_admin_data[tbl]
        )

    # populate cualertmsgs -- reset alertid (unique index key constraint)
    mmth_admin_cualertmsgs_coll = mmth_admin_data["cualertmsgs"]
    for cualertmsgs_record in mmth_admin_cualertmsgs_coll:
        # reset cualertmsgs.alertid
        del cualertmsgs_record["alertid"]

    admin_transaction(
        INSERT,
        "cualertmsgs",
        collection=mmth_admin_cualertmsgs_coll
    )

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

    # populate cucontact table
    cucontact_coll = mmth_admin_data["cucontact"]
    admin_transaction(
        INSERT,
        "cucontact",
        collection=cucontact_coll
    )

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

    # populate <cu>extkey table
    extkey_coll = mmth_admin_data["extkey"]
    for extkey_rec in extkey_coll:
        extk_accnum = get_strip_value_dict("accountnumber", extkey_rec)
        assert extk_accnum is not None
        user_id = admin_transaction(
            SELECT_RETURN,
            "{}memberacct".format(_cu.lower()),
            where_conditions={"accountnumber": extk_accnum},
            select_columns=["primary_user"]
        )
        # assert len(user_id) == 1
        if len(user_id) == 1:
            user_id = user_id[0][0]
        else:
            # used just for cudrop test case (sometimes this script might be
            # executed with no user_id in cuuser table)
            user_id = "1000"
        extkey_rec["user_id"] = user_id

    admin_transaction(
        INSERT,
        "{}extkey".format(_cu.lower()),
        collection=extkey_coll
    )
    progress_dict["completed"] += 1
    log_progress(progress_dict)


    # populate cualtroute
    cualtroute_coll = mmth_admin_data["cualtroute"]
    admin_transaction(
        INSERT,
        "cualtroute",
        collection=cualtroute_coll
    )

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


@file_exc_decorator
@pg_crsr_hndlr_decrtr
def cleanup_admin(del_admin_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 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_memhist_transaction:    instance of PgTransaction to which this
                                    cleanup process is part of
        _cu:                        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": _cu}
    for tbl in ["cuauditadmin", "cuadminallow", "cuadminexclude"]:
        del_admin_transaction(
            DELETE,
            tbl,
            where_conditions=where_condition_cu
        )

    admin_unames = del_admin_transaction(
        SELECT_RETURN,
        "cuadminusers",
        select_columns=["user_name"],
        where_conditions=where_condition_cu
    )

    for tbl in ["cucontact"]:
        for admin_uname in admin_unames:
            del_admin_transaction(
                DELETE,
                tbl,
                where_conditions={"user_name": admin_uname}
            )

    del_admin_transaction(
        DELETE,
        "cuadminusers",
        where_conditions=where_condition_cu
    )

    adm_tbls_no_schema_chng = [
        "cusurveyquest",
        "cusurveydetail",
        "cusurveymaster",
        "cualertmsgs",
        "cuadmnotify",
        "cusurveyintro",
        "cuhavetrans",
        "cutrusteddetail",  # TODO, check parms encryption handling
        "cualtroute"

    ]
    for tbl in adm_tbls_no_schema_chng:
        del_admin_transaction(
            DELETE,
            tbl,
            where_conditions=where_condition_cu
        )

    del_admin_transaction(
        DELETE,
        "{}extkey".format(_cu.lower())
    )

    if os.path.exists(ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(_cu.lower())):
        os.remove(ADMIN_CUSURVEY_ID_MAP_CSV_FILE.format(_cu.lower()))

    if not commit_later:
        # commit all the table deletions here
        del_admin_transaction.commit()
        del_admin_transaction(
            SUMMARY,
            DATA_OPT_ADMIN,
            _cu
        )


@pg_crsr_hndlr_decrtr
def migrate_admin(cu, server, action, user, passwd, verbose, reset):
    """Entry point to the admin 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_ADMIN,
                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 admin data
    if action == ACTION_OPT_MIGRATE:
        if reset:
            # clean up admin data
            with pg_handler.PGSession() as conn:
                with conn.cursor() as cur:
                    delete_transaction = PgTransaction(conn, cur)
                    cleanup_admin(delete_transaction, cu)

        # get members list/content from Mammoth
        admin_migration = MammothMigration(
            cu,
            DATA_OPT_ADMIN,
            server,
            user,
            passwd
        )
        admin_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:
                admin_transaction = PgTransaction(conn, cur)
                # log summary before migration
                admin_transaction(
                    SUMMARY,
                    DATA_OPT_ADMIN,
                    cu,
                    msg_prefix="BEFORE"
                )
                # we will only commit this cleanup later together with
                # all the migrations for settings
                cleanup_admin(admin_transaction, cu, commit_later=True)

                # migrate obtained response to Odyssey
                move_admin(
                    admin_transaction,
                    admin_migration.response,
                    verbose,
                    cu
                )
                admin_transaction.commit()

                # log summary after migration
                admin_transaction(
                    SUMMARY,
                    DATA_OPT_ADMIN,
                    cu,
                    msg_prefix="AFTER"
                )

        LOGGER.info("Admin migration completed!")

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

        LOGGER.info("Admin clean up completed!")

    # log display admin 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_ADMIN,
                    cu
                )