def fill_id(conn, user_id, email_id):
    assert user_id is not None or email_id is not None

    if email_id is None:
        email_id = conn.execute(
            sql('select id from email where user_id = :user_id order by ctime limit 1'),
            user_id=user_id
        ).fetchone()
        if email_id:
            email_id = email_id[0]

    if user_id is None:
        user_id = conn.execute(
            sql('select user_id from email where id = :email_id'),
            email_id=email_id
        ).fetchone()
        if user_id:
            user_id = user_id[0]

    if user_id is None:
        raise UserNotExistError()
    if email_id is None:
        raise EmailNotExistError()

    return user_id, email_id

def set_next_sync_time(conn, id, time):
    conn.execute(sql(
        '''
        update query set next_sync_time = :time
        where id = :id
        '''
    ), id=id, time=time)

def get_users(conn, offset=None, limit=None):
    result = conn.execute(sql('\n'.join([
        'select * from "user" order by id',
        '' if offset is None else 'offset :offset',
        '' if limit is None else 'limit :limit'
    ])), **dict(offset=offset, limit=limit))
    return [bunchr(**row) for row in result.fetchall()]

def get_query_bi_kind_and_text(conn, kind, text):
    ret = conn.execute(
        sql('select * from query where kind = :kind and text = :text'),
        kind=kind,
        text=text
    ).fetchone()
    return None if ret is None else mq(**ret)

def set_user_field_bi_id(conn, id, field, value):
    assert_in(field, ('name', 'email', 'openid', 'maxwatch'))
    conn.execute(
        sql('update "user" set %s = :value where id = :id' % field),
        id=id,
        value=value
    )

def is_query_active_bi_id(conn, id):
    return conn.execute(sql(
        '''
        select 1 from watch as w0
        where w0.query_id = :id
        '''
    ), id=id).fetchone() is not None

def add_query(conn, kind, text, result={}):
    ret = conn.execute(sql('''
        insert into query (kind, text, result)
        values (:kind, :text, :result)
        returning *
    '''), kind=kind, text=text, result=Json(result)).fetchone()
    return ret[0]

def rename_watch(conn, user_id, query_id, name):
    conn.execute(sql(
        '''
        update watch set name = :name
        where user_id = :user_id and query_id = :query_id
        '''
    ), user_id=user_id, query_id=query_id, name=name)

def get_watches_bi_user_id(conn, user_id, offset=None, limit=None):
    result = conn.execute(sql('\n'.join([
        '''
        select
            w0.user_id,
            w0.query_id,
            w0.name,
            w0.ctime,
            q0.kind query_kind,
            q0.text query_text,
            q0.mtime query_mtime,
            w0.email_id,
            e0.text email_text,
            e0.label email_label,
            e0.activated email_activated
        from watch w0
            inner join query q0 on w0.query_id = q0.id
            inner join email e0 on w0.email_id = e0.id
        where w0.user_id = :user_id
        order by w0.ctime desc
        ''',
        '' if offset is None else 'offset :offset',
        '' if limit is None else 'limit :limit'
    ])), user_id=user_id, offset=offset, limit=limit)
    return [bunchr(**row) for row in result.fetchall()]

def count_recent_notice_bi_user_id(conn, user_id, interval):
    return conn.execute(sql(
        '''
        select count(1) from notice
        where user_id = :user_id and ctime >= :begin
        '''
    ), user_id=user_id, begin=datetime.utcnow() - interval).fetchone()[0]

def update_email_bi_id(conn, id, email, label):
    conn.execute(
        sql('update email set text = :text, label = :label where id = :id'),
        id=id,
        text=email,
        label=label
    )

def set_next_sync_time_bi_kind_and_text(conn, kind, text, time):
    conn.execute(sql(
        '''
        update query set next_sync_time = :time
        where kind = :kind and text = :text
        '''
    ), kind=kind, text=text, time=time)

def get_user_detail_bi_id(conn, id):
    return enrich(conn, conn.execute(sql(
        '''
        select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count
        from "user" as u0
        where u0.id = :id
        '''
    ), id=id).fetchone())

def get_email_watch_states(conn, user_id, query_id):
    return [bunchr(**row) for row in conn.execute(sql(
        '''
        select e0.*, (select count(1) from watch as w0 where w0.email_id = e0.id and w0.query_id = :query_id) watching
        from email e0
        where e0.user_id = :user_id
        order by e0.ctime
        '''
    ), user_id=user_id, query_id=query_id).fetchall()]

def get_active_query_count(conn):
    return conn.execute(sql(
        '''
        select count(1) from query as q0
        where exists (
            select 1 from watch as w0
            where w0.query_id = q0.id
        )
        '''
    )).fetchone()[0]

def add_email_bi_user_id(conn, id, email, label):
    # http://stackoverflow.com/a/14485817/238472
    if not email or parseaddr(email) == ('', ''):
        raise InvalidEmailError(email)
    return conn.execute(sql(
        '''
        insert into email (text, label, user_id)
        values (:text, :label, :user_id)
        returning *
        '''
    ), text=email, label=label, user_id=id).fetchone()[0]

def get_users_detail(conn, offset=None, limit=None):
    result = conn.execute(sql('\n'.join([
        '''
        select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count
        from "user" as u0
        order by u0.id
        ''',
        '' if offset is None else 'offset :offset',
        '' if limit is None else 'limit :limit'
    ])), **dict(offset=offset, limit=limit))
    return [bunchr(**row) for row in result.fetchall()]

def unwatch(conn, query_id, user_id=None, email_id=None):
    user_id, email_id = fill_id(conn, user_id, email_id)
    if user_id is None or email_id is None:
        return

    conn.execute(sql(
        '''
        delete from watch
        where email_id = :email_id and query_id = :query_id
        '''
    ), email_id=email_id, query_id=query_id)

def rename_watch(conn, query_id, name, user_id=None, email_id=None):
    user_id, email_id = fill_id(conn, user_id, email_id)
    if user_id is None or email_id is None:
        return

    conn.execute(sql(
        '''
        update watch set name = :name
        where email_id = :email_id and query_id = :query_id
        '''
    ), email_id=email_id, query_id=query_id, name=name)

def get_queries(conn, offset=None, limit=None, order_by=None, desc=False):
    ignore_none(check_order_field)(order_by)
    result = conn.execute(sql('\n'.join([
        '''
        select * from query as q0
        ''',
        '' if order_by is None else 'order by q0.%s' % order_by,
        '' if order_by is None or not desc else 'desc',
        '' if offset is None else 'offset :offset',
        '' if limit is None else 'limit :limit'
    ])), **dict(offset=offset, limit=limit))
    return [mq(**row) for row in result.fetchall()]