# Path: crawler/gather/models.py
# class LiveTVSite(Base):
#     """ 直播站点 """
#     __tablename__ = 'livetv_site'
# 
#     id = Column(Integer, primary_key=True)
#     channels = relationship('LiveTVChannel', backref='site', lazy='dynamic')
#     rooms = relationship('LiveTVRoom', backref='site', lazy='dynamic')
# 
#     code = Column(String(32), unique=True, index=True, doc='代码')
#     name = Column(String(64), doc='名称')
#     url = Column(String(1024), unique=True, index=True, doc='官网地址')
#     image = Column(String(1024), doc='图片')
#     show_seq = Column(Integer, index=True, doc='排序')
#     description = Column(String(512), doc='站点描述')
#     valid = Column(Boolean, default=True, doc='是否有效')
# 
# class LiveTVRoom(Base):
#     """ 房间 """
#     __tablename__ = 'livetv_room'
#     id = Column(Integer, primary_key=True)
#     channel_id = Column(Integer, ForeignKey('livetv_channel.id'), index=True)
#     site_id = Column(Integer, ForeignKey('livetv_site.id'), index=True)
# 
#     office_id = Column(String(32), index=True, doc='官方ID')
#     name = Column(String(258), index=True, doc='名称')
#     url = Column(String(1024), index=True, doc='访问URL')
#     image = Column(String(1024), doc='图片')
#     host = Column(String(128), doc='主持')
#     online = Column(Integer, default=0, index=True, doc='观众数')
#     opened = Column(Boolean, default=True, index=True, doc='是否正在直播')
#     crawl_date = Column(DateTime, index=True, doc='最近一次扫描时间')
#     followers = Column(Integer, default=0, index=True, doc='关注者数')
#     description = Column(TEXT, doc='描述')
#     announcement = Column(String(1024), doc='公告')
#     start_time = Column(DateTime, index=True, doc='开播时间')
# 
#     def from_item(self, item):
#         self.office_id = item['office_id']
#         self.name = item['name']
#         self.url = item['url']
#         self.image = item['image']
#         self.host = item['host']
#         self.online = item['online']
#         self.opened = True
#         self.crawl_date = datetime.utcnow()
#         self.followers = item.get('followers', self.followers)
#         self.description = item.get('description', self.description)
#         self.announcement = item.get('announcement', self.announcement)
#         self.start_time = item.get('start_time', self.start_time)
# 
# class LiveTVRoomPresent(Base):
#     """ 房间更新实时信息 """
#     __tablename__ = 'livetv_room_present'
#     id = Column(Integer, primary_key=True)
#     room_id = Column(Integer, ForeignKey('livetv_room.id'), index=True)
# 
#     crawl_date = Column(DateTime, default=datetime.utcnow, index=True, doc='扫描时间')
#     online = Column(Integer, default=0, index=True, doc='观众数')
#     crawl_date_format = Column(String(32), default=lambda: datetime.utcnow().strftime(DAILY_DATE_FORMAT), index=True,
#                                doc='扫描时间格式化')
# 
# DAILY_DATE_FORMAT = '%Y%m%d'
# 
# Path: crawler/gather/items.py
# class DailyItem(Item):
#     site_id = Field()
#     room_id = Field()
#     summary_date = Field()
#     online = Field()
#     followers = Field()
#     description = Field()
#     announcement = Field()
#     fallback = Field()


from scrapy import Spider, Request
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker
from datetime import datetime, timedelta
from ..models import LiveTVSite, LiveTVRoom, LiveTVRoomPresent, DAILY_DATE_FORMAT
from ..items import DailyItem
import numpy
import json
# -*- coding: utf-8 -*-




class BilibiliDailySpider(Spider):
    name = 'bilibili_daily'
    allowed_domains = ['bilibili.com']
    custom_settings = {
        'ITEM_PIPELINES': {
            'gather.pipelines.StatisticPipeline': 300
        }
    }

    def start_requests(self):
        summary_utc = datetime.utcnow() #- timedelta(days=1)
        db_engine = create_engine(self.settings.get('SQLALCHEMY_DATABASE_URI'))
        db_session = sessionmaker(bind=db_engine)()
        db_query = db_session.query(LiveTVSite.id.label('site_id'), LiveTVRoom.id.label('room_id'),
                                    LiveTVRoom.url.label('room_url'),
                                    LiveTVRoomPresent.crawl_date_format.label('summary_date'),
                                    func.array_agg(LiveTVRoomPresent.online).label('online_list')) \

# Path: crawler/gather/models.py
# class LiveTVSite(Base):
#     """ 直播站点 """
#     __tablename__ = 'livetv_site'
# 
#     id = Column(Integer, primary_key=True)
#     channels = relationship('LiveTVChannel', backref='site', lazy='dynamic')
#     rooms = relationship('LiveTVRoom', backref='site', lazy='dynamic')
# 
#     code = Column(String(32), unique=True, index=True, doc='代码')
#     name = Column(String(64), doc='名称')
#     url = Column(String(1024), unique=True, index=True, doc='官网地址')
#     image = Column(String(1024), doc='图片')
#     show_seq = Column(Integer, index=True, doc='排序')
#     description = Column(String(512), doc='站点描述')
#     valid = Column(Boolean, default=True, doc='是否有效')
# 
# class LiveTVRoom(Base):
#     """ 房间 """
#     __tablename__ = 'livetv_room'
#     id = Column(Integer, primary_key=True)
#     channel_id = Column(Integer, ForeignKey('livetv_channel.id'), index=True)
#     site_id = Column(Integer, ForeignKey('livetv_site.id'), index=True)
# 
#     office_id = Column(String(32), index=True, doc='官方ID')
#     name = Column(String(258), index=True, doc='名称')
#     url = Column(String(1024), index=True, doc='访问URL')
#     image = Column(String(1024), doc='图片')
#     host = Column(String(128), doc='主持')
#     online = Column(Integer, default=0, index=True, doc='观众数')
#     opened = Column(Boolean, default=True, index=True, doc='是否正在直播')
#     crawl_date = Column(DateTime, index=True, doc='最近一次扫描时间')
#     followers = Column(Integer, default=0, index=True, doc='关注者数')
#     description = Column(TEXT, doc='描述')
#     announcement = Column(String(1024), doc='公告')
#     start_time = Column(DateTime, index=True, doc='开播时间')
# 
#     def from_item(self, item):
#         self.office_id = item['office_id']
#         self.name = item['name']
#         self.url = item['url']
#         self.image = item['image']
#         self.host = item['host']
#         self.online = item['online']
#         self.opened = True
#         self.crawl_date = datetime.utcnow()
#         self.followers = item.get('followers', self.followers)
#         self.description = item.get('description', self.description)
#         self.announcement = item.get('announcement', self.announcement)
#         self.start_time = item.get('start_time', self.start_time)
# 
# class LiveTVRoomPresent(Base):
#     """ 房间更新实时信息 """
#     __tablename__ = 'livetv_room_present'
#     id = Column(Integer, primary_key=True)
#     room_id = Column(Integer, ForeignKey('livetv_room.id'), index=True)
# 
#     crawl_date = Column(DateTime, default=datetime.utcnow, index=True, doc='扫描时间')
#     online = Column(Integer, default=0, index=True, doc='观众数')
#     crawl_date_format = Column(String(32), default=lambda: datetime.utcnow().strftime(DAILY_DATE_FORMAT), index=True,
#                                doc='扫描时间格式化')
# 
# DAILY_DATE_FORMAT = '%Y%m%d'
# 
# Path: crawler/gather/items.py
# class DailyItem(Item):
#     site_id = Field()
#     room_id = Field()
#     summary_date = Field()
#     online = Field()
#     followers = Field()
#     description = Field()
#     announcement = Field()
#     fallback = Field()


# Path: crawler/gather/daily_spiders/bilibili.py
from scrapy import Spider, Request
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker
from datetime import datetime, timedelta
from ..models import LiveTVSite, LiveTVRoom, LiveTVRoomPresent, DAILY_DATE_FORMAT
from ..items import DailyItem
import numpy
import json

# -*- coding: utf-8 -*-




class BilibiliDailySpider(Spider):
    name = 'bilibili_daily'
    allowed_domains = ['bilibili.com']
    custom_settings = {
        'ITEM_PIPELINES': {
            'gather.pipelines.StatisticPipeline': 300
        }
    }

    def start_requests(self):
        summary_utc = datetime.utcnow() #- timedelta(days=1)
        db_engine = create_engine(self.settings.get('SQLALCHEMY_DATABASE_URI'))
        db_session = sessionmaker(bind=db_engine)()
        db_query = db_session.query(LiveTVSite.id.label('site_id'), LiveTVRoom.id.label('room_id'),
                                    LiveTVRoom.url.label('room_url'),
                                    LiveTVRoomPresent.crawl_date_format.label('summary_date'),
                                    func.array_agg(LiveTVRoomPresent.online).label('online_list')) \

            .join(LiveTVRoom, LiveTVRoomPresent) \