namespace OKRDAL.QueryBuilders;

public static class CheckInQB
{
    private const string SELECT_COLS = @"
                ci.CHECKINID        AS CheckInId,
                ci.GOALID           AS GoalId,
                g.NAME              AS GoalName,
                ci.EMPLOYEEID       AS EmployeeId,
                ISNULL(e.EMPLOYEENAME, '') AS EmployeeName,
                ci.MANAGERID        AS ManagerId,
                ISNULL(m.EMPLOYEENAME, '') AS ManagerName,
                ci.CHECKINDATE      AS CheckInDate,
                ci.SELFPROGRESS     AS SelfProgress,
                ci.MANAGERPROGRESS  AS ManagerProgress,
                ci.SELFNOTES        AS SelfNotes,
                ci.MANAGERNOTES     AS ManagerNotes,
                ci.TOPICS           AS Topics,
                ci.NEXTCHECKINDATE  AS NextCheckInDate,
                ci.STATUS           AS Status,
                ci.TENANTID         AS TenantId,
                ci.CREATEDBY        AS CreatedBy,
                ci.CREATEDON        AS CreatedOn,
                ci.MODIFIEDBY       AS ModifiedBy,
                ci.MODIFIEDON       AS ModifiedOn";

    public const string GET_CHECKIN = @"
        SELECT  " + SELECT_COLS + @"
        FROM    TCHECKIN ci
        JOIN    TGOAL g           ON g.GOALID     = ci.GOALID AND g.TENANTID = ci.TENANTID
        LEFT JOIN MEMPLOYEE e     ON e.EMPLOYEEID = ci.EMPLOYEEID
        LEFT JOIN MEMPLOYEE m     ON m.EMPLOYEEID = ci.MANAGERID
        WHERE   ci.CHECKINID = @CheckInId
        AND     ci.TENANTID  = @TenantId";

    public const string GET_CHECKIN_LIST = @"
        SELECT  " + SELECT_COLS + @"
        FROM    TCHECKIN ci
        JOIN    TGOAL g           ON g.GOALID     = ci.GOALID AND g.TENANTID = ci.TENANTID
        LEFT JOIN MEMPLOYEE e     ON e.EMPLOYEEID = ci.EMPLOYEEID
        LEFT JOIN MEMPLOYEE m     ON m.EMPLOYEEID = ci.MANAGERID
        WHERE   ci.TENANTID   = @TenantId
        AND     (@GoalId      = 0 OR ci.GOALID     = @GoalId)
        AND     (@EmployeeId  = 0 OR ci.EMPLOYEEID = @EmployeeId)
        ORDER BY ci.CHECKINDATE DESC";

    public const string GET_CHECKIN_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   TCHECKIN ci
        WHERE  ci.TENANTID  = @TenantId
        AND    (@GoalId     = 0 OR ci.GOALID     = @GoalId)
        AND    (@EmployeeId = 0 OR ci.EMPLOYEEID = @EmployeeId)";

    public const string INSERT_CHECKIN = @"
        INSERT INTO TCHECKIN
               (CHECKINID, GOALID, EMPLOYEEID, MANAGERID, CHECKINDATE,
                SELFPROGRESS, MANAGERPROGRESS, SELFNOTES, MANAGERNOTES,
                TOPICS, NEXTCHECKINDATE, STATUS,
                TENANTID, CREATEDBY, CREATEDON, MODIFIEDBY, MODIFIEDON)
        VALUES (@CheckInId, @GoalId, @EmployeeId, @ManagerId, @CheckInDate,
                @SelfProgress, 0, @SelfNotes, '',
                @Topics, @NextCheckInDate, 0,
                @TenantId, @CreatedBy, GETUTCDATE(), @CreatedBy, GETUTCDATE())";

    public const string SUBMIT_CHECKIN = @"
        UPDATE TCHECKIN
        SET    STATUS      = 1,
               MODIFIEDBY  = @ModifiedBy,
               MODIFIEDON  = GETUTCDATE()
        WHERE  CHECKINID = @CheckInId
        AND    TENANTID  = @TenantId
        AND    STATUS    = 0";

    public const string REVIEW_CHECKIN = @"
        UPDATE TCHECKIN
        SET    MANAGERPROGRESS = @ManagerProgress,
               MANAGERNOTES   = @ManagerNotes,
               STATUS         = 2,
               MODIFIEDBY     = @ModifiedBy,
               MODIFIEDON     = GETUTCDATE()
        WHERE  CHECKINID = @CheckInId
        AND    TENANTID  = @TenantId
        AND    STATUS    = 1";
}