package com.erp.systemtool.repository;

import com.erp.systemtool.entity.OperationLog;
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 org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * Repository for OperationLog entity
 */
@Repository
public interface OperationLogRepository extends JpaRepository<OperationLog, Long>,
        JpaSpecificationExecutor<OperationLog> {

    Page<OperationLog> findByTenantIdOrderByCreatedAtDesc(Integer tenantId, Pageable pageable);

    Page<OperationLog> findByUserIdOrderByCreatedAtDesc(Integer userId, Pageable pageable);

    Page<OperationLog> findByModuleAndTenantIdOrderByCreatedAtDesc(String module, Integer tenantId, Pageable pageable);

    @Modifying
    @Query("DELETE FROM OperationLog l WHERE l.createdAt < :cutoffDate")
    int deleteOlderThan(@Param("cutoffDate") LocalDateTime cutoffDate);

    @Query("SELECT l.module, COUNT(l) as cnt FROM OperationLog l " +
           "WHERE l.tenantId = :tenantId AND l.createdAt BETWEEN :start AND :end " +
           "GROUP BY l.module ORDER BY cnt DESC")
    List<Object[]> countByModule(@Param("tenantId") Integer tenantId,
                                 @Param("start") LocalDateTime start,
                                 @Param("end") LocalDateTime end);

    @Query("SELECT l.userId, l.userName, COUNT(l) as cnt FROM OperationLog l " +
           "WHERE l.tenantId = :tenantId AND l.createdAt BETWEEN :start AND :end " +
           "GROUP BY l.userId, l.userName ORDER BY cnt DESC")
    List<Object[]> countByUser(@Param("tenantId") Integer tenantId,
                                @Param("start") LocalDateTime start,
                                @Param("end") LocalDateTime end);

    @Query(value = "SELECT DATE(created_at) as date, COUNT(*) as cnt FROM operation_logs " +
           "WHERE tenant_id = :tenantId AND created_at BETWEEN :start AND :end " +
           "GROUP BY DATE(created_at) ORDER BY date ASC",
           nativeQuery = true)
    List<Object[]> countByDay(@Param("tenantId") Integer tenantId,
                               @Param("start") LocalDateTime start,
                               @Param("end") LocalDateTime end);

    @Query(value = "SELECT module, COUNT(*) as total, " +
           "SUM(CASE WHEN response_code < 400 THEN 1 ELSE 0 END) as success, " +
           "SUM(CASE WHEN response_code >= 400 THEN 1 ELSE 0 END) as error " +
           "FROM operation_logs WHERE tenant_id = :tenantId AND created_at BETWEEN :start AND :end " +
           "GROUP BY module ORDER BY total DESC",
           nativeQuery = true)
    List<Object[]> moduleStats(@Param("tenantId") Integer tenantId,
                               @Param("start") LocalDateTime start,
                               @Param("end") LocalDateTime end);

    @Query(value = "SELECT AVG(execution_time), MIN(execution_time), MAX(execution_time) " +
           "FROM operation_logs WHERE tenant_id = :tenantId AND created_at BETWEEN :start AND :end",
           nativeQuery = true)
    Object[] responseTimeStats(@Param("tenantId") Integer tenantId,
                                @Param("start") LocalDateTime start,
                                @Param("end") LocalDateTime end);

    @Query("SELECT DISTINCT l.module FROM OperationLog l WHERE l.tenantId = :tenantId ORDER BY l.module")
    List<String> findDistinctModules(@Param("tenantId") Integer tenantId);

    @Query("SELECT DISTINCT l.action FROM OperationLog l WHERE l.tenantId = :tenantId ORDER BY l.action")
    List<String> findDistinctActions(@Param("tenantId") Integer tenantId);
}
