import os
from DB import  query_to_excel, query_to_list, OUT_FOLDER
from tkinter import messagebox

stats_by_group_cmd = f""" SELECT  profile_group,
    COUNT(distinct assets.ipaddress) as "numHosts",
	COUNT(CASE WHEN risk = 'High' Then 1 END) as 'High', 
	COUNT(CASE WHEN risk = 'Medium' Then 1 END) as 'Medium',
	COUNT(CASE WHEN risk in ('High', 'Medium') Then 1 END) as 'Total'
	--COUNT(CASE WHEN risk = 'Low' Then 1 END) as 'Low',
	-- COUNT(CASE WHEN risk = 'Warning' Then 1 END) as 'Warning',
	-- COUNT(risk) as "Total"
    FROM vulnerability vul LEFT JOIN assets USING(ipaddress)
    GROUP by profile_group
UNION
SELECT  "Total",
    count(distinct assets.ipaddress) as "numHosts",
	COUNT(CASE WHEN risk = 'High' Then 1 END) as 'High', 
	COUNT(CASE WHEN risk = 'Medium' Then 1 END) as 'Medium',
    COUNT(CASE WHEN risk in ('High', 'Medium') Then 1 END) as 'Total'
	--COUNT(CASE WHEN risk = 'Low' Then 1 END) as 'Low',
	--COUNT(CASE WHEN risk = 'Warning' Then 1 END) as 'Warning',
	--COUNT(risk) as "Total"
    FROM vulnerability vul LEFT JOIN assets on assets.ipaddress = vul.ipaddress
ORDER BY 1,2 DESC"""   
    
stats_by_ip_cmd = f"""SELECT followup_group, assets.ipaddress, assets.assetName,
	COUNT(risk) as "Total",
	count(CASE WHEN fix_category = 'Irrelevant' THEN 1 END) as "Irrelevant",
	count(CASE WHEN substr(fix_category, 1,22)  = 'Applied latest package' THEN 1 END) as "Applied latest package",
	count(CASE WHEN substr(fix_category, 1,17)  = 'Application Issue' THEN 1 END) as 'Application Issue',
	count(CASE WHEN fix_category  = 'Oracle DBA' THEN 1 END) as 'Oracle DBA',
	count(CASE WHEN substr(fix_category, 1,5)  = 'Fixed' THEN 1 END) as 'Fixed, rescan needed',
	count(CASE WHEN substr(fix_category, 1,10)  = 'No Package' THEN 1 END) as 'No Package to Fix',
	count(CASE WHEN substr(fix_category, 1,14)  = 'False Positive' THEN 1 END) as 'False Positive',
    count(CASE WHEN ifnull(fix_category,0) = 0 THEN 1 END) as 'unclassified' 
    FROM vulnerability vul 
		LEFT JOIN assets USING(ipaddress)  
		LEFT JOIN vul_fix_category vfc USING (vulnerability, followup_group)
	WHERE risk in ('High', 'Medium') AND ifnull(assets.exclude,0) = 0 
    GROUP by location, profile_group, assets.ipaddress
    ORDER BY 5 DESC"""   
    
vulners_list_all_cmd = """ select Location, profile_name, profile_group as "Group", assets.ipaddress, assets.assetName,vulnerability, risk 
    FROM vulnerability vul LEFT JOIN assets on assets.ipaddress  = vul.ipaddress
    ORDER BY 1,2,3,4"""
    
 
def VLAN_stats(file_name):
    cmd = f"""select name,count(distinct ipaddress) as "# of hosts",
	COUNT(CASE WHEN risk = 'High' Then 1 END) as 'High', 
	COUNT(CASE WHEN risk = 'Medium' Then 1 END) as 'Medium',
	COUNT(CASE WHEN risk = 'Low' Then 1 END) as 'Low',
	COUNT(CASE WHEN risk = 'Warning' Then 1 END) as 'Warning',
	COUNT(risk) as "Total"
    FROM vulnerability
    GROUP by name"""
    query_to_excel(cmd, file_name)
    
    
def Windows_and_Krom_stats(file_name):
    cmd = f"""select name
    FROM vulnerability
    WHERE name LIKE '%Windows%' or name like "%krom% or name like "internet"
    GROUP by name"""
    query_to_excel(cmd, file_name)
    
    
def progress_by_group(export=None):

    cmd = f"""
    SELECT profile_group "Profile Group", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
        sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
        (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
        ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
    FROM stats
    GROUP BY profile_group
    UNION
    SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
            (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
            ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1) as "Fixed %"
    FROM stats"""
    
    return query_to_list(cmd)    # return header, rows
    

def progress_by_milestone():
    
    cmd = f"""
    SELECT milestone, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
            (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
            ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
    FROM stats
    GROUP BY milestone
    UNION
    SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
            (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
            ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1) as "Fixed %"
    FROM stats"""
  
    return query_to_list(cmd)    # return header, rows
        

def progress_by_profile():
    cmd = f"""
    SELECT row_number() OVER() "#", * FROM 
    (    
        SELECT profile_group "Profile Group", profile_name "Profile Name", numHosts "Base # Hosts", 
            current_numHosts "Last Scan # Hosts", 
            total_HM "Base # Vulner", current_total_HM "Last Scan # Vulner", 
            (total_HM - current_total_HM) as "# Fixed", 
            ifnull(ROUND((total_HM - current_total_HM)*1.0 /total_HM* 100.0, 0),0) as "Fixed %"
        FROM stats
        ORDER BY "Fixed %" DESC 
        )
        UNION
        SELECT  "Total", "", "", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
                sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
                (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
                ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 0) as "Fixed %"
        FROM stats"""


    return query_to_list(cmd)    # return header, rows


def progress_VT_by_profile():

    cmd = """
    SELECT milestone, profile_name, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
        sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
        (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
        ifnull(ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1),0) as "Fixed %"
    FROM stats
    GROUP BY milestone, profile_name
    HAVING "# Fixed" != 0
    UNION
    SELECT "Total", "", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
            (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
            ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1) as "Fixed %"
    FROM stats"""
 
    # print (cmd)
    return query_to_list(cmd)    # return header, rows


def progress_VT_by_group():  #stats by VT Vulnetability Type

    cmd = f"""
    SELECT profile_group, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
        sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
        (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
        ifnull(ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1),0) as "Fixed %"
    FROM stats
    GROUP BY profile_group
    UNION
    SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
            (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
            ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1) as "Fixed %"
    FROM stats"""
   
    return query_to_list(cmd)    # return header, rows


def progress_VT_by_milestone():

    cmd = f"""
        SELECT milestone, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
            (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
            ifnull(ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1),0) as "Fixed %"
            FROM stats
            GROUP BY milestone
        UNION
            SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts", 
            sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner", 
            (sum (total_vt) - sum(current_total_vt)) as "# Fixed", 
            ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1) as "Fixed %"
        FROM stats"""
  
    return query_to_list(cmd)    # return header, rows


def progress_VT_by_followup_group():
    cmd = """
    SELECT followup_group, base_numHosts "Base # Hosts", current_numHosts "Last Scan # Hosts", 
        base_total_vt "Base # Vulner", current_total_vt "Last Scan # Vulner",
        (base_total_vt - current_total_vt) as "# Fixed"
		,  ifnull(ROUND((base_total_vt - current_total_vt)*100.0 /base_total_vt,0), 0)as "Fixed %"
    FROM stats_vt
    UNION
    SELECT "Total", sum (base_numHosts),  sum (current_numHosts), 
            sum (base_total_vt) , sum(current_total_vt) , 
            (sum (base_total_vt) - sum(current_total_vt)) , 
                ROUND((sum (base_total_vt) - sum(current_total_vt))*100.0 /sum(base_total_vt), 0)
            FROM stats_vt"""
  
    return query_to_list(cmd)    # return header, rows


def progress_by_profile_proj_scope():
    
    cmd = f"""
    SELECT milestone, profile_name, sum (numHosts) "# Hosts",  
        sum (total_HM) "Base # Vulner", sum(proj_scope_current_HM) "Last Scan # Vulner", 
        (sum (total_HM) - sum(proj_scope_current_HM)) as "# Fixed", 
        ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1) as "Fixed %"
        FROM stats
        GROUP BY milestone, profile_name
        HAVING "# Fixed" != 0
        UNION
        SELECT "Total", "", sum (numHosts),  
                sum (total_HM) , sum(proj_scope_current_HM) , 
                (sum (total_HM) - sum(proj_scope_current_HM)) , 
                ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1)
                FROM stats"""
  
    return query_to_list(cmd)    # return header, rows


def progress_by_group_proj_scope():
    
    cmd = """
    SELECT milestone, profile_group, sum (numHosts) "# Hosts",  
        sum (total_HM) "Base # Vulner", sum(proj_scope_current_HM) "Last Scan # Vulner", 
        (sum (total_HM) - sum(proj_scope_current_HM)) as "# Fixed", 
        ifnull(ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
        FROM stats
        GROUP BY milestone, profile_group
        UNION
        SELECT "Total", "", sum (numHosts),  
        sum (total_HM) , sum(proj_scope_current_HM) , 
        (sum (total_HM) - sum(proj_scope_current_HM)) , 
        ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1)
        FROM stats"""
  
    return query_to_list(cmd)    # return header, rows


def progress_by_milestone_proj_scope():
    
    cmd = """
    SELECT milestone, "", sum (numHosts) "# Hosts",  sum (current_numHosts) "Last Scan # Hosts",
        sum (total_HM) "Base # Vulner", sum(proj_scope_current_HM) "Last Scan # Vulner", 
        (sum (total_HM) - sum(proj_scope_current_HM)) as "# Fixed", 
        ifnull(ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
        FROM stats
        GROUP BY milestone
        UNION
            SELECT "Total", "", sum (numHosts),  sum (current_numHosts),
            sum (total_HM) , sum(proj_scope_current_HM) , 
            (sum (total_HM) - sum(proj_scope_current_HM)) , 
            ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1)
            FROM stats"""
  
    return query_to_list(cmd)    # return header, rows
    
def progress_by_followup_group():
    cmd = """
    SELECT followup_group, sum (numHosts) "# Hosts", sum (current_numHosts) "Last Scan # Hosts",
        sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner", 
        (sum (total_HM) - sum(current_total_HM)) as "# Fixed", 
        ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
    FROM stats
    GROUP BY followup_group
    UNION
    SELECT "Total", sum (numHosts),  sum (current_numHosts) "Last Scan # Hosts", 
            sum (total_HM) , sum(current_total_HM) , 
            (sum (total_HM) - sum(current_total_HM)) , 
            ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1)
            FROM stats"""
  
    return query_to_list(cmd)    # return header, rows

    
def vulner_CVEID_list():
    cmd = """
    SELECT ast.followup_group, name as 'profile name', vul.vulnerability, vul.cveid, vfc.fix_category
        FROM vulnerability vul 
        LEFT JOIN assets ast using (ipaddress)
		LEFT JOIN vul_fix_category vfc USING (vulnerability, followup_group)
    WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) = 0
    GROUP by  vul.vulnerability,vfc.fix_category, followup_group
	order by 1"""
    return query_to_list(cmd)    # return header, rows


def list_excluded_servers():
    cmd = """
    SELECT   ipaddress, assetName, profile_name, location,  followup_group, Role
    FROM assets 
    WHERE ifnull(exclude,0) = 1"""
    return query_to_list(cmd)    # return header, rows

def cveid_fix_category_stats():
    cmd = """
    SELECT followup_group,  COUNT(vul.vulnerability) as "CVEID Count",
        count(CASE WHEN fix_category = 'Irrelevant' THEN 1 END) as "Irrelevant",
        count(CASE WHEN substr(fix_category, 1,22)  = 'Applied latest package' THEN 1 END) as "Applied latest package",
        count(CASE WHEN substr(fix_category, 1,17)  = 'Application Issue' THEN 1 END) as 'Application Issue',
        count(CASE WHEN fix_category  = 'Oracle DBA' THEN 1 END) as 'Oracle DBA',
        count(CASE WHEN substr(fix_category, 1,5)  = 'Fixed' THEN 1 END) as 'Fixed, rescan needed',
        count(CASE WHEN substr(fix_category, 1,10)  = 'No Package' THEN 1 END) as 'No Package to Fix',
        count(CASE WHEN substr(fix_category, 1,14)  = 'False Positive' THEN 1 END) as 'False Positive', 
        count(CASE WHEN ifnull(fix_category,0) = 0 THEN 1 END) as 'unclassified' 
		FROM(
			SELECT DISTINCT followup_group, vulnerability
			FROM vulnerability vul 
			LEFT JOIN assets ast using (ipaddress)
			WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) = 0	
			GROUP by  followup_group, vulnerability
		) vul
		LEFT JOIN vul_fix_category vfc USING (vulnerability, followup_group)
		GROUP BY followup_group
	order by 1
    """
    return query_to_list(cmd)    # return header, rows