from pyramid.view import view_config
from sqlalchemy import func, distinct, over, desc
import transaction
from brewman.models import DBSession

from brewman.models.voucher import Attendance


@view_config(request_method='POST', route_name='api_db_integrity', renderer='json', permission='Authenticated')
def post_check_db(request):
    info = {}

    duplicate_attendances = get_duplicate_attendances()
    if duplicate_attendances > 0:
        fix_duplicate_attendances(duplicate_attendances)
        info['Duplicate Attendances Fixed'] = duplicate_attendances
    transaction.commit()
    return info


def get_duplicate_attendances():
    sub_query = DBSession.query(
        over(distinct(func.first_value(Attendance.id)), partition_by=[Attendance.employee_id, Attendance.date])
    ).filter(Attendance.is_valid == True).subquery()
    query = DBSession.query(func.count(Attendance.id)). \
        filter(~Attendance.id.in_(sub_query)). \
        filter(Attendance.is_valid == True)
    return query.scalar()


def fix_duplicate_attendances():
    sub = DBSession.query(over(distinct(func.first_value(Attendance.id)),
                               partition_by=[Attendance.employee_id, Attendance.date],
                               order_by=desc(Attendance.creation_date))).filter(Attendance.is_valid == True).subquery()
    DBSession.query(Attendance).filter(~Attendance.id.in_(sub)).filter(Attendance.is_valid == True).delete(False)
