import logging
import pickle
from collections import MutableSet
from datetime import datetime

from sqlalchemy import Unicode, select, Column, Integer, DateTime, ForeignKey, or_, func
from sqlalchemy.orm import relationship
from sqlalchemy.sql.elements import and_

from flexget import db_schema
from flexget.db_schema import versioned_base
from flexget.entry import Entry
from flexget.manager import Session
from flexget.utils import json
from flexget.utils.database import entry_synonym, with_session
from flexget.utils.sqlalchemy_utils import table_schema, table_add_column

log = logging.getLogger('entry_list.db')
Base = versioned_base('entry_list', 1)


@db_schema.upgrade('entry_list')
def upgrade(ver, session):
    if None is ver:
        ver = 0
    if ver == 0:
        table = table_schema('entry_list_entries', session)
        table_add_column(table, 'json', Unicode, session)
        # Make sure we get the new schema with the added column
        table = table_schema('entry_list_entries', session)
        for row in session.execute(select([table.c.id, table.c.entry])):
            try:
                p = pickle.loads(row['entry'])
                session.execute(
                    table.update()
                    .where(table.c.id == row['id'])
                    .values(json=json.dumps(p, encode_datetime=True))
                )
            except KeyError as e:
                log.error('Unable error upgrading entry_list pickle object due to %s' % str(e))

        ver = 1
    return ver


class EntryListList(Base):
    __tablename__ = 'entry_list_lists'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode, unique=True)
    added = Column(DateTime, default=datetime.now)
    entries = relationship(
        'EntryListEntry', backref='list', cascade='all, delete, delete-orphan', lazy='dynamic'
    )

    def to_dict(self):
        return {'id': self.id, 'name': self.name, 'added_on': self.added}


class EntryListEntry(Base):
    __tablename__ = 'entry_list_entries'
    id = Column(Integer, primary_key=True)
    list_id = Column(Integer, ForeignKey(EntryListList.id), nullable=False)
    added = Column(DateTime, default=datetime.now)
    title = Column(Unicode)
    original_url = Column(Unicode)
    _json = Column('json', Unicode)
    entry = entry_synonym('_json')

    def __init__(self, entry, entry_list_id):
        self.title = entry['title']
        self.original_url = entry.get('original_url') or entry['url']
        self.entry = entry
        self.list_id = entry_list_id

    def __repr__(self):
        return '<EntryListEntry,title=%s,original_url=%s>' % (self.title, self.original_url)

    def to_dict(self):
        return {
            'id': self.id,