/*
**************************************************************************************
* Program Name  : DesignationRepository.java
* Author        : Kawade Swapnali
* Date          : Feb 12, 2026
* System Name   : gswbs
* Purpose       : To perform DB operations for Designation module.
**************************************************************************************
*/

package com.coder.designation.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.data.repository.query.Param;

import com.coder.designation.model.Designation;

@Repository
public interface DesignationRepository extends JpaRepository<Designation, String> {

    /* FIND LAST DESIGNATION ID FOR AUTO GENERATION */
    @Query("""
        SELECT d.designationId
        FROM Designation d
        WHERE d.designationId LIKE CONCAT(:prefix, '%')
        ORDER BY d.designationId DESC
    """)
    List<String> findTopByPrefix(@Param("prefix") String prefix,Pageable pageable);
    
    /* FIND THE MAX DESIGNATION ID FOR CREATE A NEW DESIGNATION ID AFTER DATA IMPORT TIME */ 
	@Query(value = "SELECT designation_id FROM designation_info WHERE designation_id LIKE :prefix ORDER BY designation_id DESC LIMIT 1", nativeQuery = true)
    String findMaxDesignationIdByPrefix(@Param("prefix") String prefix);

    /* FIND ALL DESIGNATIONS LOGIN-WISE */
    List<Designation> findAllByLoginId(String loginId);

    /* CHECK DUPLICATE DESIGNATION NAME LOGIN-WISE */
    @Query("""
        SELECT d FROM Designation d
        WHERE d.designationName = :designationName
        AND d.loginId = :loginId
    """)
    Optional<Designation> checkDuplicateRecords(
            @Param("designationName") String designationName,
            @Param("loginId") String loginId
    );
}