class Comments(db.Model):
    __tablename__ = 'Coments'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    publish_date = db.Column(db.DateTime(timezone=True), index=True, unique=False, nullable=False,
                             default=datetime.datetime.utcnow())
    body = db.Column(db.String(255), unique=False, nullable=False)
    user_to = db.Column(db.Integer, db.ForeignKey('Users.id'))
    user_from = db.Column(db.Integer, db.ForeignKey('Users.id'))

    @staticmethod
    def add_comment(user_to, user_from, body):
        c = Comments(user_to=user_to, user_from=user_from, body=body)

        db.session.add(c)
        db.session.commit()
        db.session.flush()

        return c.id

    @staticmethod
    def delete_comment(id):
        c = Comments.query.get(id)

        db.session.delete(c)
        db.session.commit()

    @staticmethod
    def list_by_user(id):
        # TODO doc
        list = Comments.query.filter_by(user_to=id)
        return list

    def __repr__(self):
        return '{},{},{},{}'.format(self.id, self.body, self.user_to, self.user_from)
class Notifications(db.Model):
    __tablename__ = 'Notifications'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'))
    category = db.Column(db.String(255), db.ForeignKey('Categories.cat_name'))
    date = db.Column(db.DateTime(timezone=True), default=datetime.datetime.utcnow())
    text = db.Column(db.String(255), unique=False, nullable=False)

    @staticmethod
    def push(user, text, product=None, category=None):
        n = Notifications(user_id=user, product_id=product, category=category, text=text)

        db.session.add(n)
        db.session.commit()

    @staticmethod
    def delete_id(id):
        Notifications.query.filter_by(id=id).delete()
        db.session.commit()

    @staticmethod
    def delete_all(user):
        Notifications.query.filter_by(user_id=user).delete()
        db.session.commit()

    @staticmethod
    def list_by_user(user_id):
        items = Notifications.query.filter_by(user_id=user_id)
        return items

    def __repr__(self):
        return '{},{},{},{},{},{}'.format(self.id, self.user_id, self.product_id, self.category, self.date, self.text)
class Interests(db.Model):
    __tablename__ = 'Interest'
    cat_name = db.Column(db.String, db.ForeignKey('Categories.cat_name'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), primary_key=True)

    def __repr__(self):
        return '{},{}'.format(self.cat_name, self.user_id)

    @staticmethod
    def add_interest(cat_name, user_id):
        c = Interests(cat_name=cat_name, user_id=user_id)
        db.session.add(c)
        db.session.commit()

    @staticmethod
    def get_users_interest_cat(cat_name):
        list = Interests.query.with_entities(Interests.user_id).filter_by(cat_name=cat_name)
        return list

    @staticmethod
    def interest_byUser(id):
        list = Interests.query.filter_by(user_id=id).all()
        return list

    @staticmethod
    def delete_all(user_id):
        Interests.query.filter_by(user_id=user_id).delete()
        db.session.commit()

    @staticmethod
    def delete_interest(cat, user_id):
        Interests.query.filter_by(user_id=user_id, cat_name=cat).delete()
        db.session.commit()
class Reports(db.Model):
    __tablename__ = 'Reports'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    reason = db.Column(db.String(255), unique=False, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'))
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'))
    report_date = db.Column(db.DateTime(timezone=True), index=True, unique=False, nullable=True,
                            default=datetime.datetime.utcnow())

    @staticmethod
    def new_report(user_id, product_id, reason):
        r = Reports(user_id=user_id, product_id=product_id, reason=reason)
        db.session.add(r)
        db.session.commit()
        db.session.flush()

        return r.id

    @staticmethod
    def list():
        # TODO doc
        list = Reports.query.all()
        return list

    def delete_me(self):
        # TODO doc
        db.session.delete(self)
        db.session.commit()

    @staticmethod
    def delete_by_id(report_id):
        Reports.query.get(report_id).delete_me()

    def __repr__(self):
        return '{},{},{},{}'.format(self.id, self.reason, self.user_id, self.product_id)
class Trades(db.Model):
    __tablename__ = 'Trades'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'), nullable=False)
    user_sell = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    user_buy = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    closed_s = db.Column(db.Boolean, unique=False, nullable=False, default=False)
    closed_b = db.Column(db.Boolean, unique=False, nullable=False, default=False)
    price = db.Column(db.Float, unique=False, nullable=False)
    ts_create = db.Column(db.DateTime(timezone=True), default=datetime.datetime.utcnow())
    ts_edit = db.Column(db.DateTime(timezone=True), unique=False, nullable=False, default=datetime.datetime.utcnow(),
                        onupdate=datetime.datetime.utcnow())

    offers = db.relationship("TradesOffers", cascade="all, delete-orphan")
    messages = db.relationship("Messages", cascade="all, delete-orphan")

    @staticmethod
    def add(product_id, seller_id, buyer_id):
        p = Products.query.get(product_id)

        t = Trades(product_id=product_id,
                   user_sell=seller_id,
                   user_buy=buyer_id,
                   price=p.price,
                   closed_b=False,
                   closed_s=False)

        db.session.add(t)
        db.session.commit()
        db.session.flush()

        return t.id

    @staticmethod
    def delete_id(id):
        t = Trades.query.get(id)
        db.session.delete(t)
        db.session.commit()

    @staticmethod
    def get_trades(user_id):
        items = Trades.query.filter((Trades.user_sell == str(user_id)) | (Trades.user_buy == str(user_id)))
        return items

    def set_price(self, price):
        self.price = price

        db.session.commit()

    def switch(self, who):
        if who == 's':
            self.closed_s = not self.closed_s
        elif who == 'b':
            self.closed_b = not self.closed_b

        db.session.commit()

    def __repr__(self):
        return '{},{},{},{},{}'.format(self.id, self.user_sell, self.user_buy, self.product_id, self.price)
class Follows(db.Model):
    __tablename__ = 'Follows'
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), primary_key=True)

    @staticmethod
    def get_users_follow_prod(product_id):
        list = Follows.query.with_entities(Follows.user_id).filter_by(product_id=product_id)
        return list

    def __repr__(self):
        return '{},{}'.format(self.product_id, self.user_id)
class Bids(db.Model):
    __tablename__ = 'Bids'
    bid = db.Column(db.Float, unique=False, nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), primary_key=True)
    ts_create = db.Column(db.DateTime(timezone=True), default=datetime.datetime.utcnow())

    @staticmethod
    def get_max(product_id):
        return db.session.query(Bids.bid, Bids.user_id, ).filter(Bids.product_id == product_id).order_by(
            db.desc(Bids.bid)).first()

    @staticmethod
    def add_bid(product_id, user_id, money):
        b = Bids(bid=float(money), product_id=product_id, user_id=user_id)
        db.session.add(b)
        db.session.commit()

    def __repr__(self):
        return '{},{},{}'.format(self.product_id, self.user_id, self.bid)
class CatProducts(db.Model):
    __tablename__ = 'CatProducts'
    cat_name = db.Column(db.String, db.ForeignKey('Categories.cat_name'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'), primary_key=True)

    @staticmethod
    def delete_cats_by_prod(product_id):
        CatProducts.query.filter_by(product_id=product_id).delete()

    @staticmethod
    def get_cat_names_by_prod(product_id):
        cats = CatProducts.query.with_entities(CatProducts.cat_name).filter_by(product_id=product_id)
        return cats

    @staticmethod
    def add_prod(cat_name, product_id):
        cp = CatProducts(cat_name=cat_name, product_id=product_id)
        db.session.add(cp)
        db.session.commit()

    def __repr__(self):
        return '{},{}'.format(self.cat_name, self.product_id)
class TradesOffers(db.Model):
    __tablename__ = 'TradesOffers'
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'), primary_key=True)
    trade_id = db.Column(db.Integer, db.ForeignKey('Trades.id'), primary_key=True)

    @staticmethod
    def add_product(trade_id, product_id):
        to = TradesOffers(product_id=product_id, trade_id=trade_id)

        db.session.add(to)
        db.session.commit()

    @staticmethod
    def delete_all(trade_id):
        TradesOffers.query.filter_by(trade_id=trade_id).delete()

    @staticmethod
    def get_prods_by_id(trade_id):
        items = TradesOffers.query.with_entities(TradesOffers.product_id).filter_by(trade_id=trade_id)
        return items

    def __repr__(self):
        return '{},{}'.format(self.product_id, self.trade_id)
class Messages(db.Model):
    __tablename__ = 'Messages'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    trade_id = db.Column(db.Integer, db.ForeignKey('Trades.id'), nullable=False)
    user_to = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    user_from = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
    body = db.Column(db.String(255), unique=False, nullable=False)
    msg_date = db.Column(db.DateTime(timezone=True), unique=False, nullable=True, default=datetime.datetime.utcnow())

    @staticmethod
    def new_msg(trade_id, user_to, user_from, body):
        m = Messages(trade_id=trade_id, user_to=user_to, user_from=user_from, body=body)

        db.session.add(m)
        db.session.commit()

    @staticmethod
    def get_msgs(trade_id):
        items = Messages.query.filter((Messages.trade_id == str(trade_id)))
        return items

    def __repr__(self):
        return '{},{},{},{},{}'.format(self.id, self.user_to, self.user_from, self.trade_id, self.body)
class Payments(db.Model):
    __tablename__ = 'Payments'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, index=True, nullable=False)
    pay_date = db.Column(db.DateTime(timezone=True), index=True, unique=False, nullable=False,
                         default=datetime.datetime.utcnow())
    amount = db.Column(db.Float, unique=False, nullable=False)
    iban = db.Column(db.String(255), unique=False, nullable=False)
    boost_date = db.Column(db.DateTime(timezone=True), unique=False, nullable=True)
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'))

    def __repr__(self):
        return '{},{},{}'.format(self.id, self.amount, self.product_id)

    @staticmethod
    def list():
        # TODO doc
        list = Payments.query.all()
        return list

    def delete_me(self):
        # TODO doc
        db.session.delete(self)
        db.session.commit()

    @staticmethod
    def add(amount, iban, product_id, boost_date):
        pays = Payments(product_id=product_id,
                        amount=amount,
                        iban=iban,
                        boost_date=boost_date)

        p = Products.query.get(product_id)
        p.boost_date = boost_date

        db.session.add(pays)
        db.session.commit()
        db.session.flush()

        return pays.id
class Images(db.Model):
    __tablename__ = 'Images'
    product_id = db.Column(db.Integer, db.ForeignKey('Products.id'))
    image_url = db.Column(db.String(255), primary_key=True, index=True, nullable=False)

    @staticmethod
    def delete_images_by_prod(product_id):
        Images.query.filter_by(product_id=product_id).delete()

    @staticmethod
    def get_images_by_prod(product_id):
        cats = Images.query.with_entities(Images.image_url).filter_by(product_id=product_id)
        return cats

    @staticmethod
    def add_photo(image_url, product_id):
        pp = Images(image_url=image_url, product_id=product_id)
        db.session.add(pp)
        db.session.commit()

    def __repr__(self):
        return '{},{}'.format(self.products_id, self.image_url)
class Products(db.Model):
    __tablename__ = 'Products'
    id = db.Column(db.Integer, primary_key=True, unique=True, index=True, nullable=False, autoincrement=True)
    title = db.Column(db.String(255), unique=False, index=True, nullable=False)
    descript = db.Column(db.String(255), unique=False, nullable=False)
    price = db.Column(db.Float, unique=False, nullable=False)
    publish_date = db.Column(db.Date, unique=False, nullable=False, default=datetime.datetime.utcnow())
    ban_reason = db.Column(db.String(255), unique=False, nullable=True)
    bid_date = db.Column(db.DateTime, unique=False, nullable=True)
    visits = db.Column(db.Integer, unique=False, nullable=False)
    boost_date = db.Column(db.DateTime, unique=False, nullable=True)
    followers = db.Column(db.Integer, unique=False, nullable=False)
    is_removed = db.Column(db.Boolean, unique=False, nullable=False)
    main_img = db.Column(db.String(255), nullable=False)
    place = db.Column(db.String(255), unique=False, nullable=False)
    ts_edit = db.Column(db.DateTime(timezone=True), unique=False, nullable=False, default=datetime.datetime.utcnow(),
                        onupdate=datetime.datetime.utcnow())
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'))

    offers = db.relationship("TradesOffers", cascade="all, delete-orphan")
    trades = db.relationship("Trades", cascade="all, delete-orphan")
    cats = db.relationship("CatProducts", cascade="all, delete-orphan")
    bids = db.relationship("Bids", cascade="all, delete-orphan")
    payments = db.relationship("Payments", cascade="all, delete-orphan")
    images = db.relationship("Images", cascade="all, delete-orphan")
    follows = db.relationship("Follows", cascade="all, delete-orphan")
    reports = db.relationship("Reports", cascade="all, delete-orphan")
    notifications = db.relationship("Notifications", cascade="all, delete-orphan")

    @staticmethod
    def list(user_id):
        # TODO doc
        list = Products.query.filter(Products.is_removed == False)
        list = list.filter(Products.ban_reason == None)
        if user_id is not None:
            list = list.filter(Products.user_id != user_id)
        return list.order_by(desc(Products.boost_date)).all()

    @staticmethod
    def list_by_id(id):
        # TODO doc
        list = Products.query.filter_by(user_id=id).order_by(desc(Products.boost_date))
        return list

    @staticmethod
    def get_title(id):
        # TODO doc
        prod = Products.query.get(id)
        return prod.title

    @staticmethod
    def search(user_id, title):
        list = Products.query.filter(Products.title.like('%' + title + '%')).order_by(desc(Products.boost_date))
        if user_id is not None:
            list = list.filter(Products.user_id != user_id)
        list = list.filter(Products.is_removed == False)
        list = list.filter(Products.ban_reason == None)
        return list.all()

    @staticmethod
    def search_adv(user_id, title, price_min, price_max, place, descc, category):
        list = db.session.query(Products.id.label('id'),
                                Products.title.label('title'),
                                Products.price.label('price'),
                                Products.descript.label('descript'),
                                Products.user_id.label('user_id'),
                                Products.visits.label('visits'),
                                Products.place.label('place'),
                                Products.bid_date.label('bid_date'),
                                Products.main_img.label('main_img'),
                                CatProducts.cat_name).filter(CatProducts.product_id == Products.id)

        # if user_id is not None:
        #   list = list.filter(Products.user_id != user_id)
        if price_min is not None:
            list = list.filter(Products.price >= price_min)
        if price_max is not None:
            list = list.filter(Products.price <= price_max)
        if title is not None:
            list = list.filter(Products.title.like('%' + title + '%'))
        if descc is not None:
            list = list.filter(Products.descript.like('%' + descc + '%'))
        if place is not None:
            list = list.filter(Products.place.like('%' + place + '%'))
        if category is not None:
            list = list.filter(CatProducts.cat_name == category)

        list = list.filter(Products.is_removed == False)
        list = list.filter(Products.ban_reason == None).order_by(desc(Products.boost_date))
        list = list.distinct(Products.id, Products.boost_date)

        return list.all()

    @staticmethod
    def new_product(user_id, title, descript, price, place, main_img):
        p = Products(user_id=user_id,
                     title=title,
                     descript=descript,
                     price=price,
                     place=place,
                     visits=0,
                     followers=0,
                     is_removed=False,
                     main_img=main_img
                     )
        db.session.add(p)
        db.session.commit()
        db.session.flush()

        return p.id

    def sold_me(self):
        self.is_removed = True
        db.session.commit()

    def update_me(self, title, price, descript, bid, place, main_img):
        # TODO doc
        self.title = title
        self.price = price
        self.descript = descript
        self.main_img = main_img
        self.place = place
        self.bid_date = bid

        db.session.commit()

    def delete_me(self):
        # TODO doc
        db.session.delete(self)
        db.session.commit()

    def ban_me(self, reason):
        # TODO doc
        self.ban_reason = str(reason)
        db.session.commit()

    def bid_set(self, bid):
        # TODO doc
        self.bid_date = bid
        db.session.commit()

    def increment_views(self):
        self.visits = self.visits + 1
        db.session.commit()

    def followers_up(self):
        self.followers = self.followers + 1
        db.session.commit()

    def followers_down(self):
        self.followers = self.followers - 1
        db.session.commit()

    def __repr__(self):
        return '{},{},{},{},{}'.format(self.id, self.title, self.user_id, self.visits, self.user_id)