def get_duration_of_all_videos_per_youtuber():
     existing = DbSession.query(func.count(Video.video_id), Youtuber.channel_title).filter(Video.type.isnot(VideoType.EXISTING.video_type_id)).join(Youtuber).group_by(Video.channel_id).all()
     purged = DbSession.query(func.count(PurgedVideo.video_id), Youtuber.channel_title).join(Youtuber).group_by(PurgedVideo.channel_id).all()
     existing_result = [(ex[0], ex[1]) for ex in existing]
     for ex in existing_result:
         matching = next((purged_video for purged_video in purged if ex[1] == purged_video[1]), None)
         if matching is not None:
             existing_result[existing_result.index(ex)] = (ex[0] + matching[0], ex[1])
     return existing_result

 def get_existing_stat_for_youtuber(self):
     existing = DbSession.query(func.count(Video.video_id), func.sum(Video.size)).filter(and_(Video.type.isnot(VideoType.EXISTING.video_type_id), (Video.channel_id == self.channel_id))).all()
     purged = DbSession.query(func.count(PurgedVideo.video_id), func.sum(PurgedVideo.size)).filter(PurgedVideo.channel_id == self.channel_id).all()
     old_statistic = DbSession.query(Statistic.number_downloaded, Statistic.size_downloaded).filter(Statistic.channel_id == self.channel_id).all()
     count = 0
     size = 0
     # old_statistic is grouped into types
     for stat in old_statistic:
         count += int(stat[0])
         size += int(stat[1])
     return [int(existing[0][0]) + int(purged[0][0]) + count, int(existing[0][1]) + int(purged[0][1]) + size]

 def setup_constants():
     VideoType.PLAYLIST_ITEM = VideoType.load(name=DownloadFlag.PLAYLIST_ITEM.value)
     VideoType.SCHEDULED = VideoType.load(name=DownloadFlag.SCHEDULED.value)
     VideoType.CRAWLED = VideoType.load(name=DownloadFlag.CRAWLED.value)
     VideoType.EXISTING = VideoType.load(name=DownloadFlag.EXISTING.value)
     DbSession.expunge(VideoType.PLAYLIST_ITEM)
     DbSession.expunge(VideoType.SCHEDULED)
     DbSession.expunge(VideoType.CRAWLED)
     DbSession.expunge(VideoType.EXISTING)

 def insert_contants():
     if not ErrorFatality.exists(error_name='info'):
         DbSession.add(ErrorFatality(error_name='info'))
     if not ErrorFatality.exists(error_name='warning'):
         DbSession.add(ErrorFatality(error_name='warning'))
     if not ErrorFatality.exists(error_name='error'):
         DbSession.add(ErrorFatality(error_name='error'))
     if not ErrorFatality.exists(error_name='severe'):
         DbSession.add(ErrorFatality(error_name='severe'))

 def get_duration_of_videos_per_youtuber():
     return DbSession.query(func.sum(Video.duration), Youtuber.channel_title).filter(and_(Video.type.isnot(VideoType.EXISTING.video_type_id)), Video.stored.is_(True)).join(Youtuber).group_by(Video.channel_id).all()

 def create_without_transaction(cls, **kwargs):
     instance = cls(**kwargs)
     DbSession.add(instance)
     return instance

 def get_number(cls):
     return DbSession.query(cls).count()

 def load_all(cls, **kwargs):
     q = DbSession.query(cls)
     filters = [getattr(cls, field_name) == kwargs[field_name] for field_name in kwargs]
     return q.filter(and_(*filters)).all()

 def save_to_delete(self):
     video_part = self.type == DownloadFlag.CRAWLED or self.type == DownloadFlag.SCHEDULED
     audio_part = DbSession.query(Audio).filter(Audio.video_id == self.video_id)
     playlist_item_part = PlaylistItem.exists(video_id=self.video_id)
     return not (audio_part and video_part and playlist_item_part)

 def load_with_youtuber():
     return DbSession.query(Profile, Youtuber).join(YoutuberInProfile).join(Youtuber).all()

 def delete_audio_file(self):
     full_path = self.absolute_path
     DbSession.delete(self)
     if os.path.isfile(full_path):
         os.remove(full_path)

 def get_single_statistic(channel_id, video_type):
     return DbSession.query(Statistic).filter(
         and_(Statistic.channel_id == channel_id, Statistic.video_type == video_type)).one()

 def get_videos_after_time_stamp(time_stamp):
     all_video_queue = DbSession.query(VideoQueueItem).options(Load(Video).load_only('video_id')).all()
     actual_list = [video.video_id for video in all_video_queue]
     return DbSession.query(VideoQueueItem, Video, Youtuber, VideoType, PlaylistItem, Playlist).filter(
         and_(Video.video_id.in_(actual_list), Video.downloaded_date > datetime.datetime.fromtimestamp(int(time_stamp)/1000))).order_by(
         Video.downloaded_date).all()

 def get_whole_video_queue_obj():
     return DbSession.query(VideoQueueItem, Video, Youtuber, VideoType, PlaylistItem, Playlist).join(Video).join(Youtuber).join(VideoType)\
         .outerjoin(PlaylistItem).outerjoin(Playlist).order_by(asc(Video.downloaded_date)).all()

 def get_pending_converted_videos_by_channel_id(channel_id):
     return DbSession.query(Video).filter(and_(Video.to_be_converted.is_(True), Video.channel_id == channel_id)).all()

 def get_all_audio_information():
     return DbSession.query(Audio, Video, Youtuber, Artist, Album, Cover).join(Video).join(Youtuber).outerjoin(Artist).outerjoin(Album).outerjoin(Cover).filter(Video.stored.is_(True)).all()

 def get_number_of_profiles():
     return DbSession.query(Profile).count()

 def get_whole_audio_info(video_id):
     return DbSession.query(Audio, Video, Youtuber, Artist, Album, Cover).join(Video).join(Youtuber).outerjoin(Artist).outerjoin(Album).outerjoin(Cover).filter(and_(Video.stored.is_(True), Audio.video_id == video_id)).all()

 def load(cls, **kwargs):
     q = DbSession.query(cls)
     filters = [getattr(cls, field_name) == kwargs[field_name] for field_name in kwargs]
     obj = q.filter(and_(*filters)).one()
     return obj

 def get_all_audio_files_from_channel(channel_id):
     return DbSession.query(Audio).join(Video).filter(Video.channel_id==channel_id).all()