package com.itsol.quantrivanphong.report.timesheet.repository;

import com.itsol.quantrivanphong.model.Eproject;
import com.itsol.quantrivanphong.model.TimeSheet;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface TimeSheetRepository extends JpaRepository<TimeSheet, Integer> {
    TimeSheet findTimeSheetById(int id);

//    @Query("select ts from TimeSheet ts order by ts.createdAt desc")
//    List<TimeSheet> getAllTimeSheet();

    @Modifying
    @Query("update TimeSheet ts set ts.title = ?1, ts.content = ?2, ts.note = ?3 where ts.id = ?4")
    int updateTimeSheet(String title, String content, String note, int timeSheetId);

    @Modifying
    @Query("update TimeSheet ts set ts.checked = ?1 where ts.id = ?2")
    int updateCheck(String check, int timeSheetId);

//    @Modifying
//    @Query("update TimeSheet ts set ts.status = ?1 where ts.id = ?2")
//    int updateTimeSheetStatus(boolean status, int id);

    @Query("select ts from TimeSheet ts where ts.eproject = ?1")
    List<TimeSheet> showTimeSheetByEproject(Eproject eproject);

    TimeSheet findTimeSheetByEprojectAndId(Eproject eproject, int timeSheetId);


    //==================================================================================================================
    //Timesheet Of Hieunv

    @Query(value = "SELECT * FROM timesheet ts JOIN eproject ep ON ep.id = ts.eproject_id WHERE ep.project_id = ?1 AND ts.status = true and ts.created_at like ?2%", nativeQuery = true)
    List<TimeSheet> findAllTimesheetStatusTrue(int projectId, String currentDate);


    @Transactional
    @Modifying
    @Query(value = "INSERT INTO timesheet(eproject_id, status, created_at, updated_at, checked) VALUES (?1, ?2,current_date(), current_date(),?3)",nativeQuery = true)
    void updateLack(int eprojectId, boolean status, String checked);

    @Transactional
    @Modifying
    @Query(value = "update TimeSheet ts set ts.checked = ?1 where ts.id = ?2", nativeQuery = true)
    void updateTimesheetChecked(String checked, int id);

    @Query(value = "SELECT Count(DISTINCT ts.eproject_id) FROM timesheet ts JOIN eproject ep ON ep.id = ts.eproject_id WHERE ts.status = false AND ep.project_id = ?1 AND created_at >= ?2 AND created_at <= ?3",nativeQuery = true)
    int numberEmployeeLack(int ProjectId, String firstDate, String finalDate);

    @Query(value = "SELECT * FROM timesheet ts JOIN eproject ep ON ep.id = ts.eproject_id JOIN employee e ON e.id = ep.employee_id WHERE ts.status = false AND created_at >= ?1 AND created_at <= ?2 AND ep.project_id = ?3 AND e.id = ?4", nativeQuery = true)
    List<TimeSheet> listDateOfLack(String firstDate, String finalDate, int projectId, int employeeId);
}
