sql_queries_and_updates.txt

	increaseHighestTransactionID(user_id):
UPDATE User_Table
SET highest_transaction_id = highest_transaction_id + 1
WHERE user_id = ?;

	getHighestTransactionID(user_id):
SELECT highest_transaction_id
FROM User_Table
WHERE user_id = ?;

	createTransaction(user_id, transaction_id, date, amount, description, external_iban, type):
INSERT INTO Transaction_Table (user_id, transaction_id, date, amount, description, external_iban, type)
VALUES (?, ?, ?, ?, ?, ?, ?);

	getTransaction(user_id, transaction_id):
SELECT transaction_id, date, amount, description, external_iban, type
FROM Transaction_Table
WHERE user_id = ?
AND transaction_id = ?;

	updateTransactionDate(date, user_id, transaction_id):
UPDATE Transaction_Table
SET date = ?
WHERE user_id = ?
AND transaction_id = ?;

	updateTransactionAmount(amount, user_id, transaction_id):
UPDATE Transaction_Table
SET amount = ?
WHERE user_id = ?
AND transaction_id = ?;

	updateTransactionDescription(description, user_id, transaction_id):
UPDATE Transaction_Table
SET description = ?
WHERE user_id = ?
AND transaction_id = ?;

	updateTransactionExternalIBAN(external_iban, user_id, transaction_id):
UPDATE Transaction_Table
SET external_iban = ?
WHERE user_id = ?
AND transaction_id = ?;

	updateTransactionType(type, user_id, transaction_id):
UPDATE Transaction_Table
SET type = ?
WHERE user_id = ?
AND transaction_id = ?;

	deleteTransaction(user_id, transaction_id):
DELETE FROM Transaction_Table
WHERE user_id = ?
AND transaction_id = ?;

	getTransactions(user_id, limit, offset):
SELECT transaction_id, date, amount, description, external_iban, type
FROM Transaction_Table
WHERE user_id = ?
LIMIT ?
OFFSET ?;

	getTransactionsByCategory(user_id, category, limit, offset):
SELECT t.transaction_id, t.date, t.amount, t.description, t.external_iban, t.type
FROM Transaction_Table t, Category_Table c, Transaction_Category tc
WHERE t.transaction_id = tc.transaction_id
AND tc.category_id = c.category_id
AND t.user_id = tc.user_id
AND tc.user_id = c.user_id
AND t.user_id = ?
AND c.name = ?
LIMIT ?
OFFSET ?;

	increaseHighestCategoryID(user_id):
UPDATE User_Table
SET highest_category_id = highest_category_id + 1
WHERE user_id = ?;

	getHighestCategoryID(user_id):
SELECT highest_category_id
FROM User_Table
WHERE user_id = ?;

	createCategory(user_id, category_id, name):
INSERT INTO Category_Table (user_id, category_id, name)
VALUES (?, ?, ?);

	getCategory(user_id, category_id):
SELECT category_id, name
FROM Category_Table
WHERE user_id = ?
AND category_id = ?;

	updateCategoryName(name, user_id, category_id):
UPDATE Category_Table
SET name = ?
WHERE user_id = ?
AND category_id = ?;

	deleteCategory(user_id, category_id):
DELETE FROM Category_Table
WHERE user_id = ?
AND category_id = ?;

	getCategories(user_id, limit, offset):
SELECT category_id, name
FROM Category_Table
WHERE user_id = ?
LIMIT ?
OFFSET ?;

	increaseHighestCategoryRuleID(user_id):
UPDATE User_Table
SET highest_category_rule_id = highest_category_rule_id + 1
WHERE user_id = ?;

	getHighestCategoryRuleID(user_id):
SELECT highest_category_rule_id
FROM User_Table
WHERE user_id = ?;

	createCategoryRule(user_id, category_rule_id, description, external_iban, type, category_id, apply_on_history):
INSERT INTO Category_Rule (user_id, category_rule_id, description, external_iban, type, category_id, apply_on_history)
VALUES (?, ?, ?, ?, ?, ?, ?);

	getCategoryRule(user_id, category_rule_id):
SELECT category_rule_id, description, external_iban, type, category_id, apply_on_history
FROM Category_Rule
WHERE user_id = ?
AND category_rule_id = ?;

	updateCategoryRule(description, external_iban, type, category_id, user_id, category_rule_id):
UPDATE Category_Rule
SET description = ?, external_iban = ?, type = ?, category_id = ?
WHERE user_id = ?
AND category_rule_id = ?;

	deleteCategoryRule(user_id, category_rule_id):
DELETE FROM Category_Rule
WHERE user_id = ?
AND category_rule_id = ?;

	getCategoryRules(user_id):
SELECT category_rule_id, description, external_iban, type, category_id, apply_on_history
FROM Category_Rule
WHERE user_id = ?;

    getMatchingTransactionIDs(user_id, description, external_iban, type):
SELECT transaction_id
FROM Transaction_Table
WHERE user_id = ?
AND description LIKE ?
AND external_iban LIKE ?
AND type LIKE ?;

    getTransactionsAscending(user_id):
SELECT transaction_id, date, amount, description, external_iban, type
FROM Transaction_Table
WHERE user_id = ?
ORDER BY date ASC;

    getCurrentDate(user_id):
SELECT date
FROM Transaction_Table
WHERE user_id = ?
ORDER BY date DESC
LIMIT 1;

	increaseHighestSavingGoalID(user_id):
UPDATE User_Table
SET highest_saving_goal_id = highest_saving_goal_id + 1
WHERE user_id = ?;

	getHighestSavingGoalID(user_id):
SELECT highest_saving_goal_id
FROM User_Table
WHERE user_id = ?;

	createSavingGoal(user_id, saving_goal_id, creation_date, name, goal, save_per_month, min_balance_required):
INSERT INTO Saving_Goal (user_id, saving_goal_id, creation_date, name, goal, save_per_month, min_balance_required)
VALUES (?, ?, ?, ?, ?, ?, ?);

	getSavingGoal(user_id, saving_goal_id):
SELECT saving_goal_id, creation_date, deletion_date, name, goal, save_per_month, min_balance_required
FROM Saving_Goal
WHERE user_id = ?
AND saving_goal_id = ?;

	deleteSavingGoal(deletion_date, user_id, saving_goal_id):
UPDATE Saving_Goal
SET deletion_date = ?
WHERE user_id = ?
AND saving_goal_id = ?;

	getSavingGoals(user_id):
SELECT saving_goal_id, creation_date, deletion_date, name, goal, save_per_month, min_balance_required
FROM Saving_Goal
WHERE user_id = ?;

	increaseHighestPaymentRequestID(user_id):
UPDATE User_Table
SET highest_payment_request_id = highest_payment_request_id + 1
WHERE user_id = ?;

	getHighestPaymentRequestID(user_id):
SELECT highest_payment_request_id
FROM User_Table
WHERE user_id = ?;

    createPaymentRequest(user_id, payment_request_id, description, due_date, amount, number_of_requests, filled):
INSERT INTO Payment_Request (user_id, payment_request_id, description, due_date, amount, number_of_requests, filled)
VALUES (?, ?, ?, ?, ?, ?, ?);

    getPaymentRequest(user_id, payment_request_id):
SELECT payment_request_id, description, due_date, amount, number_of_requests, filled
FROM Payment_Request
WHERE user_id = ?
AND payment_request_id = ?;

    getPaymentRequests(user_id):
SELECT payment_request_id, description, due_date, amount, number_of_requests, filled
FROM Payment_Request
WHERE user_id = ?;

    setPaymentRequestFilled(user_id, payment_request_id):
UPDATE Payment_Request
SET filled = 1
WHERE user_id = ?
AND payment_request_id = ?;

    getTransactionsByPaymentRequest(user_id, payment_request_id):
SELECT t.transaction_id, t.date, t.amount, t.description, t.external_iban, t.type
FROM Payment_Request pr, Payment_Request_Transaction prt, Transaction_Table t
WHERE pr.payment_request_id = prt.payment_request_id
AND prt.transaction_id = t.transaction_id
AND pr.user_id = prt.user_id
AND prt.user_id = t.user_id
AND t.user_id = ?
AND pr.payment_request_id = ?;

    linkTransactionToPaymentRequest(user_id, transaction_id, payment_request_id):
INSERT INTO Payment_Request_Transaction (user_id, transaction_id, payment_request_id)
VALUES (?, ?, ?);

	increaseHighestUserMessageID(user_id):
UPDATE User_Table
SET highest_user_message_id = highest_user_message_id + 1
WHERE user_id = ?;

	getHighestUserMessageID(user_id):
SELECT highest_user_message_id
FROM User_Table
WHERE user_id = ?;

    getUserMessage(user_id, user_message_id):
SELECT user_message_id, message, date, read, type
FROM User_Message
WHERE user_id = ?
AND user_message_id = ?;

    createUserMessage(user_id, user_message_id, message, date, type):
INSERT INTO User_Message (user_id, user_message_id, message, date, read, type)
VALUES (?, ?, ?, ?, 0, ?);

    getUnreadUserMessages(user_id):
SELECT user_message_id, message, date, read, type
FROM User_Message
WHERE user_id = ?
AND read = 0;

    getAllUserMessages(user_id):
SELECT user_message_id, message, date, read, type
FROM User_Message
WHERE user_id = ?;

    setUserMessageRead(user_id, user_message_id):
UPDATE User_Message
SET read = 1
WHERE user_id = ?
AND user_message_id = ?;

    getHighestLifetimeBalance(user_id):
SELECT highest_lifetime_balance
FROM User_Table
WHERE user_id = ?;

    updateHighestLifetimeBalance(current_balance, current_balance, user_id):
UPDATE User_Table
SET highest_lifetime_balance = 
  CASE WHEN ? > highest_lifetime_balance 
    THEN ? 
    ELSE highest_lifetime_balance 
  END
WHERE user_id = ?;

	increaseHighestMessageRuleID(user_id):
UPDATE User_Table
SET highest_message_rule_id = highest_message_rule_id + 1
WHERE user_id = ?;

	getHighestMessageRuleID(user_id):
SELECT highest_message_rule_id
FROM User_Table
WHERE user_id = ?;

    createMessageRule(user_id, message_rule_id, category_id, type, value):
INSERT INTO Message_Rule (user_id, message_rule_id, category_id, type, value)
VALUES (?, ?, ?, ?, ?);

    getMessageRules(user_id):
SELECT message_rule_id, category_id, type, value
FROM Message_Rule
WHERE user_id = ?;

    getMessageRule(user_id, message_rule_id):
SELECT message_rule_id, category_id, type, value
FROM Message_Rule
WHERE user_id = ?
AND message_rule_id = ?;

	linkTransactionToCategory(user_id, transaction_id, category_id):
INSERT INTO Transaction_Category (user_id, transaction_id, category_id)
VALUES (?, ?, ?);

	unlinkTransactionFromCategory(user_id, transaction_id, category_id):
DELETE FROM Transaction_Category
WHERE user_id = ?
AND transaction_id = ?
AND category_id = ?;

	unlinkTransactionFromAllCategories(user_id, transaction_id):
DELETE FROM Transaction_Category
WHERE user_id = ?
AND transaction_id = ?;

	unlinkCategoryFromAllTransactions(user_id, category_id):
DELETE FROM Transaction_Category
WHERE user_id = ?
AND category_id = ?;

	getCategoryIDByTransactionID(user_id, transaction_id):
SELECT tc.category_id
FROM Transaction_Table t, Transaction_Category tc
WHERE t.transaction_id = tc.transaction_id
AND t.user_id = tc.user_id
AND t.user_id = ?
AND t.transaction_id = ?;

	createNewUser(session_id):
INSERT INTO User_Table (session_id, highest_lifetime_balance, highest_transaction_id, highest_category_id, highest_category_rule_id, highest_saving_goal_id, highest_payment_request_id, highest_user_message_id, highest_message_rule_id)
VALUES (?, 0, 0, 0, 0, 0, 0, 0, 0);

	getUserID(session_id):
SELECT user_id
FROM User_Table
WHERE session_id = ?;