Main.java

import java.sql.*;
import java.util.*;

public class Main {

    static String url = "jdbc:postgresql://localhost:5432/finance_manager";
    static String user = "finance_user";
    static String password = "1234";

    public static void main(String[] args) {

        Scanner scanner = new Scanner(System.in);

        while (true) {
            System.out.println("\nMenu:");
            System.out.println("1. Show transactions");
            System.out.println("2. Add transaction");
            System.out.println("3. Delete transaction");
            System.out.println("4. Update transaction");
            System.out.println("5. Filter by date");
            System.out.println("6. Show analysis");
            System.out.println("7. Show smart analysis");
            System.out.println("8. Exit");

            int choice = scanner.nextInt();

            switch (choice) {
                case 1 -> showTransactions();
                case 2 -> addTransaction(scanner);
                case 3 -> deleteTransaction(scanner);
                case 4 -> updateTransaction(scanner);
                case 5 -> filterByDate(scanner);
                case 6 -> showAnalysis();
                case 7 -> showSmartAnalysis();
                case 8 -> {
                    System.out.println("Exit");
                    return;
                }
                default -> System.out.println("Invalid option");
            }
        }
    }

    static void showTransactions() {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = """
                SELECT t.id, c.name, t.amount, t.date, t.description
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                ORDER BY t.date
            """;

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                System.out.println(
                        rs.getInt(1) + " | " +
                        rs.getString(2) + " | " +
                        rs.getDouble(3) + " | " +
                        rs.getDate(4) + " | " +
                        rs.getString(5)
                );
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static void addTransaction(Scanner scanner) {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            Map<Integer, String> categories = new HashMap<>();

            String catSql = "SELECT id, name FROM categories";
            Statement catStmt = conn.createStatement();
            ResultSet crs = catStmt.executeQuery(catSql);

            System.out.println("Categories:");
            while (crs.next()) {
                int id = crs.getInt("id");
                String name = crs.getString("name");
                categories.put(id, name);
                System.out.println(id + " - " + name);
            }

            System.out.print("Category ID: ");
            int category = scanner.nextInt();

            if (!categories.containsKey(category)) {
                System.out.println("Invalid category ID");
                return;
            }

            System.out.print("Amount: ");
            double amount = scanner.nextDouble();

            scanner.nextLine();

            System.out.print("Description: ");
            String desc = scanner.nextLine();

            String sql = "INSERT INTO transactions (user_id, category_id, amount, date, description) VALUES (1, ?, ?, CURRENT_DATE, ?)";

            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, category);
            pstmt.setDouble(2, amount);
            pstmt.setString(3, desc);

            pstmt.executeUpdate();

            System.out.println("Added");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static void deleteTransaction(Scanner scanner) {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sqlShow = """
                SELECT t.id, c.name, t.amount, t.date
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                ORDER BY t.id
            """;

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sqlShow);

            System.out.println("Transactions:");
            while (rs.next()) {
                System.out.println(
                        rs.getInt("id") + " | " +
                        rs.getString("name") + " | " +
                        rs.getDouble("amount") + " | " +
                        rs.getDate("date")
                );
            }

            System.out.print("Enter transaction ID to delete: ");
            int id = scanner.nextInt();

            String checkSql = "SELECT COUNT(*) FROM transactions WHERE id = ?";
            PreparedStatement checkStmt = conn.prepareStatement(checkSql);
            checkStmt.setInt(1, id);
            ResultSet crs = checkStmt.executeQuery();

            crs.next();
            if (crs.getInt(1) == 0) {
                System.out.println("Transaction not found");
                return;
            }

            String deleteSql = "DELETE FROM transactions WHERE id = ?";
            PreparedStatement delStmt = conn.prepareStatement(deleteSql);
            delStmt.setInt(1, id);

            delStmt.executeUpdate();

            System.out.println("Deleted");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static void updateTransaction(Scanner scanner) {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sqlShow = """
                SELECT t.id, c.name, t.amount, t.date, t.description
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                ORDER BY t.id
            """;

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sqlShow);

            System.out.println("Transactions:");
            while (rs.next()) {
                System.out.println(
                        rs.getInt("id") + " | " +
                        rs.getString("name") + " | " +
                        rs.getDouble("amount") + " | " +
                        rs.getDate("date") + " | " +
                        rs.getString("description")
                );
            }

            System.out.print("Enter transaction ID to update: ");
            int id = scanner.nextInt();
            scanner.nextLine();

            String checkSql = "SELECT COUNT(*) FROM transactions WHERE id = ?";
            PreparedStatement checkStmt = conn.prepareStatement(checkSql);
            checkStmt.setInt(1, id);
            ResultSet crs = checkStmt.executeQuery();

            crs.next();
            if (crs.getInt(1) == 0) {
                System.out.println("Transaction not found");
                return;
            }

            System.out.print("New amount: ");
            double amount = scanner.nextDouble();
            scanner.nextLine();

            System.out.print("New description: ");
            String desc = scanner.nextLine();

            String updateSql = "UPDATE transactions SET amount = ?, description = ? WHERE id = ?";
            PreparedStatement upStmt = conn.prepareStatement(updateSql);

            upStmt.setDouble(1, amount);
            upStmt.setString(2, desc);
            upStmt.setInt(3, id);

            upStmt.executeUpdate();

            System.out.println("Updated");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static void filterByDate(Scanner scanner) {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            scanner.nextLine();

            System.out.print("Enter start date (YYYY-MM-DD): ");
            String start = scanner.nextLine();

            System.out.print("Enter end date (YYYY-MM-DD): ");
            String end = scanner.nextLine();

            String sql = """
                SELECT t.id, c.name, t.amount, t.date, t.description
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                WHERE t.date BETWEEN ? AND ?
                ORDER BY t.date
            """;

            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, java.sql.Date.valueOf(start));
            pstmt.setDate(2, java.sql.Date.valueOf(end));

            ResultSet rs = pstmt.executeQuery();

            System.out.println("\nFiltered transactions:");
            while (rs.next()) {
                System.out.println(
                        rs.getInt("id") + " | " +
                        rs.getString("name") + " | " +
                        rs.getDouble("amount") + " | " +
                        rs.getDate("date") + " | " +
                        rs.getString("description")
                );
            }

        } catch (Exception e) {
            System.out.println("Invalid date format");
        }
    }

    static void showAnalysis() {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = """
                SELECT c.name, SUM(t.amount)
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                WHERE c.type = 'expense'
                GROUP BY c.name
            """;

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                System.out.println(rs.getString(1) + " -> " + rs.getDouble(2));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static void showSmartAnalysis() {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = """
                SELECT c.name AS category, SUM(t.amount) AS total
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                WHERE t.user_id = 1 AND c.type = 'expense'
                GROUP BY c.name
            """;

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            Map<String, Double> data = new HashMap<>();
            double totalExpenses = 0;

            while (rs.next()) {
                double amount = rs.getDouble("total");
                totalExpenses += amount;
                data.put(rs.getString("category"), amount);
            }

            System.out.println("\nExpenses:");
            for (var e : data.entrySet()) {
                System.out.println(e.getKey() + " -> " + e.getValue());
            }

            System.out.println("\nRecommendations:");
            for (var e : data.entrySet()) {
                double percent = (e.getValue() / totalExpenses) * 100;
                if (percent > 40) {
                    System.out.println("High spending on " + e.getKey());
                }
            }

            String budgetSql = """
                SELECT c.name, b.limit_amount
                FROM budgets b
                JOIN categories c ON b.category_id = c.id
                WHERE b.user_id = 1
            """;

            Statement bstmt = conn.createStatement();
            ResultSet brs = bstmt.executeQuery(budgetSql);

            System.out.println("\nBudget:");
            while (brs.next()) {
                String cat = brs.getString(1);
                double limit = brs.getDouble(2);
                double spent = data.getOrDefault(cat, 0.0);

                if (spent > limit) {
                    System.out.println("Over budget: " + cat);
                } else {
                    System.out.println(cat + " OK");
                }
            }

            String forecastSql = """
                SELECT date, SUM(amount) as daily_total
                FROM transactions t
                JOIN categories c ON t.category_id = c.id
                WHERE t.user_id = 1 AND c.type = 'expense'
                GROUP BY date
            """;

            Statement fstmt = conn.createStatement();
            ResultSet frs = fstmt.executeQuery(forecastSql);

            double total = 0;
            int days = 0;

            while (frs.next()) {
                total += frs.getDouble("daily_total");
                days++;
            }

            if (days > 0) {
                double avgPerDay = total / days;
                double forecast = avgPerDay * 30;

                System.out.println("\nForecast:");
                System.out.println("Average per day: " + avgPerDay);
                System.out.println("Estimated monthly expenses: " + forecast);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}