# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models
from django.db import connection


class Campus(models.Model):
	name = models.CharField(max_length=128)

	def __unicode__(self):
		return str(self.name)

	class Meta:
		verbose_name_plural = "Campuses"


class Faculty(models.Model):
	campus = models.ForeignKey(Campus, null=True, on_delete=models.SET_NULL)

	name = models.CharField(max_length=128)

	def __unicode__(self):
		return str(self.name)

	class Meta:
		verbose_name_plural = "Faculties"


class Degree(models.Model):
	faculty = models.ForeignKey(Faculty, null=True, on_delete=models.SET_NULL)
	code = models.CharField(primary_key=True, max_length=64)
	name = models.CharField(max_length=128)

	@property
	def mean_grade(self):
		with connection.cursor() as c:
			c.execute("SELECT value FROM stats_degree_average WHERE degree_id ='%s'" % self.pk)
			return c.fetchone()[0]

	@property
	def yearly_mean_grade(self):
		with connection.cursor() as c:
			c.execute("SELECT year, value FROM stats_degree_year_average WHERE degree_id ='%s'" % self.pk)
			res = c.fetchall()
			return [{'year' : row[0], 'value': row[1]} for row in res]

	def __unicode__(self):
		return str(self.name)


class Course(models.Model):
	code = models.CharField(primary_key=True, max_length=64)
	degree = models.ForeignKey(Degree, on_delete=models.CASCADE)

	name = models.CharField(max_length=128)
	tag = models.CharField(max_length=16)

	@property
	def mean_grade(self):
		with connection.cursor() as c:
			c.execute("SELECT value FROM stats_course_average WHERE course_id ='%s'" % self.pk)
			return c.fetchone()[0]

	def __unicode__(self):
		return str(self.name)


class Teacher(models.Model):
	faculty = models.ForeignKey(Faculty, null=True, on_delete=models.SET_NULL)

	@property
	def name(self):
		return str(self)

	def __unicode__(self):
		if hasattr(self, "profile"):
			return str(self.profile.full_name)
		else:
			return "Professor at " + self.faculty.name

	def is_mentor(student):
		for r in student.record_set:
			if r.teacher == self:
				return True
		return False


class Student(models.Model):

	# "Fake PK" when using imported data, outside IDs must remain consistent
	external_key = models.IntegerField(primary_key=True)

	# "Real PK", ideally outside data will be loaded by niub, this wont be the case always
	niub = models.CharField(db_index=True, max_length=32)

	@property
	def name(self):
		return str(self)

	def __unicode__(self):
		if hasattr(self, "profile"):
			return str(self.profile.full_name)
		else:
			return str(self.niub)


class Record(models.Model):

	degree = models.ForeignKey(Degree, null=True, on_delete=models.SET_NULL)
	teacher = models.ForeignKey(Teacher, null=True, on_delete=models.SET_NULL)
	student = models.ForeignKey(Student, on_delete=models.CASCADE)

	year_started = models.IntegerField()
	year_ended = models.IntegerField(null=True, blank=True)

	# Dropout Prediction for this particular Record (linked to a Student)
	pred_dropout = models.BooleanField(default=False)

	def __unicode__(self):
		return "%s [%d]" % (self.degree.name, self.year_started)

	@property
	def mean_grade(self):
		with connection.cursor() as c:
			c.execute("SELECT value FROM stats_record_average WHERE record_id ='%d'" % self.pk)
			return c.fetchone()[0]

	@property
	def percentile(self):
		with connection.cursor() as c:
			c.execute("SELECT percentile FROM stats_record_percentile WHERE record_id ='%d'" % self.pk)
			return c.fetchone()[0]


class Grade(models.Model):
	record = models.ForeignKey(Record, on_delete=models.CASCADE)
	course = models.ForeignKey(Course, null=True, on_delete=models.SET_NULL)

	value = models.DecimalField(max_digits=4, decimal_places=2)
	year = models.IntegerField()

	predicted = models.BooleanField(default=False)

	def __unicode__(self):
		return "%s [%2.2f]" % (self.course.name, float(self.value))


class YearlyCourseAverage(models.Model):
	course = models.ForeignKey(Course, related_name="yearly_average")
	year = models.IntegerField()
	value = models.DecimalField(max_digits=4, decimal_places=2)

	class Meta:
		db_table = 'stats_course_year_average'
		managed = False


class YearlyDegreeAverage(models.Model):
	degree = models.ForeignKey(Degree, related_name="yearly_average" )
	year = models.IntegerField()
	value = models.DecimalField(max_digits=4, decimal_places=2)

	class Meta:
		db_table = 'stats_degree_year_average'
		managed = False


class YearlyRecordAverage(models.Model):
	record = models.ForeignKey(Record, related_name="yearly_average" )
	year = models.IntegerField()
	value = models.DecimalField(max_digits=4, decimal_places=2)

	class Meta:
		db_table = 'stats_record_year_average'
		managed = False
