def convert_imdb(mysql):
    """Converts Newznab imdb table into Pynab format."""

    print('Converting imdb...')

    with db_session() as db:
        from_query = """
            SELECT imdbID, title, year, language, genre
            FROM movieinfo
            WHERE imdbID > 0
            ORDER BY imdbID
        """

        cursor = mysql.cursor()
        cursor.execute(from_query)

        for r in cursor.fetchall():
            if not r[2]:
                # Blank years do not work, skip them
                continue
            movie = db.query(Movie).filter(Movie.id==str(r[0])).first()
            if not movie:
                movie = Movie(
                    id=r[0],
                    name=r[1],
                    year=r[2],
                    genre=r[4]
                )
                db.add(movie)

def local_postprocess():
    with db_session() as db:
        # noinspection PyComparisonWithNone,PyComparisonWithNone
        db.query(MetaBlack).filter(MetaBlack.status=='IMPOSSIBLE').filter((MetaBlack.movie!=None)|(MetaBlack.tvshow!=None)).delete(synchronize_session=False)

    pynab.ids.process('movie', online=False)
    pynab.ids.process('tv', online=False)

def convert_users(mysql):
    """Converts Newznab users table into Pynab format. More or less
    of this may be necessary depending on what people want. I'm pretty
    much just after bare API access, so we only really need rsstoken."""

    print('Converting users...')

    with db_session() as db:

        from_query = """
            SELECT username, email, password, rsstoken, userseed, grabs
            FROM users
            ORDER BY id;
        """

        cursor = mysql.cursor()
        cursor.execute(from_query)

        db.query(User).delete()

        for r in cursor.fetchall():
            u = User(
                email=r[1],
                api_key=r[3],
                grabs=r[5]
            )
            db.add(u)

    def test_load_and_categorise(self):
        from pynab.db import db_session, Release, Group, windowed_query
        from pickle import load

        with open('release_categoriser.pkl', 'rb') as cat_file:
            categoriser = load(cat_file)

        with db_session() as db:
            errors = []
            i = 0
            query = db.query(Release).join(Group)
            count = query.count()
            for result in windowed_query(query, Release.id, 500):
                features = extract_features(result.name)
                features['group'] = result.group.name
                features['name'] = result.name

                guess = categoriser.classify(features)
                if guess[:2] != str(result.category_id)[:2]:
                    errors.append((result.category_id, guess, features))

                i += 1
                if i % 500 == 0:
                    print('{} - {:.3f}%'.format((i/count)*100, (1 - (len(errors) / i)) * 100))

        for tag, guess, features in errors:
            print('correct={} guess={} name={}'.format(tag, guess, features['name'].encode('utf-8')))

        print('accuracy={}'.format(1 - (len(errors)/i)))

def get_nzb(dataset=None):
    user = auth()
    if user:
        id = request.query.guid or None
        if not id:
            id = request.query.id or None

        if id:
            with db_session() as db:
                release = db.query(Release).join(NZB).join(Category).filter(Release.id == id).one()
                if release:
                    release.grabs += 1
                    user.grabs += 1
                    db.merge(release)
                    db.merge(user)
                    db.commit()

                    data = release.nzb.data
                    response.set_header('Content-type', 'application/x-nzb-compressed-gzip')
                    response.set_header('X-DNZB-Name', release.search_name)
                    response.set_header('X-DNZB-Category', release.category.name)
                    response.set_header('Content-Disposition', 'attachment; filename="{0}"'
                                        .format(release.search_name.replace(' ', '_') + '.nzb.gz')
                    )
                    return gzip.decompress(data)
                else:
                    return api_error(300)
        else:
            return api_error(200)
    else:
        return api_error(100)

def upgrade():
    with db_session() as db:
        db.query(Release).\
            filter(Release.tvshow_metablack_id == MetaBlack.id).\
            filter(MetaBlack.status=='IMPOSSIBLE').\
            update({Release.tvshow_metablack_id: None}, synchronize_session='fetch')
        db.commit()

def remove_group(group_name):
    with db_session() as db:
        deleted = db.query(Group).filter(Group.name == group_name).delete()
        if deleted:
            db.commit()
            return True
    return False

def convert_groups(mysql):
    """Converts Newznab groups table into Pynab. Only really
    copies backfill records and status."""
    # removed minsize/minfiles, since we're not really using them
    # most of the groups I index don't come up with too many stupid
    # releases, so if anyone has problem groups they can re-add it

    print('Converting groups...')

    with db_session() as db:
        from_query = """
            SELECT name, first_record, last_record, active
            FROM groups;
        """
        cursor = mysql.cursor()
        cursor.execute(from_query)

        for r in cursor.fetchall():
            g = db.query(Group).filter(Group.name==r[0]).first()
            if not g:
                g = Group(name=r[0])

            g.first = r[1]
            g.last = r[2]
            g.active = bool(r[3])

            db.add(g)

def process(limit=None, category=0):
    """Process releases for NFO parts and download them."""

    with Server() as server:
        with db_session() as db:
            # noinspection PyComparisonWithNone,PyComparisonWithNone
            query = db.query(Release).join(Group).join(NZB).filter(Release.nfo == None).filter(
                Release.nfo_metablack_id == None)
            if category:
                query = query.filter(Release.category_id == int(category))

            if limit:
                releases = query.order_by(Release.posted.desc()).limit(limit)
            else:
                releases = query.order_by(Release.posted.desc()).all()

            for release in releases:
                found = False
                nzb = pynab.nzbs.get_nzb_details(release.nzb)

                if nzb:
                    nfos = []
                    for nfo in nzb['nfos']:
                        for part in nfo['segments']:
                            if int(part['size']) > NFO_MAX_FILESIZE:
                                continue
                            nfos.append(part)

                    for nfo in nfos:
                        try:
                            article = server.get(release.group.name, [nfo['message_id'], ])
                        except Exception as e:
                            # if usenet's not accessible, don't block it forever
                            log.error('nfo: unable to get nfo: {}'.format(e))
                            continue

                        if article:
                            data = gzip.compress(article.encode('utf-8'))
                            nfo = NFO(data=data)
                            db.add(nfo)

                            release.nfo = nfo
                            release.nfo_metablack_id = None
                            db.add(release)

                            log.debug('nfo: [{}] - nfo added'.format(
                                release.search_name
                            ))
                            found = True
                            break

                    if not found:
                        log.debug('nfo: [{}] - [{}] - no nfos in release'.format(
                            release.id,
                            release.search_name
                        ))
                        mb = MetaBlack(nfo=release, status='IMPOSSIBLE')
                        db.add(mb)
                db.commit()

    def test_nzb_parse(self):
        import pynab.nzbs
        from pynab.db import NZB

        with db_session() as db:
            nzb = db.query(NZB).filter(NZB.id==1).one()
            import pprint
            pprint.pprint(pynab.nzbs.get_nzb_details(nzb))

def group_list():
    with db_session() as db:
        groups = db.query(Group).order_by(Group.name)
        group_list = []
        for group in groups:
            group_list.append(group)

        return group_list

    def test_search_releases(self):
        from sqlalchemy_searchable import search
        from pynab.db import Release

        with db_session() as db:
            q = db.query(Release)
            q = search(q, 'engaged e06')
            print(q.first().search_name)

def info(email):
    """Information about a specific email."""
    with db_session() as db:
        user = db.query(User).filter(User.email == email).first()
        if user:
            return [user.email, user.api_key, user.grabs]
        else:
            return None

def list():
    """List all users."""
    with db_session() as db:
        users = db.query(User).order_by(User.email)
        user_list = []
        for user in users:
            user_list.append([user.email, user.api_key, user.grabs])

        return user_list

def auth():
    api_key = request.query.apikey or ''

    with db_session() as db:
        user = db.query(User).filter(User.api_key == api_key).first()
        if user:
            return user
        else:
            return None

 def get_categories(self):
     if self.categories:
         return self.categories
     else:
         self.categories = {}
         with db_session() as db:
             for category in db.query(Category).all():
                 self.categories[int(category.id)] = category.name
         return self.categories

def disable_group(group_name):
    with db_session() as db:
        group = db.query(Group).filter(Group.name == group_name).first()
        if group:
            group.active = False
            db.add(group)
            db.commit()
            return True
    return False

def delete(email):
    """Deletes a user by email."""

    with db_session() as db:
        deleted = db.query(User).filter(User.email == email).delete()
        if deleted:
            db.commit()
            return True

    return False

def reset_group(group_name):
    with db_session() as db:
        group = db.query(Group).filter(Group.name == group_name).first()
        if group:
            group.first = 0
            group.last = 0
            db.add(group)
            db.commit()
            return True
    return False

def process(limit=None, category=0):
    """Processes release rarfiles to check for passwords and filecounts."""

    with Server() as server:
        with db_session() as db:
            # noinspection PyComparisonWithNone
            query = db.query(Release).join(Group).join(NZB).filter(~Release.files.any()). \
                filter(Release.passworded == 'UNKNOWN').filter(Release.rar_metablack_id == None)
            if category:
                query = query.filter(Release.category_id == int(category))

            if limit:
                releases = query.order_by(Release.posted.desc()).limit(limit)
            else:
                releases = query.order_by(Release.posted.desc()).all()

            for release in releases:
                log.debug('rar: processing {}'.format(release.search_name))
                nzb = pynab.nzbs.get_nzb_details(release.nzb)

                if nzb and nzb['rars']:
                    try:
                        passworded, info = check_release_files(server, release.group.name, nzb)
                    except Exception as e:
                        # if usenet isn't accessible, we don't want to blacklist it
                        log.error('rar: file info failed: {}'.format(e))
                        continue

                    if info:
                        log.info('rar: file info add [{}]'.format(
                            release.search_name
                        ))
                        release.passworded = passworded

                        size = 0
                        for file in info:
                            f = File(name=file['name'][:512],
                                     size=file['size'])
                            f.release = release
                            size += file['size']
                            db.add(f)

                        if size != 0:
                            release.size = size

                        release.rar_metablack_id = None
                        db.add(release)
                        db.commit()
                        continue
                log.debug('rar: [{}] - file info: no readable rars in release'.format(
                    release.search_name
                ))
                mb = MetaBlack(rar=release, status='IMPOSSIBLE')
                db.add(mb)
                db.commit()