import datetime
import logging
from django.contrib.auth.models import User, UserManager
from django.db import connection, models, transaction
from django.db.models import Q
from forum.models import *
from urllib import quote, unquote

class QuestionManager(models.Manager):
    def get_translation_questions(self, orderby, page_size):
        questions = self.filter(deleted=False, author__id__in=[28,29]).order_by(orderby)[:page_size]
        return questions
    
    def get_questions_by_pagesize(self, orderby, page_size):
        questions = self.filter(deleted=False).order_by(orderby)[:page_size]
        return questions
    
    def get_questions_by_tag(self, tagname, orderby):
        questions = self.filter(deleted=False, tags__name = unquote(tagname)).order_by(orderby)
        return questions
    
    def get_unanswered_questions(self, orderby):
        questions = self.filter(deleted=False, answer_accepted=False).order_by(orderby)
        return questions
    
    def get_questions(self, orderby):
        questions = self.filter(deleted=False).order_by(orderby)
        return questions
    
    def update_tags(self, question, tagnames, user):
        """
        Updates Tag associations for a question to match the given
        tagname string.

        Returns ``True`` if tag usage counts were updated as a result,
        ``False`` otherwise.
        """
        from forum.models import Tag
        current_tags = list(question.tags.all())
        current_tagnames = set(t.name for t in current_tags)
        updated_tagnames = set(t for t in tagnames.split(' ') if t)
        modified_tags = []

        removed_tags = [t for t in current_tags
                        if t.name not in updated_tagnames]
        if removed_tags:
            modified_tags.extend(removed_tags)
            question.tags.remove(*removed_tags)

        added_tagnames = updated_tagnames - current_tagnames
        if added_tagnames:
            added_tags = Tag.objects.get_or_create_multiple(added_tagnames,
                                                            user)
            modified_tags.extend(added_tags)
            question.tags.add(*added_tags)

        if modified_tags:
            Tag.objects.update_use_counts(modified_tags)
            return True

        return False

    def update_answer_count(self, question):
        """
        Executes an UPDATE query to update denormalised data with the
        number of answers the given question has.
        """
        
        # for some reasons, this Answer class failed to be imported,
        # although we have imported all classes from models on top.
        from forum.models import Answer
        self.filter(id=question.id).update(
            answer_count=Answer.objects.get_answers_from_question(question).filter(deleted=False).count())
    
    def update_view_count(self, question):
        """
        update counter+1 when user browse question page
        """
        self.filter(id=question.id).update(view_count = question.view_count + 1)
    
    def update_favorite_count(self, question):
        """
        update favourite_count for given question
        """
        from forum.models import FavoriteQuestion
        self.filter(id=question.id).update(favourite_count = FavoriteQuestion.objects.filter(question=question).count())
        
    def get_similar_questions(self, question):
        """
        Get 10 similar questions for given one.
        This will search the same tag list for give question(by exactly same string) first.
        Questions with the individual tags will be added to list if above questions are not full.
        """
        #print datetime.datetime.now()
        from forum.models import Question
        questions = list(Question.objects.filter(tagnames = question.tagnames, deleted=False).all())

        tags_list = question.tags.all()
        for tag in tags_list:
            extend_questions = Question.objects.filter(tags__id = tag.id, deleted=False)[:50]
            for item in extend_questions:
                if item not in questions and len(questions) < 10:
                    questions.append(item)
          
        #print datetime.datetime.now()
        return questions    
    
class TagManager(models.Manager):
    UPDATE_USED_COUNTS_QUERY = (
        'UPDATE tag '
        'SET used_count = ('
            'SELECT COUNT(*) FROM question_tags '
            'INNER JOIN question ON question_id=question.id '
            'WHERE tag_id = tag.id AND question.deleted=0'
        ') '
        'WHERE id IN (%s)')

    def get_valid_tags(self, page_size):
      from forum.models import Tag
      tags = Tag.objects.all().filter(deleted=False).exclude(used_count=0).order_by("-id")[:page_size]
      return tags
    
    def get_or_create_multiple(self, names, user):
        """
        Fetches a list of Tags with the given names, creating any Tags
        which don't exist when necesssary.
        """
        tags = list(self.filter(name__in=names))
        #Set all these tag visible
        for tag in tags:
            if tag.deleted:
                tag.deleted = False
                tag.deleted_by = None
                tag.deleted_at = None
                tag.save()
                
        if len(tags) < len(names):
            existing_names = set(tag.name for tag in tags)
            new_names = [name for name in names if name not in existing_names]
            tags.extend([self.create(name=name, created_by=user)
                         for name in new_names if self.filter(name=name).count() == 0 and len(name.strip()) > 0])
             
        return tags

    def update_use_counts(self, tags):
        """Updates the given Tags with their current use counts."""
        if not tags:
            return
        cursor = connection.cursor()
        query = self.UPDATE_USED_COUNTS_QUERY % ','.join(['%s'] * len(tags))
        cursor.execute(query, [tag.id for tag in tags])
        transaction.commit_unless_managed()
    
    def get_tags_by_questions(self, questions):
        question_ids = []
        for question in questions:
            question_ids.append(question.id)

        question_ids_str = ','.join([str(id) for id in question_ids])
        related_tags = self.extra(
                tables=['tag', 'question_tags'],
                where=["tag.id = question_tags.tag_id AND question_tags.question_id IN (" + question_ids_str + ")"]
        ).distinct()
        
        return related_tags

class AnswerManager(models.Manager):
    GET_ANSWERS_FROM_USER_QUESTIONS = u'SELECT answer.* FROM answer INNER JOIN question ON answer.question_id = question.id WHERE question.author_id =%s AND answer.author_id <> %s'
    def get_answers_from_question(self, question, user=None):
        """
        Retrieves visibile answers for the given question. Delete answers
        are only visibile to the person who deleted them.
        """
       
        if user is None or not user.is_authenticated():
            return self.filter(question=question, deleted=False)
        else:
            return self.filter(Q(question=question),
                               Q(deleted=False) | Q(deleted_by=user))
                               
    def get_answers_from_questions(self, user_id):
        """
        Retrieves visibile answers for the given question. Which are not included own answers
        """
        cursor = connection.cursor()
        cursor.execute(self.GET_ANSWERS_FROM_USER_QUESTIONS, [user_id, user_id])
        return cursor.fetchall()

class VoteManager(models.Manager):
    COUNT_UP_VOTE_BY_USER = "SELECT count(*) FROM vote WHERE user_id = %s AND vote = 1"
    COUNT_DOWN_VOTE_BY_USER = "SELECT count(*) FROM vote WHERE user_id = %s AND vote = -1"
    COUNT_VOTES_PER_DAY_BY_USER = "SELECT COUNT(*) FROM vote WHERE user_id = %s AND DATE(voted_at) = DATE(NOW())"
    def get_up_vote_count_from_user(self, user):
        if user is not None:
            cursor = connection.cursor()
            cursor.execute(self.COUNT_UP_VOTE_BY_USER, [user.id])
            row = cursor.fetchone()
            return row[0]
        else:
            return 0
    
    def get_down_vote_count_from_user(self, user):
        if user is not None:
            cursor = connection.cursor()
            cursor.execute(self.COUNT_DOWN_VOTE_BY_USER, [user.id])
            row = cursor.fetchone()
            return row[0]
        else:
            return 0
            
    def get_votes_count_today_from_user(self, user):
        if user is not None:
            cursor = connection.cursor()
            cursor.execute(self.COUNT_VOTES_PER_DAY_BY_USER, [user.id])
            row = cursor.fetchone()
            return row[0]

        else:
            return 0
            
class FlaggedItemManager(models.Manager):
    COUNT_FLAGS_PER_DAY_BY_USER = "SELECT COUNT(*) FROM flagged_item WHERE user_id = %s AND DATE(flagged_at) = DATE(NOW())"
    def get_flagged_items_count_today(self, user):
        if user is not None:
            cursor = connection.cursor()
            cursor.execute(self.COUNT_FLAGS_PER_DAY_BY_USER, [user.id])
            row = cursor.fetchone()
            return row[0]

        else:
            return 0

class ReputeManager(models.Manager):
    COUNT_REPUTATION_PER_DAY_BY_USER = "SELECT SUM(positive)+SUM(negative) FROM repute WHERE user_id = %s AND (reputation_type=1 OR reputation_type=-8) AND DATE(reputed_at) = DATE(NOW())"
    def get_reputation_by_upvoted_today(self, user):
        """
        For one user in one day, he can only earn rep till certain score (ep. +200) 
        by upvoted(also substracted from upvoted canceled). This is because we need
        to prohibit gaming system by upvoting/cancel again and again.
        """
        if user is not None:
            cursor = connection.cursor()
            cursor.execute(self.COUNT_REPUTATION_PER_DAY_BY_USER, [user.id])
            row = cursor.fetchone()
            return row[0]

        else:
            return 0    
class AwardManager(models.Manager):
    def get_recent_awards(self):
        awards = super(AwardManager, self).extra(
            select={'badge_id': 'badge.id', 'badge_name':'badge.name',
                          'badge_description': 'badge.description', 'badge_type': 'badge.type',
                          'user_id': 'auth_user.id', 'user_name': 'auth_user.username'
                          },
            tables=['award', 'badge', 'auth_user'],
            order_by=['-awarded_at'],
            where=['auth_user.id=award.user_id AND badge_id=badge.id'],
        ).values('badge_id', 'badge_name', 'badge_description', 'badge_type', 'user_id', 'user_name')
        return awards