class Idea(db.Model):
    __tablename__ = 'idea'

    idea_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)
    user_id = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    title = db.Column(db.String(500), nullable=False)
    desc_md = db.Column(db.Text, nullable=False)
    desc_html = db.Column(db.Text, nullable=False)

    status = db.Column(db.String(20), default='')

    # Note: The UTC timestamps will be converted to correct timezones
    # by the client
    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, title, desc, user_id):
        self.title = title
        self.user_id = user_id

        self.desc_md = desc
        self.desc_html = str(markdown2.markdown(desc))

    def __repr__(self):
        return '<Idea %r>' % self.title

    @classmethod
    def get(cls, **kwargs):
        return cls.query.filter_by(**kwargs).first()

    def new(title, desc, user_id, tags=None):
        """
        Add a new idea to the database
        """
        new_idea = Idea(title, desc, user_id)
        db.session.add(new_idea)
        db.session.commit()
        new_idea.__repr__()
        new_idea.add_tags(tags)
        return new_idea

    def delete(self):
        """
        Remove an idea from the database
        """
        db.session.delete(self)
        db.session.commit()
        return self

    def update(self, title=None, desc=None, status=None, tags=None):
        if title is not None:
            self.title = title
        if desc is not None:
            self.desc_md = desc
            self.desc_html = str(markdown2.markdown(desc))
        if status is not None:
            self.status = status
        db.session.commit()
        self.add_tags(tags)
        return self

    def add_tags(self, tags):
        if tags:
            for tagname in tags:
                tag = Tag.query.filter_by(tagname=tagname.strip()).first()
                if tag:
                    Tagging.new(tag.tag_id, self.idea_id)
                else:
                    newtag = Tag.new(tagname, '')
                    Tagging.new(newtag.tag_id, self.idea_id)

    @property
    def subscribers(self):
        """
        Users who have subscribed
        """

        ideas = IdeaSub.query.filter_by(sub_to=self.idea_id).all()

        return [str(i.sub_by) for i in ideas]

    @property
    def url(self):
        return url_for('ideas.id', idea_id=self.idea_id, _external=True)

    @property
    def vote_count(self):
        return Vote.query.filter_by(idea_id=self.idea_id).count()

    @property
    def comments_url(self):
        return url_for('comments.list', idea_id=self.idea_id, _external=True)

    @property
    def user(self):
        return User.get(user_id=self.user_id).basic

    @property
    def tags(self):
        """
        Get all tags of the idea
        """

        taggings = Tagging.query.filter_by(idea_id=self.idea_id).all()

        tags = []
        for t in taggings:
            tags.append(Tag.query.filter_by(tag_id=t.tag_id).first().tagname)

        return tags

    @property
    def basic(self):
        json = dict(
            idea_id=str(self.idea_id),
            title=self.title
        )
        return json

    @property
    def json(self):
        """
        Return the idea's data in json form
        """
        json = dict(
            idea_id=str(self.idea_id),
            title=self.title,
            desc_md=self.desc_md,
            desc_html=self.desc_html,
            status=self.status,
            vote_count=self.vote_count,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
            tags=self.tags,
            url=self.url,
            comments_url=self.comments_url,
            subscribers=self.subscribers,
            user=self.user
        )
        return json class User(db.Model, UserMixin):
    __tablename__ = 'user'

    user_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    password = db.Column(db.String(180), nullable=False)
    first_name = db.Column(db.String(120), default='')
    last_name = db.Column(db.String(120), default='')

    blog_url = db.Column(db.String(512), default='')
    facebook_url = db.Column(db.String(512), default='')
    github_url = db.Column(db.String(512), default='')
    twitter_url = db.Column(db.String(512), default='')

    # Possible values: 'Noob', 'Admin', 'Blocked'
    role = db.Column(db.String(32), default='Noob')

    # Note: The UTC timestamps will be converted to correct timezones
    # by the client
    created_on = db.Column(db.DateTime, default=dt.datetime.utcnow)
    last_login_on = db.Column(db.DateTime, default=dt.datetime.utcnow)

    def __init__(self, username, password, email):
        self.username = username
        self.password = password
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

    @classmethod
    def get(cls, **kwargs):
        return cls.query.filter_by(**kwargs).first()

    @staticmethod
    def get_anon():
        return User.query.filter_by(username='anonymous').first()

    def new(username, password, email):
        """
        Add a new user to the database
        """
        new_user = User(username, password, email)
        try:
            db.session.add(new_user)
            db.session.commit()
        except SQLexc.IntegrityError as e:
            # Todo: Raise a proper exception that the view will catch
            # raise(e)
            db.session.rollback()

        new_user.__repr__()
        return new_user

    def delete(self):
        """
        Remove a user from the database
        """
        db.session.delete(self)
        db.session.commit()
        return self

    def update(self, **kwargs):
        """
        Update a user's data to new values
        """
        for key, value in kwargs.items():
            setattr(self, key, value)
        db.session.commit()
        return self

    @property
    def subscriptions(self):
        """
        Everything subscribed by the user
        """

        ideas = IdeaSub.query.filter_by(sub_by=self.user_id).all()
        users = UserSub.query.filter_by(sub_by=self.user_id).all()
        tags = TagSub.query.filter_by(sub_by=self.user_id).all()

        subs = {}
        subs['ideas'] = [str(i.sub_to) for i in ideas]
        subs['users'] = [str(i.sub_to) for i in users]
        subs['tags'] = [str(i.sub_to) for i in tags]

        return subs

    @property
    def subscribers(self):
        """
        Users who have subscribed
        """

        users = UserSub.query.filter_by(sub_to=self.user_id).all()

        return [str(i.sub_by) for i in users]

    @property
    def avatar(self):
        """
        Return the url for user's gravatar image
        """
        return 'https://www.gravatar.com/avatar/%s?d=mm&s=%d' \
            % (md5(self.email.encode('utf-8')).hexdigest(), 128)

    @property
    def url(self):
        """
        Get the url for current user
        """
        return url_for('users.id', uid=self.user_id, _external=True)

    @property
    def basic(self):
        json = dict(
            user_id=str(self.user_id),
            username=self.username
        )
        return json

    @property
    def json(self):
        """
        Return the user's data in json form
        """
        json = dict(
            user_id=str(self.user_id),
            username=self.username,
            avatar=self.avatar,
            url=self.url,
            first_name=self.first_name,
            last_name=self.last_name,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
            blog_url=self.blog_url,
            facebook_url=self.facebook_url,
            twitter_url=self.twitter_url,
            github_url=self.github_url,
            subscriptions=self.subscriptions,
            subscribers=self.subscribers,
            role=self.role
        )
        return json

    def get_id(self):
        return str(self.user_id)

    def is_admin(self):
        return self.role == 'Admin' class Vote(db.Model):
    __tablename__ = 'vote'

    user_id = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False, primary_key=True)
    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False, primary_key=True)

    def __init__(self, user_id, idea_id):
        self.user_id = user_id
        self.idea_id = idea_id

    def new(user_id, idea_id):
        """
        Add a new vote by an user on an existing idea
        """
        new_user = Vote(user_id, idea_id)
        try:
            db.session.add(new_user)
            db.session.commit()
        except SQLexc.IntegrityError as e:
            db.session.rollback()
        return True

    def delete(self):
        """
        Remove a tag from the database
        """
        db.session.delete(self)
        db.session.commit()
        return True class Comment(db.Model):
    __tablename__ = 'comment'

    comment_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)
    user_id = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)
    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False)

    desc_md = db.Column(db.Text, nullable=False)
    desc_html = db.Column(db.Text, nullable=False)

    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, user_id, idea_id, desc):
        self.user_id = user_id
        self.idea_id = idea_id

        self.desc_md = desc
        self.desc_html = str(markdown2.markdown(desc))

    def __repr__(self):
        return '<Comment %r>' % self.desc_md

    @classmethod
    def get(cls, **kwargs):
        return cls.query.filter_by(**kwargs).first()

    def new(user_id, idea_id, desc):
        """
        Add a new comment to the database
        """
        new_cmnt = Comment(user_id, idea_id, desc)
        db.session.add(new_cmnt)
        db.session.commit()
        new_cmnt.__repr__()
        return new_cmnt

    def delete(self):
        """
        Remove a comment from the database
        """
        db.session.delete(self)
        db.session.commit()
        return self

    def update(self, **kwargs):
        """
        Update a comments's data
        """
        for key, value in kwargs.items():
            setattr(self, key, value)
        db.session.commit()
        return self

    @property
    def user(self):
        return User.get(user_id=self.user_id).basic

    @property
    def basic(self):
        json = dict(
            comment_id=str(self.comment_id),
            desc_md=self.desc_md
        )
        return json

    @property
    def json(self):
        """
        Return the comment's data in json form
        """
        json = dict(
            comment_id=str(self.comment_id),
            desc_md=self.desc_md,
            desc_html=self.desc_html,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
            user=self.user
        )
        return json class Tag(db.Model):
    __tablename__ = 'tag'

    tag_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)
    tagname = db.Column(db.String(50), unique=True, nullable=False)

    # Todo: Tag description should be makrdown?
    desc = db.Column(db.Text(), default='')
    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, tagname, desc):
        self.tagname = tagname.strip()
        self.desc = desc

    def __repr__(self):
        return '<Tag %r>' % self.tagname

    @classmethod
    def get(cls, **kwargs):
        return cls.query.filter_by(**kwargs).first()

    def new(tagname, desc=''):
        """
        Add a new tag to the database
        """
        new_tag = Tag(tagname, desc)
        db.session.add(new_tag)
        db.session.commit()

        new_tag.__repr__()
        return new_tag

    def delete(self):
        """
        Remove a tag from the database
        """
        db.session.delete(self)
        db.session.commit()
        return self

    # Todo: Renaming to a new (or existing?) one
    def update(self, tagname=None, desc=None):
        if tagname is not None:
            self.tagname = tagname
        if desc is not None:
            self.desc = desc

        db.session.commit()
        return self

    @property
    def subscribers(self):
        """
        Users who have subscribed
        """

        tags = TagSub.query.filter_by(sub_to=self.tag_id).all()

        return [str(i.sub_by) for i in tags]

    @property
    def url(self):
        return url_for('tags.id', tag_id=self.tag_id, _external=True)

    @property
    def ideas(self):
        """
        Get all ideas of the tag
        """

        taggings = Tagging.query.filter_by(tag_id=self.tag_id).all()

        return [str(t.idea_id) for t in taggings]

    @property
    def json(self):
        """
        Return the tag's data in json form
        """
        json = dict(
            tag_id=str(self.tag_id),
            tagname=self.tagname,
            desc=self.desc,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
            ideas=self.ideas,
            subscribers=self.subscribers,
            url=self.url
        )
        return json class Tagging(db.Model):
    __tablename__ = 'tagging'

    tag_id = db.Column(
        UUID(), db.ForeignKey('tag.tag_id', ondelete='CASCADE'),
        nullable=False, primary_key=True)
    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False, primary_key=True)

    def __init__(self, tag_id, idea_id):
        self.tag_id = tag_id
        self.idea_id = idea_id

    def __repr__(self):
        return '<Tagging %r>' % self.tag_id

    def new(tag_id, idea_id):
        """
        Add a new tag to an existing idea
        """
        new_tag = Tagging(tag_id, idea_id)
        try:
            db.session.add(new_tag)
            db.session.commit()
        except SQLexc.IntegrityError as e:
            db.session.rollback()

        new_tag.__repr__()
        return new_tag

    def delete(self):
        """
        Remove a tag from the database
        """
        db.session.delete(self)
        db.session.commit()
        return self class NotifIdeaByUser(db.Model):
    __tablename__ = 'notif_idea_by_user'

    notif_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)

    user_by = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    user_to = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False)

    # Todo: Add proper Read/Unread support
    read = db.Column(db.Boolean, nullable=False, server_default='0')

    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, user_by, user_to, idea_id):
        self.user_by = user_by
        self.user_to = user_to
        self.idea_id = idea_id

    def __repr__(self):
        return '<Notif Idea By User: %r>' % self.notif_id

    def new(user_by, user_to, idea_id):
        """
        Add a new notif to the database
        """
        notif = NotifIdeaByUser(user_by, user_to, idea_id)
        db.session.add(notif)
        db.session.commit()

        notif.__repr__()
        return notif

    @property
    def idea(self):
        return Idea.get(idea_id=self.idea_id)

    @property
    def title(self):
        username = User.get(user_id=self.user_by).username
        return "%s added a new idea \"%s\"." % (username, self.idea.title)

    @property
    def json(self):
        json = dict(
            id=str(self.notif_id),
            by=User.get(user_id=self.user_by).basic,
            to=User.get(user_id=self.user_to).basic,
            title=self.title,
            link=self.idea.url,
            object=self.idea.basic,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
        )
        return json class NotifIdeaUpdate(db.Model):
    __tablename__ = 'notif_idea_update'

    notif_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)

    user_by = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    user_to = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False)

    # Todo: Add proper Read/Unread support
    read = db.Column(db.Boolean, nullable=False, server_default='0')

    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, user_by, user_to, idea_id):
        self.user_by = user_by
        self.user_to = user_to
        self.idea_id = idea_id

    def __repr__(self):
        return '<Notif Idea Update: %r>' % self.notif_id

    def new(user_by, user_to, idea_id):
        """
        Add a new notif to the database
        """
        notif = NotifIdeaUpdate(user_by, user_to, idea_id)
        db.session.add(notif)
        db.session.commit()

        notif.__repr__()
        return notif

    @property
    def idea(self):
        return Idea.get(idea_id=self.idea_id)

    @property
    def title(self):
        username = User.get(user_id=self.user_by).username
        return "%s updated the idea \"%s\"." % (username, self.idea.title)

    @property
    def json(self):
        json = dict(
            id=str(self.notif_id),
            by=User.get(user_id=self.user_by).basic,
            to=User.get(user_id=self.user_to).basic,
            title=self.title,
            link=self.idea.url,
            object=self.idea.basic,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
        )
        return json class NotifCommentByUser(db.Model):
    __tablename__ = 'notif_comment_by_user'

    notif_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)

    user_by = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    user_to = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False)

    comment_id = db.Column(
        UUID(), db.ForeignKey('comment.comment_id', ondelete='CASCADE'),
        nullable=False)

    # Todo: Add proper Read/Unread support
    read = db.Column(db.Boolean, nullable=False, server_default='0')

    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, user_by, user_to, idea_id, comment_id):
        self.user_by = user_by
        self.user_to = user_to
        self.idea_id = idea_id
        self.comment_id = comment_id

    def __repr__(self):
        return '<Notif Comment by User: %r>' % self.notif_id

    def new(user_by, user_to, idea_id, comment_id):
        """
        Add a new notif to the database
        """
        notif = NotifCommentByUser(user_by, user_to, idea_id, comment_id)
        db.session.add(notif)
        db.session.commit()

        notif.__repr__()
        return notif

    @property
    def idea(self):
        return Idea.get(idea_id=self.idea_id)

    @property
    def comment(self):
        return Comment.get(comment_id=self.comment_id)

    @property
    def title(self):
        username = User.get(user_id=self.user_by).username
        return "%s commented on the idea \"%s\"." % (username, self.idea.title)

    @property
    def json(self):
        json = dict(
            id=str(self.notif_id),
            by=User.get(user_id=self.user_by).basic,
            to=User.get(user_id=self.user_to).basic,
            title=self.title,
            link=self.idea.url,
            object=self.comment.basic,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
        )
        return json class NotifCommentOnIdea(db.Model):
    __tablename__ = 'notif_comment_on_idea'

    notif_id = db.Column(UUID(), primary_key=True, default=uuid.uuid4)

    user_by = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    user_to = db.Column(
        UUID(), db.ForeignKey('user.user_id', ondelete='CASCADE'),
        nullable=False)

    idea_id = db.Column(
        UUID(), db.ForeignKey('idea.idea_id', ondelete='CASCADE'),
        nullable=False)

    comment_id = db.Column(
        UUID(), db.ForeignKey('comment.comment_id', ondelete='CASCADE'),
        nullable=False)

    # Todo: Add proper Read/Unread support
    read = db.Column(db.Boolean, nullable=False, server_default='0')

    created_on = db.Column(
        db.DateTime, default=dt.datetime.utcnow, nullable=False)

    def __init__(self, user_by, user_to, idea_id, comment_id):
        self.user_by = user_by
        self.user_to = user_to
        self.idea_id = idea_id
        self.comment_id = comment_id

    def __repr__(self):
        return '<Notif Comment on Idea: %r>' % self.notif_id

    def new(user_by, user_to, idea_id, comment_id):
        """
        Add a new notif to the database
        """
        notif = NotifCommentOnIdea(user_by, user_to, idea_id, comment_id)
        db.session.add(notif)
        db.session.commit()

        notif.__repr__()
        return notif

    @property
    def idea(self):
        return Idea.get(idea_id=self.idea_id)

    @property
    def comment(self):
        return Comment.get(comment_id=self.comment_id)

    @property
    def title(self):
        username = User.get(user_id=self.user_by).username
        return "A new comment on the idea \"%s\"." % self.idea.title

    @property
    def json(self):
        json = dict(
            id=str(self.notif_id),
            by=User.get(user_id=self.user_by).basic,
            to=User.get(user_id=self.user_to).basic,
            title=self.title,
            link=self.idea.url,
            object=self.idea.basic,
            created_on=self.created_on.strftime('%a, %d %b %Y %H:%M:%S'),
        )
        return json
from flask import (
    Blueprint,
    redirect,
    render_template,
    request,
    url_for
)
from flask_login import (
    current_user,
    login_required
)
from server.exceptions import *
from server.ideas.models import Idea
from server.users.models import User
from server.votes.models import Vote
from server.comments.models import Comment
from server.tags.models import Tag
from server.tagging.models import Tagging
from server.notifications.models import (
    NotifIdeaByUser,
    NotifIdeaUpdate,
    NotifCommentByUser,
    NotifCommentOnIdea
)


# Todo: (7) Create frontend based exceptions and an error page



frontend_bp = Blueprint('frontend', __name__)


@frontend_bp.route('/', endpoint='index')
def homepage():
    # if not current_user.is_anonymous():
    return redirect(url_for('frontend.explore'))
    # return render_template('index.html')


@frontend_bp.route('/add/', endpoint='add')
def add_idea():
    return render_template('add.html')


@frontend_bp.route('/register/', endpoint='register')
def register():
    return render_template('register.html')


@frontend_bp.route('/profile/', endpoint='profile')
@login_required
def profile():
    return render_template('profile.html', user=current_user)


@frontend_bp.route('/explore/', endpoint='explore')
def explore():
    ideas = Idea.query.order_by(Idea.created_on.desc()).limit(50)
    return render_template('explore.html', ideas=ideas)


@frontend_bp.route('/notifications/', endpoint='notifications')
@login_required
def explore():

    notifs = NotifIdeaByUser.query \
        .filter_by(user_to=current_user.user_id, read=False) \
        .order_by(NotifIdeaByUser.created_on.desc()).limit(50).all()

    notifs += NotifIdeaUpdate.query \
        .filter_by(user_to=current_user.user_id, read=False) \
        .order_by(NotifIdeaUpdate.created_on.desc()).limit(50).all()

    notifs += NotifCommentByUser.query \
        .filter_by(user_to=current_user.user_id, read=False) \
        .order_by(NotifCommentByUser.created_on.desc()).limit(50).all()

    notifs += NotifCommentOnIdea.query \
        .filter_by(user_to=current_user.user_id, read=False) \
        .order_by(NotifCommentOnIdea.created_on.desc()).limit(50).all()

    return render_template('notifications.html', notifs=notifs)


@frontend_bp.route('/search/', endpoint='search', methods=['GET'])
def search():
    try:
        term = request.args['q']
        ideas = Idea.query.filter(Idea.title.contains(term)) \

            .order_by(Idea.created_on.desc()).limit(50).all()