""" NAME
        fix_foreignkey_is_null

    SYNOPSIS
        fix_foreignkey_is_null

    DESCRIPTION
        It happens that foreign field 'publications.id_collaborations'
        and 'publications.id_teams' have the value NULL instead of 1.

        This script fix this issues.

    OPTIONS

    EXAMPLE

        > cd ...limbra/scripts
        > run script myapp fix_foreignkey_is_null.py

    AUTHOR
        R. Le Gac -- Jan 2021

"""
import logging
from plugin_dbui import UNDEF_ID


def cli():

    # start logger
    logger = logging.getLogger()
    logger.setLevel(logging.INFO)

    formatter = logging.Formatter(
        "%(asctime)s %(levelname)s %(message)s",
        datefmt="%H:%M:%S")

    logger.handlers[0].setFormatter(formatter)

    # list of foreign fields
    fields = ["id_collaborations",
              "id_publishers",
              "id_countries",
              "id_reports",
              "id_authors_roles",
              "id_teams",
              "id_projects",
              "id_categories",
              "id_status"]

    for field in fields:
        logger.info(f"scan foreign field '{field}'")

        query = db.publications[field] == None
        rows = db(query).iterselect(db.publications.id)

        for row in rows:
            print(f"    fix {row.id}", type(row.id))
            dct = dict()
            dct[field] = UNDEF_ID
            db(db.publications.id == row.id).update(**dct)

    rep = input("\n    Commit change (y/N): ")
    if rep in ("y", "Y"):
        db.commit()
        logger.info("database modified")


if __name__ == "__main__":
    cli()