Code

import com.google.gson.*;
import java.sql.*;
import java.util.*;
import java.net.URL;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class elephantSQL {
    public static void main(String[] args) throws Exception {

        String url = "jdbc:postgresql://isabelle.db.elephantsql.com/yczrhgub";
        String username = "yczrhgub";
        String password = "xspSCM8b4pLLGkYTW088oIgK4fbyzOtC";
        Connection con = DriverManager.getConnection(url, username, password);

        Map<Integer, List<String>> detailsMap = new HashMap<>();
        //key=pid
        TreeMap<String, List<String>> citiesMap = new TreeMap<>();
        //key=seq+&&&+pid+@@@+cityName
        Map<Integer, List<String>> itiMap = new HashMap<>();
        //key=(id*100)+day
        Map<String, List<String>> packageTypesMap = new HashMap<>();
        //key=pid+packageType
        Map<String, List<String>> amenitiesMap = new HashMap<>();
        //key=pid+packageType+amenity
        Map<Integer, List<URL>> imagesMap = new HashMap<>();
        //key=pid

        Statement statement = con.createStatement();

        String sql2 = "SELECT * FROM public.\"Itineraries\"";
        ResultSet rs2 = statement.executeQuery(sql2);

        while (rs2.next()) {
            int id = rs2.getInt("Package_pid");
            String day = rs2.getString("Day");
            String description = rs2.getString("Description");

            List<String> itinerariesList = new ArrayList<>();
            itinerariesList.add(day);
            itinerariesList.add(description);

            //creating key
            int key_iti = (id * 100) + (Integer.parseInt(day.substring(4)));

            //store details
            itiMap.put(key_iti, itinerariesList);
        }
        Map<Integer, List<String>> itinerariesMap = sortbykey(itiMap);

        String sql3 = "SELECT * FROM public.\"Package_Type\"";
        ResultSet rs3 = statement.executeQuery(sql3);

        while (rs3.next()) {
            int id = rs3.getInt("Package_pid");
            String packageType = rs3.getString("Package_Type");
            int cost = rs3.getInt("Cost");
            String descrip = rs3.getString("Description");

            List<String> packageTypeList = new ArrayList<>();
            packageTypeList.add(packageType);
            packageTypeList.add(String.valueOf(cost));
            packageTypeList.add(descrip);

            //creating key
            String key_pt = String.valueOf(id).concat(packageType);

            //store details
            packageTypesMap.put(key_pt, packageTypeList);
        }

        String sql = "SELECT * FROM public.\"Packages\"";
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            int id = rs.getInt("Package_pid");
            String name = rs.getString("Package_name");
            String des = rs.getString("Description");
            int dur = rs.getInt("Duration");
            int stars = rs.getInt("Stars");
            String vf = rs.getString("valid_from");
            String vt = rs.getString("valid_to");
            String cancellation = rs.getString("Cancellation_policy");
            String inc = rs.getString("Inclusions");
            String exc = rs.getString("Exclusions");

            //Create ArrayList
            List<String> packageDetails = new ArrayList<>();

            packageDetails.add(name);
            packageDetails.add(des);
            packageDetails.add(String.valueOf(dur));
            packageDetails.add(String.valueOf(stars));
            packageDetails.add(vf);
            packageDetails.add(vt);
            packageDetails.add(cancellation);
            packageDetails.add(inc);
            packageDetails.add(exc);
            packageDetails.add(String.valueOf(getMinCost(String.valueOf(id), packageTypesMap)));

            //store details
            detailsMap.put(id, packageDetails);
        }

        String sql4 = "SELECT * FROM public.\"Amenities\"";
        ResultSet rs4 = statement.executeQuery(sql4);

        while (rs4.next()) {
            int id = rs4.getInt("Package_pid");
            String packageType = rs4.getString("Package_Type");
            String a = rs4.getString("Amenities");

            List<String> amenitiesList = new ArrayList<>();
            amenitiesList.add(a);

            //creating key
            String c = String.valueOf(id).concat(packageType);
            String key_a = c.concat(a);

            //store values
            amenitiesMap.put(key_a, amenitiesList);
        }

        String sql5 = "SELECT * FROM public.\"Images\"";
        ResultSet rs5 = statement.executeQuery(sql5);

        while (rs5.next()) {
            int id = rs5.getInt("Package_pid");
            String image = rs5.getString("Image");

            // Convert string URL to URL object
            URL imageUrl = new URL(image);

            List<URL> imagesURL = new ArrayList<>();
            imagesURL.add(imageUrl);

            //store details
            imagesMap.put(id, imagesURL);
        }

        Scanner sc = new Scanner((System.in));

        int pid = 0;
        String city = "";
        int dur = 0;
        String date = "2024-05-01";
        int noOfPpl = 1;

        System.out.println("Enter city name:");
        city = sc.nextLine();

        System.out.println("Enter duration of tour:");
        dur = sc.nextInt();
        sc.nextLine();

        System.out.println("Enter date of departure in yyyy-mm--dd format:");
        date = sc.nextLine();

        System.out.println("Enter no of people:");
        noOfPpl = sc.nextInt();
        sc.nextLine();

        System.out.println("Enter package if from 1-8:");
        pid = sc.nextInt();

        System.out.println("Sort packages by-\n1-Cost\n2-Star rating\n3-Duration");
        int sortOption = sc.nextInt();
        sc.nextLine();

        System.out.println("Sort in:\n1-Ascending\n2-Descending");
        int sortOrder = sc.nextInt();
        sc.nextLine();

        Map<Integer, List<String>> SortedDetailsMap=new HashMap<>();
        switch (sortOption) {
            case 1:
                if(sortOrder==1 || sortOrder==2) {
                    SortedDetailsMap = sortPackagesByCost(detailsMap, sortOrder);
                }
                else {
                    System.out.println("Invalid option chosen!!!");
                }
                break;
            case 2:
                if(sortOrder==1 || sortOrder==2) {
                    SortedDetailsMap = sortPackagesByStarRatings(detailsMap, sortOrder);
                }
                else {
                    System.out.println("Invalid option chosen!!!");
                }
                break;
            case 3:
                if(sortOrder==1 || sortOrder==2) {
                    SortedDetailsMap = sortPackagesByDuration(detailsMap, sortOrder);
                }
                else {
                    System.out.println("Invalid option chosen!!!");
                }
                break;
            default:
                System.out.println("Invalid option chosen!!!");
                return;
        }

        String sql1 = "SELECT * FROM public.\"Cities\"";
        ResultSet rs1 = statement.executeQuery(sql1);

        while (rs1.next()) {
            int id = rs1.getInt("Package_pid");
            String cities = rs1.getString("City");
            int no_of_days = rs1.getInt("No_Of_Days");

            List<String> citiesList = new ArrayList<>();
            citiesList.add(cities);
            citiesList.add(String.valueOf(no_of_days));

            // Get the sequence from the sorted details map based on the current package ID
            List<String> packageDetails = SortedDetailsMap.get(id);
            String sequence = packageDetails != null ? packageDetails.get(packageDetails.size() - 1) : "";

            // Creating key
            String key_city = sequence + "&&&" + id + "@@@" + cities;

            // Store details
            citiesMap.put(key_city, citiesList);
        }

        Gson gson = new Gson();
        JsonElement result = search(city, pid, dur, date, noOfPpl, detailsMap, citiesMap, itinerariesMap, packageTypesMap, amenitiesMap, imagesMap);
        // Convert the result to JSON string
        String jsonString = gson.toJson(result);
        // Print or further process the JSON string
        System.out.println(jsonString);
    }

    public static JsonArray search(String city, int packageId, int dur, String date, int noOfPpl,
                                   Map<Integer, List<String>> detailsMap,
                                   Map<String, List<String>> citiesMap,
                                   Map<Integer, List<String>> itinerariesMap,
                                   Map<String, List<String>> packageTypesMap,
                                   Map<String, List<String>> amenitiesMap,
                                   Map<Integer, List<URL>> imagesMap) {

        JsonArray jsonArray = new JsonArray();

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate date_new = LocalDate.parse(date, formatter);

        // Flag
        boolean found = false;

        // Check if packageId is provided
        if (packageId != 0) {
            // Iterate over the detailsMap to find the package with the given packageId
            for (Map.Entry<Integer, List<String>> entry : detailsMap.entrySet()) {
                int id = entry.getKey();
                List<String> details = entry.getValue();
                // Check if the current package matches the given packageId
                if (id == packageId) {
                    // Create JSON object for the package
                    JsonObject packageJson = createPackageJsonObject(id, details, citiesMap, itinerariesMap, packageTypesMap, amenitiesMap, imagesMap);
                    jsonArray.add(packageJson);
                    found = true;
                    break; // Stop searching once a match is found
                }
            }
        } else {
            // No packageId provided, so we'll search by city and duration
            for (Map.Entry<String, List<String>> cityEntry : citiesMap.entrySet()) {
                List<String> cities = cityEntry.getValue();
                // Check if the city matches the provided city
                if (cities.contains(city)) {
                    //System.out.println(cityEntry.getKey());
                    String[] parts = cityEntry.getKey().split("&&&"); // Split the key string using "&&&" delimiter
                    String packageIdStr = parts[1].split("@@@")[0]; // Extract the package ID part before "@@@" delimiter
                    int packageIdFromCity = Integer.parseInt(packageIdStr);
                    List<String> packageDetails = detailsMap.get(packageIdFromCity);
                    // Check if the package duration matches the provided duration
                    if (packageDetails != null && Integer.parseInt(packageDetails.get(2)) >= dur) {
                        // Check if the date is within the package's validity period
                        LocalDate validFrom = LocalDate.parse(packageDetails.get(4), formatter);
                        LocalDate validTo = LocalDate.parse(packageDetails.get(5), formatter);
                        if (date_new.isAfter(validFrom) && date_new.isBefore(validTo)) {
                            // Create JSON object for the package
                            JsonObject packageJson = createPackageJsonObject(packageIdFromCity, packageDetails, citiesMap, itinerariesMap, packageTypesMap, amenitiesMap, imagesMap);
                            jsonArray.add(packageJson);
                            found = true;
                        }
                    }
                }
            }
        }

        if (!found) {
            // No packages found matching the search criteria
            System.out.println("No packages found matching the search criteria.");
        }

        return jsonArray;
    }

    // Helper method to create JSON object for a package
    private static JsonObject createPackageJsonObject(int packageId, List<String> details,
                                                      Map<String, List<String>> citiesMap,
                                                      Map<Integer, List<String>> itinerariesMap,
                                                      Map<String, List<String>> packageTypesMap,
                                                      Map<String, List<String>> amenitiesMap,
                                                      Map<Integer, List<URL>> imagesMap) {
        JsonObject packageJson = new JsonObject();
        packageJson.addProperty("PackageId", packageId);
        packageJson.addProperty("PackageName", details.get(0));
        packageJson.addProperty("Description", details.get(1));
        packageJson.addProperty("Duration", details.get(2));
        packageJson.addProperty("Stars", details.get(3));
        packageJson.addProperty("ValidFrom", details.get(4));
        packageJson.addProperty("ValidTo", details.get(5));

        // Get images for this package
        JsonArray imagesArray = new JsonArray();
        List<URL> images = imagesMap.get(packageId);
        if (images != null) {
            for (URL imageUrl : images) {
                JsonObject imageJson = new JsonObject();
                imageJson.addProperty("ImageUrl", imageUrl.toString());
                imagesArray.add(imageJson);
            }
        }
        packageJson.add("Image", imagesArray);

        // Get cities for this package
        JsonArray citiesArray = new JsonArray();
        for (Map.Entry<String, List<String>> cityEntry : citiesMap.entrySet()) {

            String[] parts = cityEntry.getKey().split("&&&"); // Split the key string using "&&&" delimiter
            String packageIdStr = parts[1].split("@@@")[0]; // Extract the package ID part before "@@@" delimiter
            int packageIdFromCity = Integer.parseInt(packageIdStr);


            if ((packageIdFromCity)==(packageId)) {
                JsonObject cityJson = new JsonObject();
                cityJson.addProperty("City", cityEntry.getValue().get(0));
                cityJson.addProperty("NoOfDays", cityEntry.getValue().get(1));
                citiesArray.add(cityJson);
            }
        }
        packageJson.add("Cities", citiesArray);

        // Get itineraries for this package
        JsonArray itinerariesArray = new JsonArray();
        for (Map.Entry<Integer, List<String>> itiEntry : itinerariesMap.entrySet()) {
            if ((itiEntry.getKey()) / 100 == packageId) {
                JsonObject itineraryJson = new JsonObject();
                itineraryJson.addProperty("Day", itiEntry.getValue().get(0));
                itineraryJson.addProperty("Description", itiEntry.getValue().get(1));
                itinerariesArray.add(itineraryJson);
            }
        }
        packageJson.add("Itineraries", itinerariesArray);

        // Get package types for this package
        JsonArray packageTypesArray = new JsonArray();
        for (Map.Entry<String, List<String>> typeEntry : packageTypesMap.entrySet()) {
            if (typeEntry.getKey().startsWith(Integer.toString(packageId))) {
                List<String> packageType = typeEntry.getValue();
                JsonObject packageTypeJson = new JsonObject();
                packageTypeJson.addProperty("PackageType", packageType.get(0));
                packageTypeJson.addProperty("Cost", packageType.get(1));
                packageTypeJson.addProperty("PackageTypeDescription", packageType.get(2));

                // Get amenities for this package type
                JsonArray amenitiesArray = new JsonArray();
                for (Map.Entry<String, List<String>> amenityEntry : amenitiesMap.entrySet()) {
                    if (amenityEntry.getKey().contains(Integer.toString(packageId)) && amenityEntry.getKey().contains(packageType.get(0))) {
                        JsonObject amenityJson = new JsonObject();
                        amenityJson.addProperty("Amenity", amenityEntry.getValue().get(0));
                        amenitiesArray.add(amenityJson);
                    }
                }
                packageTypeJson.add("Amenities", amenitiesArray);
                packageTypesArray.add(packageTypeJson);
            }
        }

        // Bubble sort the package types array by cost
        for (int i = 0; i < packageTypesArray.size() - 1; i++) {
            for (int j = 0; j < packageTypesArray.size() - i - 1; j++) {
                JsonObject packageType1 = packageTypesArray.get(j).getAsJsonObject();
                JsonObject packageType2 = packageTypesArray.get(j + 1).getAsJsonObject();
                int cost1 = packageType1.get("Cost").getAsInt();
                int cost2 = packageType2.get("Cost").getAsInt();
                if (cost1 > cost2) {
                    // Swap elements
                    JsonElement temp = packageTypesArray.get(j);
                    packageTypesArray.set(j, packageTypesArray.get(j + 1));
                    packageTypesArray.set(j + 1, temp);
                }
            }
        }
        packageJson.add("PackageTypes", packageTypesArray);
        return packageJson;
    }

    public static Map<Integer, List<String>> sortbykey(Map<Integer, List<String>> map) {
        // TreeMap sorts the keys automatically
        TreeMap<Integer, List<String>> sortedMap = new TreeMap<>(map);
        return sortedMap;
    }

    public static Map<Integer, List<String>> sortPackagesByDuration(Map<Integer, List<String>> detailsMap, int a) {
        List<Map.Entry<Integer, List<String>>> list = new LinkedList<>(detailsMap.entrySet());
        int n = list.size();
        for (int i = 0; i < n - 1; i++) {
            for (int j = 0; j < n - i - 1; j++) {
                int duration1 = Integer.parseInt(list.get(j).getValue().get(2));
                int duration2 = Integer.parseInt(list.get(j + 1).getValue().get(2));
                if ((a == 1 && duration1 > duration2) || (a == 2 && duration1 < duration2)) {
                    // Swap elements
                    Map.Entry<Integer, List<String>> temp = list.get(j);
                    list.set(j, list.get(j + 1));
                    list.set(j + 1, temp);
                }
            }
        }

        return mapEntriesToSortedMap(list);
    }

    public static Map<Integer, List<String>> sortPackagesByStarRatings(Map<Integer, List<String>> detailsMap, int a) {
        List<Map.Entry<Integer, List<String>>> list = new LinkedList<>(detailsMap.entrySet());
        int n = list.size();
        for (int i = 0; i < n - 1; i++) {
            for (int j = 0; j < n - i - 1; j++) {
                int stars1 = Integer.parseInt(list.get(j).getValue().get(3));
                int stars2 = Integer.parseInt(list.get(j + 1).getValue().get(3));
                if ((a == 1 && stars1 > stars2) || (a == 2 && stars1 < stars2)) {
                    // Swap elements
                    Map.Entry<Integer, List<String>> temp = list.get(j);
                    list.set(j, list.get(j + 1));
                    list.set(j + 1, temp);
                }
            }
        }

        return mapEntriesToSortedMap(list);
    }

    public static Map<Integer, List<String>> sortPackagesByCost(Map<Integer, List<String>> detailsMap, int a) {
        List<Map.Entry<Integer, List<String>>> list = new LinkedList<>(detailsMap.entrySet());
        int n = list.size();
        for (int i = 0; i < n - 1; i++) {
            for (int j = 0; j < n - i - 1; j++) {
                int cost1 = Integer.parseInt(list.get(j).getValue().get(9));
                int cost2 = Integer.parseInt(list.get(j + 1).getValue().get(9));
                if ((a == 1 && cost1 > cost2) || (a == 2 && cost1 < cost2)) {
                    // Swap elements
                    Map.Entry<Integer, List<String>> temp = list.get(j);
                    list.set(j, list.get(j + 1));
                    list.set(j + 1, temp);
                }
            }
        }

        return mapEntriesToSortedMap(list);
    }

    private static Map<Integer, List<String>> mapEntriesToSortedMap(List<Map.Entry<Integer, List<String>>> list) {
        LinkedHashMap<Integer, List<String>> sortedMap = new LinkedHashMap<>();
        int sequence = 1;
        for (Map.Entry<Integer, List<String>> entry : list) {
            List<String> value = new ArrayList<>(entry.getValue());
            value.add(Integer.toString(sequence));
            sortedMap.put(entry.getKey(), value);
            sequence++;
        }
        return sortedMap;
    }

    public static int getMinCost(String packageId, Map<String, List<String>> packageTypesMap) {
        int minCost = Integer.MAX_VALUE;
        for (Map.Entry<String, List<String>> entry : packageTypesMap.entrySet()) {
            if (entry.getKey().startsWith(packageId)) {
                int cost = Integer.parseInt(entry.getValue().get(1));
                if (cost < minCost) {
                    minCost = cost;
                }
            }
        }
        return minCost;
    }
}