package com.company.ecommerce.domain.order;
 
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.time.Instant;
import java.util.List;
import java.util.Optional;
 
/*
 * JpaSpecificationExecutor: Adds findAll(Specification) for dynamic queries.
 * Essential for search filters, complex WHERE conditions.
 */
public interface OrderRepository extends JpaRepository<Order, Long>,
                                         JpaSpecificationExecutor<Order> {
 
    // ===<mark class="obsidian-highlight"> DERIVED QUERIES (auto-generated from method name) </mark>=<mark class="obsidian-highlight">
 
    Optional<Order> findByOrderNumber(String orderNumber);
 
    List<Order> findByCustomerId(Long customerId);
 
    Page<Order> findByCustomerIdOrderByPlacedAtDesc(Long customerId, Pageable pageable);
 
    List<Order> findByStatus(OrderStatus status);
 
    long countByCustomerIdAndStatus(Long customerId, OrderStatus status);
 
    boolean existsByOrderNumber(String orderNumber);
 
 
    // </mark>=<mark class="obsidian-highlight"> JPQL QUERIES </mark>===
 
    @Query("""
        SELECT DISTINCT o FROM Order o
        LEFT JOIN FETCH o.items i
        LEFT JOIN FETCH i.product
        WHERE o.id = :id
        """)
    Optional<Order> findByIdWithItemsAndProducts(@Param("id") Long id);
 
    @Query("""
        SELECT o FROM Order o
        WHERE o.customer.id = :customerId
          AND o.status = :status
          AND o.placedAt BETWEEN :from AND :to
        ORDER BY o.placedAt DESC
        """)
    List<Order> findByCustomerAndStatusAndDateRange(
        @Param("customerId") Long customerId,
        @Param("status") OrderStatus status,
        @Param("from") Instant from,
        @Param("to") Instant to
    );
 
 
    // ===<mark class="obsidian-highlight"> BULK UPDATE (use @Modifying) </mark>===
 
    /*
     * @Modifying: Required for UPDATE and DELETE queries.
     * clearAutomatically = true: Clears the first-level cache after the bulk update.
     * Without this, entities loaded before the bulk update may be stale.
     *
     * flushAutomatically = true: Flushes pending changes before executing the bulk update.
     * Prevents inconsistency between in-memory state and the DB update.
     */
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("UPDATE Order o SET o.status = :status WHERE o.id IN :ids")
    int bulkUpdateStatus(@Param("ids") List<Long> ids, @Param("status") OrderStatus status);
 
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("""
        UPDATE Order o SET o.status = 'CANCELLED'
        WHERE o.status = 'PENDING'
          AND o.placedAt < :cutoffTime
        """)
    int cancelStalePendingOrders(@Param("cutoffTime") Instant cutoffTime);
 
 
    // ===<mark class="obsidian-highlight"> PAGINATION-SAFE FETCH WITH ASSOCIATIONS </mark>===
 
    /*
     * Step 1: Paginate by IDs only (no JOIN FETCH for collections)
     * Used together with findByIdsWithItems for safe pagination.
     */
    @Query("""
        SELECT o.id FROM Order o
        WHERE o.customer.id = :customerId
        ORDER BY o.placedAt DESC
        """)
    Page<Long> findIdsByCustomerId(@Param("customerId") Long customerId, Pageable pageable);
 
    /*
     * Step 2: Fetch full entities for the paginated IDs
     */
    @Query("""
        SELECT DISTINCT o FROM Order o
        LEFT JOIN FETCH o.items i
        LEFT JOIN FETCH i.product
        WHERE o.id IN :ids
        ORDER BY o.placedAt DESC
        """)
    List<Order> findByIdsWithItems(@Param("ids") List<Long> ids);
 
 
    // ===<mark class="obsidian-highlight"> NATIVE SQL QUERY </mark>===
 
    /*
     * Use native queries when:
     * - You need database-specific features (MySQL-specific functions)
     * - JPQL cannot express the query (complex GROUP BY, window functions)
     * - Performance requires fine-grained SQL control
     *
     * nativeQuery = true: The query is SQL, not JPQL.
     * countQuery: Required for native queries with Pageable.
     */
    @Query(
        value = """
            SELECT o.id, o.order_number, o.total_amount, o.status,
                   COUNT(oi.order_id) as item_count
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.customer_id = :customerId
            GROUP BY o.id
            ORDER BY o.placed_at DESC
            LIMIT :limit OFFSET :offset
            """,
        nativeQuery = true
    )
    List<Object[]> findOrderSummariesNative(
        @Param("customerId") Long customerId,
        @Param("limit") int limit,
        @Param("offset") int offset
    );
}

package com.company.ecommerce.domain.order;
 
import jakarta.persistence.criteria.*;
import org.springframework.data.jpa.domain.Specification;
import java.time.Instant;
 
public class OrderSpecifications {
 
    /*
     * Each method returns a Specification<Order>.
     * Specifications can be combined with .and(), .or(), .not().
     * This builds a Criteria API query under the hood.
     */
 
    public static Specification<Order> hasStatus(OrderStatus status) {
        return (root, query, cb) ->
            status <mark class="obsidian-highlight"> null ? null : cb.equal(root.get("status"), status);
    }
 
    public static Specification<Order> hasCustomerId(Long customerId) {
        return (root, query, cb) ->
            customerId </mark> null ? null : cb.equal(root.get("customer").get("id"), customerId);
    }
 
    public static Specification<Order> placedAfter(Instant from) {
        return (root, query, cb) ->
            from <mark class="obsidian-highlight"> null ? null : cb.greaterThanOrEqualTo(root.get("placedAt"), from);
    }
 
    public static Specification<Order> placedBefore(Instant to) {
        return (root, query, cb) ->
            to </mark> null ? null : cb.lessThanOrEqualTo(root.get("placedAt"), to);
    }
 
    public static Specification<Order> totalAmountBetween(
            BigDecimal min, BigDecimal max) {
        return (root, query, cb) -> {
            if (min <mark class="obsidian-highlight"> null && max </mark> null) return null;
            if (min <mark class="obsidian-highlight"> null) return cb.lessThanOrEqualTo(root.get("totalAmount"), max);
            if (max </mark> null) return cb.greaterThanOrEqualTo(root.get("totalAmount"), min);
            return cb.between(root.get("totalAmount"), min, max);
        };
    }
 
    public static Specification<Order> orderNumberContains(String keyword) {
        return (root, query, cb) ->
            keyword == null ? null :
                cb.like(cb.lower(root.get("orderNumber")), "%" + keyword.toLowerCase() + "%");
    }
}

// Search Request DTO
@Data
public class OrderSearchRequest {
    private OrderStatus status;
    private Long customerId;
    private Instant from;
    private Instant to;
    private BigDecimal minAmount;
    private BigDecimal maxAmount;
    private String orderNumber;
    private int page = 0;
    private int size = 20;
    private String sortBy = "placedAt";
    private String sortDir = "desc";
}

// Service using Specifications
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class OrderQueryService {
 
    private final OrderRepository orderRepository;
 
    public Page<OrderResponse> searchOrders(OrderSearchRequest request) {
        Specification<Order> spec = Specification
            .where(OrderSpecifications.hasStatus(request.getStatus()))
            .and(OrderSpecifications.hasCustomerId(request.getCustomerId()))
            .and(OrderSpecifications.placedAfter(request.getFrom()))
            .and(OrderSpecifications.placedBefore(request.getTo()))
            .and(OrderSpecifications.totalAmountBetween(request.getMinAmount(), request.getMaxAmount()))
            .and(OrderSpecifications.orderNumberContains(request.getOrderNumber()));
 
        Sort sort = request.getSortDir().equalsIgnoreCase("asc")
            ? Sort.by(request.getSortBy()).ascending()
            : Sort.by(request.getSortBy()).descending();
 
        Pageable pageable = PageRequest.of(request.getPage(), request.getSize(), sort);
 
        return orderRepository.findAll(spec, pageable)
            .map(orderMapper::toResponse);
    }
}

@Transactional(
    propagation = Propagation.REQUIRED,      // Default: join existing or create new
    isolation = Isolation.DEFAULT,           // Default: use DB default (REPEATABLE_READ in MySQL)
    readOnly = false,                        // Default: read-write transaction
    rollbackFor = Exception.class,           // Roll back for checked exceptions too
    noRollbackFor = BusinessException.class, // Do NOT roll back for this specific exception
    timeout = 30                             // Transaction timeout in seconds
)

@Service
public class OrderService {
 
    @Transactional
    public void createOrder(CreateOrderRequest request) {
        // ... create order ...
        sendConfirmationEmail(order);  // Internal call - NO proxy involved!
    }
 
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void sendConfirmationEmail(Order order) {
        // This @Transactional has NO EFFECT when called from createOrder() above!
        // Spring @Transactional works via AOP proxy.
        // When you call a method on 'this', you bypass the proxy entirely.
        // The @Transactional on sendConfirmationEmail is IGNORED.
    }
}

@Service
@RequiredArgsConstructor
public class OrderService {
 
    private final EmailNotificationService emailService; // Separate bean
 
    @Transactional
    public void createOrder(CreateOrderRequest request) {
        // ... create order ...
        emailService.sendConfirmationEmail(order); // Goes through proxy - @Transactional works
    }
}
 
@Service
public class EmailNotificationService {
    @Transactional(propagation = Propagation.REQUIRES_NEW)  // Now this works correctly
    public void sendConfirmationEmail(Order order) { ... }
}

@Service
public class OrderService {
 
    @Autowired
    @Lazy
    private OrderService self;  // Injects the proxy version of itself
 
    public void createOrder(CreateOrderRequest request) {
        self.sendConfirmationEmail(order);  // Goes through proxy now
    }
 
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void sendConfirmationEmail(Order order) { ... }
}

/*
 * MySQL InnoDB default: REPEATABLE_READ
 * This prevents dirty reads and non-repeatable reads.
 * Phantom reads are prevented by InnoDB's gap locking.
 *
 * For financial operations or inventory:
 * Use SERIALIZABLE to prevent all anomalies.
 * But: SERIALIZABLE has the highest locking overhead.
 */
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void processPayment(Long orderId, PaymentRequest request) { ... }
 
@Transactional(isolation = Isolation.SERIALIZABLE)
public void deductInventory(Long productId, int quantity) { ... }
 
/*
 * For read-only reporting queries:
 * readOnly = true tells Hibernate to:
 * 1. Skip dirty checking (no need to track changes)
 * 2. Use a read-only snapshot (potentially use a read replica)
 * 3. Optimize session for reads
 * Result: Significantly better performance for read-heavy operations.
 */
@Transactional(readOnly = true)
public Page<OrderResponse> getOrderHistory(Long customerId, Pageable pageable) { ... }

// Default behavior:
@Transactional
public void processOrder(Long orderId) throws OrderProcessingException {
    // If OrderProcessingException (checked) is thrown -> NO rollback!
    // Database changes are committed even though an exception occurred.
}
 
// Explicit rollback for checked exceptions:
@Transactional(rollbackFor = {Exception.class})
public void processOrder(Long orderId) throws OrderProcessingException {
    // Now rolls back for any exception
}
 
// Or convert checked to unchecked at the service boundary:
@Transactional
public void processOrder(Long orderId) {
    try {
        // ... process ...
    } catch (OrderProcessingException e) {
        throw new OrderServiceException("Failed to process order", e); // RuntimeException
    }
}

@Entity
@Table(name = "products")
public class Product extends AuditableEntity {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(name = "stock_quantity", nullable = false)
    private Integer stockQuantity;
 
    @Column(name = "price", nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    /*
     * @Version: Hibernate automatically manages this field.
     * On every UPDATE: Hibernate adds "WHERE version = ?" to the SQL.
     * If the version in DB does not match the entity's version -> 0 rows updated.
     * Hibernate detects 0 rows updated and throws OptimisticLockException.
     *
     * MySQL DDL: version BIGINT NOT NULL DEFAULT 0
     *
     * Hibernate's generated UPDATE:
     * UPDATE products SET stock_quantity = ?, price = ?, version = (version + 1)
     * WHERE id = ? AND version = ?  <- The version check
     */
    @Version
    @Column(name = "version", nullable = false)
    private Long version;
}

@Service
@RequiredArgsConstructor
public class InventoryService {
 
    private final ProductRepository productRepository;
 
    /*
     * Retry logic for optimistic locking conflicts.
     * @Retryable from Spring Retry handles automatic retry.
     * If two threads try to update stock simultaneously:
     *   Thread A reads product (version=5), deducts 1
     *   Thread B reads product (version=5), deducts 1
     *   Thread A commits: version becomes 6
     *   Thread B tries to commit: version 5 != 6 -> OptimisticLockException -> RETRY
     *   Thread B retries: reads fresh product (version=6), deducts 1 -> commits as version 7
     */
    @Retryable(
        retryFor = OptimisticLockingFailureException.class,
        maxAttempts = 3,
        backoff = @Backoff(delay = 100, multiplier = 2.0)
    )
    @Transactional
    public void deductStock(Long productId, int quantity) {
        Product product = productRepository.findById(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product not found"));
 
        if (product.getStockQuantity() < quantity) {
            throw new InsufficientStockException(
                "Not enough stock for product: " + productId);
        }
 
        product.setStockQuantity(product.getStockQuantity() - quantity);
        productRepository.save(product);
        // Hibernate generates:
        // UPDATE products SET stock_quantity=?, version=? WHERE id=? AND version=?
    }
 
    @Recover  // Called when all retries are exhausted
    public void recoverFromOptimisticLock(
            OptimisticLockingFailureException e, Long productId, int quantity) {
        log.error("Failed to deduct stock after retries for product: {}", productId, e);
        throw new ServiceUnavailableException(
            "Could not update inventory due to high contention. Please try again.");
    }
}

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    /*
     * LockModeType.PESSIMISTIC_WRITE:
     * Acquires an exclusive lock (SELECT ... FOR UPDATE in MySQL).
     * No other transaction can read OR write this row until the lock is released.
     *
     * LockModeType.PESSIMISTIC_READ:
     * Acquires a shared lock (SELECT ... LOCK IN SHARE MODE).
     * Other transactions can read, but NOT write.
     */
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Product p WHERE p.id = :id")
    Optional<Product> findByIdWithLock(@Param("id") Long id);
}

@Service
@RequiredArgsConstructor
public class FlashSaleService {
 
    private final ProductRepository productRepository;
 
    @Transactional
    public void purchaseFlashSaleItem(Long productId, int quantity) {
        // Lock acquired here - no other transaction can touch this product
        Product product = productRepository.findByIdWithLock(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product not found"));
 
        // Safe to check and update - we own the lock
        if (product.getStockQuantity() < quantity) {
            throw new InsufficientStockException("Flash sale item sold out");
        }
 
        product.setStockQuantity(product.getStockQuantity() - quantity);
        productRepository.save(product);
        // Lock released when transaction commits
    }
}

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableEntity {
 
    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;
 
    @LastModifiedDate
    @Column(name = "updated_at", nullable = false)
    private Instant updatedAt;
 
    @CreatedBy
    @Column(name = "created_by", length = 100, updatable = false)
    private String createdBy;
 
    @LastModifiedBy
    @Column(name = "updated_by", length = 100)
    private String updatedBy;
}

@Component("auditorAware")
public class AuditorAwareImpl implements AuditorAware<String> {
 
    @Override
    public Optional<String> getCurrentAuditor() {
        return Optional.ofNullable(
            SecurityContextHolder.getContext().getAuthentication()
        ).filter(Authentication::isAuthenticated)
         .filter(auth -> !"anonymousUser".equals(auth.getPrincipal()))
         .map(Authentication::getName)
         .or(() -> Optional.of("SYSTEM"));
    }
}

@Configuration
@EnableJpaAuditing(auditorAwareRef = "auditorAware")
public class JpaConfig {
    // auditorAwareRef must match the @Component name of AuditorAwareImpl
}

@Entity
@Table(name = "products")
/*
 * @SQLDelete: Override the SQL Hibernate uses for DELETE operations.
 * Instead of DELETE FROM products WHERE id = ?, Hibernate will:
 * UPDATE products SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?
 */
@SQLDelete(sql = "UPDATE products SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?")
/*
 * @Where: Automatically adds this WHERE clause to ALL queries for this entity.
 * Ensures soft-deleted records are invisible to the application.
 * SELECT * FROM products WHERE deleted_at IS NULL AND ...
 *
 * NOTE: Hibernate 6.x uses @SQLRestriction instead of @Where
 * @SQLRestriction("deleted_at IS NULL")
 */
@SQLRestriction("deleted_at IS NULL")
@Getter
@Setter
@NoArgsConstructor
public class Product extends AuditableEntity {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    // ... other fields ...
 
    /*
     * Null means not deleted.
     * Non-null means deleted and the timestamp when it was deleted.
     */
    @Column(name = "deleted_at")
    private Instant deletedAt;
 
    public boolean isDeleted() {
        return deletedAt != null;
    }
}

-- V10__add_soft_delete_to_products.sql
ALTER TABLE products
    ADD COLUMN deleted_at DATETIME(6) NULL AFTER updated_by;
 
-- Add an index to speed up the IS NULL filter
ALTER TABLE products
    ADD INDEX idx_products_deleted_at (deleted_at);

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    // This automatically applies @SQLRestriction: only returns non-deleted products
    Optional<Product> findById(Long id);
 
    // To explicitly find deleted products (bypass @SQLRestriction)
    @Query(value = "SELECT * FROM products WHERE deleted_at IS NOT NULL", nativeQuery = true)
    List<Product> findAllDeleted();
 
    // Restore a soft-deleted product
    @Modifying
    @Query("UPDATE Product p SET p.deletedAt = NULL WHERE p.id = :id")
    void restoreProduct(@Param("id") Long id);
 
    // Hard delete if needed (admin operation)
    @Modifying
    @Query(value = "DELETE FROM products WHERE id = :id", nativeQuery = true)
    void hardDeleteById(@Param("id") Long id);
 
    // Count including deleted (for analytics)
    @Query(value = "SELECT COUNT(*) FROM products", nativeQuery = true)
    long countIncludingDeleted();
}

@Service
@RequiredArgsConstructor
@Transactional
public class ProductService {
 
    private final ProductRepository productRepository;
 
    /*
     * This LOOKS like a delete, but Hibernate executes the @SQLDelete override.
     * The product's deleted_at column is set to current timestamp.
     * The product disappears from all queries because of @SQLRestriction.
     */
    public void deleteProduct(Long id) {
        Product product = productRepository.findById(id)
            .orElseThrow(() -> new ResourceNotFoundException("Product not found: " + id));
 
        productRepository.delete(product);
        // Hibernate runs: UPDATE products SET deleted_at = NOW() WHERE id = ?
        log.info("Soft-deleted product: {}", id);
    }
 
    @Transactional(readOnly = true)
    public List<ProductResponse> getDeletedProducts() {
        return productRepository.findAllDeleted()
            .stream()
            .map(productMapper::toResponse)
            .collect(Collectors.toList());
    }
}

@RestController
@RequestMapping("/api/v1/products")
@RequiredArgsConstructor
public class ProductController {
 
    private final ProductService productService;
 
    @GetMapping
    public ResponseEntity<PageResponse<ProductResponse>> getProducts(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "name") String sortBy,
            @RequestParam(defaultValue = "asc") String sortDir,
            @RequestParam(required = false) ProductStatus status) {
 
        // Validate sort field to prevent injection
        Set<String> allowedSortFields = Set.of("name", "price", "createdAt", "sku");
        if (!allowedSortFields.contains(sortBy)) {
            throw new ValidationException("Invalid sort field: " + sortBy);
        }
 
        Sort sort = "desc".equalsIgnoreCase(sortDir)
            ? Sort.by(sortBy).descending()
            : Sort.by(sortBy).ascending();
 
        Pageable pageable = PageRequest.of(page, Math.min(size, 100), sort); // Cap at 100
 
        Page<ProductResponse> products = productService.getProducts(status, pageable);
        return ResponseEntity.ok(PageResponse.of(products));
    }
}

// Custom PageResponse wrapper
@Value
public class PageResponse<T> {
    List<T> content;
    int page;
    int size;
    long totalElements;
    int totalPages;
    boolean first;
    boolean last;
 
    public static <T> PageResponse<T> of(Page<T> page) {
        return new PageResponse<>(
            page.getContent(),
            page.getNumber(),
            page.getSize(),
            page.getTotalElements(),
            page.getTotalPages(),
            page.isFirst(),
            page.isLast()
        );
    }
}

public interface OrderRepository extends JpaRepository<Order, Long> {
 
    // Keyset pagination: start from last seen ID
    @Query("""
        SELECT o FROM Order o
        WHERE o.customer.id = :customerId
          AND o.id > :lastSeenId
        ORDER BY o.id ASC
        """)
    List<Order> findNextPageByCustomer(
        @Param("customerId") Long customerId,
        @Param("lastSeenId") Long lastSeenId,
        Pageable pageable  // Use PageRequest.of(0, pageSize) - page is always 0
    );
}

spring:
  datasource:
    hikari:
      pool-name: EcommerceHikariPool-${spring.application.instance-id:main}
 
      # Core sizing
      maximum-pool-size: 10
      minimum-idle: 3
 
      # Timeouts
      connection-timeout: 30000 # 30s: throw if no connection available
      idle-timeout: 600000 # 10min: remove idle connections
      max-lifetime:
        1800000 # 30min: replace connections periodically
        # Must be < MySQL wait_timeout (28800s default)
      keepalive-time: 300000 # 5min: keepalive ping to prevent timeout
 
      # Validation
      connection-test-query: SELECT 1 # Validate connections before use
      validation-timeout: 5000 # 5s: max time to validate a connection
 
      # Connection leak detection
      leak-detection-threshold: 60000 # Warn if connection held > 1 minute
 
      # Data source properties
      data-source-properties:
        cachePrepStmts: true # Cache prepared statements
        prepStmtCacheSize: 250 # Cache up to 250 unique statements
        prepStmtCacheSqlLimit: 2048 # Max SQL length to cache
        useServerPrepStmts: true # Use server-side prepared statements
        rewriteBatchedStatements: true # Enable JDBC batch operations

management:
  metrics:
    tags:
      application: ${spring.application.name}
  endpoint:
    metrics:
      enabled: true

# application-prod.yml
spring:
  datasource:
    url: >
      jdbc:mysql://${RDS_ENDPOINT}:3306/${RDS_DB_NAME}
      ?useSSL=true
      &requireSSL=true
      &verifyServerCertificate=true
      &serverTimezone=UTC
      &characterEncoding=UTF-8
      &connectTimeout=5000
      &socketTimeout=30000
      &autoReconnect=false
      &failOverReadOnly=false
      &rewriteBatchedStatements=true
      &useConfigs=maxPerformance
    username: ${RDS_USERNAME}
    password: ${RDS_PASSWORD}

<!-- pom.xml -->
<dependency>
    <groupId>io.awspring.cloud</groupId>
    <artifactId>spring-cloud-aws-secrets-manager</artifactId>
    <version>3.0.4</version>
</dependency>

# application-prod.yml
spring:
  config:
    import: aws-secretsmanager:/ecommerce/prod/db-credentials
 
# The secret /ecommerce/prod/db-credentials in AWS Secrets Manager:
# {
#   "spring.datasource.username": "ecommerce_app",
#   "spring.datasource.password": "s3cur3P@ssw0rd"
# }

-- Key MySQL parameters for production JPA application
max_connections          = 200          -- Increased from default 151
innodb_buffer_pool_size  = 75% of RAM   -- Most critical InnoDB setting
slow_query_log           = ON           -- Log slow queries
long_query_time          = 1            -- Log queries taking > 1 second
log_queries_not_using_indexes = ON      -- Log queries with full table scans
wait_timeout             = 3600         -- 1 hour (HikariCP max-lifetime must be less)
interactive_timeout      = 3600
innodb_lock_wait_timeout = 50           -- Deadlock detection
character_set_server     = utf8mb4
collation_server         = utf8mb4_unicode_ci

package com.company.ecommerce.common.config;
 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;
 
public class RoutingDataSource extends AbstractRoutingDataSource {
 
    public static final String REPLICA = "replica";
    public static final String PRIMARY = "primary";
 
    @Override
    protected Object determineCurrentLookupKey() {
        /*
         * TransactionSynchronizationManager.isCurrentTransactionReadOnly():
         * Returns true when the current transaction was started with @Transactional(readOnly = true).
         * Use the replica for read-only transactions.
         * Use the primary for all write transactions.
         */
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return isReadOnly ? REPLICA : PRIMARY;
    }
}

@Configuration
public class DataSourceConfig {
 
    @Bean
    @ConfigurationProperties("spring.datasource.primary.hikari")
    public HikariDataSource primaryDataSource() {
        return new HikariDataSource();
    }
 
    @Bean
    @ConfigurationProperties("spring.datasource.replica.hikari")
    public HikariDataSource replicaDataSource() {
        return new HikariDataSource();
    }
 
    @Bean
    @Primary
    public DataSource routingDataSource(
            @Qualifier("primaryDataSource") DataSource primaryDataSource,
            @Qualifier("replicaDataSource") DataSource replicaDataSource) {
 
        RoutingDataSource routing = new RoutingDataSource();
 
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(RoutingDataSource.PRIMARY, primaryDataSource);
        targetDataSources.put(RoutingDataSource.REPLICA, replicaDataSource);
 
        routing.setTargetDataSources(targetDataSources);
        routing.setDefaultTargetDataSource(primaryDataSource);
 
        return routing;
    }
}

# application-prod.yml
spring:
  datasource:
    primary:
      hikari:
        jdbc-url: jdbc:mysql://${RDS_PRIMARY_ENDPOINT}:3306/${RDS_DB_NAME}?...
        username: ${RDS_USERNAME}
        password: ${RDS_PASSWORD}
        maximum-pool-size: 10
    replica:
      hikari:
        jdbc-url: jdbc:mysql://${RDS_REPLICA_ENDPOINT}:3306/${RDS_DB_NAME}?...
        username: ${RDS_USERNAME}
        password: ${RDS_PASSWORD}
        maximum-pool-size: 15 # More connections for read replica

// Usage: @Transactional(readOnly = true) automatically routes to replica
@Service
@RequiredArgsConstructor
public class ProductQueryService {
 
    private final ProductRepository productRepository;
 
    @Transactional(readOnly = true)  // Routes to REPLICA
    public Page<ProductResponse> getProducts(Pageable pageable) {
        return productRepository.findAll(pageable).map(productMapper::toResponse);
    }
 
    @Transactional  // Routes to PRIMARY (writes)
    public Product createProduct(CreateProductRequest request) {
        return productRepository.save(productMapper.toEntity(request));
    }
}

# Base config
spring:
  flyway:
    enabled: true
    validate-on-migrate: true  # Checksums verified before applying
 
# Test config (application-test.yml)
spring:
  flyway:
    enabled: true
    clean-on-validation-error: false  # Never enable in production
    baseline-on-migrate: false
 
# Test with TestContainers resets automatically

-- V15__add_loyalty_points_to_users.sql
-- SAFE: Adding nullable column does not lock the table
ALTER TABLE users ADD COLUMN loyalty_points INT NULL DEFAULT 0;

-- V16__add_email_verified_to_users.sql
-- SAFE: Default value fills existing rows immediately
ALTER TABLE users
    ADD COLUMN email_verified TINYINT(1) NOT NULL DEFAULT 0
    AFTER email;

-- WRONG: Rename in one step (application using old column name fails immediately)
-- ALTER TABLE users RENAME COLUMN phone TO phone_number;  -- NEVER DO THIS
 
-- CORRECT: Expand-and-contract migration (3 steps, 3 deployments)
 
-- Step 1: Add new column (V17)
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
UPDATE users SET phone_number = phone;  -- Backfill
 
-- Step 2: Deploy application reading from BOTH columns, writing to BOTH (V18 = nothing)
 
-- Step 3: Remove old column after confirming new column works (V18)
ALTER TABLE users DROP COLUMN phone;

-- MySQL 8.0 Online DDL with ALGORITHM=INPLACE (non-locking)
ALTER TABLE orders
    ADD INDEX idx_orders_new_field (new_field),
    ALGORITHM=INPLACE, LOCK=NONE;

package com.company.ecommerce;
 
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
 
@SpringBootTest
@Testcontainers
public abstract class BaseIntegrationTest {
 
    /*
     * @Container: TestContainers manages lifecycle.
     * static: One container shared across all test classes (faster).
     * The container starts once and is reused by all test methods.
     *
     * This uses the same MySQL version as production - exact same behavior.
     */
    @Container
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0.35")
        .withDatabaseName("ecommerce_test")
        .withUsername("test_user")
        .withPassword("test_pass")
        .withReuse(true);  // Reuse container across test runs (faster local dev)
 
    /*
     * @DynamicPropertySource: Override Spring properties at test time.
     * TestContainers assigns a random port to the container.
     * This method tells Spring to use that random port.
     */
    @DynamicPropertySource
    static void registerMySQLProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", mysql::getJdbcUrl);
        registry.add("spring.datasource.username", mysql::getUsername);
        registry.add("spring.datasource.password", mysql::getPassword);
    }
}

package com.company.ecommerce.domain.order;
 
import com.company.ecommerce.BaseIntegrationTest;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import static org.assertj.core.api.Assertions.assertThat;
 
@Transactional  // Rolls back after each test - keeps tests isolated
class OrderRepositoryTest extends BaseIntegrationTest {
 
    @Autowired
    private OrderRepository orderRepository;
 
    @Autowired
    private UserRepository userRepository;
 
    private User testUser;
 
    @BeforeEach
    void setUp() {
        testUser = userRepository.save(User.builder()
            .email("test@example.com")
            .username("testuser")
            .passwordHash("hashed")
            .build());
    }
 
    @Test
    void shouldSaveOrderWithItems() {
        Product product = createTestProduct();
        Order order = Order.builder()
            .orderNumber("ORD-001")
            .customer(testUser)
            .placedAt(Instant.now())
            .subtotal(new BigDecimal("99.99"))
            .taxAmount(new BigDecimal("18.00"))
            .totalAmount(new BigDecimal("117.99"))
            .build();
 
        OrderItem item = OrderItem.builder()
            .product(product)
            .quantity(1)
            .unitPrice(new BigDecimal("99.99"))
            .build();
 
        order.addItem(item);
        Order saved = orderRepository.save(order);
 
        assertThat(saved.getId()).isNotNull();
        assertThat(saved.getItems()).hasSize(1);
        assertThat(saved.getOrderNumber()).isEqualTo("ORD-001");
    }
 
    @Test
    void shouldFetchOrderWithItemsInSingleQuery() {
        // Arrange
        Order saved = createTestOrderWithItems(3);
 
        // Act - clear first-level cache to force DB read
        entityManager.clear();
 
        long queryCountBefore = countQueries();
        Order found = orderRepository.findByIdWithItemsAndProducts(saved.getId())
            .orElseThrow();
        long queryCountAfter = countQueries();
 
        // Assert - only 1 query should be issued
        assertThat(found.getItems()).hasSize(3);
        assertThat(queryCountAfter - queryCountBefore).isEqualTo(1);
    }
}

package com.company.ecommerce.service;
 
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import static org.assertj.core.api.Assertions.*;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.Mockito.*;
 
@ExtendWith(MockitoExtension.class)
class OrderServiceTest {
 
    @Mock
    private OrderRepository orderRepository;
 
    @Mock
    private ProductRepository productRepository;
 
    @InjectMocks
    private OrderService orderService;
 
    @Test
    void shouldThrowExceptionWhenInsufficientStock() {
        // Arrange
        Product product = Product.builder()
            .id(1L)
            .stockQuantity(5)
            .price(new BigDecimal("29.99"))
            .build();
 
        when(productRepository.findById(1L)).thenReturn(Optional.of(product));
 
        CreateOrderRequest.ItemRequest item = new CreateOrderRequest.ItemRequest(1L, 10);
        CreateOrderRequest request = new CreateOrderRequest(List.of(item));
 
        // Act & Assert
        assertThatThrownBy(() -> orderService.createOrder(1L, request))
            .isInstanceOf(InsufficientStockException.class)
            .hasMessageContaining("Not enough stock");
 
        verify(orderRepository, never()).save(any());
    }
}

# Only enable in staging/performance testing, not production (adds overhead)
spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 100 # Log slow queries > 100ms

// Log statistics on application shutdown for analysis
@Component
@RequiredArgsConstructor
public class HibernateStatisticsLogger {
 
    private final EntityManagerFactory entityManagerFactory;
 
    @EventListener(ContextClosedEvent.class)
    public void logStatistics() {
        Statistics stats = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
        log.info("Hibernate Statistics Summary:");
        log.info("  Query execution count: {}", stats.getQueryExecutionCount());
        log.info("  Entities loaded: {}", stats.getEntityLoadCount());
        log.info("  Collections loaded: {}", stats.getCollectionLoadCount());
        log.info("  Second-level cache hits: {}", stats.getSecondLevelCacheHitCount());
        log.info("  Transaction count: {}", stats.getTransactionCount());
    }
}

// Custom DataSource proxy to log slow queries
@Configuration
public class DataSourceConfig {
 
    @Bean
    @Primary
    public DataSource proxyDataSource(DataSourceProperties properties) {
        HikariDataSource dataSource = properties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
 
        // Wrap with ProxyDataSource for query logging/counting
        return ProxyDataSourceBuilder.create(dataSource)
            .name("EcommerceDataSource")
            .logSlowQueryBySlf4j(200, TimeUnit.MILLISECONDS)  // Log queries > 200ms
            .countQuery()
            .build();
    }
}

hikari:
  leak-detection-threshold: 30000 # Warn at 30 seconds (start investigation)
  maximum-pool-size: 20 # Increase pool size
  connection-timeout: 10000 # Fail fast instead of queuing

// Ensure all service methods complete quickly
// Never do external API calls inside a @Transactional method
@Transactional
public void createOrder(CreateOrderRequest request) {
    Order order = buildOrder(request);
    orderRepository.save(order);
    // WRONG: sendEmailNotification() here holds transaction open
}
 
// Publish event, let listener handle email OUTSIDE transaction
@Transactional
public Order createOrder(CreateOrderRequest request) {
    Order order = buildOrder(request);
    order = orderRepository.save(order);
    eventPublisher.publishEvent(new OrderCreatedEvent(order.getId())); // Async
    return order;
}

// BAD: Returning entity from service
@Service
public class OrderService {
    @Transactional(readOnly = true)
    public Order getOrder(Long id) {
        return orderRepository.findById(id).orElseThrow(...);
        // Transaction closes here. Jackson will try to serialize items later.
    }
}
 
// GOOD: Map to DTO inside transaction
@Service
public class OrderService {
    @Transactional(readOnly = true)
    public OrderResponse getOrder(Long id) {
        Order order = orderRepository.findByIdWithItemsAndProducts(id).orElseThrow(...);
        return orderMapper.toResponse(order);  // All fields accessed within transaction
    }
}

// Transaction A: locks Order 1, then tries to lock Product 5
// Transaction B: locks Product 5, then tries to lock Order 1
// => Deadlock
 
// Solution: Always acquire locks in a consistent order
// Use @Lock with consistent ordering in all queries
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT p FROM Product p WHERE p.id IN :ids ORDER BY p.id ASC")
List<Product> findAllByIdWithLock(@Param("ids") List<Long> ids);
// ORDER BY p.id ASC ensures all transactions acquire locks in the same order

-- Always write migrations defensively
-- V20__add_column_safe.sql
 
-- Check before adding (MySQL 8.0+)
ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS tracking_number VARCHAR(100) NULL;
 
-- For complex migrations, always have a rollback plan
-- Test with: SELECT count(*) FROM orders WHERE <condition that migration touches>

spring:
  jpa:
    hibernate:
      ddl-auto: validate # Fail fast if entities don't match schema