WITH transaction_standardised AS (
    SELECT
        Date,
        (CASE WHEN Currency = "USD" THEN Amount ELSE Amount * (1 / Rate.Rate) END) AS Amount,
        Type
    FROM 
        Transaction
    LEFT JOIN
        Rate
    ON 
        Transaction.Currency = Rate.TargetCurrency
),
inflow_outflow AS (
    SELECT 
        WEEK(Date, 2) as week_number,
        CASE
            WHEN WEEKDAY(Date) <> 6 
                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))
            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))
        END as week_start_date,
        SUM(CASE WHEN Type = "Income" THEN Amount ELSE 0 END) AS inflow, 
        SUM(CASE WHEN `Type` = "Expense" THEN Amount ELSE 0 END) AS outflow,
        ROW_NUMBER() OVER (ORDER BY WEEK(Date, 2)) AS row_num
    FROM transaction_standardised
    GROUP BY 1, 2
),
rolling_inflow_outflow AS (
    SELECT
        week_number,
        week_start_date,
        SUM(inflow) OVER (ORDER BY row_num ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_inflow,
        SUM(outflow) OVER (ORDER BY row_num ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_outflow,
        row_num
    FROM inflow_outflow
)
SELECT
    week_number,
    week_start_date,
    rolling_inflow,
    rolling_outflow,
    IF(rolling_inflow = 0 OR rolling_outflow > rolling_inflow, 
        0, 
        ROUND((rolling_inflow - rolling_outflow) / rolling_inflow * 100, 2)) AS savings_percentage,
    row_num
FROM rolling_inflow_outflow

WITH transaction_standardised AS (
    SELECT
        Date,
        (CASE WHEN Currency = "USD" THEN Amount ELSE Amount * (1 / Rate.Rate) END) AS Amount,
        Type
    FROM 
        Transaction
    LEFT JOIN
        Rate
    ON 
        Transaction.Currency = Rate.TargetCurrency
),
weekly_money_in AS (
    SELECT
        WEEK(Date, 2) AS week_number_in,
        CASE
            WHEN WEEKDAY(Date) <> 6 
                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))
            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))
        END AS week_start_date_in,
        SUM(CASE WHEN Type IN ('Income', 'Cashback') THEN Amount ELSE 0 END) AS amount_in
    FROM 
        transaction_standardised
    GROUP BY 
        week_number_in, week_start_date_in
),
weekly_money_out AS (
    SELECT
        WEEK(Date, 2) AS week_number_out,
        CASE
            WHEN WEEKDAY(Date) <> 6 
                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))
            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))
        END AS week_start_date_out,
        SUM(CASE WHEN Type = 'Expense' THEN Amount ELSE 0 END) AS amount_out
    FROM 
        transaction_standardised
    GROUP BY 
        week_number_out, week_start_date_out
),
combined_result AS (
    SELECT
        combined.week_number_in,
        combined.week_start_date_in,
        combined.week_number_out,
        combined.week_start_date_out,
        ROUND(combined.amount_in, 2) AS amount_in,
        ROUND(combined.amount_out, 2) AS amount_out,
        ROUND(SUM(combined.amount_in - combined.amount_out) OVER (ORDER BY combined.week_start_date_in), 2) AS weekly_total_balance
    FROM (
        SELECT *
        FROM weekly_money_in
        LEFT JOIN weekly_money_out ON weekly_money_in.week_start_date_in = weekly_money_out.week_start_date_out
        UNION
        SELECT *
        FROM weekly_money_in
        RIGHT JOIN weekly_money_out ON weekly_money_in.week_start_date_in = weekly_money_out.week_start_date_out
    ) AS combined
),
percentage_savings AS (
    SELECT
        week_start_date_in,
        week_number_in,
        amount_in,
        amount_out,
        weekly_total_balance,
        ROUND(((weekly_total_balance - LAG(weekly_total_balance) OVER (ORDER BY week_start_date_in ASC)) / weekly_total_balance) * 100, 2) AS percentage_savings_per_week
    FROM combined_result
)
SELECT
    week_number_in as week_number,
    week_start_date_in as week_start_date,
    amount_in,
    amount_out,
    weekly_total_balance,
    percentage_savings_per_week
FROM percentage_savings
ORDER BY week_start_date_in ASC

function RecommendCards(five: Five, context: any, result: FiveError) : FiveError {

    const amount: number = Number(context.Amount);
    
    // Query tables
    const sqlStatement1: string = `
        SELECT CashbackKey, Percentage, Constant 
        FROM Cashback 
        WHERE 
            ? >= MinimumSpend AND Currency IN (?, "Any") AND 
            (
                (Category = ? AND MerchantName = "Any") OR
                (Category = "Any" AND MerchantName = ?)
            )`;
    const queryresults1: QueryResult = five.executeQuery(sqlStatement1, 0, 
        amount, 
        context.Currency, 
        context.Category, 
        context.MerchantName);

    const sqlStatement2: string = `
        SELECT PointKey, PointsPerUnit, Constant 
        FROM Point 
        WHERE 
            ? >= MinimumSpend AND Currency IN (?, "Any") AND 
            (
                (Category = ? AND MerchantName = "Any") OR
                (Category = "Any" AND MerchantName = ?)
            )`;;
    const queryresults2: QueryResult = five.executeQuery(sqlStatement2, 0, 
        amount, 
        context.Currency, 
        context.Category, 
        context.MerchantName);
    
    if (queryresults1.values === null && queryresults2.values === null) {
        return five.createError(queryresults2, "No cards offer cashback or points for this spend");
    }

    five.executeQuery("DELETE FROM CashbackRecommendation", 0);
    five.executeQuery("DELETE FROM PointRecommendation", 0);

    // Calculate cashback
    if (queryresults1.values !== null) {
        const insertSqlStatement: string = `
            INSERT INTO CashbackRecommendation 
            (
                CashbackRecommendationKey,
                CashbackKey,
                Category,
                MerchantName,
                Amount,
                Currency,
                CashbackAmount
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
        `

        for (let i = 0; i < queryresults1.values.length; i++) {
            const row = queryresults1.values[i];
            const percentage: number = Number(row.Percentage);
            const constant: number = Number(row.Constant);

            const cashback: number = amount * percentage / 100.0 + constant;

            five.executeQuery(insertSqlStatement, 0, 
                five.uuid(), 
                row.CashbackKey, 
                context.Category,
                context.MerchantName,
                amount, 
                context.Currency,
                cashback
            );
        }
    }
    
    // Calculate points
    if (queryresults2.values !== null) {
        const insertSqlStatement: string = `
            INSERT INTO PointRecommendation 
            (
                PointRecommendationKey,
                PointKey,
                Category,
                MerchantName,
                Amount,
                Currency,
                Point
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
        `

        for (let i = 0; i < queryresults2.values.length; i++) {
            const row = queryresults2.values[i];
            const pointsPerUnit: number = Number(row.PointsPerUnit);
            const constant: number = Number(row.Constant);

            const points: number = amount * pointsPerUnit + constant;

            five.executeQuery(insertSqlStatement, 0, 
                five.uuid(), 
                row.PointKey, 
                context.Category,
                context.MerchantName,
                amount, 
                context.Currency,
                points
            );
        }
    }
    
    return five.success(result);
}

function GetRates(five: Five, context: any, result: FiveError) : FiveError {
    const API_URL = `https://v6.exchangerate-api.com/v6/<API_KEY>/latest/USD`
    const client = five.httpClient();
    let httpResult = client.get(API_URL);

    if (httpResult.response === null) {
        return five.createError(httpResult, "Error in retrieving rates");
    }

    const rates = httpResult.response.conversion_rates;

    const insertSqlStatement: string = `
        INSERT INTO Rate
        (
            RateKey,
            BaseCurrency,
            TargetCurrency,
            Rate
        ) VALUES (?, ?, ?, ?)
    `

    Object.keys(rates).forEach(targetCurrency => {
        five.executeQuery(insertSqlStatement, 0, 
            five.uuid(), 
            "USD", 
            targetCurrency,
            rates[targetCurrency]
        );
    })

    return five.success(result, "Rates refreshed");
}