from sqlalchemy import Table, text
from sqlalchemy.dialects.postgresql import UUID
import uuid

from sqlalchemy.ext.hybrid import hybrid_property

import Models
from Models.AnalyticsModel import todays_analytics
from app import db

attendance_table = Table('attend', Models.Base.metadata,
                         db.Column('user_id', db.ForeignKey('user.id')),
                         db.Column('project_id', db.ForeignKey('project.id')))

banned_table = Table('banned', Models.Base.metadata,
                     db.Column('user_id', db.ForeignKey('user.id')),
                     db.Column('project_id', db.ForeignKey('project.id')))

leader_table = Table('leader', Models.Base.metadata,
                     db.Column('user_id', db.ForeignKey('user.id')),
                     db.Column('project_id', db.ForeignKey('project.id')))


class ProjectModel(Models.Base):
    __tablename__ = "project"

    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = db.Column(db.String, nullable=False)
    description = db.Column(db.String, nullable=True)
    has_exam = db.Column(db.Boolean, nullable=False, default=False)
    is_private = db.Column(db.Boolean, nullable=False, default=True)
    is_visible = db.Column(db.Boolean, nullable=False, default=True)
    project_type_id = db.Column(db.ForeignKey('project_types.id'), nullable=True)
    show_only_reviewed_media = db.Column(db.Boolean, nullable=False, default=False)
    project_leader = db.relationship(
        "UserModel",
        order_by="UserModel.username",
        secondary=leader_table
    )
    channel_id = db.Column(db.ForeignKey('channel.id'), nullable=False)
    channel = db.relationship(
        "ChannelModel",
        back_populates="project",
        cascade="all, delete-orphan",
        single_parent=True
    )
    attendees = db.relationship(
        "UserModel",
        order_by="UserModel.username",
        secondary=attendance_table
    )
    @hybrid_property
    def len_attendees(self):
        return len(self.attendees)

    @len_attendees.expression
    def len_attendees(self):
        return (db.select([db.func.count(attendance_table.c.user_id).label("len_attendees")])
                .where(attendance_table.c.project_id == self.id).correlate(self)
                .label("all_attendees"))

    project_type = db.relationship(
        "ProjectTypeModel"
    )

    def get_project_type(self, lang):
        if not self.project_type_id:
            return Models.ProjectTypeModel.get_default().get_name(lang)
        return self.project_type.get_name(lang)

    def set_project_type(self, p_type):
        self.project_type = Models.ProjectTypeModel.from_string(p_type)

    black_list = db.relationship(
        "UserModel",
        secondary=banned_table
    )
    join_requests = db.relationship(
        "JoinRequestModel",
        back_populates="project",
        cascade="all, delete-orphan"
    )
    invites = db.relationship(
        "InviteModel",
        back_populates="project",
        cascade="all, delete-orphan"
    )
    media = db.relationship(
        "MediaModel",
        back_populates="project",
        order_by="MediaModel.order",
        cascade="all, delete-orphan"
    )

    def __init__(self, **kwargs):
        super().__init__(**kwargs)
        todays_analytics().projects_count += 1

    def delete(self):
        self.channel.delete()
        declaration = Models.GroupMatchingDeclarationModel.query.filter_by(declared_project=self).first()
        if declaration:
            db.engine.execute(text(f"DELETE FROM declare WHERE group_matching_id = '{declaration.id}';"))
            db.session.delete(declaration)
        db.engine.execute(text(f"DELETE FROM attend WHERE project_id = '{self.id}';"))
        db.engine.execute(text(f"DELETE FROM banned WHERE project_id = '{self.id}';"))
        db.session.delete(self)
        db.session.commit()


moderator_id = '2bea3794-7cb8-4677-8b6e-387df36a065d'


def create_moderator_project():
    mods = ProjectModel(name="Moderator",
                        is_private=True,
                        is_visible=False,
                        has_exam=False,
                        channel=Models.ChannelModel(),
                        id=uuid.UUID(moderator_id))
    db.session.add(mods)
    db.session.commit()
    return mods


def moderator_project():
    mods = ProjectModel.query.get(moderator_id)
    if not mods:
        return create_moderator_project()
    return mods
