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

stats_by_group_cmd = f""" SELECT  vlan_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 = '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
    GROUP by vlan_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 = '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  vlan_group, location, assets.ipaddress, assets.assetName,
	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 vul LEFT JOIN assets on assets.ipaddress = vul.ipaddress
    GROUP by location, vlan_group, assets.ipaddress
    ORDER BY 1,2 DESC"""   
    
vulners_list_all_cmd = """ select Location, profile_name, vlan_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 SAN_stats(file_name):
    cmd = f""" select name,ipaddress, assetName, 
	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
    WHERE name LIKE '%SAN%'
    GROUP by name"""
    query_to_excel(cmd, file_name)
    
 
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)
    
load_stats_cmd= """INSERT into TABLE "stats" (
    select date('now') as "Date", vlan_group as "Group", location, 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 = '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
    GROUP by location, vlan_group
    ORDER BY 1,2 DESC"""
    
def progress_by_group(export=None):

    if export:
        if not os.path.exists(OUT_FOLDER):
            messagebox.showinfo("Error", f"Folder does not exist: {OUT_FOLDER}")
            return -1
    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT profile_group, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", 
        sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
        ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  a.profile_group, a.numHosts BH, b.numHosts LH,
          (a.High + a.Medium) BT,
          (b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY profile_group
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  a.profile_group, a.numHosts BH, b.numHosts LH,
          (a.High + a.Medium) BT,
          (b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
  )"""
    
    return query_to_list(cmd)    # return header, rows
    

def progress_by_milestone(export=None):

    # print ("export: ", export)
    if export:
        if not os.path.exists(OUT_FOLDER):
            messagebox.showinfo("Error", f"Folder does not exist: {OUT_FOLDER}")
            return -1
    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT milestone, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  b.milestone, a.numHosts BH, b.numHosts LH,
          (a.High + a.Medium) BT,
          (b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")

)
GROUP BY milestone
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  b.milestone, a.numHosts BH, b.numHosts LH,
          (a.High + a.Medium) BT,
          (b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
  )"""
  
    # if export:
        # query_to_excel(cmd, os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))  
        # messagebox.showinfo("Export DOne", os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))
    # else:
    return query_to_list(cmd)    # return header, rows
        
def progress_by_profile():

    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT milestone, profile_name, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", 
        sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
        ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  a.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
          a.total_HM BT,
          b.total_HM LT, (a.total_HM - b.total_HM) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY milestone, profile_name
HAVING "# Fixed" != 0
UNION
SELECT "Total", "",sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  b.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
          a.total_HM BT,
          b.total_HM LT, (a.total_HM - b.total_HM) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
  )"""
  
    # print (cmd)
    # if export:
        # query_to_excel(cmd, os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))  
    # else:
    return query_to_list(cmd)    # return header, rows
    
def progress_VT_by_profile():

    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT milestone, profile_name, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", 
        sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
        ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 0) as "Fixed %"
FROM (
SELECT  a.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY milestone, profile_name
HAVING "# Fixed" != 0
UNION
SELECT "Total", "",sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 0) as "Fixed %"
FROM (
SELECT  b.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
  )"""
  
    # print (cmd)
    return query_to_list(cmd)    # return header, rows
    
def progress_VT_by_group(export=None):  #stats by VT Vulnetability Type

    if export:
        if not os.path.exists(OUT_FOLDER):
            messagebox.showinfo("Error", f"Folder does not exist: {OUT_FOLDER}")
            return -1
    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT profile_group, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", 
        sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
        ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT a.profile_group, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY profile_group
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", 
        (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT a.profile_group, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
)"""
    
    return query_to_list(cmd)    # return header, rows
    
def progress_VT_by_milestone(export=None):

    # print ("export: ", export)
    if export:
        if not os.path.exists(OUT_FOLDER):
            messagebox.showinfo("Error", f"Folder does not exist: {OUT_FOLDER}")
            return -1
    base_date = "2020-09-22"
    header, lst = query_to_list ("select date from stats order by 1 DESC limit 1")
    last_date = lst[0][0]
    
    cmd = f"""
    SELECT milestone, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  b.milestone, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")

)
GROUP BY milestone
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed", 
         ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT  b.milestone, a.numHosts BH, b.numHosts LH,
          a.total_vt BT,
          b.total_vt LT, (a.total_vt - b.total_vt) as F
          FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
          AND a.profile_name = b.profile_name
          AND (a.date = "{base_date}" AND b.date = "{last_date}")
  )"""
  
    return query_to_list(cmd)    # return header, rows