from pymysql.connections import Connection
from pymysql.cursors import DictCursor

from mako.template import Template

from datetime import datetime

import os
import logging

__DATETIME_PATTERN = "%Y-%m-%dT%H:%M:%S"

def get_metrics(dms_ids, start, end, aggr):
	template = Template(filename="server/metrics.sql")
	sql = template.render(
		dms_ids=",".join(dms_ids),
		start=start.strftime(__DATETIME_PATTERN),
		end=end.strftime(__DATETIME_PATTERN),
		aggr=",".join(aggr)
	)
	print(sql)
	conn = Connection(
		host=os.getenv("DB_HOST"),
		database=os.getenv("DB_DATABASE"),
		user=os.getenv("DB_USER"),
		password=os.getenv("DB_PWD")
	)
	try:
		data = {
			"stores": []
		}
		curr_dms = None
		curr_status = None
		curr_priority = None
		with conn.cursor(DictCursor) as cur:
			cur.execute(sql)
			for row in cur:
				totalCnt = 0
				if curr_dms is None or row["dms_id"] != curr_dms["dms_id"]:
					curr_dms = {
						"dms_id": row["dms_id"],
						"statuses": []
					}
					data["stores"].append(curr_dms)
				if curr_status is None or row["status"] != curr_status["code"]:
					curr_status = {
						"code": row["status"],
						"label": row["status"],
						"totalCnt": 0,
						"priorities": []
					}
					curr_dms["statuses"].append(curr_status)
				if curr_priority is None or row["priority"] != curr_priority["code"]:
					curr_priority = {
						"code": row["priority"],
						"label": "P{}".format(row["priority"]),
						"metrics": []
					}
					curr_status["priorities"].append(curr_priority)
				if "cnt" in aggr:
					curr_priority["metrics"].append({
						"code": "cnt",
						"val": int(row["job_count"])
					})
					curr_status["totalCnt"] += int(row["job_count"])
				if "avg" in aggr:
					curr_priority["metrics"].append({
						"code": "avg",
						"val": int(row["average_time"])
					})
				if "min" in aggr and row["min_time"] is not None:
					curr_priority["metrics"].append({
						"code": "min",
						"val": int(row["min_time"])
					})
				if "max" in aggr and row["max_time"] is not None:
					curr_priority["metrics"].append({
						"code": "max",
						"val": int(row["max_time"])
					})
				if row["missed_street_date"] is not None:
					curr_priority["metrics"].append({
						"code": "missed",
						"val": int(row["missed_street_date"])
					})

		return {"data": data}
	finally:
		conn.close()

if __name__ == "__main__":
	import json
	result = get_metrics(["1"], "2015-01-01T00:00:00", "2015-01-02T00:00:00", ["cnt", "max", "min"])
	print(json.dumps(result, indent=4))