#!/usr/bin/env python3
#
# ex:ts=4:sw=4:sts=4:et
# -*- tab-width: 4; c-basic-offset: 4; indent-tabs-mode: nil -*-
#
# Security Response Tool Commandline Tool
#
# Copyright (C) 2018-2020  Wind River Systems
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.


import os
import sys
import argparse
import sqlite3
from datetime import datetime, date
import time
import re
import subprocess
import json
import xml.etree.ElementTree as ET

# load the srt.sqlite schema indexes
dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
sys.path.insert(0, dir_path)
#from common.srt_schema import ORM

# Setup:
verbose = False
cmd_skip = 0
cmd_count = 0
force = False

srtDbName = 'srt.sqlite'
srtSchemaName = 'srt_schema.py'

#################################
# Common routines
#

# quick development/debugging support
def _log(msg):
    DBG_LVL =  os.environ['SRTDBG_LVL'] if ('SRTDBG_LVL' in os.environ) else 2
    DBG_LOG =  os.environ['SRTDBG_LOG'] if ('SRTDBG_LOG' in os.environ) else '/tmp/srt_dbg.log'
    if 1 == DBG_LVL:
        print(msg)
    elif 2 == DBG_LVL:
        f1=open(DBG_LOG, 'a')
        f1.write("|" + msg + "|\n" )
        f1.close()

# Sub Process calls
def execute_process(*args):
    cmd_list = []
    for arg in args:
        if isinstance(arg, (list, tuple)):
            # Flatten all the way down
            for a in arg:
                cmd_list.append(a)
        else:
            cmd_list.append(arg)

    # Python < 3.5 compatible
    if sys.version_info < (3,5):
        process = subprocess.Popen(cmd_list, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        try:
            stdout, stderr = process.communicate(input)
        except:
            process.kill()
            process.wait()
            raise
        retcode = process.poll()
        return retcode, stdout, stderr
    else:
        result = subprocess.run(cmd_list, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        return result.returncode,result.stdout,result.stderr

#################################
# ORM mapping for the given database file
#

# ORM mapping for the given database file
class ORM_Class(object):
    # Members will be added dynamically

    # General routine to return string name of a constant (e.g. 'DATASOURCE_FREQUENCY_STR')
    @staticmethod
    def get_orm_string(value,string_set):
        if None == value: return('None')
        string_list = string_set.split(',')
        string_count = len(string_list)
        value = int(value)
        if (value < 0) or (value >= string_count):
            print("ERROR: value '%d' out of range of '%s'" % (value,string_set))
            return '<error>'
        return string_list[value]

# Instantiate the ORM class object
ORM = ORM_Class()

# Attach the specific database schema attibutes and values
def import_orm_schema(databaseDir):
    global ORM

    # Local or generate database schema file
    if not databaseDir or ('.' == databaseDir):
        # Normal database and schema locations
        schema = os.path.join(databaseDir,'bin/common',srtSchemaName)
    else:
        # Remote database location (e.g. backups)
        schema = os.path.join(databaseDir,srtSchemaName)
        # Generate the schema file if not found
        if not os.path.isfile(schema):
            ret = os.system("%s  --generate-schema-header-dir %s" % (os.path.join(srtool_basepath,'bin/common/srtool_common.py'),databaseDir))

    with open(schema) as fp:
        for line in fp:
            try:
                name = line[:line.index('=')].strip()
                value = line[line.index('=')+1:].strip()
                if '"' == value[0]:
                    value = value[1:-1]
                elif "'" == value[0]:
                    value = value[1:-1]
                else:
                    value = int(value)
            except:
                continue
            setattr(ORM, name, value)

#################################
# reset sources
#

# source_data = (source_id)
def commit_to_source(conn, source_data):
    sql = ''' UPDATE orm_datasource
              SET loaded = ?
              WHERE id = ?'''
    cur = conn.cursor()
    print("UPDATE_SCORE:%s" % str(source_data))
    cur.execute(sql, source_data)

def sources(cmnd):
    conn = sqlite3.connect(srtDbName)
    c = conn.cursor()

    print('Sources(%s)' % cmnd)

    c.execute("SELECT * FROM orm_datasource")
    is_change = False
    for ds in c:
        if 'set' == cmnd:
            commit_to_source(conn,(True,ds[ORM.DATASOURCE_ID]))
            is_change = True
        elif 'reset' == cmnd:
            commit_to_source(conn,(False,ds[ORM.DATASOURCE_ID]))
            is_change = True
        elif 'reset_not_nist' == cmnd:
            if 'nist' != ds[ORM.DATASOURCE_SOURCE]:
                print("RESETTING Data source [%s] data='%s' of '%s' load state from '%s' is '%s'" % (ds[ORM.DATASOURCE_ID],ds[ORM.DATASOURCE_DATA],ds[ORM.DATASOURCE_DESCRIPTION],ds[ORM.DATASOURCE_SOURCE],ds[ORM.DATASOURCE_LOADED]))
                commit_to_source(conn,(False,ds[ORM.DATASOURCE_ID]))
            else:
                commit_to_source(conn,(True,ds[ORM.DATASOURCE_ID]))
            is_change = True
        elif 'triage_keywords' == cmnd:
            if 'triage_keywords' == ds[ORM.DATASOURCE_DATA]:
                print("RESETTING Data source [%s] data='%s' of '%s' load state from '%s' is '%s'" % (ds[ORM.DATASOURCE_ID],ds[ORM.DATASOURCE_DATA],ds[ORM.DATASOURCE_DESCRIPTION],ds[ORM.DATASOURCE_SOURCE],ds[ORM.DATASOURCE_LOADED]))
                commit_to_source(conn,(False,ds[ORM.DATASOURCE_ID]))
                is_change = True
        else:
            print("Data source [%s] data='%s' of '%s' load state from '%s' is '%s'" % (ds[ORM.DATASOURCE_ID],ds[ORM.DATASOURCE_DATA],ds[ORM.DATASOURCE_DESCRIPTION],ds[ORM.DATASOURCE_SOURCE],ds[ORM.DATASOURCE_LOADED]))

    if is_change:
        conn.commit()


def settings():
    conn = sqlite3.connect(srtDbName)
    c = conn.cursor()

    # Scan the SRTool Settings
    c.execute("SELECT * FROM orm_srtsetting")

    for setting in c:
        print("Setting[%s] = '%s'" % (setting[ORM.SRTSETTING_NAME], setting[ORM.SRTSETTING_VALUE][0:40]))


#################################
# remove_app_sources
#

def remove_app_sources(master_app):
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    # Scan the SRTool Settings
    cur.execute("SELECT * FROM orm_datasource")

    is_change = False
    for setting in cur:
        if master_app == setting[ORM.DATASOURCE_SOURCE]:
            print("Deleting [%s] = '%s','%s'" % (setting[ORM.DATASOURCE_ID], setting[ORM.DATASOURCE_SOURCE], setting[ORM.SRTSETTING_VALUE]))
            sql = 'DELETE FROM orm_datasource WHERE id=?'
            cur_write.execute(sql, (setting[ORM.DATASOURCE_ID],))
            is_change = True

    if is_change:
        conn.commit()
    conn.close()

#################################
# fix_new_reserved
#

# Is this reserved by Mitre? Is '** RESERVED **' within the first 20 char positions?
def fix_new_reserved():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    cur.execute('SELECT * FROM orm_cve WHERE status = "%s"' % ORM.STATUS_NEW)
    i = 0
    j = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
        if (0 == i % 200):
            conn.commit()
        # Development/debug support
        if cmd_skip:
            if i < cmd_skip:
                continue
        if cmd_count:
            if (i - cmd_skip) > cmd_count:
                print("Count return: %s,%s" % (i,cmd_count))
                break

        reserved_pos = cve[ORM.CVE_DESCRIPTION].find('** RESERVED **')
        if (0 <= reserved_pos) and (20 > reserved_pos):
            print("STATUS_NEW_RESERVED:%s:%s:%s" % (cve[ORM.CVE_STATUS],cve[ORM.CVE_NAME],cve[ORM.CVE_DESCRIPTION][:40]))
            # NOTE: we do not touch 'cve.srt_updated' for this background change
            sql = ''' UPDATE orm_cve
                      SET status = ?
                      WHERE id = ?'''
            cur_write.execute(sql, (ORM.STATUS_NEW_RESERVED, cve[ORM.CVE_ID],))
            j += 1
    print("\nCVE COUNT=%5d,%5d" % (i,j))
    conn.commit()

#################################
# fix_new_tags
#

# Fix the None "cve.tags" fields
def fix_new_tags():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    cur.execute('SELECT * FROM orm_cve')
    i = 0
    j = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
        if (0 == i % 200):
            conn.commit()
        # Development/debug support
        if cmd_skip:
            if i < cmd_skip:
                continue
        if cmd_count:
            if (i - cmd_skip) > cmd_count:
                print("Count return: %s,%s" % (i,cmd_count))
                break

        if not cve[ORM.CVE_TAGS]:
            # NOTE: we do not touch 'cve.srt_updated' for this background change
            sql = ''' UPDATE orm_cve
                      SET tags = ?
                      WHERE id = ?'''
            cur_write.execute(sql, ('', cve[ORM.CVE_ID],))
            j += 1
    print("\nCVE COUNT=%5d,%5d" % (i,j))
    conn.commit()

#################################
# fixup fix_name_sort
#

# Recompute all of the CVE name_sort fields
def fix_name_sort():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    cur.execute('SELECT * FROM orm_cve')
    for i,cve in enumerate(cur):
        name_sort = get_name_sort(cve[ORM.CVE_NAME])

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s to %20s\r' % (i,cve[ORM.CVE_NAME],name_sort), end='')
        if (0 == i % 200):
            conn.commit()

        # NOTE: we do not touch 'cve.srt_updated' for this background change
        sql = ''' UPDATE orm_cve
                  SET name_sort = ?
                  WHERE id = ?'''
        cur_write.execute(sql, (name_sort, cve[ORM.CVE_ID],))
    conn.commit()

#################################
# fixup fix_cve_recommend
#

# Reset empty CVE recommend fields to the proper integer zero
def fix_cve_recommend():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    cur.execute('SELECT * FROM orm_cve WHERE recommend = ""')
    i = 0
    fix_count = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')

        #
        # Fix miss-write to lastModifiedDate, missing integer for recommend
        #

        fix = False

        lastModifiedDate = cve[ORM.CVE_LASTMODIFIEDDATE]
        if '0' == lastModifiedDate:
            lastModifiedDate = ''
            fix = True

        recommend = cve[ORM.CVE_RECOMMEND]
        if not recommend:
            recommend = 0
            fix = True

        # NOTE: we do not touch 'cve.srt_updated' for this background change
        if fix:
            sql = ''' UPDATE orm_cve
                      SET recommend = ?, lastModifiedDate = ?
                      WHERE id = ?'''
            cur_write.execute(sql, (recommend, lastModifiedDate, cve[ORM.CVE_ID],))

            fix_count += 1
            if (199 == fix_count % 200):
                conn.commit()

    print("CVE RECOMMEND FIX COUNT=%d of %d" % (fix_count,i))
    if fix_count:
        conn.commit()
    conn.close()

#################################
# fixup fix_srt_dates
#

# Reset older 'date' values as 'datetime' values

def _fix_datetime(value,default):
    if (not value) or (not value[0].isdigit()):
        return(default)
    elif ':' in value:
        return(value)
    else:
        return(datetime.strptime(value, '%Y-%m-%d'))

def _fix_date(value,default):
    if (not value) or (not value[0].isdigit()):
        return(False,default)
    elif not ':' in value:
        return(False,value)
    else:
        value = re.sub('\..*','',value)
        dt = datetime.strptime(value,ORM.DATASOURCE_DATETIME_FORMAT)
        return(True,dt.strftime(ORM.DATASOURCE_DATE_FORMAT))


def fix_srt_datetime(scope):
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    if ('d' == scope) or ('all' == scope):
        cur.execute('SELECT * FROM orm_defect')
        i = 0
        is_change_count = 0
        for defect in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: %20s\r' % (i,defect[ORM.DEFECT_NAME]), end='')
            if (0 == i % 200):
                if force: conn.commit()
            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if (i - cmd_skip) > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            defect_srt_updated = _fix_datetime(defect[ORM.DEFECT_SRT_UPDATED],defect[ORM.DEFECT_DATE_UPDATED])
            if defect_srt_updated == defect[ORM.DEFECT_SRT_UPDATED]:
                continue

            if force:
                sql = ''' UPDATE orm_defect
                          SET srt_updated = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (defect_srt_updated, defect[ORM.DEFECT_ID],))
            is_change_count += 1
        print("DEFECT DATE FIX COUNT=%d/%d" % (is_change_count,i))
        if force: conn.commit()

    # INVESTIGATION DATE FIX COUNT=1089363, real 12m20.041s = 1472 recs/sec
    if ('i' == scope) or ('all' == scope):
        cur.execute('SELECT * FROM orm_investigation')
        i = 0
        is_change_count = 0
        for investigation in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: %20s\r' % (i,investigation[ORM.INVESTIGATION_NAME]), end='')
            if (0 == i % 200):
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if (i - cmd_skip) > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            srt_updated = _fix_datetime(investigation[ORM.INVESTIGATION_SRT_UPDATED],None)
            srt_created = _fix_datetime(investigation[ORM.INVESTIGATION_SRT_CREATED],None)
            if (not srt_updated) or (not srt_created):
                print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,investigation[ORM.INVESTIGATION_ID],investigation[ORM.INVESTIGATION_SRT_UPDATED],investigation[ORM.INVESTIGATION_SRT_CREATED]))
                exit(1)
            if (srt_updated == investigation[ORM.INVESTIGATION_SRT_UPDATED]) and (srt_created == investigation[ORM.INVESTIGATION_SRT_CREATED]):
                continue

            if force:
                sql = ''' UPDATE orm_investigation
                          SET srt_updated = ?, srt_created = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (srt_updated, srt_created, investigation[ORM.INVESTIGATION_ID],))
            is_change_count += 1
        print("INVESTIGATION DATE FIX COUNT=%d/%d" % (is_change_count,i))
        if force: conn.commit()

    # VULNERABILITY DATE FIX COUNT=86585, real 1m2.969s = 1374 recs/sec
    if ('v' == scope) or ('all' == scope):
        cur.execute('SELECT * FROM orm_vulnerability')
        i = 0
        is_change_count = 0
        for vulnerability in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: %20s\r' % (i,vulnerability[ORM.VULNERABILITY_NAME]), end='')
            if (0 == i % 200):
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if (i - cmd_skip) > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            srt_updated = _fix_datetime(vulnerability[ORM.VULNERABILITY_SRT_UPDATED],None)
            srt_created = _fix_datetime(vulnerability[ORM.VULNERABILITY_SRT_CREATED],None)
            if (not srt_updated) or (not srt_created):
                print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,vulnerability[ORM.VULNERABILITY_ID],vulnerability[ORM.VULNERABILITY_SRT_UPDATED],vulnerability[ORM.VULNERABILITY_SRT_CREATED]))
                exit(1)
            if (srt_updated == vulnerability[ORM.VULNERABILITY_SRT_UPDATED]) and (srt_created == vulnerability[ORM.VULNERABILITY_SRT_CREATED]):
                continue

            if force:
                sql = ''' UPDATE orm_vulnerability
                          SET srt_updated = ?, srt_created = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (srt_updated, srt_created, vulnerability[ORM.VULNERABILITY_ID],))
            is_change_count += 1
        print("VULNERABILITY DATE FIX COUNT=%d/%d" % (is_change_count,i))
        if force: conn.commit()

    # CVE DATE FIX COUNT=86585, real 1m2.969s = 1374 recs/sec
    # NOTE: only ACK dates need fixing, received bad apha content from srtool_mitre
    if ('c' == scope) or ('all' == scope):
        cur.execute('SELECT * FROM orm_cve')
        i = 0
        # Sparse updates
        is_change = False
        is_change_count = 0
        for cve in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
            if (0 == i % 200) and is_change:
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
                is_change = False

            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if is_change_count > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            is_change = False

            if cve[ORM.CVE_ACKNOWLEDGE_DATE]:
                acknowledge_date = _fix_datetime(cve[ORM.CVE_ACKNOWLEDGE_DATE],'alpha')
                # If the default 'alpha' happens, then date had bad format and must go away
                if ('alpha' == acknowledge_date) or (acknowledge_date != cve[ORM.CVE_ACKNOWLEDGE_DATE]):
                    acknowledge_date = None
                    is_change = True

            srt_updated = _fix_datetime(cve[ORM.CVE_SRT_UPDATED],None)
            srt_created = _fix_datetime(cve[ORM.CVE_SRT_CREATED],None)
            if (not srt_updated) or (not srt_created):
                print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,cve[ORM.CVE_ID],cve[ORM.CVE_SRT_UPDATED],cve[ORM.CVE_SRT_CREATED]))
                exit(1)
            if (srt_updated != cve[ORM.CVE_SRT_UPDATED]) or (srt_created != cve[ORM.CVE_SRT_CREATED]):
                is_change = True

            # Anything to do?
            if not is_change:
                continue

            if force:
                sql = ''' UPDATE orm_cve
                          SET srt_updated = ?, srt_created = ?, acknowledge_date = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (srt_updated, srt_created, acknowledge_date, cve[ORM.CVE_ID],))
                is_change = True
            is_change_count += 1
        print("CVE DATE FIX COUNT=%d/%d" % (is_change_count,i))
        if force and is_change: conn.commit()

    # Fix CVE History
    if scope in ('ch','all','history'):
        cur.execute('SELECT * FROM orm_cvehistory')
        i = 0
        # Sparse updates
        is_change = False
        is_change_count = 0
        for cve_history in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: \r' % (i), end='')
            if (0 == i % 200) and is_change:
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
                is_change = False

            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if is_change_count > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            updated,history_date = _fix_date(cve_history[ORM.CVEHISTORY_DATE],'')
            if not updated:
                continue

            if force:
                sql = ''' UPDATE orm_cvehistory
                          SET date = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (history_date, cve_history[ORM.CVEHISTORY_ID],))
                is_change = True
            is_change_count += 1

        # Commit all remaining changes
        if force and is_change: conn.commit()
        print("CVE HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))

    # Fix Vulnerability History
    if scope in ('vh','all','history'):
        cur.execute('SELECT * FROM orm_vulnerabilityhistory')
        i = 0
        # Sparse updates
        is_change = False
        is_change_count = 0
        for vulnerabilityhistory in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: \r' % (i), end='')
            if (0 == i % 200) and is_change:
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
                is_change = False

            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if is_change_count > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            updated,history_date = _fix_date(vulnerabilityhistory[ORM.VULNERABILITYHISTORY_DATE],'')
            if not updated:
                continue

            if force:
                is_change = True
                sql = ''' UPDATE orm_vulnerabilityhistory
                          SET date = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (history_date, vulnerabilityhistory[ORM.VULNERABILITYHISTORY_ID],))
                is_change_count += 1

        # Commit all remaining changes
        if force and is_change: conn.commit()
        print("VULNERABILITY HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))

    # Fix Investigation History
    if scope in ('ih','all','history'):
        cur.execute('SELECT * FROM orm_investigationhistory')
        i = 0
        # Sparse updates
        is_change = False
        is_change_count = 0
        for investigation_history in cur:
            i += 1

            # Progress indicator support
            if 0 == i % 10:
                print('%05d: \r' % (i), end='')
            if (0 == i % 200) and is_change:
                if force: conn.commit()
                time.sleep(0.1) # give time for Sqlite to sync
                is_change = False

            # Development/debug support
            if cmd_skip:
                if i < cmd_skip:
                    continue
            if cmd_count:
                if is_change_count > cmd_count:
                    print("Count return: %s,%s" % (i,cmd_count))
                    break

            updated,history_date = _fix_date(investigation_history[ORM.INVESTIGATIONHISTORY_DATE],'')
            if not updated:
                continue

            if force:
                is_change = True
                sql = ''' UPDATE orm_investigationhistory
                          SET date = ?
                          WHERE id = ?'''
                cur_write.execute(sql, (history_date, investigation_history[ORM.INVESTIGATIONHISTORY_ID],))
            is_change_count += 1

        # Commit all remaining changes
        if force and is_change: conn.commit()
        print("INVESTIGATION HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))

#################################
# fixup fix_cve_srt_create
#

# Reset CVE srt_create to NIST release dates
def fix_reset_nist_to_create(cve_prefix):
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    def date_nist2srt(nist_date,default,cve_name,i):
        if not nist_date or (4 > len(nist_date)):
            return default
        try:
            return(datetime.strptime(nist_date, '%Y-%m-%d'))
        except Exception as e:
            print("\n\ndate_nist2srt:%s,%s,%s,%s" % (cve_name,e,cve_name,i))
            exit(1)
            return default

    cur.execute('SELECT * FROM orm_cve WHERE name LIKE "'+cve_prefix+'%"')

    i = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
        if (0 == i % 200):
            conn.commit()
            print('')
        # Development/debug support
        if cmd_skip and (i < cmd_skip): continue
        if cmd_count and ((i - cmd_skip) > cmd_count): break

        nist_released = date_nist2srt(cve[ORM.CVE_PUBLISHEDDATE],cve[ORM.CVE_SRT_CREATED],cve[ORM.CVE_NAME],i)
        nist_modified = date_nist2srt(cve[ORM.CVE_LASTMODIFIEDDATE],cve[ORM.CVE_SRT_UPDATED],cve[ORM.CVE_NAME],i)

        sql = ''' UPDATE orm_cve
                  SET srt_created = ?, srt_updated = ?
                  WHERE id = ?'''
        cur_write.execute(sql, (nist_released, nist_modified, cve[ORM.CVE_ID],))
    print("CVE DATE FIX COUNT=%d" % i)
    conn.commit()

#################################
# fixup fix_missing_create_dates
#

# Reset CVE None creation dates to 2019-01-01, out of the way of reports
def fix_missing_create_dates():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    fix_date =  datetime.strptime('Jan 1 2019', '%b %d %Y')
    fix_count = 0

    cur.execute('SELECT * FROM orm_cve')
    i = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
        if (0 == i % 200):
#            conn.commit()
            #print('')
            pass
        # Development/debug support
        if cmd_skip:
            if i < cmd_skip:
                continue
        if cmd_count:
            if (i - cmd_skip) > cmd_count:
                print("Count return: %s,%s" % (i,cmd_count))
                break

        fix = False
        if not cve[ORM.CVE_SRT_CREATED] or (0 > cve[ORM.CVE_SRT_CREATED].find(':')):
            srt_created = fix_date
            fix = True
        else:
            srt_created = cve[ORM.CVE_SRT_CREATED]
            #srt_created = datetime.strptime(cve[ORM.CVE_SRT_CREATED],'%Y-%m-%d')
        if not cve[ORM.CVE_SRT_UPDATED] or (0 > cve[ORM.CVE_SRT_UPDATED].find(':')):
            srt_updated = fix_date
            fix = True
        else:
            srt_updated = cve[ORM.CVE_SRT_UPDATED]
            #srt_updated = datetime.strptime(cve[ORM.CVE_SRT_UPDATED],'%Y-%m-%d')

        if fix:
            sql = ''' UPDATE orm_cve
                      SET srt_created = ?, srt_updated = ?
                      WHERE id = ?'''
            cur_write.execute(sql, (srt_created, srt_updated, cve[ORM.CVE_ID],))
            fix_count += 1
    print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i))
    conn.commit()

#################################
# fixup fix_public_reserved
#

# Reset CVE 'New-Reserved' if now public from NIST
def fix_public_reserved():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_write = conn.cursor()

    fix_count = 0

    cur.execute('SELECT * FROM orm_cve WHERE status = "%s"' % ORM.STATUS_NEW_RESERVED)
    i = 0
    for cve in cur:
        i += 1

        # Progress indicator support
        if 0 == i % 10:
            print('%05d: %20s %d\r' % (i,cve[ORM.CVE_NAME],cve[ORM.CVE_STATUS]), end='')
        if (0 == i % 200):
            conn.commit()
            #print('')
            pass
        # Development/debug support
        if cmd_skip:
            if i < cmd_skip:
                continue
        if cmd_count:
            if (i - cmd_skip) > cmd_count:
                print("Count return: %s,%s" % (i,cmd_count))
                break

        if  cve[ORM.CVE_CVSSV3_BASESCORE] or cve[ORM.CVE_CVSSV2_BASESCORE]:
            sql = ''' UPDATE orm_cve
                      SET status = ?
                      WHERE id = ?'''
            cur_write.execute(sql, (ORM.STATUS_NEW, cve[ORM.CVE_ID],))
            fix_count += 1
    print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i))
    conn.commit()

#################################
# fix_remove_bulk_cve_history
#

# Remove a specific/accidental set of bulk CVE history updates intended to be background
def fix_remove_bulk_cve_history():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_cve = conn.cursor()
    cur_del = conn.cursor()

    fix_count = 0

    cur.execute('SELECT * FROM orm_cvehistory WHERE date LIKE "2019-03-2%"')

    i = 0
    for cvehistory in cur:
        i += 1

        # Progress indicator support
        if 9 == i % 10:
#            print('%05d: %20s %s    \r' % (i,cvehistory[ORM.CVEHISTORY_COMMENT],cvehistory[ORM.CVEHISTORY_DATE]), end='')
            pass
        if (0 == i % 200):
#            conn.commit()
            #print('')
            pass
        # Development/debug support
        if cmd_skip and (i < cmd_skip): continue
        if cmd_count and ((i - cmd_skip) > cmd_count): break

        if not (cvehistory[ORM.CVEHISTORY_DATE] in ('2019-03-28','2019-03-27')):
            continue
        if not (cvehistory[ORM.CVEHISTORY_COMMENT].startswith("UPDATE(CVE):")):
            continue

        cur_cve.execute('SELECT * FROM orm_cve WHERE id = "%s"' %  cvehistory[ORM.CVEHISTORY_CVE_ID])
        cve = cur_cve.fetchone()
        if not (cve[ORM.CVE_NAME].startswith("CVE-200")):
            continue

        if 19 == fix_count % 20:
            print("%4d) CVE=%s,CH_Comment=%s,CH_Date=%s" % (fix_count,cve[ORM.CVE_NAME],cvehistory[ORM.CVEHISTORY_COMMENT],cvehistory[ORM.CVEHISTORY_DATE]))

        mydata = cur_del.execute("DELETE FROM orm_cvehistory WHERE id=?", (cvehistory[ORM.CVEHISTORY_ID],))
        fix_count += 1

    print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i))
    conn.commit()

#################################
# report_defects_to_products
#
# Report all defects without a product link
#

def report_defects_to_products():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_cve = conn.cursor()
    cur_del = conn.cursor()

    fix_count = 0

    # Find all products
    products = {}
    cur.execute('SELECT * FROM orm_product')
    for product in cur:
        id =  product[ORM.PRODUCT_ID]
        name =  "%s %s %s" % (product[ORM.PRODUCT_NAME],product[ORM.PRODUCT_VERSION],product[ORM.PRODUCT_PROFILE])
        products[id] = name
        print("[%2d] %s" % (id,name))

    # Test product field for all defects
    cur.execute('SELECT * FROM orm_defect')
    i = 0
    for defect in cur:
        i += 1

        # Progress indicator support
        if 99 == i % 100:
            print('%05d: %-20s\r' % (i,defect[ORM.DEFECT_NAME]), end='')
            pass
        if (0 == i % 200):
#            conn.commit()
            #print('')
            pass
        # Development/debug support
        if cmd_skip and (i < cmd_skip): continue
        if cmd_count and ((i - cmd_skip) > cmd_count): break

        product_id = defect[ORM.DEFECT_PRODUCT_ID]
        if not product_id in products:
            print("ERROR:[%5d] %-20s => %s" % (defect[ORM.DEFECT_ID],defect[ORM.DEFECT_NAME],product_id))

#    print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i))
    conn.commit()

#################################
# fix_bad_mitre_init
#

#
# Fix MITRE reserved CVEs that were mistakenly set at "New" instead of
# "New-Reserved" due to column ordering issue in the MITRE "Init" routine.
#
def fix_bad_mitre_init():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_ds = conn.cursor()
    cur_cve = conn.cursor()
    cur_del = conn.cursor()

    new_count = 0
    mitre_count = 0
    cve_name = ''

    nist_source_list = []
    # Find NIST data sources
    cur.execute('SELECT * FROM orm_datasource WHERE source = "nist"')
    for i,ds in enumerate(cur):
        nist_source_list.append(ds[ORM.DATASOURCE_ID])
    print('NIST DataSource List=[%s]' % nist_source_list)

    mitre_source_list = []
    # Find MITRE data sources
    cur.execute('SELECT * FROM orm_datasource WHERE source = "mitre"')
    for i,ds in enumerate(cur):
        mitre_source_list.append(ds[ORM.DATASOURCE_ID])
    print('MITRE DataSource List=[%s]' % mitre_source_list)

    # Find all bad MITRE reserved CVEs
    cur.execute('SELECT * FROM orm_cve WHERE description = ""')
    for i,cve in enumerate(cur):
        new_count += 1

        cur_ds.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
        is_mitre = False
        is_nist = False
        for cvesource in cur_ds:
            if cvesource[ORM.CVESOURCE_DATASOURCE_ID] in mitre_source_list:
                is_mitre = True
            if cvesource[ORM.CVESOURCE_DATASOURCE_ID] in nist_source_list:
                is_nist = True

        if is_mitre and not is_nist:
            mitre_count += 1
            cve_name = cve[ORM.CVE_NAME]

            if force:
                sql = ''' UPDATE orm_cve
                          SET status = ?
                          WHERE id = ?'''
                cur_cve.execute(sql, (ORM.STATUS_NEW_RESERVED,cve[ORM.CVE_ID],))
                conn.commit()

        # Progress indicator support
        if 19 == i % 20:
            print('%05d: %-20s\r' % (i,cve[ORM.CVE_NAME]), end='')
            pass
        if (0 == i % 200):
#            conn.commit()
            print('')
            pass
        # Development/debug support
        if cmd_skip and (i < cmd_skip): continue
        if cmd_count and ((i - cmd_skip) > cmd_count): break

    print("\nCVE NEW_COUNT=%d, mitre=%d, name=%s, database=%s" % (new_count,mitre_count,cve_name,srtDbName))
#    conn.commit()


#
# Fix MITRE CVEs that are missing a description in the top level CVE
# records due to column ordering issue in the MITRE "Init" routine.
#
def fix_bad_mitre_descr(datasource_list):
    conn = sqlite3.connect(srtDbName)
    cur_ds = conn.cursor()
    cur_cs = conn.cursor()
    cur_cve = conn.cursor()
    cur_del = conn.cursor()

    cve_count = 0
    fix_count = 0
    nist_ds_list = {}
    mitre_ds_list = {}
    modified_cve_list = []

    DATA_MAP_DESCRIPTION = 0

    def description_summary(description):
            desc_sum = 0
            for ch in description:
                desc_sum += ord(ch)
            if 37 < len(description):
                description = "%-37s..." % description[:37]
            return("%-40s [sum=%d]" % (description,desc_sum))

    #
    # Gather the MITRE and NIST data source lists
    #

    cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "mitre" ORDER BY key DESC;')
    for i,ds in enumerate(cur_ds):
        mitre_ds_list[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_DESCRIPTION]
    cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "nist" ORDER BY key DESC;')
    for i,ds in enumerate(cur_ds):
       nist_ds_list[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_DESCRIPTION]

    #
    # Iterate over the MITRE data sources
    #

    cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "mitre" ORDER BY key DESC;')
    for i,ds in enumerate(cur_ds):
        # Development/debug support
        if cmd_count and ((cve_count - cmd_skip) > cmd_count):
            break

        if "ALL" == datasource_list:
            pass
        elif not ds[ORM.DATASOURCE_DESCRIPTION] in datasource_list.split(','):
            continue
        print("MITRE Source:%s" % ds[ORM.DATASOURCE_DESCRIPTION])

        # Scan the NIST datasource file and extract required values into a map
        # bin/mitre/srtool_mitre.py --download-only --source='Mitre 2010' --file=data/allitems-cvrf-year-2010.xml --url-file=allitems-cvrf-year-2010.xml
        cve_source_file = ''
        for param in ds[ORM.DATASOURCE_LOOKUP].split(' '):
            if param.startswith('--file='):
                cve_source_file = param.replace('--file=','')
                print("     File:%s" % cve_source_file)
                break
        mitre_data_map = {}
        mitre_file = os.path.join(srtool_basepath,cve_source_file)

        #
        # Gather the Descriptions of all the CVEs in this MITRE data sources
        #
        #   <Vulnerability Ordinal="158066" xmlns="http://www.icasi.org/CVRF/schema/vuln/1.1">
        #      <Notes>
        #         <Note Ordinal="1" Type="Description">In getProcessRecordLocked ...</Note>
        #      </Notes>
        #      <CVE>CVE-2020-0001</CVE>

        # Find the CVE and extract the description
        if not os.path.isfile(mitre_file):
            print("ERROR: no such file '%s'" % mitre_file)
            exit(1)
        f = open(mitre_file, 'r')
        tree = ET.parse(mitre_file)
        root = tree.getroot()
        for child in root:
            cve_name = ''
            description = ''
            if 'Vulnerability' in child.tag:
                for child_v in child:
                    if 'CVE' in child_v.tag:
                        cve_name = child_v.text
                    if 'Notes' in child_v.tag:
                        for child_n in child_v:
                            if 'Note' in child_n.tag:
                                if 'Description' == child_n.attrib['Type']:
                                    description = child_n.text.replace('\n','\\r')

#                   # Debugging support
#                   if cve_name != "CVE-2016-0887":     #"CVE-2020-7470","CVE-2019-15031"
#                      continue

                if cve_name:
                    mitre_data_map[cve_name] = [description]


        #
        # Update the 'description' for all found CVE records in this datasource
        #

        for cve_name in mitre_data_map:
            description = mitre_data_map[cve_name][DATA_MAP_DESCRIPTION]
            cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve_name)
            cve = cur_cve.fetchone()
            if not cve:
                print("WARNING: MISSING CVE in orm : %s" %  cve_name)
                continue


            #
            # Repair the data source mappings
            # * Add missing MITRE links
            # * Replace old MITRE links with found links
            #

            found_mapping = False
            is_nist = False
            cur_cs.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
            for cve2ds in cur_cs:
                if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in nist_ds_list:
                    is_nist = True
                if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in mitre_ds_list:
                    # Do we have an obsolete MITRE mapping?
                    if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] != ds[ORM.DATASOURCE_ID]:
                        # Delete old mapping
                        print("Delete old mapping %s,%s" % (cve_name,mitre_ds_list[cve2ds[ORM.CVESOURCE_DATASOURCE_ID]]))
                        if force:
                            sql = 'DELETE FROM orm_cvesource WHERE id=?'
                            cur_del.execute(sql, (cve2ds[ORM.CVESOURCE_ID],))
                    else:
                        # We are good to go
                        found_mapping = True

            # Disable this feature for now, since we do want to add unneeded pasive MITRE records
            if False:
                # Add if missing or deleted as obsolete
                if not found_mapping:
                    print("Insert new mapping %s,%s" % (cve_name,mitre_ds_list[ds[ORM.DATASOURCE_ID]]))
                    if force:
                        sql = ''' INSERT into orm_cvesource (cve_id, datasource_id) VALUES (?, ?)'''
                        cur_cs.execute(sql, (cve[ORM.CVE_ID],ds[ORM.DATASOURCE_ID],))

            # If this CVE has any NIST data sources, then skip description checking (e.g. NIST Modified preempt)
            if is_nist:
                continue

            # Test is desciption needs repair
            if cve[ORM.CVE_DESCRIPTION] != description:
                fix_count += 1
                print("CHANGE: %s DESCRIPTION('%s' to '%s')" % (
                    cve_name, description_summary(cve[ORM.CVE_DESCRIPTION]), description_summary(description)))
                if force:
                    sql = ''' UPDATE orm_cve
                              SET description = ?
                              WHERE id = ?'''
                    cur_cve.execute(sql, (description,cve[ORM.CVE_ID],))
#                print('%05d: %-20s = %-20s' % (j,cve_name,nist_data_map[cve_name]))


            #
            # Repair the data source mappings
            # * Add missing MITRE links
            # * Replace old MITRE links with found links
            #

            found_mapping = False
            cur_cs.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
            for j,cve2ds in enumerate(cur_cs):
                if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in mitre_ds_list:
                    # Do we have an obsolete MITRE mapping?
                    if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] != ds[ORM.DATASOURCE_ID]:
                        # Delete old mapping
                        print("Delete old mapping %s,%s" % (cve_name,mitre_ds_list[cve2ds[ORM.CVESOURCE_DATASOURCE_ID]]))
                        if force:
                            sql = 'DELETE FROM orm_cvesource WHERE id=?'
                            cur_del.execute(sql, (cve2ds[ORM.CVESOURCE_ID],))
                    else:
                        # We are good to go
                        found_mapping = True
            # Add if missing or deleted as obsolete
            if not found_mapping:
                print("Insert new mapping %s,%s" % (cve_name,mitre_ds_list[ds[ORM.DATASOURCE_ID]]))
                if force:
                    sql = ''' INSERT into orm_cvesource (cve_id, datasource_id) VALUES (?, ?)'''
                    cur_cs.execute(sql, (cve[ORM.CVE_ID],ds[ORM.DATASOURCE_ID],))


            # Development/debug support
            cve_count += 1
            if cmd_skip and (cve_count < cmd_skip): continue
            if cmd_count and ((cve_count - cmd_skip) > cmd_count): break

            # Progress indicator support
            if (0 == cve_count % 1000):
                print('%05d: %-20s\r' % (cve_count,cve_name), end='')
                if force: conn.commit()
                print('')
                pass

    print("CVE COUNT=%d, fix_count=%d" % (cve_count,fix_count))
    if force: conn.commit()

#
# Fix CVE records with missing 'cvssV2_severity' values in the top-level CVE records, due to
# CVE imports before a patch was sent upstream
#
# The NIST Modified list is processed first. If any of its CVEs are found in a regular
# list, that CVE is skipped since it was preempted
#

def fix_severity(datasource_list):
    conn = sqlite3.connect(srtDbName)
    cur_ds = conn.cursor()
    cur_cs = conn.cursor()
    cur_cve = conn.cursor()
    cur_del = conn.cursor()

    cve_count = 0
    fix_count = 0
    nist_ds_list = {}
    modified_cve_list = []

    DATA_MAP_V3_Score = 0
    DATA_MAP_V3_Severity = 1
    DATA_MAP_V2_Score = 2
    DATA_MAP_V2_Severity = 3


    # Allow "MOD" as shorthand for the modification datasource
    datasource_list = datasource_list.replace('MOD','NIST Modified Data')

    #
    # Gather the NIST data source list
    #

    cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "nist" ORDER BY key DESC;')
    for i,ds in enumerate(cur_ds):
        nist_ds_list[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_DESCRIPTION]

    #
    # Iterate over the NIST data sources
    #

    cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "nist" ORDER BY key DESC;')
    for i,ds in enumerate(cur_ds):
        # Development/debug support
        if cmd_count and ((cve_count - cmd_skip) > cmd_count):
            break

        if ds[ORM.DATASOURCE_DESCRIPTION] in ['NIST Common Weakness Enumeration Data']:
            continue
        elif "ALL" == datasource_list:
            pass
        elif not ds[ORM.DATASOURCE_DESCRIPTION] in datasource_list.split(','):
            continue
        print("NIST Source:%s" % ds[ORM.DATASOURCE_DESCRIPTION])
        is_modified_list = ds[ORM.DATASOURCE_DESCRIPTION] == 'NIST Modified Data'

        # Scan the NIST datasource file and extract required values into a map
        # (bin/nist/srtool_nist.py --download-only --source='NIST 2002' --file=data/nvdcve-1.1-2002.json --url-file=nvdcve-1.1-2002.json.gz --url-meta=nvdcve-1.1-2002.meta)
        cve_source_file = ''
        for param in ds[ORM.DATASOURCE_LOOKUP].split(' '):
            if param.startswith('--file='):
                cve_source_file = param.replace('--file=','')
                print("     File:%s" % cve_source_file)
                break
        nist_data_map = {}
        nist_file = os.path.join(srtool_basepath,cve_source_file)

        #
        # Gather the V3/V2 status of all the CVEs in this NIST data sources
        #

        try:
            if not os.path.isfile(nist_file):
                print("ERROR: no such file '%s'" % nist_file)
                exit(1)
            f = open(nist_file, 'r')
            source_dct = json.load(f)
            for item in source_dct["CVE_Items"]:
                if not 'cve' in item:
                    continue
                if not 'CVE_data_meta' in item['cve']:
                    continue
                if not 'ID' in item['cve']['CVE_data_meta']:
                    continue
                cve_name = item['cve']['CVE_data_meta']['ID']

                # Is this the NIST Modified list?
                if is_modified_list:
                    # Add CVE name to Modified list
                    modified_cve_list.append(cve_name)
                elif cve_name in modified_cve_list:
                    # Skip if already process by Modified list
                    continue

#                # Debugging support
#                if cve_name != "CVE-2016-0887":     #"CVE-2020-7470","CVE-2019-15031"
#                   continue

                cvssV3_baseScore = ''
                cvssV3_baseSeverity = ''
                cvssV2_baseScore = ''
                cvssV2_severity = ''
                if ('impact' in item) and ('baseMetricV3' in item['impact']):
                    cvssV3_baseScore = "%.1f" % float(item['impact']['baseMetricV3']['cvssV3']['baseScore'])
                    cvssV3_baseSeverity = item['impact']['baseMetricV3']['cvssV3']['baseSeverity']
                if ('impact' in item) and ('baseMetricV2' in item['impact']):
                    cvssV2_baseScore = "%.1f" % float(item['impact']['baseMetricV2']['cvssV2']['baseScore'])
                    cvssV2_severity = item['impact']['baseMetricV2']['severity']

                nist_data_map[cve_name] = [cvssV3_baseScore,cvssV3_baseSeverity,cvssV2_baseScore,cvssV2_severity]

        except Exception as e:
            print("ERROR:%s (%s)" % (e,item['impact']['baseMetricV3']))
            return

        #
        # Update the V3/V2 status for all found CVE records in this datasource
        #

        for cve_name in nist_data_map:
            cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve_name)
            cve = cur_cve.fetchone()
            if not cve:
                print("WARNING: MISSING CVE in orm_cve : %s" %  cve_name)
                continue
            cve_name = cve[ORM.CVE_NAME]
            if cve_name in nist_data_map:
                fix_count += 1
                if (nist_data_map[cve_name][DATA_MAP_V3_Score] != cve[ORM.CVE_CVSSV3_BASESCORE]) or (nist_data_map[cve_name][DATA_MAP_V3_Severity] != cve[ORM.CVE_CVSSV3_BASESEVERITY]) or \
                   (nist_data_map[cve_name][DATA_MAP_V2_Score] != cve[ORM.CVE_CVSSV2_BASESCORE]) or (nist_data_map[cve_name][DATA_MAP_V2_Severity] != cve[ORM.CVE_CVSSV2_SEVERITY    ]):
                    print("CHANGE: %s V3(%s to %s,%s to %s)V2(%s to %s,%s to %s) (%s,%s)" % (
                        cve_name,
                        cve[ORM.CVE_CVSSV3_BASESCORE],nist_data_map[cve_name][DATA_MAP_V3_Score],cve[ORM.CVE_CVSSV3_BASESEVERITY],nist_data_map[cve_name][DATA_MAP_V3_Severity],
                        cve[ORM.CVE_CVSSV2_BASESCORE],nist_data_map[cve_name][DATA_MAP_V2_Score],cve[ORM.CVE_CVSSV2_SEVERITY    ],nist_data_map[cve_name][DATA_MAP_V2_Severity],
                        ORM.get_orm_string(cve[ORM.CVE_STATUS],ORM.STATUS_STR),cve[ORM.CVE_COMMENTS],
                        ))

                if force:
                    sql = ''' UPDATE orm_cve
                              SET cvssV3_baseScore = ?, cvssV3_baseSeverity = ?, cvssV2_baseScore = ?, cvssV2_severity = ?
                              WHERE id = ?'''
                    cur_cve.execute(sql, (nist_data_map[cve_name][DATA_MAP_V3_Score],nist_data_map[cve_name][DATA_MAP_V3_Severity],nist_data_map[cve_name][DATA_MAP_V2_Score],nist_data_map[cve_name][DATA_MAP_V2_Severity],cve[ORM.CVE_ID],))
#                print('%05d: %-20s = %-20s' % (j,cve_name,nist_data_map[cve_name]))
            else:
                print("ERROR:CVE_NAME '%s' NOT MAPPED" % cve_name)

            #
            # Repair the data source mappings
            # * Add missing NIST links
            # * Replace old NIST links with found links (e.g. Modified datasource preempts regular datasources)
            #

            found_mapping = False
            cur_cs.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
            for j,cve2ds in enumerate(cur_cs):
                if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in nist_ds_list:
                    # Do we have an obsolete NIST mapping?
                    if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] != ds[ORM.DATASOURCE_ID]:
                        # Delete old mapping
                        print("Delete old mapping %s,%s" % (cve_name,nist_ds_list[cve2ds[ORM.CVESOURCE_DATASOURCE_ID]]))
                        if force:
                            sql = 'DELETE FROM orm_cvesource WHERE id=?'
                            cur_del.execute(sql, (cve2ds[ORM.CVESOURCE_ID],))
                    else:
                        # We are good to go
                        found_mapping = True
            # Add if missing or deleted as obsolete
            if not found_mapping:
                print("Insert new mapping %s,%s" % (cve_name,nist_ds_list[ds[ORM.DATASOURCE_ID]]))
                if force:
                    sql = ''' INSERT into orm_cvesource (cve_id, datasource_id) VALUES (?, ?)'''
                    cur_cs.execute(sql, (cve[ORM.CVE_ID],ds[ORM.DATASOURCE_ID],))


            # Development/debug support
            cve_count += 1
            if cmd_skip and (cve_count < cmd_skip): continue
            if cmd_count and ((cve_count - cmd_skip) > cmd_count): break

            # Progress indicator support
            if (0 == cve_count % 1000):
                print('%05d: %-20s\r' % (cve_count,cve_name), end='')
                if force: conn.commit()
                print('')
                pass

    print("CVE COUNT=%d, fix_count=%d" % (cve_count,fix_count))
    if force: conn.commit()

#
# Trim the V3/V2 scores to one decimal place, in line with NIST public pages
#

def fix_trim_cve_scores():
    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_wr = conn.cursor()

    def fixscore(score):
        if not score:
            return ''
        return '%02.1f' % float(score)

    cve_count = 0
    fix_count = 0

    cur.execute('SELECT * FROM orm_cve')
    for i,cve in enumerate(cur):
        if 0 == i % 100:
           print("%4d) C=%-30s\r" % (i,cve[ORM.CVE_NAME]), end='')

        new_v3score = fixscore(cve[ORM.CVE_CVSSV3_BASESCORE])
        new_v2score = fixscore(cve[ORM.CVE_CVSSV2_BASESCORE])

        if (new_v3score != cve[ORM.CVE_CVSSV3_BASESCORE]) or (new_v2score != cve[ORM.CVE_CVSSV2_BASESCORE]):
            fix_count += 1
            if verbose:
                print("CHANGE:%s:%s to %s,%s to %s" % (cve[ORM.CVE_NAME],cve[ORM.CVE_CVSSV3_BASESCORE],new_v3score,cve[ORM.CVE_CVSSV2_BASESCORE],new_v2score))

            if force:
                sql = ''' UPDATE orm_cve
                          SET cvssV3_baseScore = ?, cvssV2_baseScore = ?
                          WHERE id = ?'''
                cur_wr.execute(sql, (new_v3score,new_v2score,cve[ORM.CVE_ID],))

        # Development/debug support
        cve_count += 1
        if cmd_skip and (cve_count < cmd_skip): continue
        if cmd_count and ((cve_count - cmd_skip) > cmd_count): break

        # Progress indicator support
        if (0 == cve_count % 1000):
            print('%05d: %-20s\r' % (cve_count,cve[ORM.CVE_NAME]), end='')
            if force: conn.commit()
            print('')
            pass

    if force: conn.commit()
    print("CVE COUNT=%d, fix_count=%d" % (cve_count,fix_count))

# Sample code that does a CVE lookup data fetch and CVE update
#def example_datasource_lookup(cve,nist_ds,cvesource,cur):
#    if force:
#        if nist_ds:
#            lookup_command = nist_lookup[ cvesource[ORM.CVESOURCE_DATASOURCE_ID] ].replace('%command%','--cve-detail=%s' % cve[ORM.CVE_NAME])
#            result_returncode,result_stdout,result_stderr = execute_process(lookup_command.split(' '))
#            if 0 != result_returncode:
#                print("ERROR_LOOKUP:%s" % lookup_command)
#                return(1)
#            cvssV2_severity = ''
#            for line in result_stdout.decode("utf-8").splitlines():
#                try:
#                    name = line[:line.index('=')]
#                    value = line[line.index('=')+1:].replace("[EOL]","\n")
#                except:
#                    continue
#                if name == 'cvssV2_severity':
#                    cvssV2_severity = value
#                    break
#            if cvssV2_severity:
#                fix_count += 1
#                sql = ''' UPDATE orm_cve
#                          SET cvssV2_severity = ?
#                          WHERE id = ?'''
#                cur.execute(sql, (cvssV2_severity,cve[ORM.CVE_ID],))
#                    print('%05d: %-20s = %-20s' % (i,cve[ORM.CVE_NAME],cvssV2_severity))
##                    return(0)


#################################
# report_multiple_defects
#
# Normally for each CVE there is one defect per
# product. This report finds all cases of multiple
# defects per Investigation.
#

def report_multiple_defects():

    conn = sqlite3.connect(srtDbName)
    cur_i2d = conn.cursor()
    cur_inv = conn.cursor()
    cur_def = conn.cursor()

    cur_inv.execute('SELECT * FROM orm_investigation')
    count = 0
    for i,investigation in enumerate(cur_inv):
        if 0 == i % 100:
           print("%4d) V=%-30s\r" % (i,investigation[ORM.VULNERABILITY_NAME]), end='')

        cur_i2d.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = "%s"' % investigation[ORM.INVESTIGATION_ID])
        i2d_list = cur_i2d.fetchall()
        if 1 < len(i2d_list):
            count += 1
            for k,i2d in enumerate(i2d_list):
                cur_def.execute('SELECT * FROM orm_defect WHERE id = "%s"' %  i2d[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
                defect = cur_def.fetchone()
                if 0 == k:
                    print("[%02d] Multiple defects for investigation '%s':" % (count,investigation[ORM.INVESTIGATION_NAME]))
                print("  [%02d] %s: %s (%s)" % (k+1,defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_SUMMARY],ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR)))
    conn.close()

#################################
# report_duplicate_names
#
#
#
#

def report_duplicate_names():

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()


    cur.execute('SELECT * FROM orm_cve')
    cve_dict = {}
    for i,cve in enumerate(cur):
        if 0 == i % 100:
           print("%4d) C=%-30s\r" % (i,cve[ORM.CVE_NAME]), end='')

        if not cve[ORM.CVE_NAME] in cve_dict:
            cve_dict[cve[ORM.CVE_NAME]] = cve[ORM.CVE_ID]
        else:
            print("\nERROR:Multiple cve names '%s'" % cve[ORM.CVE_NAME])
            print("   a) id=%d" % cve_dict[cve[ORM.CVE_NAME]])
            print("   b) id=%d" % cve[ORM.CVE_ID])
    cve_dict = {}
    print('')

    cur.execute('SELECT * FROM orm_vulnerability')
    vul_dict = {}
    for i,vulnerability in enumerate(cur):
        if 0 == i % 100:
           print("%4d) V=%-30s\r" % (i,vulnerability[ORM.VULNERABILITY_NAME]), end='')

        if not vulnerability[ORM.VULNERABILITY_NAME] in vul_dict:
            vul_dict[vulnerability[ORM.VULNERABILITY_NAME]] = vulnerability[ORM.VULNERABILITY_ID]
        else:
            print("\nERROR:Multiple vulnerability names '%s'" % vulnerability[ORM.VULNERABILITY_NAME])
            print("   a) id=%d" % vul_dict[vulnerability[ORM.VULNERABILITY_NAME]])
            print("   b) id=%d" % vulnerability[ORM.VULNERABILITY_ID])
    vul_dict = {}
    print('')

    cur.execute('SELECT * FROM orm_investigation')
    inv_dict = {}
    for i,investigation in enumerate(cur):
        if 0 == i % 100:
           print("%4d) I=%-30s\r" % (i,investigation[ORM.INVESTIGATION_NAME]), end='')

        if not investigation[ORM.INVESTIGATION_NAME] in inv_dict:
            inv_dict[investigation[ORM.INVESTIGATION_NAME]] = investigation[ORM.INVESTIGATION_ID]
        else:
            print("\nERROR:Multiple investigation names '%s'" % investigation[ORM.INVESTIGATION_NAME])
            print("   a) id=%d" % inv_dict[investigation[ORM.INVESTIGATION_NAME]])
            print("   b) id=%d" % investigation[ORM.INVESTIGATION_ID])
    inv_dict = {}
    print('')

    cur.execute('SELECT * FROM orm_defect')
    dev_dict = {}
    for i,defect in enumerate(cur):
        if 0 == i % 100:
           print("%4d) D=%-30s\r" % (i,defect[ORM.DEFECT_NAME]), end='')

        if not defect[ORM.DEFECT_NAME] in dev_dict:
            dev_dict[defect[ORM.DEFECT_NAME]] = defect[ORM.DEFECT_ID]
        else:
            print("\nERROR:Multiple defect names '%s'" % defect[ORM.DEFECT_NAME])
            print("   a) id=%d" % dev_dict[defect[ORM.DEFECT_NAME]])
            print("   b) id=%d" % defect[ORM.DEFECT_ID])
    dev_dict = {}
    print('')

    conn.close()

#################################
# fix_bad_links
#

def fix_bad_links():

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_del = conn.cursor()

    #
    print('\n=== CVE Source Check ===\n')
    #

    # Find the data source mapping
    cur.execute('SELECT * FROM orm_datasource;')
    datasource_map = {}
    for datasource in cur:
        # DataSource Map is [cve_file,ds_desc,ds_lastmodifieddate,ds_lastupdateddate]
        datasource_map[datasource[ORM.DATASOURCE_ID]] = datasource[ORM.DATASOURCE_DESCRIPTION]

    cur.execute('SELECT * FROM orm_cvesource')
    is_change = False
    for i,cs in enumerate(cur):
        cveid = cs[ORM.CVESOURCE_CVE_ID]
        srcid = cs[ORM.CVESOURCE_DATASOURCE_ID]
        if 0 == i % 100:
            print("%4d) CVE=%6d,SRC=%6d\r" % (cs[ORM.CVESOURCE_ID],cveid,srcid), end='')
        error = False
        if (1 > cveid): error = True
        if (1 > srcid): error = True

        if error:
            print("ERROR: [%4d] CVE=%6d,SRC=%6d (%s)" % (cs[ORM.CVESOURCE_ID],cveid,srcid,datasource_map[srcid]))
            if force:
                sql = 'DELETE FROM orm_cvesource WHERE id=?'
                cur_del.execute(sql, (cs[ORM.CVESOURCE_ID],))
                is_change = True

    print('')
    if is_change:
        conn.commit()

    #
    print('\n=== Defect to Product Check ===\n')
    #

    # Find all products
    products = {}
    cur.execute('SELECT * FROM orm_product')
    for product in cur:
        id =  product[ORM.PRODUCT_ID]
        name =  "%s %s %s" % (product[ORM.PRODUCT_NAME],product[ORM.PRODUCT_VERSION],product[ORM.PRODUCT_PROFILE])
        products[id] = name
        print("[%2d] %s" % (id,name))

    # Test product field for all defects
    cur.execute('SELECT * FROM orm_defect')
    i = 0
    for defect in cur:
        i += 1

        # Progress indicator support
        if 99 == i % 100:
            print('%05d: %-20s\r' % (i,defect[ORM.DEFECT_NAME]), end='')
            pass
        if (0 == i % 200):
#            conn.commit()
            #print('')
            pass
        # Development/debug support
        if cmd_skip and (i < cmd_skip): continue
        if cmd_count and ((i - cmd_skip) > cmd_count): break

        product_id = defect[ORM.DEFECT_PRODUCT_ID]
        if not product_id in products:
            print("ERROR:[%5d] %-20s => %s" % (defect[ORM.DEFECT_ID],defect[ORM.DEFECT_NAME],product_id))

    conn.close()

#################################
# fix_bad_score_date
#
# Remove the 'score_date' value to repair the migration to '0006_reconcile', allowing
# the field for new CVEs to be regenerated. The schema for this field is 'models.DateField'
# but the scoring method in # "srtool_common --score-new-cves" was setting an obsolete
# date_time value. That crashes Django-2.2 (but not Django-1.11).
#

def fix_bad_score_date():

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_fix = conn.cursor()

    #
    print('\n=== CVE fix_bad_score_date Check ===\n')
    #

    cur.execute('SELECT * FROM orm_cve WHERE status = %d' % ORM.STATUS_NEW)
    for i,cve in enumerate(cur):
        for j,item in enumerate(cve):
            print("%s\t" % (item), end='')
        if force:
            sql = ''' UPDATE orm_cve
                      SET score_date = ?
                      WHERE id = ?'''
            cur_fix.execute(sql, (None, cve[ORM.CVE_ID],))

        print("")

    conn.commit()

#################################
#  fix_inherit_affected_components()
#
# Inherit the "Affected Components" from CVEs
# to the new field of their children VUL/INV/DEF

def fix_inherit_affected_components():

    conn = sqlite3.connect(srtDbName)
    cur_cve = conn.cursor()
    cur_cve2vul = conn.cursor()
    cur_vul = conn.cursor()
    cur_vul2inv = conn.cursor()
    cur_inv = conn.cursor()
    cur_inv2def = conn.cursor()
    cur_def = conn.cursor()
    cur_write = conn.cursor()

    def merge_affected_components(alist,blist):
        affected_components = ''
        affected_components_list = {}
        for package in alist.split():
            affected_components_list[package] = True
        for package in blist.split():
            affected_components_list[package] = True
        if affected_components_list:
            affected_components = ' '.join(affected_components_list)
        return(affected_components)

    updates = 0
    cur_cve.execute('SELECT * FROM orm_cve')
    for i,cve in enumerate(cur_cve):
        cve_affect_components = cve[ORM.CVE_PACKAGES]
        if not cve_affect_components:
            continue
        print("CVE:%s, '%s'" % (cve[ORM.CVE_NAME],cve_affect_components))

        # Find all related Vulnerabilities
        cur_cve2vul.execute('SELECT * FROM orm_cvetovulnerablility WHERE cve_id = %d' % cve[ORM.CVE_ID])
        for cve2vul in cur_cve2vul:
            # Update the Vulnerability status
            cur_vul.execute('SELECT * FROM orm_vulnerability WHERE id = %d' % cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID])
            for vul in cur_vul:
                vul_affected_components = merge_affected_components(cve_affect_components,vul[ORM.VULNERABILITY_PACKAGES])
                if vul_affected_components != vul[ORM.VULNERABILITY_PACKAGES]:
                    updates += 1
                    if force:
                        sql = ''' UPDATE orm_vulnerability
                                  SET packages = ?
                                  WHERE id = ?'''
                        cur_write.execute(sql, (vul_affected_components, vul[ORM.VULNERABILITY_ID],))
                    print("  Vul:%s, '%s' to '%s'" % (vul[ORM.VULNERABILITY_NAME],vul[ORM.VULNERABILITY_PACKAGES],vul_affected_components))

                # Find all related Investigations
                cur_vul2inv.execute('SELECT * FROM orm_vulnerabilitytoinvestigation WHERE vulnerability_id = %d' % vul[ORM.VULNERABILITY_ID])
                for vul2inv in cur_vul2inv:
                    # Update the Investigation status
                    cur_inv.execute('SELECT * FROM orm_investigation WHERE id = %d' % vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID])
                    for inv in cur_inv:
                        inv_affected_components = merge_affected_components(vul_affected_components,inv[ORM.INVESTIGATION_PACKAGES])
                        if inv_affected_components != inv[ORM.INVESTIGATION_PACKAGES]:
                            updates += 1
                            if force:
                                sql = ''' UPDATE orm_investigation
                                          SET packages = ?
                                          WHERE id = ?'''
                                cur_write.execute(sql, (inv_affected_components, inv[ORM.INVESTIGATION_ID],))
                            print("    Inv:%s, '%s' to '%s'" % (inv[ORM.INVESTIGATION_NAME],inv[ORM.INVESTIGATION_PACKAGES],inv_affected_components))

                        # Find all related Defects
                        cur_inv2def.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = %d' % inv[ORM.INVESTIGATION_ID])
                        for inv2def in cur_inv2def:
                            # Update the Defect status
                            cur_def.execute('SELECT * FROM orm_defect WHERE id = %d' % inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
                            for defect in cur_def:
                                defect_affected_components = merge_affected_components(inv_affected_components,defect[ORM.DEFECT_PACKAGES])
                                if defect_affected_components != defect[ORM.DEFECT_PACKAGES]:
                                    updates += 1
                                    if force:
                                        sql = ''' UPDATE orm_defect
                                                  SET packages = ?
                                                  WHERE id = ?'''
                                        cur_write.execute(sql, (defect_affected_components, defect[ORM.DEFECT_ID],))
                                    print("      Defect:%s, '%s' to '%s'" % (defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_PACKAGES],defect_affected_components))

        if 999 == (i % 1000) :
            print("%7d: %-20s  %6d\r" % (i+1,cve[ORM.CVE_NAME],updates),end='')
            if force: conn.commit()
#        if 60000 < i:
#            break

    if updates and force: conn.commit()
    print("Affected Component Updates = %d" % updates)


#################################
#  report_cve_status_summary()
#
# Report the distribution of the CVE status and V3/V2
# severities across the years. Used to track trends, and
# also to validate the data and its repairs/migrations.
#

def report_cve_status_summary():

    I_COUNT          =  0

    I_NEW            =  1
    I_VULNERABLE     =  2
    I_INVESTIGATE    =  3
    I_NOT_VULNERABLE =  4
    I_NEW_RESERVED   =  5
    I_HISTORICAL     =  6
    I_STATUS_OTHER   =  7

    I_V3_CRITICAL    =  8
    I_V3_HIGH        =  9
    I_V3_MEDIUM      = 10
    I_V3_LOW         = 11
    I_V3_EMPTY       = 12
    I_V3_OTHER       = 13

    I_V2_CRITICAL    = 14
    I_V2_HIGH        = 15
    I_V2_MEDIUM      = 16
    I_V2_LOW         = 17
    I_V2_EMPTY       = 18
    I_V2_OTHER       = 19

    I_MAX            = 20

    YEAR_START       = 1999
    YEAR_STOP        = 2020

    label = [
        'NEW',
        'VUL',
        'INV',
        'NVUL',
        'NEW_R',
        'HIST',
        'OTHER',
        ]

    table = {}

    def blank_row():
        return [0 for i in range(I_MAX)]

    # Prepare the array
    for year in range(YEAR_START,YEAR_STOP+1):
        table[year] = blank_row()
    for status in range(I_NEW,I_STATUS_OTHER+1):
        table[status] = blank_row()
    table['total1'] = blank_row()
    table['total2'] = blank_row()

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur.execute('SELECT * FROM orm_cve')
    i = 0
    for cve in cur:
        i += 1

        year = int(cve[ORM.CVE_NAME].split('-')[1])
        v3_severity = cve[ORM.CVE_CVSSV3_BASESEVERITY].upper().strip()
        v2_severity = cve[ORM.CVE_CVSSV2_SEVERITY].upper().strip()
        status = cve[ORM.CVE_STATUS]

        if status == ORM.STATUS_NEW:
            table[year][I_NEW] += 1
            status_row = I_NEW
        elif status == ORM.STATUS_VULNERABLE:
            table[year][I_VULNERABLE] += 1
            status_row = I_VULNERABLE
        elif status == ORM.STATUS_INVESTIGATE:
            table[year][I_INVESTIGATE] += 1
            status_row = I_INVESTIGATE
        elif status == ORM.STATUS_NOT_VULNERABLE:
            table[year][I_NOT_VULNERABLE] += 1
            status_row = I_NOT_VULNERABLE
        elif status == ORM.STATUS_NEW_RESERVED:
            table[year][I_NEW_RESERVED] += 1
            status_row = I_NEW_RESERVED
        elif status == ORM.STATUS_HISTORICAL:
            table[year][I_HISTORICAL] += 1
            status_row = I_HISTORICAL
        else:
            table[year][I_STATUS_OTHER] += 1
            status_row = I_STATUS_OTHER

        if v3_severity == 'CRITICAL':
            table[year][I_V3_CRITICAL] += 1
            table[status_row][I_V3_CRITICAL] += 1
        elif v3_severity == 'HIGH':
            table[year][I_V3_HIGH] += 1
            table[status_row][I_V3_HIGH] += 1
        elif v3_severity == 'MEDIUM':
            table[year][I_V3_MEDIUM] += 1
            table[status_row][I_V3_MEDIUM] += 1
        elif v3_severity == 'LOW':
            table[year][I_V3_LOW] += 1
            table[status_row][I_V3_LOW] += 1
        elif v3_severity == '':
            table[year][I_V3_EMPTY] += 1
            table[status_row][I_V3_EMPTY] += 1
        else:
            table[year][I_V3_OTHER] += 1
            table[status_row][I_V3_OTHER] += 1

        if v2_severity == 'CRITICAL':
            table[year][I_V2_CRITICAL] += 1
            table[status_row][I_V2_CRITICAL] += 1
        elif v2_severity == 'HIGH':
            table[year][I_V2_HIGH] += 1
            table[status_row][I_V2_HIGH] += 1
        elif v2_severity == 'MEDIUM':
            table[year][I_V2_MEDIUM] += 1
            table[status_row][I_V2_MEDIUM] += 1
        elif v2_severity == 'LOW':
            table[year][I_V2_LOW] += 1
            table[status_row][I_V2_LOW] += 1
        elif v2_severity == '':
            table[year][I_V2_EMPTY] += 1
            table[status_row][I_V2_EMPTY] += 1
        else:
            table[year][I_V2_OTHER] += 1
            table[status_row][I_V2_OTHER] += 1

    #
    # Year Summary
    #

    print("")
    print("     |Cve   |Status                                          |V3 Severity                              |V2 Severity                              |")
    print("     | COUNT|   NEW,   VUL,   INV,  NVUL, NEW_R,  HIST,  OTHR|  CRIT,  HIGH,   MED,   LOW, EMPTY,  OTHR|  CRIT,  HIGH,   MED,   LOW, EMPTY,  OTHR|")
    print("-----|------|------------------------------------------------|-----------------------------------------|-----------------------------------------|")

    for year in range(YEAR_START,YEAR_STOP+1):
        # Sum the status counts
        for index in range(I_COUNT,I_STATUS_OTHER+1):
            table[year][I_COUNT] += table[year][index]
        # print the columns
        print("%05d|" % year,end = '')
        for index in range(I_COUNT,I_V2_OTHER+1):
            print("%6d%s" % (table[year][index],'|' if index in [I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER,I_COUNT] else ','),end = '')
        print("")

    print(" ====|======|================================================|=========================================|=========================================|")
    totals = blank_row()
    # Vertical Totals
    for year in range(YEAR_START,YEAR_STOP+1):
        for index in range(I_COUNT,I_V2_OTHER+1):
            totals[index] += table[year][index]
    print("%-5s|" % 'Total',end = '')
    for index in range(I_COUNT,I_V2_OTHER+1):
        print("%6d%s" % (totals[index],'|' if index in [I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER,I_COUNT] else ','),end = '')
    print("")

    #
    # Status Summary
    #

    print("-----|-------------------------------------------------------|-----------------------------------------|-----------------------------------------|")
    for status in range(I_NEW,I_STATUS_OTHER+1):
        print("%-5s|" % label[status-I_NEW],end = '')
        print("                                                       |",end = '')
        for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
            print("%6d%s" % (table[status][index],'|' if index in [I_COUNT,I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER] else ','),end = '')
        print("")

    print(" ====|=======================================================|=========================================|=========================================|")
    # Calculate Status Totals
    totals = blank_row()
    # Vertical Totals
    for status in range(I_NEW,I_STATUS_OTHER+1):
        for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
            totals[index] += table[status][index]
    print("%-5s|" % 'Total',end = '')
    print("                                                       |",end = '')
    for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
        print("%6d%s" % (totals[index],'|' if index in [I_COUNT,I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER] else ','),end = '')
    print("")


#################################
# report_db_status_summary()
#
# Report the distribution of the status across the
# CVE/VUL/INV/DEF by years. Used to track trends, and
# also to validate the data and its repairs/migrations.
#

def report_db_status_summary():

    today_str = datetime.today().strftime(ORM.DATASOURCE_DATETIME_FORMAT)

    YEAR_START   = 1999
    YEAR_STOP    = 2020

    I_COUNT   =  0

    # Object Status indexes
    I_CVE_NEW =  1; I_CVE_VULNERABLE =  2; I_CVE_INVESTIGATE =  3; I_CVE_NOT_VULNERABLE =  4; I_CVE_NEW_RESERVED =  5; I_CVE_HISTORICAL =  6; I_CVE_STATUS_OTHER =  7
    I_VUL_NEW =  8; I_VUL_VULNERABLE =  9; I_VUL_INVESTIGATE = 10; I_VUL_NOT_VULNERABLE = 11; I_VUL_NEW_RESERVED = 12; I_VUL_HISTORICAL = 13; I_VUL_STATUS_OTHER = 14
    I_INV_NEW = 15; I_INV_INVNERABLE = 16; I_INV_INVESTIGATE = 17; I_INV_NOT_VULNERABLE = 18; I_INV_NEW_RESERVED = 19; I_INV_HISTORICAL = 20; I_INV_STATUS_OTHER = 21
    I_DEF_NEW = 22; I_DEF_DEFNERABLE = 23; I_DEF_DEFESTIGATE = 24; I_DEF_NOT_VULNERABLE = 25; I_DEF_NEW_RESERVED = 26; I_DEF_HISTORICAL = 27; I_DEF_STATUS_OTHER = 28
    I_STATUS_MAX = 29
    # Summary Status indexes
    I_NEW     =  1; I_VULNERABLE     =  2; I_INVESTIGATE     =  3; I_NOT_VULNERABLE     =  4; I_NEW_RESERVED     =  5; I_HISTORICAL     =  6; I_STATUS_OTHER     =  7

    # SRTool Priority
    I_CVE_CRITICAL =  1; I_CVE_HIGH =  2; I_CVE_MEDIUM =  3; I_CVE_LOW =  4; I_CVE_UNDEFINED =  5; I_CVE_ERROR =  6
    I_VUL_CRITICAL =  7; I_VUL_HIGH =  8; I_VUL_MEDIUM =  9; I_VUL_LOW = 10; I_VUL_UNDEFINED = 11; I_VUL_ERROR = 12
    I_INV_CRITICAL = 13; I_INV_HIGH = 14; I_INV_MEDIUM = 15; I_INV_LOW = 16; I_INV_UNDEFINED = 17; I_INV_ERROR = 18
    I_DEF_CRITICAL = 19; I_DEF_HIGH = 20; I_DEF_MEDIUM = 21; I_DEF_LOW = 22; I_DEF_UNDEFINED = 23; I_DEF_ERROR = 24
    I_PRIORITY_MAX = 25

    cve_idx = 100
    vul_idx = 101
    inv_idx = 102
    def_idx = 103
    object_label = {
        I_NEW:'NEW',I_VULNERABLE:'VUL',I_INVESTIGATE:'INV',I_NOT_VULNERABLE:'NVUL',I_NEW_RESERVED:'NEW_R',I_HISTORICAL:'HIST',I_STATUS_OTHER:'OTHER',
        cve_idx:'CVE',vul_idx:'VUL',inv_idx:'INV',def_idx:'DEF',
    }

    # Prepare the sum table
    def blank_row():
        return [0 for i in range(max(I_STATUS_MAX,I_PRIORITY_MAX)+1)]
    table_status = {}
    table_priority = {}
    for year in range(YEAR_START,YEAR_STOP+1):
        table_status[year] = blank_row()
        table_priority[year] = blank_row()
    for status_idx in range(I_NEW,I_STATUS_OTHER+1):
        table_status[status_idx] = blank_row()

    def sum_object_status(status,year,obj_offset,merge_inactive=True):
        offset = obj_offset - I_CVE_NEW
        if merge_inactive:
            if status == ORM.STATUS_NEW_INACTIVE:
                status = ORM.STATUS_NEW
            elif status == ORM.STATUS_INVESTIGATE_INACTIVE:
                status = ORM.STATUS_INVESTIGATE
            elif status == ORM.STATUS_VULNERABLE_INACTIVE:
                status = ORM.STATUS_VULNERABLE
            elif status == ORM.STATUS_NOT_VULNERABLE_INACTIVE:
                status = ORM.STATUS_NOT_VULNERABLE
        if status == ORM.STATUS_NEW:
            table_status[year][I_NEW+offset] += 1
            table_status[I_NEW][I_NEW+offset] += 1
        elif status == ORM.STATUS_VULNERABLE:
            table_status[year][I_VULNERABLE+offset] += 1
            table_status[I_VULNERABLE][I_VULNERABLE+offset] += 1
        elif status == ORM.STATUS_INVESTIGATE:
            table_status[year][I_INVESTIGATE+offset] += 1
            table_status[I_INVESTIGATE][I_INVESTIGATE+offset] += 1
        elif status == ORM.STATUS_NOT_VULNERABLE:
            table_status[year][I_NOT_VULNERABLE+offset] += 1
            table_status[I_NOT_VULNERABLE][I_NOT_VULNERABLE+offset] += 1
        elif status == ORM.STATUS_NEW_RESERVED:
            table_status[year][I_NEW_RESERVED+offset] += 1
            table_status[I_NEW_RESERVED][I_NEW_RESERVED+offset] += 1
        elif status == ORM.STATUS_HISTORICAL:
            table_status[year][I_HISTORICAL+offset] += 1
            table_status[I_HISTORICAL][I_HISTORICAL+offset] += 1
        else:
            table_status[year][I_STATUS_OTHER+offset] += 1
            table_status[I_STATUS_OTHER][I_STATUS_OTHER+offset] += 1

    def sum_object_priority(priority,year,obj_offset):
        offset = obj_offset - I_CVE_CRITICAL
        if priority == ORM.PRIORITY_LOW:
            table_priority[year][I_CVE_LOW+offset] += 1
        elif priority == ORM.PRIORITY_MEDIUM:
            table_priority[year][I_CVE_MEDIUM+offset] += 1
        elif priority == ORM.PRIORITY_HIGH:
            table_priority[year][I_CVE_HIGH+offset] += 1
        elif priority == ORM.PRIORITY_CRITICAL:
            table_priority[year][I_CVE_CRITICAL+offset] += 1
        else:   # priority == ORM.PRIORITY_UNDEFINED:
            table_priority[year][I_CVE_UNDEFINED+offset] += 1

    conn = sqlite3.connect(srtDbName)
    cur_cve = conn.cursor()
    cur_cve2vul = conn.cursor()
    cur_vul = conn.cursor()
    cur_vul2inv = conn.cursor()
    cur_inv = conn.cursor()
    cur_inv2def = conn.cursor()
    cur_def = conn.cursor()

    #
    # Year-specific table_status
    #

    i = 0
    cur_cve.execute('SELECT * FROM orm_cve')
    for count,cve in enumerate(cur_cve):
        year = int(cve[ORM.CVE_NAME].split('-')[1])

        # Sum the CVE status
        sum_object_status(cve[ORM.CVE_STATUS],year,I_CVE_NEW)
        sum_object_priority(cve[ORM.CVE_PRIORITY],year,I_CVE_CRITICAL)

        i += 1

        # Find all related Vulnerabilities
        cur_cve2vul.execute('SELECT * FROM orm_cvetovulnerablility WHERE cve_id = %d' % cve[ORM.CVE_ID])
        for cve2vul in cur_cve2vul:
            # Sum the Vulnerability status
            cur_vul.execute('SELECT * FROM orm_vulnerability WHERE id = %d' % cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID])
            for vul in cur_vul:
                sum_object_status(vul[ORM.VULNERABILITY_STATUS],year,I_VUL_NEW)
                sum_object_priority(vul[ORM.VULNERABILITY_PRIORITY],year,I_VUL_CRITICAL)

                # Find all related Investigations
                cur_vul2inv.execute('SELECT * FROM orm_vulnerabilitytoinvestigation WHERE vulnerability_id = %d' % vul[ORM.VULNERABILITY_ID])
                for vul2inv in cur_vul2inv:
                    # Sum the Investigation status
                    cur_inv.execute('SELECT * FROM orm_investigation WHERE id = %d' % vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID])
                    for inv in cur_inv:
                        sum_object_status(inv[ORM.INVESTIGATION_STATUS],year,I_INV_NEW)
                        sum_object_priority(inv[ORM.INVESTIGATION_PRIORITY],year,I_INV_CRITICAL)

                        # Find all related Defects
                        cur_inv2def.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = %d' % inv[ORM.INVESTIGATION_ID])
                        for inv2def in cur_inv2def:
                            # Sum the Defect status
                            cur_def.execute('SELECT * FROM orm_defect WHERE id = %d' % inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
                            for defect in cur_def:
                                sum_object_status(defect[ORM.DEFECT_SRT_STATUS],year,I_DEF_NEW)
                                sum_object_priority(defect[ORM.DEFECT_SRT_PRIORITY],year,I_DEF_CRITICAL)

        if 1000 == i:
            print("%7d: %-20s\r" % (count+1,cve[ORM.CVE_NAME]),end='')
            i = 0
#        if 10000 < count:
#            break

    #
    # Object-specific table_status
    #

    for object_idx in range(cve_idx,def_idx+1):
        table_status[object_idx] = blank_row()
        table_priority[object_idx] = blank_row()
    table_status['total'] = blank_row()
    table_priority['total'] = blank_row()

    print("CVEs...")
    cur_cve.execute('SELECT * FROM orm_cve')
    for cve in cur_cve:
        sum_object_status(cve[ORM.CVE_STATUS],cve_idx,I_CVE_NEW)
        sum_object_priority(cve[ORM.CVE_PRIORITY],cve_idx,I_CVE_CRITICAL)
    print("Vulnerabilities...")
    cur_vul.execute('SELECT * FROM orm_vulnerability')
    for vul in cur_vul:
        sum_object_status(vul[ORM.VULNERABILITY_STATUS],vul_idx,I_CVE_NEW)
        sum_object_priority(vul[ORM.VULNERABILITY_PRIORITY],vul_idx,I_CVE_CRITICAL)
    cur_inv.execute('SELECT * FROM orm_investigation')
    print("Investigations...")
    for inv in cur_inv:
        sum_object_status(inv[ORM.INVESTIGATION_STATUS],inv_idx,I_CVE_NEW)
        sum_object_priority(inv[ORM.INVESTIGATION_PRIORITY],inv_idx,I_CVE_CRITICAL)
    print("Defects...")
    cur_def.execute('SELECT * FROM orm_defect')
    for defect in cur_def:
        sum_object_status(defect[ORM.DEFECT_SRT_STATUS],def_idx,I_CVE_NEW)
        sum_object_priority(defect[ORM.DEFECT_SRT_PRIORITY],def_idx,I_CVE_CRITICAL)
        i += 1

    #
    #   Display Status Summary
    #

    # Year Summary
    print("\n=== SRTool Status Summary (%s) (%s) ===\n" % (today_str,srtool_basepath))
    print("     |Cve    |CVE Status                                       |Vulnerability Status                            |Investigation Status                             |Defect Status                                   |")
    print("     |  COUNT|   NEW,   VUL,   INV,   NVUL, NEW_R,  HIST,  OTHR|   NEW,   VUL,   INV,  NVUL, NEW_R,  HIST,  OTHR|   NEW,   VUL,   INV,   NVUL, NEW_R,  HIST,  OTHR|   NEW,   VUL,   INV,  NVUL, NEW_R,  HIST,  OTHR|")
    print("-----|-------|-------------------------------------------------|------------------------------------------------|-------------------------------------------------|------------------------------------------------|")

    for year in range(YEAR_START,YEAR_STOP+1):
        # Sum the status counts
        for index in range(I_CVE_NEW,I_CVE_STATUS_OTHER+1):
            table_status[year][I_COUNT] += table_status[year][index]
        # print the columns
        print("%5d|" % year,end = '')
        for index in range(I_COUNT,I_STATUS_MAX):
            format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
            print(format % (table_status[year][index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER,I_VUL_STATUS_OTHER,I_INV_STATUS_OTHER,I_DEF_STATUS_OTHER] else ','),end = '')
        print("")

    print("=====|=======|=================================================|================================================|=================================================|================================================|")
    totals = blank_row()
    # Vertical Totals
    for year in range(YEAR_START,YEAR_STOP+1):
        for index in range(I_COUNT,I_STATUS_MAX):
            totals[index] += table_status[year][index]
    print("%-5s|" % 'Total',end = '')
    for index in range(I_COUNT,I_STATUS_MAX):
        format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
        print(format % (totals[index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER,I_VUL_STATUS_OTHER,I_INV_STATUS_OTHER,I_DEF_STATUS_OTHER] else ','),end = '')
    print("")

    # Count Summary
    print("")
    print("     |  COUNT|   NEW,   VUL,   INV,   NVUL, NEW_R,  HIST,  OTHR|")
    print("-----|-------|-------------------------------------------------|")
    for object_idx in range(cve_idx,def_idx+1):
        # Sum the status counts
        for index in range(I_CVE_NEW,I_CVE_STATUS_OTHER+1):
            table_status[object_idx][I_COUNT] += table_status[object_idx][index]
        # print the columns
        print("%5s|" % object_label[object_idx],end = '')
        for index in range(I_COUNT,I_CVE_STATUS_OTHER+1):
            format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
            print(format % (table_status[object_idx][index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER] else ','),end = '')
        print("")
    print("-----|-------|-------------------------------------------------|")

    #
    #   Display Priority Summary
    #

    # Year Summary
    print("\n=== SRTool Priority Summary (%s) (%s) ===\n" % (today_str,srtool_basepath))
    print("     |Cve    |CVE Priority                              |Vulnerability Priority                    |Investigation Priority                    |Defect Priority                           |")
    print("     |  COUNT|  CRIT,  HIGH,   MED,   LOW,  UNDEF, ERROR|  CRIT,  HIGH,   MED,   LOW,  UNDEF, ERROR|  CRIT,  HIGH,   MED,   LOW,  UNDEF, ERROR|  CRIT,  HIGH,   MED,   LOW,  UNDEF, ERROR|")
    print("-----|-------|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|")

    for year in range(YEAR_START,YEAR_STOP+1):
        # Sum the status counts
        for index in range(I_COUNT,I_PRIORITY_MAX):
            table_priority[year][I_COUNT] += table_priority[year][index]
        # print the columns
        print("%5d|" % year,end = '')
        for index in range(I_COUNT,I_PRIORITY_MAX):
            format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED,I_VUL_UNDEFINED,I_INV_UNDEFINED,I_DEF_UNDEFINED] else "%6d%s"
            print(format % (table_priority[year][index],'|' if index in [I_COUNT,I_CVE_ERROR,I_VUL_ERROR,I_INV_ERROR,I_DEF_ERROR] else ','),end = '')
        print("")

    print("=====|=======|==========================================|==========================================|==========================================|==========================================|")
    totals = blank_row()
    # Vertical Totals
    for year in range(YEAR_START,YEAR_STOP+1):
        for index in range(I_COUNT,I_PRIORITY_MAX):
            totals[index] += table_priority[year][index]
    print("%-5s|" % 'Total',end = '')
    for index in range(I_COUNT,I_PRIORITY_MAX):
        format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED,I_VUL_UNDEFINED,I_INV_UNDEFINED,I_DEF_UNDEFINED] else "%6d%s"
        print(format % (totals[index],'|' if index in [I_COUNT,I_CVE_ERROR,I_VUL_ERROR,I_INV_ERROR,I_DEF_ERROR] else ','),end = '')
    print("")

    print("")
    print("     |  COUNT|  CRIT,  HIGH,   MED,   LOW,  UNDEF, ERROR|")
    print("-----|-------|------------------------------------------|")
    for object_idx in range(cve_idx,def_idx+1):
        # Sum the status counts
        for index in range(I_COUNT,I_CVE_ERROR+1):
            table_priority[object_idx][I_COUNT] += table_priority[object_idx][index]
        # print the columns
        print("%5s|" % object_label[object_idx],end = '')
        for index in range(I_COUNT,I_CVE_ERROR+1):
            format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED] else "%6d%s"
            print(format % (table_priority[object_idx][index],'|' if index in [I_COUNT,I_CVE_ERROR] else ','),end = '')
        print("")
    print("-----|-------|------------------------------------------|")


#################################
# report_unattached_records()
#
# Report the VUL unattached to CVE, INV unattached to VUL,
# and DEF unattached to INV records
#

def report_unattached_records():

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_cve = conn.cursor()
    cur_cve2vul = conn.cursor()
    cur_vul = conn.cursor()
    cur_vul2inv = conn.cursor()
    cur_inv = conn.cursor()
    cur_inv2def = conn.cursor()
    cur_def = conn.cursor()

    if False:
        # Add all Vulnerability record IDs
        unattached_records = {}
        cur.execute('SELECT * FROM orm_vulnerability')
        count = 0
        for vul in cur:
            count += 1
            unattached_records[vul[ORM.VULNERABILITY_ID]] = True
        print("Count = %d" % count)
        # Remove Vulnerabilities with mapping to CVEs
        cur.execute('SELECT * FROM orm_cvetovulnerablility')
        count = 0
        for cve2vul in cur:
            count += 1
            del unattached_records[cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID]]
        print("Count = %d" % count)
        print("Unattached VUL to CVE = %d" % len(unattached_records))
        for key in unattached_records:
            vul = cur.execute('SELECT * FROM orm_vulnerability WHERE id = %d' % key).fetchone()
            print("  %-10s [%7d] : %s" % (vul[ORM.VULNERABILITY_NAME],vul[ORM.VULNERABILITY_ID],vul[ORM.VULNERABILITY_DESCRIPTION][:60]))

    if False:
        # Add all Investigation record IDs
        unattached_records = {}
        cur.execute('SELECT * FROM orm_investigation')
        count = 0
        for inv in cur:
            count += 1
            unattached_records[inv[ORM.INVESTIGATION_ID]] = True
        print("Count = %d" % count)
        # Remove Investigations with mapping to Vulnerabilities
        cur.execute('SELECT * FROM orm_vulnerabilitytoinvestigation')
        count = 0
        for vul2inv in cur:
            count += 1
            del unattached_records[vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID]]
        print("Count = %d" % count)
        print("Unattached INV to VUL = %d" % len(unattached_records))
        for key in unattached_records:
            vul = cur.execute('SELECT * FROM orm_investigation WHERE id = %d' % key).fetchone()
            print("  %-10s [%7d] : %s" % (inv[ORM.INVESTIGATION_NAME],inv[ORM.INVESTIGATION_ID],inv[ORM.INVESTIGATION_DESCRIPTION][:60]))

    # Add all Defect record IDs
    unattached_records = {}
    cur.execute('SELECT * FROM orm_defect')
    count = 0
    for defect in cur:
        count += 1
        unattached_records[defect[ORM.DEFECT_ID]] = True
    print("Count = %d" % count)
    # Remove Defects with a mapping Investigations
    cur.execute('SELECT * FROM orm_investigationtodefect')
    count = 0
    for inv2def in cur:
        count += 1
        del unattached_records[inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID]]
    print("Count = %d" % count)
    print("Unattached DEV to INV = %d" % len(unattached_records))
    defect_list = []
    for i,key in enumerate(unattached_records):
        defect = cur.execute('SELECT * FROM orm_defect WHERE id = %d' % key).fetchone()
        defect_list.append(defect)
#        print("  %-10s [%7d] : %s" % (defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_ID],defect[ORM.DEFECT_SUMMARY][:60]))
#        if i > 10:
#            break

    def sortDefectRecord(defect):
        return defect[ORM.DEFECT_NAME]
    defect_list.sort(key = sortDefectRecord)
    defect_related = []
    defect_nocve = []
    defect_manycve = []
    test = 0
    test_match = 0
    for i,defect in enumerate(defect_list):
        name = defect[ORM.DEFECT_NAME]
        summary = defect[ORM.DEFECT_SUMMARY]

        if not defect[ORM.DEFECT_SUMMARY].startswith("Security Advisory - "):
            defect_related.append('%s,%s' % (name,summary))
            continue

        if name.startswith('DEFECT-'):
            name = name.replace('DEFECT-','')

        product = name.split('-')[0]
        match_set = re.findall(".*(CVE-\d+-\d+).*",summary)
        if not match_set:
            defect_nocve.append('%s,%s' % (name,summary))
#            print("ERROR: NO MATCH : %s,%s" % (name,summary))
            continue
        elif 1 < len(match_set):
            defect_manycve.append('%s,%s' % (name,summary))
#            print("ERROR: MULTIPLE MATCHES : %s,%s,%s" % (name,summary,match_set))
            continue
        else:
            cve = match_set[0]

        if not product or not cve:
            print("ERROR: MISSING : %s,%s,%s,%s" % (name,product,cve,summary))
        test += 1

        print("%s,%s,%s,%s,%s,%s" % (defect[ORM.DEFECT_NAME],product,cve,
            ORM.get_orm_string(defect[ORM.DEFECT_STATUS],ORM.DEFECT_STATUS_STR),
            ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR),
            defect[ORM.DEFECT_SUMMARY][:60],))

        match = False
        cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve)
        for cve in cur_cve:
            cur_cve2vul.execute('SELECT * FROM orm_cvetovulnerablility WHERE cve_id = %d' % cve[ORM.CVE_ID])
            for cve2vul in cur_cve2vul:
                cur_vul2inv.execute('SELECT * FROM orm_vulnerabilitytoinvestigation WHERE vulnerability_id = %d' % cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID])
                for vul2inv in cur_vul2inv:
                    # Find all related Defects
                    cur_inv2def.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = %d' % vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID])
                    for inv2def in cur_inv2def:
                        cur_def.execute('SELECT * FROM orm_defect WHERE id = %d' % inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
                        for defect in cur_def:
                            product_this = defect[ORM.DEFECT_NAME].replace('DEFECT-','').split('-')[0]
                            if product == product_this:
                                match = True
                                print("  DEFECT:%s,%s,%s,%s" % (defect[ORM.DEFECT_NAME],
                                    ORM.get_orm_string(defect[ORM.DEFECT_STATUS],ORM.DEFECT_STATUS_STR),
                                    ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR),
                                    defect[ORM.DEFECT_SUMMARY][:60],
                                    ))
        if match:
            test_match += 1

#        if test > 10:
#            break

    print("Related = %d" % len(defect_related))
    print("NoCVE = %d" % len(defect_nocve))
    print("ManyCVE = %d" % len(defect_manycve))
    print("Testable = %d" % test)
    print("TestMatches = %d" % test_match)

# extract product,cve, defect [Defect Status,Defect Resolution]
# see if CVE has VUL has INV for the product

#################################
# fix_duplicate_notifications
#
# Remove older duplicate notifications
#

def fix_duplicate_notifications():

    conn = sqlite3.connect(srtDbName)
    cur = conn.cursor()
    cur_del = conn.cursor()

    notify_descriptions = {}
    delete_list = []
    delete_count = 0

    cur.execute('SELECT * FROM orm_notify ORDER BY srt_created DESC;')
    for i,notify in enumerate(cur):
        description = notify[ORM.NOTIFY_DESCRIPTION]
        if description in notify_descriptions:
            delete_count += 1
            delete_list.append(notify[ORM.NOTIFY_ID])
        else:
            notify_descriptions[description] = True

        # Progress indicator support
        if (0 == i % 5000):
            print('%05d:%05d\r' % (i,delete_count), end='')

    print("")
    if force:
        print("Deleting %d..." % len(delete_list))
        for i,id in enumerate(delete_list):
            sql = 'DELETE FROM orm_notify WHERE id=?'
            ret = cur_del.execute(sql, (id,))
            if (0 == i % 1000):
                print('%05d:\r' % (i), end='')
            if (0 == i % 10000):
                time.sleep(0.1)
                conn.commit()
        conn.commit()

    print("")
    print('Delete count = %d of %d, Unique = %d' % (delete_count,i,len(notify_descriptions)))
    #print(notify_descriptions)
    conn.close()

#################################
# main loop
#

def main(argv):
    global verbose
    global cmd_skip
    global cmd_count
    global force
    global srtDbName

    # setup
    parser = argparse.ArgumentParser(description='srtool_utils.py: manage/repair the SRTool database')
    parser.add_argument('--sources', '-s', nargs='?', const='display', help='SRTool Sources')
    parser.add_argument('--reset-sources', '-r', action='store_const', const='reset_sources', dest='command', help='Reset SRTool Sources')
    parser.add_argument('--settings', '-S', action='store_const', const='settings', dest='command', help='Show the SRT Settings')
    parser.add_argument('--remove-app-sources', dest='remove_app_sources', help='Remove data sources for a previous app')

    # One shot database repair routines

    parser.add_argument('--fix-name-sort', action='store_const', const='fix_name_sort', dest='command', help='Recalulate the CVE name sort values')
    parser.add_argument('--fix-cve-recommend', action='store_const', const='fix_cve_recommend', dest='command', help='Fix the empty CVE recommend values')
    parser.add_argument('--fix-new-reserved', action='store_const', const='fix_new_reserved', dest='command', help='Reset new reserved CVEs to NEW_RESERVED')
    parser.add_argument('--fix-new-tags', action='store_const', const='fix_new_tags', dest='command', help='Reset new cve.tags')
    parser.add_argument('--fix-srt-datetime', dest='fix_srt_datetime', help='Fix SRT dates to datetimes [all|c|v|i|d|history|ch|vh|ih|dh]')
    parser.add_argument('--fix-reset-nist-to-create', dest='fix_reset_nist_to_create', help='Bulk reset CVE [prefix*] srt_create dates to NIST release dates')
    parser.add_argument('--fix-missing-create-dates', action='store_const', const='fix_missing_create_dates', dest='command', help='Reset CVE srt_create dates to NIST release dates')
    parser.add_argument('--fix-public-reserved', action='store_const', const='fix_public_reserved', dest='command', help='Reset CVE NEW_RESERVED if now public')
    parser.add_argument('--fix-remove-bulk-cve-history', action='store_const', const='fix_remove_bulk_cve_history', dest='command', help='Remove a specific/accidental set of bulk CVE history updates ')
    parser.add_argument('--fix-bad-mitre-init', action='store_const', const='fix_bad_mitre_init', dest='command', help='Fix MITRE "New" that should be "New-Reserved"')
    parser.add_argument('--fix-bad-mitre-descr', dest='fix_bad_mitre_descr', help='Fix MITRE that were created with empty descriptions')
    parser.add_argument('--fix-bad-score-date', action='store_const', const='fix_bad_score_date', dest='command', help='Clear score dates to fix obsolete formats')
    parser.add_argument('--fix-trim-cve-scores', action='store_const', const='fix_trim_cve_scores', dest='command', help='Trim V3/V2 scores to one decimal place standard')
    parser.add_argument('--fix-inherit-affected-components', action='store_const', const='fix_inherit_affected_components', dest='command', help='Inherit the affected components field from CVE to its children')

    # Continuous maintenance validation and repair routines

    parser.add_argument('--fix-bad-links', action='store_const', const='fix_bad_links', dest='command', help='Find bad links, e.g. "orm_cvesource" (add "-f" to fix)')
    parser.add_argument('--fix-severity', dest='fix_severity', help='Find bad score/severity values, broken cve source links {ALL|"NIST 2020[,...]*"} (add "-f" to fix)')
    parser.add_argument('--fix-duplicate-notifications', action='store_const', const='fix_duplicate_notifications', dest='command', help='Removed older duplicate notifications')

    parser.add_argument('--report-multiple-defects', action='store_const', const='report_multiple_defects', dest='command', help='Report multiple defects per investigations')
    parser.add_argument('--report-duplicate-names', action='store_const', const='report_duplicate_names', dest='command', help='Report duplicate names for CVE,VUL,INV,DEF')
    parser.add_argument('--report-defects-to-products', action='store_const', const='report_defects_to_products', dest='command', help='Report defects without product link')
    parser.add_argument('--report-cve-status-summary', action='store_const', const='report_cve_status_summary', dest='command', help='Report the CVE status summary')
    parser.add_argument('--report-db-status-summary', action='store_const', const='report_db_status_summary', dest='command', help='Report the database status summary')
    parser.add_argument('--report-unattached-records', action='store_const', const='report_unattached_records', dest='command', help='Report VUL/INV/DEF unattached to parent CVE/VUL/INV')

    # Options

    parser.add_argument('--database', '-D', dest='database', help='Select specific alternate database file (e.g. a backup)')
    parser.add_argument('--force', '-f', action='store_true', dest='force', help='Force the update')
    parser.add_argument('--update-skip-history', '-H', action='store_true', dest='update_skip_history', help='Skip history updates')
    parser.add_argument('--verbose', '-v', action='store_true', dest='verbose', help='Debugging: verbose output')
    parser.add_argument('--skip', dest='skip', help='Debugging: skip record count')
    parser.add_argument('--count', dest='count', help='Debugging: short run record count')

    args = parser.parse_args()

    master_log = open(os.path.join(srtool_basepath, "update_logs/master_log.txt"), "a")

    verbose = args.verbose
    if None != args.skip:
        cmd_skip = int(args.skip)
    if None != args.count:
        cmd_count = int(args.count)
    force = args.force

    # Test for example the backup databases
    if args.database:
        srtDbName = args.database
    import_orm_schema(os.path.dirname(srtDbName) )

    if args.sources:
        if args.sources.startswith('s'):
            sources("set")
        elif 0 <= args.sources.find('nist'):
            sources("reset_not_nist")
        elif args.sources.startswith('r'):
            sources("reset")
        elif args.sources.startswith('t'):
            sources("triage_keywords")
        else:
            sources("display")
    elif 'reset_sources' == args.command:
        sources('reset')
    elif 'settings' == args.command:
        settings()

    elif args.remove_app_sources:
        remove_app_sources(args.remove_app_sources)

    elif 'fix_name_sort' == args.command:
        fix_name_sort()
    elif 'fix_cve_recommend' == args.command:
        fix_cve_recommend()
    elif 'fix_new_reserved' == args.command:
        fix_new_reserved()
    elif 'fix_new_tags' == args.command:
        fix_new_tags()
    elif args.fix_srt_datetime:
        fix_srt_datetime(args.fix_srt_datetime)
    elif args.fix_reset_nist_to_create:
        fix_reset_nist_to_create(args.fix_reset_nist_to_create)
    elif 'fix_missing_create_dates' == args.command:
        fix_missing_create_dates()
    elif 'fix_public_reserved' == args.command:
        fix_public_reserved()
    elif 'fix_remove_bulk_cve_history' == args.command:
        fix_remove_bulk_cve_history()
    elif 'report_defects_to_products' == args.command:
        report_defects_to_products()
    elif 'fix_bad_mitre_init' == args.command:
        fix_bad_mitre_init()
    elif args.fix_bad_mitre_descr:
        fix_bad_mitre_descr(args.fix_bad_mitre_descr)
    elif 'fix_bad_score_date' == args.command:
        fix_bad_score_date()
    elif 'fix_inherit_affected_components' == args.command:
        fix_inherit_affected_components()

    elif args.fix_severity:
        fix_severity(args.fix_severity)
    elif 'fix_trim_cve_scores' == args.command:
        fix_trim_cve_scores()
    elif 'fix_duplicate_notifications' == args.command:
        fix_duplicate_notifications()

    elif 'report_multiple_defects' == args.command:
        report_multiple_defects()
    elif 'report_duplicate_names' == args.command:
        report_duplicate_names()
    elif 'fix_bad_links' == args.command:
        fix_bad_links()
    elif 'report_cve_status_summary' == args.command:
        report_cve_status_summary()
    elif 'report_db_status_summary' == args.command:
        report_db_status_summary()
    elif 'report_unattached_records' == args.command:
        report_unattached_records()


    else:
        print("Command not found")
    master_log.close()

if __name__ == '__main__':
    srtool_basepath = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(sys.argv[0]))))
    main(sys.argv[1:])