"""Lambda SQL queries."""

# Snapshot logic. The sum has a ceiling(amount) due to decimal precision being
# lost in the final snapshot amount column which has no decimal. In some cases
# the snapshot amount sum is lower than the sum of client_amount.amount if the
# ceiling() is not applied.
INSERT_NEW_SNAPSHOT = """
    INSERT INTO client_amount_snapshot
        (upc, timestamp, transaction_type_id, amount)
    SELECT
        upc, '{timestamp}', transaction_type_id, sum(ceiling(amount))
    FROM
        client_amount
    GROUP BY
        upc, transaction_type_id"""


GET_UPCS = """
    SELECT DISTINCT
        upc
    FROM
        client_amount_snapshot"""


GET_RECENT_UPC_AMOUNTS = """
    SELECT DISTINCT
        upc,
        (
            SELECT
                sum(amount)
            FROM
                client_amount_snapshot
            WHERE
                upc = %(upc)s
                AND
                timestamp = (
                    SELECT DISTINCT
                        timestamp
                    FROM
                        client_amount_snapshot
                    WHERE
                        upc = %(upc)s
                    ORDER BY
                        timestamp DESC
                    LIMIT
                        1
                    OFFSET
                        0
                )
        ) as amount_latest,
        (
            SELECT
                sum(amount)
            FROM
                client_amount_snapshot
            WHERE
                upc = %(upc)s
                AND
                timestamp = (
                    SELECT DISTINCT
                        timestamp
                    FROM
                        client_amount_snapshot
                    WHERE
                        upc = %(upc)s
                    ORDER BY
                        timestamp DESC
                    LIMIT
                        1
                    OFFSET
                        1
                )
        ) as amount_previous
    FROM
        client_amount_snapshot
    WHERE
        upc = %(upc)s"""