from django.db import connection


def get_sql(sql, params):
    """
    Execute an SQL query and return the associated cursor.
    """
    cursor = connection.cursor()
    cursor.execute(sql, params)
    return cursor


def get_sql_count(sql, params):
    """
    Execute a count on SUB-SELECT and return the count.
    """
    cursor = get_sql('''SELECT COUNT(*) FROM (%s) AS CNT''' % sql, params)
    return cursor.fetchone()[0]


def get_sql_ids(sql, params):
    """
    Retrieve a list of numerical ids.
    """
    cursor = get_sql(sql, params)
    return (row[0] for row in cursor.fetchall())


def get_complex_join(qs, sql, params):
    ids = list(get_sql_ids(sql, params))
    return qs.filter(pk__in=ids)


def get_unseen_documents_count(related_users, user):
    query = GET_UNSEEN_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
    return get_sql_count(
        query,
        (False,)
        + tuple(related_users.values_list('id', flat=True))
        + (user.pk, user.pk, False, user.pk, True),
    )


def get_documents(qs, related_users, user, outbox):
    query = GET_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
    qs = get_complex_join(
        qs,
        query,
        (outbox,) + tuple(related_users.values_list('id', flat=True)) + (user.pk, False, user.pk, True),
    )
    qs = qs.prefetch_related('to_list', 'to_user', 'mailboxes__owner')
    qs = qs.extra(select={'seen': SEEN_DOCUMENT % user.pk})
    return qs


def get_trash_documents(qs, related_users, user, outbox):
    query = GET_TRASH_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
    qs = get_complex_join(
        qs,
        query,
        (outbox,) + tuple(related_users.values_list('id', flat=True)) + (user.pk, False, user.pk, True),
    )
    qs = qs.prefetch_related('to_list', 'to_user', 'mailboxes__owner')
    return qs


GET_UNSEEN_DOCUMENTS_SQL = '''SELECT d.id
    FROM docbow_document AS d
    INNER JOIN docbow_mailbox AS mb
        ON mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
    WHERE
        NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
        AND NOT EXISTS(SELECT 1 FROM docbow_seendocument sd WHERE sd.document_id = d.id AND sd.user_id = %%s)
        AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
    GROUP BY d.id, d.date
    ORDER BY d.date
    '''

GET_DOCUMENTS_SQL = '''SELECT d.id
    FROM docbow_document AS d
    INNER JOIN docbow_mailbox AS mb ON
        mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
    WHERE
        NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
        AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
    GROUP BY d.id, d.date
    ORDER BY d.date'''


GET_TRASH_DOCUMENTS_SQL = '''SELECT d.id
    FROM docbow_document AS d
    INNER JOIN docbow_mailbox AS mb ON
        mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
    LEFT JOIN docbow_deleteddocument AS dd ON
        dd.document_id = d.id AND dd.user_id = %%s AND dd.soft_delete
    WHERE (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
    GROUP BY d.id, d.date
    ORDER BY d.date'''


SEEN_DOCUMENT = '''SELECT COUNT(*) > 0
    FROM docbow_seendocument
    WHERE docbow_seendocument.document_id = docbow_document.id
    AND docbow_seendocument.user_id = %s'''