= Advanced Application Development (Transactions, Pooling) =

== Transactional ==

In PetSitter, there are strict rules about transactions. Write operations (INSERT, UPDATE, DELETE) are labeled with `@Transactional` to ensure data consistency and enable automatic fallbacks on failure. 

Read operations (SELECT) are optimized with `@Transactional(readOnly = true)` to avoid unnecessary operations in the background and improve performance.

=== Authenticating a user ===
{{{
#!java
    @Transactional(readOnly = true)
    public User authenticate(String username, String password) {
        User user = userRepository.findByUsername(username).orElse(null);
        // P9 - use BCryptPasswordEncoder here
        if (user != null && user.getPassword().equals(password)) {
            return user;
        }
        return null;
    }
}}}

=== Registering a new user ===
{{{
#!java
    @Transactional
    public User registerUser(String username, String password, String firstName, String lastName, String email, String role) {
        if (userRepository.findByUsername(username).isPresent()) {
            throw new IllegalArgumentException("Username already exists!");
        }

        User newUser;
        if ("OWNER".equalsIgnoreCase(role)) {
            newUser = new PetOwner();
        } else if ("SITTER".equalsIgnoreCase(role)) {
            newUser = new PetSitter();
        } else {
            throw new IllegalArgumentException("Invalid role selected!");
        }

        newUser.setUsername(username);
        newUser.setPassword(password);
        newUser.setFirstName(firstName);
        newUser.setLastName(lastName);
        newUser.setEmail(email);

        // Saving a PetOwner or PetSitter automatically saves to the parent 'users' table with InhertianceType.JOINED
        if (newUser instanceof PetOwner) {
            return petOwnerRepository.save((PetOwner) newUser);
        } else {
            return petSitterRepository.save((PetSitter) newUser);
        }
    }
}}}

=== Creating a new booking ===
{{{
#!java
    @Transactional
    public Booking createBooking(String ownerId, String sitterId, LocalDate dateFrom, LocalDate dateTo, String address, List<String> petIds, String serviceType) {
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> new IllegalArgumentException("Invalid owner"));
        PetSitter sitter = petSitterRepository.findById(sitterId).orElseThrow(() -> new IllegalArgumentException("Invalid sitter"));
        List<Pet> pets = petRepository.findAllById(petIds);

        Booking booking = new Booking();
        booking.setOwner(owner);
        booking.setSitter(sitter);
        booking.setDateFrom(dateFrom);
        booking.setDateTo(dateTo);
        booking.setAddress(address);
        booking.setPets(pets);
        
        if (serviceType != null && !serviceType.isEmpty()) {
            serviceRepository.findAll().stream()
                .filter(s -> s.getType().equals(serviceType))
                .findFirst()
                .ifPresent(service -> booking.setServices(List.of(service)));
        }
        
        return bookingRepository.save(booking);
    }
}}}

=== Fetching bookings for a user ===
{{{
#!java
    @Transactional(readOnly = true)
    public List<Booking> getBookingsForSitter(String sitterId) {
        return bookingRepository.findBySitter_UserIdOrderByDateFromDesc(sitterId);
    }

    @Transactional(readOnly = true)
    public List<Booking> getBookingsForOwner(String ownerId) {
        return bookingRepository.findByOwner_UserIdOrderByDateFromDesc(ownerId);
    }
}}}

=== Adding a new pet ===
{{{
#!java
    @Transactional
    public Pet addPet(String name, Integer age, String specialNeeds, String description, String photoUrl, String ownerId, String petTypeId) {
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> new IllegalArgumentException("Invalid owner ID"));
        PetType petType = petTypeRepository.findById(petTypeId).orElseThrow(() -> new IllegalArgumentException("Invalid pet type ID"));
        
        Pet pet = new Pet();
        pet.setName(name);
        pet.setAge(age);
        pet.setSpecialNeeds(specialNeeds);
        pet.setDescription(description);
        pet.setPhoto(photoUrl);
        pet.setOwner(owner);
        pet.setPetType(petType);
        
        return petRepository.save(pet);
    }
}}}

=== Managing Reviews ===
{{{
#!java
    @Transactional
    public Review addReview(String bookingId, Integer rating, String comment) {
        Booking booking = bookingRepository.findById(bookingId).orElseThrow(() -> new IllegalArgumentException("Invalid booking"));
        Review review = new Review();
        review.setBooking(booking);
        review.setRating(rating);
        review.setComment(comment);
        return reviewRepository.save(review);
    }

    @Transactional(readOnly = true)
    public Double getAverageRating(String sitterId) {
        return reviewRepository.getAverageRatingForSitter(sitterId);
    }
}}}

== Pooling ==

Since PetSitter uses Spring Boot, the database connections are automatically handled with '''HikariCP'''.

HikariCP comes with the `spring-boot-starter-data-jpa` dependency, but I explicitly forced the library version to `<hikaricp.version>6.2.1</hikaricp.version>` in the `pom.xml` file for more verbose logging capabilities.

HikariCP and logging parameters inside `application.properties`:

{{{
#!text
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=true

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=DEBUG
logging.level.org.springframework.transaction.interceptor=TRACE
logging.level.org.springframework.orm.jpa.JpaTransactionManager=DEBUG
}}}

=== Pooling configuration ===
 * '''`maximum-pool-size` (20)''': Number of concurrent database connections. This prevents the PostgreSQL server from having too many connections during heavy usage.
 * '''`minimum-idle` (5)''': Instructs HikariCP to always keep at least 5 idle in the background, so sudden incoming requests can query the database instantly without disrupting regular users connection.
 * '''`connection-timeout` (20s)''': If all 20 connections are in use, new requests will wait 20 seconds for a free slot until it throws a timeout exception.
 * '''`idle-timeout` (5m)''': Any connection after the minimum 5th connection that is not actively used for 5 minutes will be terminated
 * '''`max-lifetime` (20m)''': Every connection can last up to maximum of 20 minutes.
 * '''`auto-commit` (true)''': The database automatically commits/saves every change without explicitly being told to do so.

=== Logging configuration ===
Additional verbose logs for the connection pools:
 * '''`com.zaxxer.hikari.HikariConfig=DEBUG`''': Outputs the pooling settings  Hikari uses when the application is started.
 * '''`com.zaxxer.hikari=DEBUG`''': Shows pooling status in real time (active vs. idle connections) and logs when connections are created or closed.
 * '''`org.springframework.transaction.interceptor=TRACE`''': Logs exactly when Spring executes a `@Transactional` method in the code
 * '''`org.springframework.orm.jpa.JpaTransactionManager=DEBUG`''': Logs the actual database transactions happening in the background


=== Pooling Logs ===
Below is the log output proving the successful initialization and population of the HikariCP connection pool upon application startup. As configured, exactly 5 background connections are established and kept idle for immediate request processing.

{{{
#!text
2026-05-25T15:27:56.827Z  INFO 1 --- [petsitter] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2026-05-25T15:27:56.842Z DEBUG 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (7c6a30fc-4cfe-446a-a352-818177bca0d1)
2026-05-25T15:27:57.233Z DEBUG 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (7c6a30fc-4cfe-446a-a352-818177bca0d1)
2026-05-25T15:27:57.235Z  INFO 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@2ab9e43e
2026-05-25T15:27:57.340Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (3367dbcd-5010-4605-ab92-423e4562c990)
2026-05-25T15:27:57.869Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (3367dbcd-5010-4605-ab92-423e4562c990)
2026-05-25T15:27:57.870Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@411815df
2026-05-25T15:27:57.900Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=2/20, idle=2/5, active=0, waiting=0)
2026-05-25T15:27:57.901Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (a71e892a-e173-41ba-909b-ca4e547437fd)
2026-05-25T15:27:58.525Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (a71e892a-e173-41ba-909b-ca4e547437fd)
2026-05-25T15:27:58.525Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4fdfe38c
2026-05-25T15:27:58.555Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=3/20, idle=3/5, active=0, waiting=0)
2026-05-25T15:27:58.556Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (670b5f5c-bddb-4a45-83ef-aa7a30a54530)
2026-05-25T15:27:58.631Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (670b5f5c-bddb-4a45-83ef-aa7a30a54530)
2026-05-25T15:27:58.632Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4be07eaf
2026-05-25T15:27:58.662Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=4/20, idle=4/5, active=0, waiting=0)
2026-05-25T15:27:58.662Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (81793045-e2cb-42de-9b40-e06e476bbea7)
2026-05-25T15:27:58.735Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (81793045-e2cb-42de-9b40-e06e476bbea7)
2026-05-25T15:27:58.735Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@ee2e9b7
2026-05-25T15:27:58.765Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=5/20, idle=5/5, active=0, waiting=0)
}}}