structure Qos :> QOS = struct

open Util Sql Init

type entry = { kind : string, kindUrl : string option, name : string, url : string option, usr : int, uname : string,
	       stamp : C.timestamp, pstamp : C.timestamp option, cstamp : C.timestamp option}

fun mkEntryRow [kind, kindUrl, name, url, usr, uname, stamp, pstamp, cstamp] =
    {kind = C.stringFromSql kind, kindUrl = if C.isNull kindUrl then NONE else SOME (C.stringFromSql kindUrl),
     name = C.stringFromSql name, url = if C.isNull url then NONE else SOME (C.stringFromSql url),
     usr = C.intFromSql usr, uname = C.stringFromSql uname, stamp = C.timestampFromSql stamp,
     pstamp = if C.isNull pstamp then NONE else SOME (C.timestampFromSql pstamp),
     cstamp = if C.isNull cstamp then NONE else SOME (C.timestampFromSql cstamp)}
  | mkEntryRow row = rowError ("QOS", row)

fun recent days =
    let
	val usr = Init.getUserId ()
	val db = getDb ()
    in
	C.map db mkEntryRow ($`SELECT SupCategory.name, 'issue?cat=' || SupCategory.id, title,
                                 'issue?cat=' || SupCategory.id || '&id=' || SupIssue.id, usr, WebUser.name,
		                 stamp, COALESCE(pstamp, cstamp), cstamp
		                 FROM SupIssue JOIN SupCategory ON SupCategory.id = cat
                                   JOIN WebUser ON WebUser.id = usr
		                 WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
				   AND (NOT priv OR usr = ^(C.intToSql usr)
				     OR (SELECT COUNT(*) FROM Membership WHERE Membership.usr = ^(C.intToSql usr)
                                       AND (Membership.grp = 0 OR Membership.grp = SupCategory.grp)) > 0)
			       UNION SELECT 'APT package', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
			         FROM Apt JOIN WebUser ON WebUser.id = usr
				 WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
			       UNION SELECT 'Domain', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
			         FROM Domain JOIN WebUser ON WebUser.id = usr
				 WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
			       UNION SELECT 'Mailing list', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
			         FROM MailingList JOIN WebUser ON WebUser.id = usr
				 WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
			       UNION SELECT 'Security', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
			         FROM Sec JOIN WebUser ON WebUser.id = usr
				 WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
		               ORDER BY stamp DESC`)
    end

type grade = { count : int, minutes : int }
type grades = { pending : grade, closed : grade }
type reportCard = { misc : grades,
		    apt : grade,
		    domain : grade,
		    mailingList : grade,
		    sec : grade }

fun mkGradeRow [count, minutes] =
    {count = C.intFromSql count,
     minutes = if C.isNull minutes then 0 else C.intFromSql minutes}
  | mkGradeRow row = rowError ("grade", row)

fun reportCard days =
    let
	val db = getDb ()

	fun gradeRow s = mkGradeRow (C.oneRow db s)

	fun default tab =
	    gradeRow ($`SELECT COUNT(*), EXTRACT(MINUTE FROM AVG(cstamp - stamp))
		          FROM ^tab
			  WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
                            AND cstamp IS NOT NULL`)
    in
	{misc = {pending = gradeRow
		 ($`SELECT COUNT(*), EXTRACT(MINUTE FROM AVG(COALESCE(pstamp, cstamp) - stamp))
	              FROM SupIssue
                      WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
                        AND COALESCE(pstamp, cstamp) IS NOT NULL`),
	         closed = gradeRow
		 ($`SELECT COUNT(*), EXTRACT(MINUTE FROM AVG(cstamp - stamp))
	              FROM SupIssue
		      WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
                        AND cstamp IS NOT NULL`)},
	 apt = default "Apt",
	 domain = default "Domain",
	 mailingList = default "MailingList",
	 sec = default "Sec"}
    end

end