package com.bankaudit.repository;

import com.bankaudit.dto.IssueDto;
import com.bankaudit.model.IssueReport;
import java.util.List;
import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.*;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface IssueReportRepository extends JpaRepository<IssueReport, Long> {

    // Basic Finders
    List<IssueReport> findByUserId(String userId);
    List<IssueReport> findByUnitIdIn(List<String> unitIds);
    Page<IssueReport> findByLegalEntityCode(String legalEntityCode, Pageable pageable);

    // Update status, issueType and actedOnByTeam
    @Modifying
    @Transactional
    @Query("UPDATE IssueReport i SET i.status = :status, i.issueType = :issueType, i.actedOnByTeam = :actedOnByTeam WHERE i.id = :id")
    void updateIssueStatusAndType(@Param("id") Long id,
                                  @Param("status") String status,
                                  @Param("issueType") String issueType,
                                  @Param("actedOnByTeam") java.util.Date actedOnByTeam);

    // Search across multiple fields
    @Query("SELECT new com.bankaudit.dto.IssueDto(" +
           "ir.id, ir.entityName, ir.legalEntityCode, ir.userId, ir.userEmail, ir.userRole, " +
           "ir.process, ir.issueDescription, ir.fileName, ir.filePath, ir.fileType, ir.status, " +
           "ir.createdAt, ir.updatedAt, ir.unitId, ir.financialYear, ir.issueType, ir.actedOnByTeam, " +
           "u.firstName, u.lastName) " +
           "FROM IssueReport ir LEFT JOIN User u ON u.userId = ir.userId and u.legalEntityCode = ir.legalEntityCode " +
           "WHERE LOWER(ir.legalEntityCode) = LOWER(:legalEntityCode)  AND (" +
           "LOWER(ir.entityName) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.status) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.issueDescription) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.userEmail) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.fileName) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.userId) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.unitId) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.userRole) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.process) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(ir.issueType) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(u.lastName) LIKE LOWER(CONCAT('%', :search, '%')) OR " +
           "LOWER(u.firstName) LIKE LOWER(CONCAT('%', :search, '%')))"  +
                  "ORDER BY CASE LOWER(ir.status) " +
                  "  WHEN 'open' THEN 1 " +
                  "  WHEN 'reopen' THEN 2 " +
                  "  WHEN 'fixed' THEN 3 " +
                  "  WHEN 'closed' THEN 4 " +
                  "  ELSE 5 END")
    Page<IssueDto> findByLegalEntityCodeAndSearchTerm(@Param("legalEntityCode") String legalEntityCode,
                                                      @Param("search") String search,
                                                      Pageable pageable);

    // === NEW METHODS ===

    // Fetch issue with associated files
    @Query("SELECT i FROM IssueReport i LEFT JOIN FETCH i.issueFiles WHERE i.id = :id")
    IssueReport findByIdWithFiles(@Param("id") Long id);

    // Find issues by file name (in app_issue_files)
    @Query("SELECT DISTINCT i FROM IssueReport i LEFT JOIN i.issueFiles f " +
           "WHERE LOWER(f.fileName) LIKE LOWER(CONCAT('%', :fileName, '%'))")
    List<IssueReport> findByFileNameContaining(@Param("fileName") String fileName);
}
