JDBCTask.java

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Scanner;

public class JDBCTask {
        public static class REPL {
                Connection con;

                public REPL(Connection con) {
                        this.con = con;
                        this.start();
                }

                String takeInput(Scanner s, String message) {
                        System.out.println(message);
                        String unvalidated = s.nextLine();
			String regexForValidation = "[^a-zA-Z0-9]";
			String validated = unvalidated.replaceAll(regexForValidation, "");
                	return validated;
		}

                void printResults(ResultSet results, int columnCount) {
                        try {
                                while (results.next()) {
                                        for (int i = 1; i <= columnCount; i++) {
                                                System.out.print(results.getString(i) + "\t");
                                        }
                                        System.out.println();
                                }
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                }

                int printColumns(ResultSetMetaData metaData) {
                        int columnCount = 0;
                        try {
                                columnCount = metaData.getColumnCount();
                                if (columnCount < 1) {
                                        return columnCount;
                                }
                                for (int i = 1; i <= columnCount; i++) {
                                        System.out.print(metaData.getColumnName(i) + "\t");
                                }
                                System.out.println();
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                        return columnCount;
                }

                void start() {
                        int option;
                        Scanner s = new Scanner(System.in);
                        Statement stmt = null;

                        try {
                                stmt = con.createStatement();
                        } catch (SQLException e) {
                                System.err.println("Error creating statement: " + e.getMessage());
                                return;
                        }

                        while (true) {
                                System.out.println("Welcome to the crime register system!");
                                System.out.println("Choose an option:\n"
                                                + "0 - See all wanted people\n"
                                                + "1 - Search for wanted people via first name and last name (case-sensitive)\n"
                                                + "2 - Register a crime\n"
                                                + "3 - See all people\n"
                                                + "4 - Register a person\n"
                                                + "5 - Change status of a person\n"
                                                + "6 - Delete Police Department\n"
                                                + "7 - Register Police Department\n"
                                                + "8 - Exit");

                                try {
                                        option = s.nextInt();
                                        s.nextLine();
                                        switch (option) {
                                                case 0:
                                                        showAllWantedPeople(s, stmt);
                                                        break;
                                                case 1:
                                                        searchWantedPerson(s);
                                                        break;
                                                case 2:
                                                        registerCrime(s);
                                                        break;
                                                case 3:
                                                        seeAllPeople(s);
                                                        break;
                                                case 4:
                                                        registerPerson(s);
                                                        break;
                                                case 5:
                                                        changeStatusOfPerson(s);
                                                        break;
                                                case 6:
                                                        deletePoliceDepartment(s);
                                                        break;
                                                case 7:
                                                        registerPoliceDepartment(s);
                                                        break;
                                                case 8:
                                                        System.out.println("Exiting the system.");
                                                        s.close();
                                                        return;
                                                default:
                                                        System.out.println("Invalid option. Please try again.");
                                        }

                                } catch (Exception e) {
                                        System.out.println("An unexpected error has occurred: " + e.getMessage());
                                        s.nextLine();
                                }
                        }
                }

                void showAllWantedPeople(Scanner s, Statement stmt) {
                        String query = "SELECT * FROM wanted_criminals_view;";
                        try (ResultSet results = stmt.executeQuery(query)) {
                                ResultSetMetaData metaData = results.getMetaData();
                                int columnCount = printColumns(metaData);
                                if (columnCount > 0) {
                                        printResults(results, columnCount);
                                } else {
                                        System.out.println("No data found.");
                                }
                        } catch (SQLException e) {
                                System.err.println("Error fetching data: " + e.getMessage());
                        }
                }

                void searchWantedPerson(Scanner s) {
                        String firstName = takeInput(s, "Enter first name of person:");
                        String lastName = takeInput(s, "Enter last name of person:");

                        String query = "SELECT * FROM wanted_criminals_view w WHERE w.first_name = ? AND w.last_name = ?;";
                        try (PreparedStatement stWanted = con.prepareStatement(query)) {
                                stWanted.setString(1, firstName);
                                stWanted.setString(2, lastName);

                                try (ResultSet results = stWanted.executeQuery()) {
                                        ResultSetMetaData metaData = results.getMetaData();
                                        int columnCount = printColumns(metaData);
                                        if (columnCount > 0) {
                                                printResults(results, columnCount);
                                        } else {
                                                System.out.println("No records found.");
                                        }
                                }
                        } catch (SQLException e) {
                                System.err.println("Error executing search: " + e.getMessage());
                        }
                }

                void registerCrime(Scanner s) {
                        ArrayList<Integer> personIds = new ArrayList<>();
                        int departmentId;

                        try {
                                System.out.println("Available Police Departments:");
                                String deptQuery = "SELECT department_id, department_name FROM police_department;";
                                try (Statement stmt = con.createStatement();
                                                ResultSet deptResults = stmt.executeQuery(deptQuery)) {
                                        System.out.println("ID\tName");
                                        while (deptResults.next()) {
                                                System.out.println(deptResults.getInt("department_id") + "\t"
                                                                + deptResults.getString("department_name"));
                                        }
                                }

                                departmentId = Integer.parseInt(takeInput(s,
                                                "Enter the ID of the department investigating this crime:"));

                                System.out.println("Available Persons:");
                                seeAllPeople(s);

                                int personId;
                                while (true) {
                                        personId = Integer.parseInt(takeInput(s,
                                                        "Enter the person ID involved in the crime (-1 to stop):"));
                                        if (personId == -1)
                                                break;
                                        personIds.add(personId);
                                }

                                String type = takeInput(s, "Enter crime type (e.g., 'Felony', 'Misdemeanor'):");
                                String description = takeInput(s, "Describe the crime:");
                                String severity = takeInput(s, "Enter severity (e.g., 'Low', 'Medium', 'High'):");
                                LocalDateTime dateCommitted = LocalDateTime.parse(
                                                takeInput(s, "Enter date committed (YYYY-MM-DDTHH:MM:SS, e.g., 2023-12-01T15:30:00):"));

                                con.setAutoCommit(false);

                                String crimeInsert = "INSERT INTO crime (type, description, date_committed, severity, investigated_by) "
                                                +
                                                "VALUES (?, ?, ?, ?, ?) RETURNING crime_id;";
                                int crimeId;
                                try (PreparedStatement crimeStmt = con.prepareStatement(crimeInsert)) {
                                        crimeStmt.setString(1, type);
                                        crimeStmt.setString(2, description);
                                        crimeStmt.setTimestamp(3, Timestamp.valueOf(dateCommitted));
                                        crimeStmt.setString(4, severity);
                                        crimeStmt.setInt(5, departmentId);

                                        try (ResultSet rs = crimeStmt.executeQuery()) {
                                                if (rs.next()) {
                                                        crimeId = rs.getInt("crime_id");
                                                } else {
                                                        throw new SQLException(
                                                                        "Crime insertion failed, no ID returned.");
                                                }
                                        }
                                }

                                String commitInsert = "INSERT INTO commits (person_id, crime_id, date_of_arrest, arresting_officer) "
                                                +
                                                "VALUES (?, ?, ?, ?);";
                                try (PreparedStatement commitStmt = con.prepareStatement(commitInsert)) {
                                        for (int id : personIds) {
                                                String officer = takeInput(s,
                                                                "Enter arresting officer for person ID " + id + ":");
                                                commitStmt.setInt(1, id);
                                                commitStmt.setInt(2, crimeId);
                                                commitStmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
                                                commitStmt.setString(4, officer);
                                                commitStmt.addBatch();
                                        }
                                        commitStmt.executeBatch();
                                }

                                con.commit();
                                System.out.println("Crime registered successfully!");

                        } catch (SQLException e) {
                                System.err.println("Database error: " + e.getMessage());
                                try {
                                        con.rollback();
                                        System.err.println("Transaction rolled back.");
                                } catch (SQLException rollbackEx) {
                                        System.err.println("Rollback failed: " + rollbackEx.getMessage());
                                }
                        } catch (Exception e) {
                                System.err.println("Error: " + e.getMessage());
                        } finally {
                                try {
                                        con.setAutoCommit(true);
                                } catch (SQLException e) {
                                        System.err.println("Failed to reset auto-commit: " + e.getMessage());
                                }
                        }
                }

                void seeAllPeople(Scanner s) {
                        String query = "SELECT person_id, first_name, last_name, date_of_birth FROM person;";
                        try (Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery(query)) {
                                System.out.println("ID\tFirst Name\tLast Name\tDate of Birth");
                                while (results.next()) {
                                        System.out.println(
                                                        results.getInt("person_id") + "\t" +
                                                                        results.getString("first_name") + "\t" +
                                                                        results.getString("last_name") + "\t" +
                                                                        results.getDate("date_of_birth"));
                                }
                        } catch (SQLException e) {
                                System.err.println("Error fetching people: " + e.getMessage());
                        }
                }

                void registerPerson(Scanner s) {
                        String firstName = takeInput(s, "Enter first name:");
                        String lastName = takeInput(s, "Enter last name:");
                        String dateOfBirth = takeInput(s, "Enter date of birth (YYYY-MM-DD):");
                        String street = takeInput(s, "Enter street:");
                        String city = takeInput(s, "Enter city:");
                        String state = takeInput(s, "Enter state:");
                        String zipcode = takeInput(s, "Enter zipcode:");
                        String contactNumber = takeInput(s, "Enter contact number:");
                        String gender = takeInput(s, "Enter gender (Male/Female/Other):");

                        String query = "INSERT INTO person (first_name, last_name, date_of_birth, street, city, state, zipcode, contact_number, gender) "
                                        +
                                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
                        try (PreparedStatement stmt = con.prepareStatement(query)) {
                                stmt.setString(1, firstName);
                                stmt.setString(2, lastName);
                                stmt.setDate(3, Date.valueOf(dateOfBirth));
                                stmt.setString(4, street);
                                stmt.setString(5, city);
                                stmt.setString(6, state);
                                stmt.setString(7, zipcode);
                                stmt.setString(8, contactNumber);
                                stmt.setString(9, gender);

                                stmt.executeUpdate();
                                System.out.println("Person registered successfully!");
                        } catch (SQLException e) {
                                System.err.println("Error registering person: " + e.getMessage());
                        }
                }

                void changeStatusOfPerson(Scanner s) {
                        seeAllPeople(s); 
                        int personId = Integer.parseInt(takeInput(s,
                                        "Enter the ID of the person whose case status you want to update:"));

                        String fetchCasesQuery = """
                                            SELECT c.case_id, c.case_status, cr.type AS crime_type
                                            FROM \"case\" c
                                            INNER JOIN filed_for ff ON c.case_id = ff.case_id
                                            INNER JOIN crime cr ON ff.crime_id = cr.crime_id
                                            INNER JOIN commits com ON cr.crime_id = com.crime_id
                                            WHERE com.person_id = ?;
                                        """;

                        try (PreparedStatement fetchCasesStmt = con.prepareStatement(fetchCasesQuery)) {
                                fetchCasesStmt.setInt(1, personId);

                                try (ResultSet results = fetchCasesStmt.executeQuery()) {
                                        System.out.println("Case ID\tStatus\tCrime Type");
                                        boolean hasCases = false;
                                        while (results.next()) {
                                                hasCases = true;
                                                System.out.println(
                                                                results.getInt("case_id") + "\t" +
                                                                                results.getString("case_status") + "\t"
                                                                                +
                                                                                results.getString("crime_type"));
                                        }

                                        if (!hasCases) {
                                                System.out.println("No cases found for the selected person.");
                                                return;
                                        }
                                }

                                int caseId = Integer
                                                .parseInt(takeInput(s, "Enter the ID of the case you want to update:"));
                                String newStatus = takeInput(s, "Enter the new status:");

                                String updateCaseQuery = "UPDATE \"case\" SET case_status = ? WHERE case_id = ?;";
                                try (PreparedStatement updateCaseStmt = con.prepareStatement(updateCaseQuery)) {
                                        updateCaseStmt.setString(1, newStatus);
                                        updateCaseStmt.setInt(2, caseId);

                                        int rowsAffected = updateCaseStmt.executeUpdate();
                                        if (rowsAffected > 0) {
                                                System.out.println("Case status updated successfully!");
                                        } else {
                                                System.out.println("No case found with the given ID.");
                                        }
                                }

                        } catch (SQLException e) {
                                System.err.println("Error updating case status: " + e.getMessage());
                        }
                }

                void deletePoliceDepartment(Scanner s) {
                        String query = "SELECT department_id, department_name FROM police_department;";
                        try (Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery(query)) {
                                System.out.println("ID\tName");
                                while (results.next()) {
                                        System.out.println(results.getInt("department_id") + "\t"
                                                        + results.getString("department_name"));
                                }
                        } catch (SQLException e) {
                                System.err.println("Error fetching departments: " + e.getMessage());
                        }

                        int departmentId = Integer.parseInt(takeInput(s, "Enter the ID of the department to delete:"));

                        String deleteQuery = "DELETE FROM police_department WHERE department_id = ?;";
                        try (PreparedStatement stmt = con.prepareStatement(deleteQuery)) {
                                stmt.setInt(1, departmentId);

                                int rowsAffected = stmt.executeUpdate();
                                if (rowsAffected > 0) {
                                        System.out.println("Department deleted successfully!");
                                } else {
                                        System.out.println("No department found with the given ID.");
                                }
                        } catch (SQLException e) {
                                System.err.println("Error deleting department: " + e.getMessage());
                        }
                }

                void registerPoliceDepartment(Scanner s) {
                        String name = takeInput(s, "Enter department name:");
                        String location = takeInput(s, "Enter location:");
                        String contactNumber = takeInput(s, "Enter contact number:");

                        String query = "INSERT INTO police_department (department_name, location, contact_number) VALUES (?, ?, ?);";
                        try (PreparedStatement stmt = con.prepareStatement(query)) {
                                stmt.setString(1, name);
                                stmt.setString(2, location);
                                stmt.setString(3, contactNumber);

                                stmt.executeUpdate();
                                System.out.println("Police department registered successfully!");
                        } catch (SQLException e) {
                                System.err.println("Error registering department: " + e.getMessage());
                        }
                }

        }

        public static void main(String[] args) {
                try {
                        Class.forName("org.postgresql.Driver");
                        Connection con = DriverManager.getConnection(
                                        "jdbc:postgresql://pgsql3.mif/studentu",
                                        "beku9995", "8Y1qPh82K");

                        REPL repl = new REPL(con);

                        con.close();
                } catch (ClassNotFoundException e) {
                        System.err.println("PostgreSQL Driver not found: " + e.getMessage());
                } catch (SQLException e) {
                        System.err.println("Database error: " + e.getMessage());
                }
        }
}