/**
 * This module provides helper logic for applying filters when searching over
 * transactions.
 */
module transaction.search_filters;

import handy_http_primitives;
import std.algorithm;
import std.conv;
import std.array;
import std.range;
import std.string;
import std.uri;
import std.uni;

import util.sqlite;
import transaction.data;

/**
 * Applies a set of filters to a query builder for searching over transactions.
 * Params:
 *   qb = The query builder to add WHERE clauses and argument bindings to.
 *   searchParams = The set of search parameters provided by the user.
 *   categoryRepo = Repository for fetching category data, which might be
 *     needed if the user is filtering by a parent category.
 */
void applyFilters(
    ref QueryBuilder qb,
    in StringMultiValueMap searchParams,
    TransactionCategoryRepository categoryRepo
) {
    applyPropertyInFilter!string(qb, searchParams, "tags.tag", "tag");
    applyPropertyInFilter!ulong(qb, searchParams, "vendor.id", "vendor");
    applyPropertyInFilter!string(qb, searchParams, "txn.currency", "currency");
    applyPropertyInFilter!ulong(qb, searchParams, "account_credit.id", "credited-account");
    applyPropertyInFilter!ulong(qb, searchParams, "account_debit.id", "debited-account");

    // Separate filter that combines both credit and debit accounts.
    if (searchParams.contains("account")) {
        ulong[] accountIds = searchParams.getAllValuesAs!ulong("account");
        string inStr = "(" ~ "?".repeat(accountIds.length).join(",") ~ ")";
        qb.where("(account_credit.id IN " ~ inStr ~ " OR account_debit.id IN " ~ inStr ~ ")");
        qb.withArgBinding((ref stmt, ref idx) {
            foreach (value; accountIds) {
                stmt.bind(idx++, value);
            }
            // Again for the second IN clause.
            foreach (value; accountIds) {
                stmt.bind(idx++, value);
            }
        });
    }

    // Separate filter that handles the hierarchical category relationship, so
    // if a parent category is filtered, all children are also included.
    if (searchParams.contains("category")) {
        ulong[] categoryIds = searchParams.getAllValuesAs!ulong("category");
        auto app = appender!(ulong[]);
        foreach (id; categoryIds) {
            app ~= getAllPossibleCategoryIds(id, categoryRepo);
        }
        ulong[] allPossibleIds = app[];
        if (allPossibleIds.length > 0) {
            qb.where("(" ~ "txn.category_id = ?".repeat(allPossibleIds.length).join(" OR ") ~ ")");
            qb.withArgBinding((ref stmt, ref idx) {
                foreach (value; allPossibleIds) {
                    stmt.bind(idx++, value);
                }
            });
        }
    }

    if (searchParams.contains("min-amount")) {
        ulong[] values = searchParams.getAllValuesAs!ulong("min-amount");
        if (values.length > 0) {
            ulong minAmount = values[0];
            qb.where("txn.amount >= ?");
            qb.withArgBinding((ref stmt, ref idx) {
                stmt.bind(idx++, minAmount);
            });
        }
    }

    if (searchParams.contains("max-amount")) {
        ulong[] values = searchParams.getAllValuesAs!ulong("max-amount");
        if (values.length > 0) {
            ulong minAmount = values[0];
            qb.where("txn.amount <= ?");
            qb.withArgBinding((ref stmt, ref idx) {
                stmt.bind(idx++, minAmount);
            });
        }
    }
    
    // Boolean filter for internal transfer.
    if (searchParams.contains("internal-transfer")) {
        string value = searchParams.getFirst("internal-transfer").orElseThrow()
            .strip().toUpper();
        Optional!bool internalTransferFilter = Optional!bool.empty();
        if (value == "Y" || value == "YES" || value == "TRUE" || value == "1") {
            internalTransferFilter = Optional!bool.of(true);
        } else if (value == "N" || value == "NO" || value == "FALSE" || value == "0") {
            internalTransferFilter = Optional!bool.of(false);
        }
        if (!internalTransferFilter.isNull) {
            qb.where("txn.internal_transfer = ?");
            qb.withArgBinding((ref stmt, ref idx) {
                stmt.bind(idx++, internalTransferFilter.value);
            });
        }
    }

    // Textual search query:
    if (searchParams.contains("q")) {
        string searchQuery = searchParams.getFirst("q").orElseThrow();
        string likeStr = "%" ~ toUpper(strip(searchQuery)) ~ "%";
        const string[] conditions = [
            "UPPER(txn.description) LIKE ?",
            "UPPER(vendor.name) LIKE ?",
            "UPPER(category.name) LIKE ?",
            "UPPER(account_credit.name) LIKE ?",
            "UPPER(account_debit.name) LIKE ?",
            "UPPER(tags.tag) LIKE ?"
        ];
        qb.where("(" ~ conditions.join(" OR ") ~ ")");
        qb.withArgBinding((ref stmt, ref idx) {
            for (int i = 0; i < conditions.length; i++) {
                stmt.bind(idx++, likeStr);
            }
        });
    }
}

/**
 * Helper function to extract query parameters into a multi-value map to be
 * used more generally for other functions that require this format for search
 * parameters.
 * Params:
 *   request = The HTTP request to extract parameters from.
 * Returns: A string-string multi-value map.
 */
StringMultiValueMap extractSearchParams(in ServerHttpRequest request) {
    auto searchParamsBuilder = StringMultiValueMap.Builder();
    foreach (queryParam; request.queryParams) {
        foreach (value; queryParam.values) {
            searchParamsBuilder.add(queryParam.key, value);
        }
    }
    return searchParamsBuilder.build();
}

private T[] getAllValuesAs(T)(in StringMultiValueMap m, string key) {
    return m.getAll(key).map!(v => v.to!T).array;
}

private void applyPropertyInFilter(T)(
    ref QueryBuilder qb,
    in StringMultiValueMap searchParams,
    string property,
    string key
) {
    if (searchParams.contains(key)) {
        T[] values = searchParams.getAllValuesAs!T(key);
        qb.where(property ~ " IN (" ~ "?".repeat(values.length).join(",") ~ ")");
        qb.withArgBinding((ref stmt, ref idx) {
            foreach (value; values) {
                stmt.bind(idx++, value);
            }
        });
        return;
    }
}

private ulong[] getAllPossibleCategoryIds(ulong parentId, TransactionCategoryRepository categoryRepo) {
    auto app = appender!(ulong[]);
    app ~= parentId;
    foreach (child; categoryRepo.findAllByParentId(Optional!ulong.of(parentId))) {
        app ~= child.id;
        app ~= getAllPossibleCategoryIds(child.id, categoryRepo);
    }
    return app[];
}
