= Advanced Application Development
== Transactional
=== Enrolling a user to a course ===
{{{
@Transactional
public void enrollUserToCourse(Integer courseId, String email) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    Course course = courseRepository.findById(courseId).get();

    if(userSubscriptionService.hasUserSubscription(email)
        && enrollmentRepository.findByCourseAndUser(course, user).isEmpty()) {

        Enrollment enrollment = new Enrollment();
        enrollment.setUser(user);
        enrollment.setCourse(course);
        enrollment.setEnrollDate(LocalDate.now());
        enrollment.setCompletionStatus("NOT COMPLETE");
        enrollment.setProgressPercentage(0);

        enrollmentRepository.save(enrollment);
    }
}
}}}
- This transaction is responsible for enrolling a user in a course.  
- The method validates the user subscription and checks if the user is already enrolled before creating a new enrollment entry.

=== Completing a course and generating a certificate ===
{{{
@Transactional
public void completeCourse(Integer courseId, String email) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    Course course = courseRepository.findById(courseId).get();

    Random random = new Random();

    Enrollment enrollment = enrollmentRepository.findByCourseAndUser(course, user).get();
    enrollment.setCompletionStatus("COMPLETE");
    enrollment.setProgressPercentage(100);
    enrollmentRepository.save(enrollment);

    Certificate certificate = new Certificate();
    certificate.setEnrollment(enrollment);
    certificate.setIssueDate(LocalDate.now());
    certificate.setStatus("COMPLETE");
    certificate.setCertificateCode("CODE" + random.nextLong());

    certificateRepository.save(certificate);
}
}}}
- This transaction updates the enrollment status and generates a certificate after successful course completion.  
- Both operations are executed within a single transaction to preserve database consistency.

=== Adding a user subscription and payment ===
{{{
@Transactional
public void addUserSubscription(String email, Integer subscriptionPlanId) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    SubscriptionPlan subscriptionPlan = subscriptionPlanRepository.findById(subscriptionPlanId).get();

    UserSubscription userSubscription = new UserSubscription();
    userSubscription.setUser(user);
    userSubscription.setPlan(subscriptionPlan);
    userSubscription.setStartDate(LocalDate.now());
    userSubscription.setEndDate(
        LocalDate.now().plusMonths(subscriptionPlan.getDurationMonths())
    );
    userSubscription.setStatus("ACTIVE");

    userSubscriptionRepository.save(userSubscription);

    Payment payment = new Payment();
    payment.setUser(user);
    payment.setSubscription(userSubscription);
    payment.setAmount(subscriptionPlan.getPrice());

    paymentRepository.save(payment);
}
}}}
- This transaction creates a new subscription for a user and also creates a payment record connected to the subscription.

=== Checking expired subscriptions ===
{{{
@Scheduled(cron = "0 0 0 * * *")
@Transactional
public void checkExpiredSubscriptions() {

    LocalDate today = LocalDate.now();

    List<UserSubscription> expiredSubscriptions =
        userSubscriptionRepository
            .findByEndDateBeforeAndStatusNot(today, "EXPIRED");

    for (UserSubscription subscription : expiredSubscriptions) {
        subscription.setStatus("EXPIRED");
        userSubscriptionRepository.save(subscription);
    }
}
}}}
- This scheduled transaction automatically checks and updates expired subscriptions every day.

=== Creating a support ticket ===
{{{
@Transactional
public void addSupportTicket(
    String email,
    String subject,
    String description
) {

    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();

    List<Administrator> admins = administratorRepository.findAll();

    Random random = new Random();
    Administrator assignedAdmin =
        admins.get(random.nextInt(admins.size()));

    SupportTicket supportTicket = new SupportTicket();

    supportTicket.setUser(user);
    supportTicket.setAdmin(assignedAdmin);
    supportTicket.setSubject(subject);
    supportTicket.setDescription(description);
    supportTicket.setStatus("UNRESOLVED");
    supportTicket.setCreatedAt(LocalDateTime.now());

    supportTicketRepository.save(supportTicket);
}
}}}
- This transaction creates a support ticket and automatically assigns an administrator responsible for resolving the issue.

=== Resolving a support ticket ===
{{{
@Transactional
public void resolveSupportTicket(Integer ticketId) {

    SupportTicket supportTicket =
        supportTicketRepository.findById(ticketId).get();

    supportTicket.setStatus("RESOLVED");

    supportTicketRepository.save(supportTicket);
}
}}}
- This transaction updates the support ticket status from unresolved to resolved.

== Database Connection Pooling ==
- The application uses Spring Boot with Spring Data JPA to communicate with the PostgreSQL database.
- Database connections are not created manually. Instead, they are automatically managed by HikariCP, which is the default connection pool implementation in Spring Boot.
- HikariCP is included automatically through:
{{{
spring-boot-starter-data-jpa
}}}

- The following configuration is used in the application:
{{{
spring.application.name=db

spring.datasource.url=jdbc:postgresql://localhost:3307/db_202526z_va_prj_olpms
spring.datasource.username=db_202526z_va_prj_olpms_owner
spring.datasource.password=3ad0b6760a91
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=validate

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=20000
}}}

- These values allow the application to:
 * reuse database connections efficiently
 * improve performance
 * reduce connection creation overhead
 * support multiple simultaneous database requests

- Database connections from the pool are automatically used in repository operations such as:
 * courseRepository.save()
 * enrollmentRepository.save()
 * userSubscriptionRepository.save()
 * supportTicketRepository.save()